QUESTION 5

Question 5
DISTRIBUTOR (DNO, DNAME, DADDRESS, DPHONE)
ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)

CREATE TABLE

Distributor

create table distributor

(

      dno number(10) primary key,

      dname varchar2(20),

      daddress varchar2(30),

      dphone number(10)

);

Item

create table  item

(

      itemno number(10) primary key,

      itemname varchar2(10),

      color varchar2(10),

      weight number(10)

);

Dist_Item

create table dist_item

(

      dno number(10) references distributor(dno),

      itemno number(10) references item(itemno),

      qty number(10),

      primary key(dno,itemno)

);

INSERT

-- INSERT INTO DESTRIBUTOR

insert into distributor values(001,'geeta','mumbai',9995556663,'pankaj');

insert into distributor values(002,'jigee','pune',9995556663,'monica');

insert into distributor values(003,'parul','banglore',9995556663,'shweta');

insert into distributor values(004,'purohit','vijaywada',9956157815,'tyagi');

insert into distributor values(006,'pranaohsiva','madinagar',9545158844,'sharad');

insert into distributor values(005,'lohnghat','chandigarh',8545264588,'rahul');

insert into distributor values(007,'sheela','ambala',8881556478,'praveen');

insert into distributor values(008,'sumit','mehasana','','amar');

-- INSERT INTO ITEM

insert into item values(01,'keyboar','black',70);

insert into item values(02,'mouse','white',20);

insert into item values(03,'monitor-T','white',500);

insert into item values(05,'printer','yellow',750);

insert into item values(04,'monitor-C','green',1500);

insert into item values(06,'cabinate','black',200);

insert into item values(07,'Mboard','white',75);

insert into item values(11,'speaker','red',10);

insert into item values(09,'scanner','yellow',50);

insert into item values(08,'ups','yellow',1500);

insert into item values(10,'mboard-j','red',200);

-- INSERT INTO DIST_ITEM

insert into dist_item values(1,5,20);

insert into dist_item values(2,2,80);

insert into dist_item values(1,3,59);

insert into dist_item values(3,2,90);

insert into dist_item values(3,5,08);

insert into dist_item values(3,4,75);

insert into dist_item values(1,2,100);

      insert into dist_item values(2,2,24);

1. Add a column CONTACT_PERSON to the DISTRIBUTOR table with the not null constraint.
   
   alter table distributor add contact_person varchar2(10) not null;
   
2. Create a view LONDON_DIST on DIST_ITEM which contains only those records where distributors are from London. Make sure that this condition is checked for every DML against this view.
   
   create view pune_dist as select di.dno,di.itemno,di.qty from distributor d,dist_item di where d.daddress='pune' and d.dno=di.dno;
   
3. Display details of all those items that have never been supplied.
   
   select * from item where itemno not in(select itemno from dist_item group by itemno);
   
4. Delete all those items that have been supplied only once.
   
   delete from dist_item where itemno in(select itemno from dist_item group by itemno having count(itemno)=1)
   
5. List the names of distributors who have an ‘A’ and also a ‘B’ somewhere in their names.
   
   select dno, dname from distributor where dname like '%a%e%' or dname like '%e%a%';
   
6. Count the number of items having the same colour but not having weight between 20 and 100.
   
   select count(color),color from item where weight  <20 or  weight>100 group by color;
   
7. Display all those distributors who have supplied more than 1000 parts of the same type.
   
   select dno,itemno,sum(qty) from dist_item group by dno,itemno having sum(qty)>=100;
   
8. Display the average weight of items of same colour provided at least three items have that colour.
   
   select color, avg(weight) "AVG WEIGHT", count(color) "TOTAL NO" from item group by color having count(color)>=3;
   
9. Display the position where a distributor name has an ‘OH’ in its spelling somewhere after the fourth character.
   
   select dname,instr(dname,'oh') “POSITION” from distributor where instr(dname,'oh',5)<>0;
   
10. Count the number of distributors who have a phone connection and are supplying item number ‘I100’.
    
    select count(d.dno) from distributor d, dist_item di where d.dno=di.dno and di.itemno=5 and d.dphone is not NULL
11. Create a view on the tables in such a way that the view contains the distributor name, item name and the quantity supplied.
    
    select * from view1;
    
12. List the name, address and phone number of distributors who have the same three digits in their number as ‘Mr. Talkative’.
    
    select dname,dphone,daddress from distributor where substr(dphone,1,3) in
    (select substr(dphone,1,3) from distributor where dname='jigee') and dname!='jigee'
    
13. List all distributor names who supply either item I1 or I7 and the quantity supplied is more than 100.
    
    select d.dno , d.dname from distributor d where dno in(select dno from dist_item where (itemno=2 or itemno=3) and qty>=50);
    
14. Display the data of the top three heaviest ITEMS.
    
    select *  from
    
    (
       select itemname, row_number()  over (order by weight desc) rank  from item
                )where rank<=3;