Question

Problem 3 [40 points, Formal Query Languages]| Consider the following relational schema to represent information about suppliers S, parts P, and projects J, S#, P#, and J# are primary keys for S, P, and J respectively. For a tuple of SPJ, a particular supplier supplies a specific part to a specific project in the specified quantity S(S#, SNAME, STATUS, CITY) P(P#, PNAME, COLOR, WEIGHT, CITY) J(J#, JNAME,CITY) SPJ(S# P# J#, QTY) Write queries for the following requests in specified languages. a) [10 points, SQL] List the supplier names who supply for project(s) in Greensboro. List in ascending order. b) [10 points, in SQL] List the parts (in PNAME) with their total quantity.

Answer 1

S#	SNAME	STATUS	CITY
1	Sabc	A	ABOHAR
2	Sdvc	B	GREENSBORO
3	Sgfd	C	GreenCity
4	Sdft	D	PinkCity

Table S (supplier)

Table P (parts)

P#	PNAME	COLOR	WEIGHT	CITY
1	Pasd	Pink	5	Greensboro
2	Psdg	Red	12	Eastern
3	ddh	Blue	2	Pinkcity
4	asd	black	10	citycity

Table J (projects)

J#	JNAME	CITY
1	Big	Greensboro
2	Great	Pinkcity
3	small	Citycity
4	Large	TriCity

Table SPJ

S#	P#	J#	QTY
1	1	1	20
2	2	1	30
3	3	2	20
1	4	3	10
3	1	4	10
2	4	4	20
4	3	3	40

NOTE: Above tables are with dummy values

a. Query for the supplier names who can supply for projects in Greensboro:

Select SNMAE from S join SPJ on S.s=SPJ.s and SPJ.p in (select j from j where CITY=’greenboro’

b. list the parts (PNAME) with their total quantity:

SELECT PNAME,SUM(QTY) FROM `p` join spj where p.p=spj.P GROUP by spj.p