GATE CSE 1991 | Question: 12-a

Question

Suppose a database consist of the following relations:

SUPPLIER (SCODE,SNAME,CITY).
PART (PCODE,PNAME,PDESC,CITY).
PROJECTS (PRCODE,PRNAME,PRCITY).
SPPR (SCODE,PCODE,PRCODE,QTY).

 Write SQL programs corresponding to the following queries:

1. Print PCODE values for parts supplied to any project in DEHLI by a supplier in DELHI.
2. Print all triples <CITY, PCODE, CITY> such that a supplier in first city supplies the specified part to a project in the second city, but do not print the triples in which the two CITY values are same.

Answer 1

I) More efficient query

Select A.pcode

from SPPR as A JOIN Projects as PR ON A.PRcode=PR.PRcode JOIN Supplier as SU ON SP.Scode=SU.Scode

where SP.city="Delhi" and PR.city="Delhi" ;

Answer 2

Using Natural join 

(i)    

select PCODE
from SUPPLIER S natural join SPPR SP join PROJECTS P using(PRCODE)
where P.PRCITY = "Delhi" and S.CITY = "Dehi"

(ii) 

select S.CITY, SP.PCODE, P.PRCITY
from SUPPLIER S natural join SPPR SP natural join PROJECTS P 
where P.PRCITY <> S.CITY