DBMS Practical Slips

6. Item_Supplier

create table item202
(
item_no number(5)primary key,
item_name varchar(20),
quantity number(5)
);

create table suppliers301
(
sup_no number(5)primary key,
sup_name varchar2(20),
address varchar2(20),
city varchar2(20),
phone_no number(10)
);

create table item_suppliers402
(
item_no number(5)references item202(item_no),
sup_no number(5)references suppliers301(sup_no),
rate number(5),
discount number(5)
);

insert into item202 values(101,'refrigerator',5);
insert into item202 values(102,'washing machine',2);
insert into item202 values(103,'refrigerator',1);
insert into item202 values(104,'washing machine',6);

insert into suppliers301 values(10,'hitesh','wadgaon','pune',9965863210);
insert into suppliers301 values(20,'ankit','narhe','nagpur',8795462135);
insert into suppliers301 values(30,'rohit','katraj','delhi',2132526398);
insert into suppliers301 values(40,'laxman','dhayari','mumbai',1123659545);

insert into item_suppliers402 values(101,10,45000,10);
insert into item_suppliers402 values(102,20,25000,5);
insert into item_suppliers402 values(103,30,62000,25);
insert into item_suppliers402 values(104,40,85000,20);

1.

2. select COUNT(suppliers301.sup_no) from item202,suppliers301, item_suppliers402
where item202.item_no= item_suppliers402.item_no and
suppliers301.sup_no=item_suppliers402.sup_no and
item202.item_name='refrigerator';

COUNT(SUPPLIERS301.SUP_NO)
---------------------------------------------
                                                          2


3. select suppliers301.sup_name from item202,suppliers301,item_suppliers402
where item202.item_no=item_suppliers402.item_no AND
suppliers301.sup_no=item_suppliers402.sup_no AND
item202.item_name='washing machine';

SUP_NAME
--------------------
ankit
laxman

4. select suppliers301.* from item202,suppliers301,item_suppliers402
where item202.item_no=item_suppliers402.item_no AND
suppliers301.sup_no=item_suppliers402.sup_no AND
item_suppliers402.discount=5;

         SUP_NO  SUP_NAME           ADDRESS               CITY
-------------------  -----------------------  -----------------------  -----------------
  PHONE_NO
-------------------
                      20  ankit                        narhe                        nagpur
  8795462135

5. select sup_name from item202,suppliers301, item_suppliers402
where item202.item_no=item_suppliers402.item_no AND
suppliers301.sup_no=item_suppliers402.sup_no AND
item202.quantity=6;

SUP_NAME
--------------------
laxman

5. select suppliers301.*  from item202,suppliers301,item_suppliers402
where item202.i_no=item_suppliers402.i_no AND
suppliers301.sup_no=item_suppliers402.sup_no AND
item202.quantity>6;