The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+17 votes
903 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).

 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.
asked in Databases by Veteran (52k points)
edited by | 903 views

5 Answers

+15 votes
Best answer

(i) --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";

(ii) --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;
answered by Boss (42.3k points)
edited by
+10 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))$
answered by Active (3.3k points)
+6 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
answered by Active (3.3k points)
+2
is this required to be compared ? "AND p.pcode=sp.pcode AND " i think its not needed
0

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

0

@Piyush Nikam 5 you can do that. Coming to the performance improvement we should do selection before join operation i.e first selecting all Delhi suppliers and Delhi projects from respective tables nad then do join operation.

+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;

answered by Boss (11.7k points)
0 votes

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" ;

answered by Active (1.8k points)

Related questions

+1 vote
0 answers
6
asked Sep 13, 2014 in CO & Architecture by Kathleen Veteran (52k points) | 208 views
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
49,541 questions
54,094 answers
187,240 comments
71,002 users