retagged by
3,298 views
22 votes
22 votes

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.
retagged by

6 Answers

Best answer
22 votes
22 votes
  1. Print PCODE values for parts supplied to any project in DELHI by a supplier in DELHI 
Select SP.PCODE
From SPPR SP, Projects PR, Supplier SU
Where SP.PRcode = PR.PRcode
and SU.Scode = SP.Scode
and PR.PRcity = "DELHI"
and SU.city = "DELHI";
  1. Print all triples <CITTY, PCODE, CITY>
Select SU.city, SP.Pcode,PR.PRcity
from Supplier SU, Projects PR, SPPR SP
Where SU.Scode = SP.Scode
And PR.PRcode = SP.PRcode
And SU.city <> PR.PRcity;
edited by
14 votes
14 votes
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))$
7 votes
7 votes
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
4 votes
4 votes

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;

Related questions

52 votes
52 votes
2 answers
2
Kathleen asked Sep 12, 2014
6,337 views
Find the number of binary strings $w$ of length $2n$ with an equal number of $1's$ and $0's$ and the property that every prefix of $w$ has at least as many $0's$ as $1's....
27 votes
27 votes
1 answer
4
Kathleen asked Sep 12, 2014
3,811 views
Analyse the circuit in Fig below and complete the following table$${\begin{array}{|c|c|c|}\hline\textbf{a}& \textbf{b}& \bf{ Q_n} \\\hline0&0\\\ 0&1 \\ 1&0 \\ 1...