Slip11
Consider
the following Entities and Relationships
Supplier
(sid, sname, addr)
Parts
(pid, pname, pdesc)
Relation
between Supplier and Parts is Many to Many with cost as descriptive attribute.
Constraint:
Primary key, cost should be > 0.
Solution:-
Create a Database in 3NF & write queries for
following.
•Display Supplier details from 'Mumbai' city.
SQL> Select * from supplier where addr='Mumbai';
•Update cost by 25 % for all parts supplied by
supplier ‘Mr. Pawar’.
SQL> Update
sup_parts set cost=cost+cost*0.25 where 'Ram'=(select sname from
supplier where sid=sup_parts.sid);
1 row updated.
SQL> select * from sup_parts;
•Display all parts supplied by each supplier.
SQL>Select
pname,sname from supplier,parts,
sup_parts where supplier.sid=sup_parts.sid and parts.pid= sup_parts.pid group
by sname,pname;
•Display details of parts which are supplied at
maximum price by each supplier.
SQL> Select
max(cost),sname,pname,pdesc from supplier,parts, sup_parts where
supplier.sid=sup_parts .sid and
parts.pid= sup_parts.pid group by sname,pname,pdesc ;
•Display all suppliers who supply part ‘wheel’ and
also display itscost.
SQL>Select
sname ,cost from supplier,parts,
sup_parts where supplier.sid=sup_parts.sid and parts.pid= sup_parts.pid and
pname=’Wheel’;
