Example Queries: Part B [100]

Problems: 6.19, 8.11.19

Query

Get all S#,P#,J# that are NOT ALL co-located.

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
		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
		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
		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.19, 8.11.19 (Cont.) [101]

Query

Get all S#,P#,J# that are NOT ALL co-located.

Algebra

$\pi_{S\char93 ,P\char93 ,J\char93 }\sigma_{SCITY\neq PCITY\vee PCITY\neq JCITY\vee JCITY\neq SCITY}$
$(\delta_{CITY\rightarrow SCITY}(S) \times \delta_{CITY\rightarrow PCITY}(P) \times \delta_{CITY\rightarrow JCITY}(J))$

Shell SQL

sql_rename S CITY SCITY >temp.tbl
sql_rename P CITY PCITY >temp1.tbl
sql_rename J CITY JCITY >temp2.tbl
sql_times temp temp1 >temp3.tbl
sql_times temp3 temp2 >temp4.tbl
sql_restrict temp4 where '(SCITY!=PCITY)|| (PCITY!=JCITY)||(JCITY!=SCITY)' >temp5.tbl
sql_project temp5 S# P# J#

Select

sql_select S.S# P.P# J.J# from S P J
where '(S.CITY!=P.CITY)||(P.CITY!=J.CITY)||(J.CITY!=S.CITY)'

Example Query: 6.19, 8.11.19 (Cont.) [102]

ANSI SQL

select S_NO, P_NO, J_NO
from   S, P, J
where  S.CITY <> P.CITY
or     P.CITY <> J.CITY
or     J.CITY <> S.CITY

S# P# J# S1 P1 J1 S1 P1 J2 S1 P1 J3 S1 P1 J4 S1 P1 J6 S1 P2 J1 S1 P2 J2 S1 P2 J3 S1 P2 J4 S1 P2 
J5 S1 P2 J6 S1 P2 J7 S1 P3 J1 S1 P3 J2 S1 P3 J3 S1 P3 J4 S1 P3 J5 S1 P3 J6 S1 P3 J7 S1 P4 J1 S1 
P4 J2 S1 P4 J3 S1 P4 J4 S1 P4 J6 S1 P5 J1 S1 P5 J2 S1 P5 J3 S1 P5 J4 S1 P5 J5 S1 P5 J6 S1 P5 J7 
S1 P6 J1 S1 P6 J2 S1 P6 J3 S1 P6 J4 S1 P6 J6 S2 P1 J1 S2 P1 J2 S2 P1 J3 S2 P1 J4 S2 P1 J5 S2 P1 
J6 S2 P1 J7 S2 P2 J2 S2 P2 J3 S2 P2 J4 S2 P2 J5 S2 P2 J6 S2 P2 J7 S2 P3 J1 S2 P3 J2 S2 P3 J3 S2 
P3 J4 S2 P3 J5 S2 P3 J6 S2 P3 J7 S2 P4 J1 S2 P4 J2 S2 P4 J3 S2 P4 J4 S2 P4 J5 S2 P4 J6 S2 P4 J7 
S2 P5 J2 S2 P5 J3 S2 P5 J4 S2 P5 J5 S2 P5 J6 S2 P5 J7 S2 P6 J1 S2 P6 J2 S2 P6 J3 S2 P6 J4 S2 P6 
J5 S2 P6 J6 S2 P6 J7 S3 P1 J1 S3 P1 J2 S3 P1 J3 S3 P1 J4 S3 P1 J5 S3 P1 J6 S3 P1 J7 S3 P2 J2 S3 
P2 J3 S3 P2 J4 S3 P2 J5 S3 P2 J6 S3 P2 J7 S3 P3 J1 S3 P3 J2 S3 P3 J3 S3 P3 J4 S3 P3 J5 S3 P3 J6 
S3 P3 J7 S3 P4 J1 S3 P4 J2 S3 P4 J3 S3 P4 J4 S3 P4 J5 S3 P4 J6 S3 P4 J7 S3 P5 J2 S3 P5 J3 S3 P5 
J4 S3 P5 J5 S3 P5 J6 S3 P5 J7 S3 P6 J1 S3 P6 J2 S3 P6 J3 S3 P6 J4 S3 P6 J5 S3 P6 J6 S3 P6 J7 S4 
P1 J1 S4 P1 J2 S4 P1 J3 S4 P1 J4 S4 P1 J6 S4 P2 J1 S4 P2 J2 S4 P2 J3 S4 P2 J4 S4 P2 J5 S4 P2 J6 
S4 P2 J7 S4 P3 J1 S4 P3 J2 S4 P3 J3 S4 P3 J4 S4 P3 J5 S4 P3 J6 S4 P3 J7 S4 P4 J1 S4 P4 J2 S4 P4 
J3 S4 P4 J4 S4 P4 J6 S4 P5 J1 S4 P5 J2 S4 P5 J3 S4 P5 J4 S4 P5 J5 S4 P5 J6 S4 P5 J7 S4 P6 J1 S4 
P6 J2 S4 P6 J3 S4 P6 J4 S4 P6 J6 S5 P1 J1 S5 P1 J2 S5 P1 J3 S5 P1 J4 S5 P1 J5 S5 P1 J6 S5 P1 J7 
S5 P2 J1 S5 P2 J2 S5 P2 J3 S5 P2 J4 S5 P2 J5 S5 P2 J6 S5 P2 J7 S5 P3 J1 S5 P3 J2 S5 P3 J3 S5 P3 
J4 S5 P3 J5 S5 P3 J6 S5 P3 J7 S5 P4 J1 S5 P4 J2 S5 P4 J3 S5 P4 J4 S5 P4 J5 S5 P4 J6 S5 P4 J7 S5 
P5 J1 S5 P5 J2 S5 P5 J3 S5 P5 J4 S5 P5 J5 S5 P5 J6 S5 P5 J7 S5 P6 J1 S5 P6 J2 S5 P6 J3 S5 P6 J4 
S5 P6 J5 S5 P6 J6 S5 P6 J7

Example Query: 6.20, 8.11.20 [103]

Query

Get S#,P#,J# such that NO TWO are co-located.

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
		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
		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
		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.20, 8.11.20 (Cont.) [104]

Query

Get S#,P#,J# such that NO TWO are co-located.

Algebra

$\pi_{S\char93 ,P\char93 ,J\char93 }\sigma_{SCITY\neq PCITY\wedge PCITY\neq JCITY\wedge JCITY\neq SCITY}$
$(\delta_{CITY\rightarrow SCITY}(S) \times \delta_{CITY\rightarrow PCITY}(P) \times \delta_{CITY\rightarrow JCITY}(J))$

Shell SQL

sql_rename S CITY SCITY >temp.tbl
sql_rename P CITY PCITY >temp1.tbl
sql_rename J CITY JCITY >temp2.tbl
sql_times temp temp1 >temp3.tbl
sql_times temp3 temp2 >temp4.tbl
sql_restrict temp4 where '(SCITY!=PCITY)&&(PCITY!=JCITY)&&(JCITY!=SCITY)' >temp5.tbl
sql_project temp5 S# P# J#

Select

sql_select S.S# P.P# J.J# from S P J
where '(S.CITY!=P.CITY)&&(P.CITY!=J.CITY)&&(J.CITY!=S.CITY)'

Example Query: 6.20, 8.11.20 (Cont.) [105]

ANSI SQL

select S_NO, P_NO, J_NO
from   S, P, J
where  S.CITY <> P.CITY
and    P.CITY <> J.CITY
and    J.CITY <> S.CITY

RESULT              S# P# J# S1 P2 J2 S1 P2 J3 S1 P2 J4 S1 P2 J6 S1 P3 J1 S1 P3 J3 S1 P3 J4 
                    S1 P3 J6 S1 P5 J2 S1 P5 J3 S1 P5 J4 S1 P5 J6 S2 P1 J2 S2 P1 J3 S2 P1 J4 
                    S2 P1 J6 S2 P3 J3 S2 P3 J4 S2 P3 J5 S2 P3 J6 S2 P3 J7 S2 P4 J2 S2 P4 J3 
                    S2 P4 J4 S2 P4 J6 S2 P6 J2 S2 P6 J3 S2 P6 J4 S2 P6 J6 S3 P1 J2 S3 P1 J3 
                    S3 P1 J4 S3 P1 J6 S3 P3 J3 S3 P3 J4 S3 P3 J5 S3 P3 J6 S3 P3 J7 S3 P4 J2 
                    S3 P4 J3 S3 P4 J4 S3 P4 J6 S3 P6 J2 S3 P6 J3 S3 P6 J4 S3 P6 J6 S4 P2 J2 
                    S4 P2 J3 S4 P2 J4 S4 P2 J6 S4 P3 J1 S4 P3 J3 S4 P3 J4 S4 P3 J6 S4 P5 J2 
                    S4 P5 J3 S4 P5 J4 S4 P5 J6 S5 P1 J1 S5 P1 J2 S5 P1 J6 S5 P2 J2 S5 P2 J5 
                    S5 P2 J6 S5 P2 J7 S5 P3 J1 S5 P3 J5 S5 P3 J6 S5 P3 J7 S5 P4 J1 S5 P4 J2 
                    S5 P4 J6 S5 P5 J2 S5 P5 J5 S5 P5 J6 S5 P5 J7 S5 P6 J1 S5 P6 J2 S5 P6 J6

Example Query: 6.21, 8.11.21 [106]

Query

Get P# parts supplied by a supplier in LONDON.

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1		J1	200
	S2	Jones	10	Paris			S1		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		J3	300
							S4		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.21, 8.11.21 (Cont.) [107]

Query

Get P# supplied by a supplier in LONDON.

Algebra

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

QBE

SPJ	S#	P#	J#	QTY
	_$x$	.P

S	S#	SNAME	STATUS	CITY
	_$x$			"London"

Shell SQL

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

Select

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

RESULT           P# P1 P6

ANSI SQL: 6.21, 8.11.21 (Cont.) [108]

select distinct P_NO
from   S, SPJ
where  S.S_NO = SPJ.S_NO
and    S.CITY = "London"

Example Query: 6.22, 8.11.22 [109]

Query

Get P# for parts from a supplier in LONDON to a 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	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		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.22, 8.11.22 (Cont.) [110]

Query

Get P# for parts from a supplier in LONDON to a project in LONDON.

Algebra

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

QBE

SPJ	S#	P#	J#	QTY
	_$x$	.P	_$y$

J	J#	JNAME	CITY
	_$y$		"London"

S	S#	SNAME	STATUS	CITY
	_$x$			"London"

Shell SQL

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

Select

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

RESULT           P# P6

ANSI SQL: 6.22, 8.11.22 (Cont.) [111]

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

[I.E] Example Query: 6.23, 8.11.23 [112]

IEQ

Query

Get all pairs of city names such that a supplier in the first city supplies a project in the second city.

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2	Jones	10				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					S5	P3	J4	200
	J7	Tape	London				S5	P4	J4	800
							S5	P5	J4	400
							S5	P6	J4	500

Example Query: 6.23, 8.11.23 (Cont.) [113]

Query

Get all pairs of city names such that a supplier in the first city supplies a project in the second city.

Algebra

$\pi_{SCITY,JCITY}$ $(\delta_{CITY\rightarrow SCITY}(S) \Join SPJ \Join \delta_{CITY\rightarrow JCITY}(J))$

QBE

SPJ	S#	P#	J#	QTY
	_$x$		_$y$

J	J#	JNAME	CITY
	_$y$		.P

S	S#	SNAME	STATUS	CITY
	_$x$			.P

Shell SQL

sql_rename S CITY SCITY >temp.tbl
sql_rename J CITY JCITY >temp1.tbl
sql_join temp SPJ >temp2.tbl
sql_join temp2 temp1 J# J# >temp3.tbl
sql_project temp3 SCITY JCITY >temp4.tbl
sql_unique temp4

Example Query: 6.23, 8.11.23 (Cont.) [114]

Select

sql_select unique S.CITY J.CITY from S SPJ J
where '(S.S#==SPJ.S#)&&(J.J#==SPJ.J#)'

ANSI SQL

select distinct S.CITY as SCITY, J.CITY as JCITY
from   S, SPJ, J
where  S.S_NO = SPJ.S_NO
and    J.J_NO = SPJ.J_NO

RESULT           SCITY  JCITY
                 Paris   Oslo
                 Paris   Rome
                 Paris  Paris
                 Paris Athens
                 Paris London
                Athens   Rome
                Athens Athens
                Athens London
                London  Paris
                London Athens
                London London

Example Query: 6.24, 8.11.24 [115]

Query

Get P# for parts supplied to any project by a supplier in the same city as that project.

(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
	J3	OCR	Athens				S5	P5	J7	100
	J4	Console	Athens				S5	P6	J2	200
	J5	RAID	London				S5		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.24, 8.11.24 (Cont.) [116]

Query

Get P# for parts supplied to any project by a supplier in the same city as that project.

Algebra

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

QBE

SPJ	S#	P#	J#	QTY
	_$x$	.P	_$y$

J	J#	JNAME	CITY
	_$y$		_$z$

S	S#	SNAME	STATUS	CITY
	_$x$			_$z$

Shell SQL

sql_join S SPJ S# S# >temp.tbl
sql_join J SPJ J# J# >temp1.tbl
sql_join temp temp1 CITY CITY >temp2.tbl
sql_project temp2 P# >temp3.tbl
sql_unique temp3

Select

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

RESULT           P# P1 P2 P3 P4 P5 P6

ANSI SQL: 6.24, 8.11.24 (Cont.) [117]

select distinct SPJ.P_NO
from   S, J, SPJ
where  S.S_NO = SPJ.S_NO
and    J.J_NO = SPJ.J_NO
and    S.CITY = J.CITY

Example Query: 6.25, 8.11.25 [118]

Query

Get J# for projects supplied by at least one supplier NOT in the same city.

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1		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
	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.25, 8.11.25 (Cont.) [119]

Query

Get J# for projects supplied by at least one supplier NOT in the same city.

Algebra

$\pi_{J\char93 }\sigma_{SCITY\neq JCITY}$
$(\delta_{CITY\rightarrow SCITY}(S) \Join SPJ \Join \delta_{CITY\rightarrow JCITY}(J))$

Shell SQL

sql_rename S CITY SCITY >temp.tbl
sql_rename J CITY JCITY >temp1.tbl
sql_join temp SPJ S# S# >temp2.tbl
sql_join temp2 temp1 J# J# >temp3.tbl
sql_restrict temp3 where 'SCITY!=JCITY' >temp4.tbl
sql_project temp4 J# >temp5.tbl
sql_unique temp5

Select

sql_select unique J.J# from S J SPJ
where '(S.S#==SPJ.S#)&&(J.J#==SPJ.J#)&&(S.CITY!=J.CITY)'

RESULT           J# J1 J2 J3 J4 J5 J6 J7

ANSI SQL: 6.25, 8.11.25 (Cont.) [120]

select distinct J.J_NO
from   S, J, SPJ
where  S.S_NO = SPJ.S_NO
and    J.J_NO = SPJ.J_NO
and    S.CITY <> J.CITY

Example Query: 6.26, 8.11.26 [121]

Query

Get all pairs of P# such that some supplier supplies both the indicated 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</