search
Log In
21 votes
1.6k 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.
in Databases
edited by
1.6k views

6 Answers

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

edited by
0

MySQL Queries -

Part (1) - Delhi to Delhi

select pcode from Sppr where 
scode in (select scode from Supplier where city="Delhi") and
prcode in (select prcode from Project where prcity="Delhi");

Part (2) - Supplier and Project belong to two different cities

create view required as 
(select scode, pcode, prcode from Sppr where 
(scode, prcode) not in (select scode, prcode from Supplier join Project on city=prcity));

select city, pcode, prcity from 
(select city, prcode, pcode from required left join Supplier on required.scode = Supplier.scode) as T1 
left join Project on T1.prcode = Project.prcode;

 

0
Why is the β€˜Part’ table not used at all here?
12 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))$
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
3
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;

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

0 votes

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 

 

ago
edited ago by

Related questions

5 votes
2 answers
1
1k 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 algebraic solution to the following : Get SCODE values for suppliers who supply to both projects PR1 and PR2. Get PRCODE values for projects supplied by at least one supplier not in the same city.
asked Apr 18, 2016 in Databases jothee 1k views
43 votes
2 answers
2
3.2k 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.$
asked Sep 13, 2014 in Combinatory Kathleen 3.2k views
13 votes
5 answers
3
935 views
Show that the product of the least common multiple and the greatest common divisor of two positive integers $a$ and $b$ is $a\times b$.
asked Sep 13, 2014 in Set Theory & Algebra Kathleen 935 views
20 votes
4 answers
4
2.2k views
Consider the binary tree in the figure below: (a). What structure is represented by the binary tree?
asked Sep 13, 2014 in DS Kathleen 2.2k views
...