506 views

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).


1.  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.

Solution:

selected
i) $\pi _{pcode}(\sigma _{city==prcity=="Delhi"\;}(sppr\Join supplier\Join project))$

ii) $\pi _{city, \;pcode,\;prcity}(\sigma _{city!=prcity}(sppr\Join supplier\Join project))$
a)i) select pcode from sprrr where prcode IN (select prcode from project where city="delhi") AND
scode IN (select scode from supplier where city="delhi")

ii) select s.city,p.pcode,pr.city from supplier s, project pr, parts p, sprr sp
where s.city != pr.city AND sp.scode=s.scode AND p.pcode=sp.pcode AND sp.prcode=pr.prcode
is this required to be compared ? "AND p.pcode=sp.pcode AND " i think its not needed

a)i) select pcode from sprrr where prcode IN (select prcode from project where city="delhi") AND
scode IN (select scode from supplier where city="delhi") [Question] - Why can't we have multiple tables with join like second query here? I'm not thinking of performance here.

ii) select s.city,p.pcode,pr.city from supplier s, project pr, parts p, sprr sp
where s.city != pr.city AND sp.scode=s.scode AND p.pcode=sp.pcode AND sp.prcode=pr.prcode

i) Print PCODE values for parts supplied to any project in DEHLI by a supplier in DELHI.

SELECT PCODE FROM SPPR, PROJECTS, SUPPLIER WHERE PROJECTS.PRCITY="DELHI" AND SUPPLIER.CITY="DELHI" AND SPPR.PRCODE = PROJECTS.PRCODE AND SPPR.SCODE = SUPPLIER.SCODE;

ii) 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.

SELECT SUPPLIER.CITY, SPPR.PCODE, PROJECTS.PRCITY FROM SUPPLIER, SPPR, PROJECTS WHERE SUPPLIER.CITY <> PROJECTS.PRCITY AND SUPPLIER.SCODE = SPPR.SCODE AND PROJECTS.PRCODE = SPPR.PRCODE;