DBMS Practical Slips

5. Wholsaler_Product

create table w201
(
wno number(10) primary key,
wname varchar(20),
address varchar2(20),
city varchar(10)
);

create table pr202
(
pno number(10) primary key ,
pname varchar2(20),
rate number(20)check(rate>0)
);

create table w_p203
(
wno number(10) references w201(wno),
pno number(10) references pr202(pno),
quantity number(10)
);

insert into w201 values(10,'rohit','dhayari','pune');
insert into w201 values(11,'ankit','kataraj','mumbai');
insert into w201 values(12,'hitesh','vadgaon','nasik');
insert into w201 values(13,'jay','narhe','up');
insert into w201 values(14,'mr khabia','dattanagar','pune');

insert into pr202 values(21,'monitor',5000);
insert into pr202 values(22,'keyboard',15000);
insert into pr202 values(23,'mouse',1000);
insert into pr202 values(24,'cpu',500);
insert into pr202 values(25,'keyboard',500);

insert into w_p203 values(10,21,7);
insert into w_p203 values(11,22,5);
insert into w_p203 values(12,23,4);
insert into w_p203 values(13,24,3);
insert into w_p203 values(14,25,2);


1. select wname from w201,pr202,w_p203
where w201.wno=w_p203.wno AND
pr202.pno=w_p203.pno AND
city='nasik' OR pr202.pname='mouse';

WNAME
--------------------
rohit
rohit
rohit
rohit
rohit
ankit
ankit
ankit
ankit
ankit
hitesh

WNAME
--------------------
hitesh
hitesh
hitesh
hitesh
jay
jay
jay
jay
jay
mr khabia
mr khabia

WNAME
--------------------
mr khabia
mr khabia
mr khabia

25 rows selected.

2. select COUNT (w201.wno) from w201,pr202,w_p203
where w201.wno=w_p203.wno AND
pr202.pno=w_p203.pno group by pname;

COUNT(W201.WNO)
---------------
              2
              1
              1
              1

3. select wname from w201,pr202,w_p203
where w201.wno=w_p203.wno AND
pr202.pno=w_p203.pno AND
pr202.pname='keyboard' OR
pr202.rate=(select MAX(rate)from pr202);

WNAME
--------------------
rohit
rohit
rohit
rohit
rohit
ankit
ankit
ankit
ankit
ankit
hitesh

WNAME
--------------------
hitesh
hitesh
hitesh
hitesh
jay
jay
jay
jay
jay
mr khabia
mr khabia

WNAME
--------------------
mr khabia
mr khabia
mr khabia
mr khabia

26 rows selected.

4. select SUM (w_p203.quantity) from w201,pr202,w_p203
where w201.wno=w_p203.wno AND
pr202.pno=w_p203.pno AND
w201.wname='mr khabia';

SUM(W_P203.QUANTITY)
--------------------
                   2

5.  UPDATE pr202 SET rate=1000
where pname='monitor';

1 row updated.