Next: Data Dictionary Up: Relational Databases Previous: Example Queries: Part B Contents

Example Queries: Part C [130]

Problems: 6.32, 8.11.32

Query

Get COLORs of parts supplied by S1.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut $\fbox{Red}$ 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

Example Query: 6.32, 8.11.32 (Cont.) [131]

Query

Get COLORs of parts supplied by S1.

Algebra

$\pi_{COLOR}\sigma_{S\char93 =''S1''}(P \Join SPJ)$

QBE

SPJ S# P# J# QTY

"S1" _

P P# PNAME COLOR WEIGHT CITY

_ .P

Shell SQL

sql_restrict SPJ where 'S# == "S1"' >temp.tbl
sql_join temp P P# P# >temp1.tbl
sql_project temp1 COLOR >temp2.tbl
sql_unique temp2

Select

sql_select unique P.COLOR from P SPJ
where '(P.P# == SPJ.P#)&&(SPJ.S# == "S1")'

ANSI SQL

select distinct COLOR from P, SPJ
where P.P_NO = SPJ.P_NO and SPJ.S_NO = "S1"

RESULT         COLOR Red

Example Query: 6.33, 8.11.33 [132]

Query

Get P# supplied to any project in LONDON.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 $\fbox{P3}$ J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 $\fbox{P3}$ J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 $\fbox{P6}$ J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 $\fbox{P5}$ J5 500

J3 OCR Athens S5 $\fbox{P5}$ J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

Example Query: 6.33, 8.11.33 (Cont.) [133]

Query

Get P# supplied to any project in LONDON.

Algebra

$\pi_{P\char93 }\sigma_{CITY=''London''}(J \Join SPJ)$

QBE

SPJ S# P# J# QTY

.P _

J J# JNAME CITY

_ "London"

Shell SQL

sql_restrict J where '(CITY=="London")' >temp.tbl
sql_join temp SPJ J# J# >temp1.tbl
sql_project temp1 P# >temp2.tbl
sql_unique temp2

Select

sql_select unique SPJ.P# from J SPJ
where '(J.J# == SPJ.J#)&&(J.CITY=="London")'

ANSI SQL

select distinct P_NO from J, SPJ
where J.J_NO = SPJ.J_NO and CITY = "London"

RESULT           P# P3 P5 P6

Example Query: 6.34, 8.11.34 [134]

Query

Get J# for projects using at least one part AVAILABLE from supplier S4.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 $\fbox{J3}$ 300

S4 P6 $\fbox{J7}$ 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 $\fbox{J2}$ 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 $\fbox{J4}$ 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2	Jones	10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	$\fbox{J3}$	300
							S4	P6	$\fbox{J7}$	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	P5	J5	500
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	$\fbox{J2}$	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	$\fbox{J4}$	500

Example Query: 6.34, 8.11.34 (Cont.) [135]

Query

Get J# for projects using at least one part AVAILABLE from supplier S4.

Algebra

$\pi_{J\char93 }(SPJ \Join \pi_{P\char93 }\sigma_{S\char93 =''S4''}(SPJ))$

QBE

SPJ S# P# J# QTY

"S4" _

SPJ S# P# J# QTY

_ .P

Shell SQL

sql_restrict SPJ where '(S# == "S4")' >temp.tbl
sql_project temp P# >temp1.tbl
sql_join temp1 SPJ P# P# >temp2.tbl
sql_project temp2 J# >temp3.tbl
sql_unique temp3

Select

sql_copy SPJ SPJX; sql_copy SPJ SPJY
sql_select unique SPJY.J# from SPJX SPJY
where '(SPJX.P# == SPJY.P#)&&(SPJX.S# == "S4")' ???

RESULT           J# J2 J3 J4 J7

ANSI SQL: 6.34, 8.11.34 (Cont.) [136]

select distinct SPJX.J_NO
from   SPJ as SPJX, SPJ as SPJY
where  SPJX.P_NO = SPJY.P_NO
and    (SPJX.S_NO = "S4" or SPJY.S_NO = "S4")

Example Query: 6.35, 8.11.35 [137]

Query

Get P# supplied by suppliers who also supply green parts.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 $\fbox{P2}$ J2 200

J1 Sorter Paris S5 $\fbox{P2}$ J4 100

J2 Display Rome S5 $\fbox{P5}$ J5 500

J3 OCR Athens S5 $\fbox{P5}$ J7 100

J4 Console Athens S5 $\fbox{P6}$ J2 200

J5 RAID London S5 $\fbox{P1}$ J4 100

J6 EDS Oslo S5 $\fbox{P3}$ J4 200

J7 Tape London S5 $\fbox{P4}$ J4 800

S5 $\fbox{P5}$ J4 400

S5 $\fbox{P6}$ J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2	Jones	10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	J3	300
							S4	P6	J7	300
(J)	J#	JNAME	CITY				S5	$\fbox{P2}$	J2	200
	J1	Sorter	Paris				S5	$\fbox{P2}$	J4	100
	J2	Display	Rome				S5	$\fbox{P5}$	J5	500
	J3	OCR	Athens				S5	$\fbox{P5}$	J7	100
	J4	Console	Athens				S5	$\fbox{P6}$	J2	200
	J5	RAID	London				S5	$\fbox{P1}$	J4	100
	J6	EDS	Oslo				S5	$\fbox{P3}$	J4	200
	J7	Tape	London				S5	$\fbox{P4}$	J4	800
							S5	$\fbox{P5}$	J4	400
							S5	$\fbox{P6}$	J4	500

Example Query: 6.35, 8.11.35 (Cont.) [138]

Query

Get P# supplied by suppliers who also supply green parts.

Algebra

$\pi_{P\char93 }(SPJ \Join \pi_{S\char93 }\sigma_{COLOR=''Green''}(SPJ \Join P))$

QBE

SPJ S# P# J# QTY

_ _

SPJ S# P# J# QTY

_ .P

P P# PNAME COLOR WEIGHT CITY

_ "Green"

Shell SQL

sql_restrict P where 'COLOR=="Green"' >temp.tbl
sql_join temp SPJ P# P# >temp1.tbl
sql_project temp1 S# >temp2.tbl
sql_join temp2 SPJ S# S# >temp3.tbl
sql_project temp3 P# >temp4.tbl; sql_unique temp4

Select

sql_select unique SPJ.S# from SPJ P
where '(SPJ.P#==P.P#)&&(P.COLOR=="Green")' >temp.tbl
sql_rename temp SPJ.S# S# >t.tbl
sql_select unique SPJ.P# from SPJ t where '(SPJ.S#==t.S#)'

RESULT           P# P1 P2 P3 P4 P5 P6

ANSI SQL: 6.35, 8.11.35 (Cont.) [139]

select distinct SPJY.P_NO
from   P, SPJ as SPJX, SPJ as SPJY
where  SPJX.P_NO = P.P_NO
and    P.COLOR = "Green"
and    SPJY.S_NO = SPJX.S_NO

Example Query: 6.36, 8.11.36 [140]

Query

Get S# for suppliers with a status lower than S1.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

$\fbox{S2}$ Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	$\fbox{S2}$	Jones	10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	J3	300
							S4	P6	J7	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	P5	J5	500
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.36, 8.11.36 (Cont.) [141]

Query

Get S# for suppliers with a status lower than S1.

Algebra

$\pi_{S\char93 }\sigma_{STATUS<STAT1}$
$(S \times \delta_{STATUS\rightarrow STAT1}\pi_{STATUS}\sigma_{S\char93 =''S1''}(S))$

Shell SQL

sql_restrict S where '(S# == "S1")' >temp.tbl
sql_project temp STATUS >temp1.tbl
sql_rename temp1 STATUS STAT1 >temp2.tbl
sql_times S temp2 >temp3.tbl
sql_restrict temp3 where '(STATUS < STAT1)' >temp4.tbl
sql_project temp4 S#

Select

sql_copy S SX; sql_copy S SY
sql_select SX.S# from SX SY
where '(SX.STATUS < SY.STATUS)&&(SY.S# == "S1")'

ANSI SQL

select SX.S_NO from S as SX, S as SY
where SX.STATUS < SY.STATUS and SY.S_NO = "S1"

RESULT           S# S2

Example Query: 6.37 [142]

Query

Get J# whose CITY is FIRST alphabetically.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

$\fbox{J3}$ OCR Athens S5 P5 J7 100

$\fbox{J4}$ Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

Example Query: 6.37 (Cont.) [143]

Query

Get J# whose CITY is FIRST alphabetically.

Algebra

$\pi_{J\char93 }(J \Join (\delta_{MIN\rightarrow CITY}\pi_{MIN}\Sigma_{CITY}(J)))$

Shell SQL

sql_summarize J CITY >temp.tbl
sql_project temp MIN >temp1.tbl
sql_join J temp1 CITY MIN >temp2.tbl
sql_project temp2 J#

ANSI SQL

select J_NO from J
where CITY = ( select min (CITY) from J )

RESULT           J# J3 J4

Example Query: 6.37a MINUS (Cont.) [144]

Algebra

$\pi_{J\char93 }(J\Join(\pi_{CITY}(J)-\pi_{XCITY}\sigma_{XCITY>YCITY}$
$(\delta_{CITY\rightarrow XCITY}(J) \times \delta_{CITY\rightarrow YCITY}(J)))$

Shell SQL

sql_rename J CITY XCITY >temp.tbl
sql_rename J CITY YCITY >temp1.tbl
sql_times temp temp1 >temp2.tbl
sql_restrict temp2 where 'XCITY > YCITY' >temp3.tbl
sql_project temp3 XCITY >temp4.tbl
sql_unique temp4 >temp5.tbl
sql_project J CITY >temp6.tbl
sql_unique temp6 >temp7.tbl
sql_minus temp7 temp5 >temp8.tbl
sql_join temp8 J CITY CITY >temp9.tbl
sql_project temp9 J#

select J_NO
from   J
where  J_NO not in
     ( select distinct J1.J_NO   /* has a city which is greater */
       from J as J1, J as J2     /* cross product */
       where J1.CITY > J2.CITY
     )

Example Query: 6.40 [145]

Query

Get J# for projects NOT supplied with any RED part by any LONDON supplier.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 $\fbox{J1}$ 200

S2 Jones 10 Paris S1 P1 $\fbox{J4}$ 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 $\fbox{J3}$ 300

S4 P6 $\fbox{J7}$ 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

$\fbox{J2}$ Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

$\fbox{J5}$ RAID London S5 P1 J4 100

$\fbox{J6}$ EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	$\fbox{J1}$	200
	S2	Jones	10	Paris			S1	P1	$\fbox{J4}$	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	$\fbox{J3}$	300
							S4	P6	$\fbox{J7}$	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	$\fbox{J2}$	Display	Rome				S5	P5	J5	500
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	J2	200
	$\fbox{J5}$	RAID	London				S5	P1	J4	100
	$\fbox{J6}$	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.40 (Cont.) [146]

Query

Get J# for projects NOT supplied with any RED part by any LONDON supplier.

Algebra

$\pi_{J\char93 }(J)-\pi_{J\char93 }$
$(\sigma_{COLOR=''Red''}(P) \Join SPJ \Join \sigma_{CITY=''London''}(S))$

Shell SQL

sql_restrict P where 'COLOR=="Red"' >temp.tbl
sql_restrict S where 'CITY=="London"' >temp1.tbl
sql_join temp SPJ P# P# >temp2.tbl
sql_join temp2 temp1 S# S# >temp3.tbl
sql_project temp3 J# >temp4.tbl
sql_unique temp4 >temp5.tbl
sql_project J J# >temp6.tbl
sql_minus temp6 temp5

RESULT           J# J2 J5 J6

ANSI SQL: 6.40 (Cont.) [147]

select J_NO
from   J
where  J_NO not in
     ( select J.J_NO
       from   SPJ, P, S
       where  SPJ.J_NO = J.J_NO
       and    SPJ.P_NO = P.P_NO
       and    SPJ.S_NO = S.S_NO
       and    P.COLOR  = "Red"
       and    S.CITY   = "London"
     )

Example Query: 6.41 [148]

Query

Get J# for projects supplied entirely by S2.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 $\fbox{J1}$ 200

S2 Jones 10 Paris S1 P1 $\fbox{J4}$ 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 $\fbox{J2}$ 500

P6 Cog Red 19 London S4 P6 $\fbox{J3}$ 300

S4 P6 $\fbox{J7}$ 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 $\fbox{J5}$ 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

$\fbox{J6}$ EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	$\fbox{J1}$	200
	S2	Jones	10	Paris			S1	P1	$\fbox{J4}$	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	$\fbox{J2}$	500
	P6	Cog	Red	19	London		S4	P6	$\fbox{J3}$	300
							S4	P6	$\fbox{J7}$	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	P5	$\fbox{J5}$	500
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	$\fbox{J6}$	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.41 (Cont.) [149]

Query

Get J# for projects supplied entirely by S2.

Algebra

$\pi_{J\char93 }(J)-\pi_{J\char93 }\sigma_{S\char93 \neq ''S2''}(SPJ)$

Shell SQL

sql_restrict SPJ where 'S# != "S2"' >temp.tbl
sql_project temp J# >temp1.tbl
sql_unique temp1 >temp2.tbl
sql_project J J# >temp3.tbl
sql_minus temp3 temp2

RESULT           J# J6

Example Query: 6.42 [150]

Query

Get P# supplied to ALL projects in LONDON.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 $\fbox{P3}$ J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 $\fbox{P3}$ J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 $\fbox{P5}$ J5 500

J3 OCR Athens S5 $\fbox{P5}$ J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2	Jones	10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	$\fbox{P3}$	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	$\fbox{P3}$	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	J3	300
							S4	P6	J7	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	$\fbox{P5}$	J5	500
	J3	OCR	Athens				S5	$\fbox{P5}$	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.42 (Cont.) [151]

Query

Get P# supplied to ALL projects in LONDON.

Algebra

$\pi_{P\char93 ,J\char93 }(SPJ)\div \pi_{J\char93 }\sigma_{CITY=''London''}(J)$

Shell SQL

sql_restrict J where 'CITY=="London"' >temp.tbl
sql_project temp J# >temp1.tbl
sql_project SPJ P# J# >temp2.tbl
sql_divide temp2 temp1

RESULT           P# P3 P5

Example Query: 6.43 [152]

Query

Get S# who supply SAME part to ALL projects.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris $\fbox{S2}$ P3 J1 400

S4 Clark 20 London $\fbox{S2}$ P3 J2 200

S5 Adams 30 Athens $\fbox{S2}$ P3 J3 200

$\fbox{S2}$ P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY $\fbox{S2}$ P3 J5 600

P1 Nut Red 12 London $\fbox{S2}$ P3 J6 400

P2 Bolt Green 17 Paris $\fbox{S2}$ P3 J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2	Jones	10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			$\fbox{S2}$	P3	J1	400
	S4	Clark	20	London			$\fbox{S2}$	P3	J2	200
	S5	Adams	30	Athens			$\fbox{S2}$	P3	J3	200
							$\fbox{S2}$	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		$\fbox{S2}$	P3	J5	600
	P1	Nut	Red	12	London		$\fbox{S2}$	P3	J6	400
	P2	Bolt	Green	17	Paris		$\fbox{S2}$	P3	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	J3	300
							S4	P6	J7	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	P5	J5	500
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.43 (Cont.) [153]

Query

Get S# who supply SAME part to ALL projects.

Algebra

$\pi_{S\char93 }(\pi_{S\char93 ,P\char93 ,J\char93 }(SPJ) \div \pi_{J\char93 }(J))$

Shell SQL

sql_project J J# >temp.tbl
sql_project SPJ S# P# J# >temp1.tbl
sql_divide temp1 temp >temp2.tbl
sql_project temp2 S#

RESULT           S# S2

Query

What is the SNAME?

Shell SQL

sql_join temp2 S S# S# >temp.tbl
sql_project temp SNAME

RESULT           SNAME Jones

Example Query: 6.44 [154]

Query

Get J# for projects supplied with at least ALL parts AVAILABLE from S2.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 $\fbox{J2}$ 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 $\fbox{J4}$ 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 $\fbox{J5}$ 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 $\fbox{J7}$ 800

P3 Screw Blue 17 Rome S2 P5 $\fbox{J2}$ 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 $\fbox{J5}$ 500

J3 OCR Athens S5 P5 $\fbox{J7}$ 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 $\fbox{J4}$ 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2	Jones	10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	$\fbox{J2}$	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	$\fbox{J4}$	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	$\fbox{J5}$	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	$\fbox{J7}$	800
	P3	Screw	Blue	17	Rome		S2	P5	$\fbox{J2}$	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	J3	300
							S4	P6	J7	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	P5	$\fbox{J5}$	500
	J3	OCR	Athens				S5	P5	$\fbox{J7}$	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	$\fbox{J4}$	400
							S5	P6	J4	500

Example Query: 6.44 (Cont.) [155]

Query

Get J# for projects supplied with at least ALL parts AVAILABLE from S2.

Algebra

$\pi_{J\char93 ,P\char93 }(SPJ) \div \pi_{P\char93 }\sigma_{S\char93 =''S2''}(SPJ)$

Shell SQL

sql_restrict SPJ where 'S# == "S2"' >temp.tbl
sql_project temp P# >temp1.tbl
sql_unique temp1 >temp2.tbl
sql_project SPJ J# P# >temp3.tbl
sql_divide temp3 temp2

RESULT           J# J2 J4 J5 J7

temp2.tbl        P# P3 P5

Example Query: 6.45 [156]

Query

Get ALL cities.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 $\fbox{London}$ S1 P1 J1 200

S2 Jones 10 $\fbox{Paris}$ S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 $\fbox{Athens}$ S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 P3 J7 800

P3 Screw Blue 17 $\fbox{Rome}$ S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS $\fbox{Oslo}$ S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	$\fbox{London}$			S1	P1	J1	200
	S2	Jones	10	$\fbox{Paris}$			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	$\fbox{Athens}$			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P3	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	P3	J7	800
	P3	Screw	Blue	17	$\fbox{Rome}$		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	P6	J3	300
							S4	P6	J7	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	P5	J5	500
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	$\fbox{Oslo}$				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.45 (Cont.) [157]

Query

Get ALL cities.

Algebra

$\pi_{CITY}(S) \bigcup \pi_{CITY}(P) \bigcup \pi_{CITY}(J)$

Shell SQL

sql_project S CITY >temp.tbl
sql_project P CITY >temp1.tbl
sql_project J CITY >temp2.tbl
sql_union temp temp1 >temp3.tbl
sql_union temp2 temp3 >temp4.tbl
sql_unique temp4 >temp5.tbl

RESULT           CITY Oslo Rome Paris Athens London

Query

How many cities are there?

Shell SQL

sql_summarize temp5 CITY >temp6.tbl
sql_project temp6 CNT

                 CNT 5

ANSI SQL: 6.45 (Cont.) [158]

select S.CITY
from   S
union
select P.CITY
from   P
union
select J.CITY
from   J

Example Query: 6.46 [159]

Query

Get P# for parts that are supplied EITHER by a LONDON supplier or to a LONDON project.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 $\fbox{P1}$ J1 200

S2 Jones 10 Paris S1 $\fbox{P1}$ J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 $\fbox{P3}$ J5 600

P1 Nut Red 12 London S2 P3 J6 400

P2 Bolt Green 17 Paris S2 $\fbox{P3}$ J7 800

P3 Screw Blue 17 Rome S2 P5 J2 100

P4 Screw Red 14 London S3 P3 J1 200

P5 Cam Blue 12 Paris S3 P4 J2 500

P6 Cog Red 19 London S4 $\fbox{P6}$ J3 300

S4 $\fbox{P6}$ J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 $\fbox{P5}$ J5 500

J3 OCR Athens S5 $\fbox{P5}$ J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	$\fbox{P1}$	J1	200
	S2	Jones	10	Paris			S1	$\fbox{P1}$	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5	Adams	30	Athens			S2	P3	J3	200
							S2	P3	J4	500
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	$\fbox{P3}$	J5	600
	P1	Nut	Red	12	London		S2	P3	J6	400
	P2	Bolt	Green	17	Paris		S2	$\fbox{P3}$	J7	800
	P3	Screw	Blue	17	Rome		S2	P5	J2	100
	P4	Screw	Red	14	London		S3	P3	J1	200
	P5	Cam	Blue	12	Paris		S3	P4	J2	500
	P6	Cog	Red	19	London		S4	$\fbox{P6}$	J3	300
							S4	$\fbox{P6}$	J7	300
(J)	J#	JNAME	CITY				S5	P2	J2	200
	J1	Sorter	Paris				S5	P2	J4	100
	J2	Display	Rome				S5	$\fbox{P5}$	J5	500
	J3	OCR	Athens				S5	$\fbox{P5}$	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5	P1	J4	100
	J6	EDS	Oslo				S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.46 (Cont.) [160]

Query

Get P# for parts that are supplied EITHER by a LONDON supplier or to a LONDON project.

Algebra

$\pi_{P\char93 }\sigma_{CITY=''London''}(S \Join SPJ) \bigcup$
$\pi_{P\char93 }\sigma_{CITY=''London''}(J \Join SPJ)$

Shell SQL

sql_restrict S where 'CITY=="London"' >temp.tbl
sql_join SPJ temp S# S# >temp1.tbl
sql_project temp1 P# >temp2.tbl
sql_restrict J where 'CITY=="London"' >temp3.tbl
sql_join SPJ temp3 J# J# >temp4.tbl
sql_project temp4 P# >temp5.tbl
sql_union temp2 temp5 >temp6.tbl
sql_unique temp6

RESULT           P# P1 P3 P5 P6

Example Query: 6.47 [161]

Query

Get S#,P# such that S# does NOT supply P#.

(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY

S1 Smith 20 London S1 P1 J1 200

S2 Jones 10 Paris S1 P1 J4 700

S3 Blake 30 Paris S2 P3 J1 400

S4 Clark 20 London S2 P3 J2 200

S5 Adams 30 Athens S2 P3 J3 200

S2 P3 J4 500

(P) P# PNAME COLOR WEIGHT CITY S2 P3 J5 600

P1 Nut Red 12 London S2 P3 J6 400

$\fbox{P2}$ Bolt Green 17 Paris S2 P3 J7 800

$\fbox{P3}$ Screw Blue 17 Rome S2 P5 J2 100

$\fbox{P4}$ Screw Red 14 London S3 P3 J1 200

$\fbox{P5}$ Cam Blue 12 Paris S3 P4 J2 500

$\fbox{P6}$ Cog Red 19 London S4 P6 J3 300

S4 P6 J7 300

(J) J# JNAME CITY S5 P2 J2 200

J1 Sorter Paris S5 P2 J4 100

J2 Display Rome S5 P5 J5 500

J3 OCR Athens S5 P5 J7 100

J4 Console Athens S5 P6 J2 200

J5 RAID London S5 P1 J4 100

J6 EDS Oslo S5 P3 J4 200

J7 Tape London S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

Example Query: 6.47 (Cont.) [162]

Query

Get S#,P# such that S# does NOT supply P#.

Algebra

$\pi_{S\char93 ,P\char93 }(S \times P) - \pi_{S\char93 ,P\char93 }(SPJ)$

Shell SQL

sql_times S P >temp.tbl
sql_project temp S# P# >temp1.tbl
sql_project SPJ S# P# >temp2.tbl
sql_minus temp1 temp2

RESULT           S# P# S1 P2 S1 P3 S1 P4 S1 P5 S1 P6 S2 P1 S2 P2 S2 P4 S2 P6 
                 S3 P1 S3 P2 S3 P5 S3 P6 S4 P1 S4 P2 S4 P3 S4 P4 S4 P5

Next: Data Dictionary Up: Relational Databases Previous: Example Queries: Part B Contents

Ted Billard 2001-10-31