next up previous contents
Next: Example Queries: Part C Up: Relational Databases Previous: Example Queries: Part A   Contents

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
\fbox{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
\fbox{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
\fbox{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.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
\fbox{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
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
\fbox{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.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 \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 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 \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 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 \fbox{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.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 \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 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 \fbox{Oslo} 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 \fbox{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.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 \fbox{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 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 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 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 \fbox{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.26, 8.11.26 (Cont.) [122]



Query

Get all pairs of P# such that some supplier supplies both the indicated parts.



Algebra

$\pi_{XP\char93 ,YP\char93 }\sigma_{(XS\char93 =YS\char93 )\wedge(XP\char93 < YP\char93 )}$
$(\delta_{S\char93 \rightarrow XS\char93 ,P\char93 \rightarrow XP\char93 }(SPJ) ... ... \delta_{S\char93 \rightarrow YS\char93 ,P\char93 \rightarrow YP\char93 }(SPJ))$



Shell SQL

sql_rename SPJ P# XP# >temp.tbl
sql_rename SPJ P# YP# >temp1.tbl
sql_join temp temp1 S# S# >temp2.tbl
sql_restrict temp2 where 'XP# < YP#' >temp3.tbl
sql_project temp3 XP# YP# >temp.tbl
sql_unique temp



Select

sql_copy SPJ SPJX; sql_copy SPJ SPJY
sql_select unique SPJX.P# SPJY.P# from SPJX SPJY
where '(SPJX.S#==SPJY.S#)&&(SPJX.P#<SPJY.P#)' 

RESULT          XP#,YP#  P1,P2   P1,P3   P1,P4   P1,P5   P1,P6   P2,P3    P2,P4
                 P2,P5   P2,P6   P3,P4   P3,P5   P3,P6   P4,P5   P4,P6    P5,P6



Example Query: 6.26, 8.11.26 (Cont.) [123] 

select distinct SPJX.P_NO as PX, SPJY.P_NO as PY
from   SPJ as SPJX, SPJ as SPJY
where  SPJX.S_NO = SPJY.S_NO
and    SPJX.P_NO < SPJY.P_NO



Example Query: 6.27, 8.11.27 [124]



Query

Get the total count of projects supplied by S1.

(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 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.27, 8.11.27 (Cont.) [125]



Query

Get the total count of projects supplied by S1.



Algebra

$\pi_{CNT}\Sigma_{J\char93 }\pi_{J\char93 }\sigma_{S\char93 =''S1''}(SPJ)$



Shell SQL

sql_restrict SPJ where '(S#=="S1")' >temp.tbl
sql_project temp J# >temp1.tbl
sql_unique temp1 >temp2.tbl
sql_summarize temp2 J# >temp3.tbl
sql_project temp3 CNT



Select

sql_select unique SPJ.J# from SPJ
where '(SPJ.S#=="S1")' >temp.tbl
sql_summarize temp SPJ.J# >temp1.tbl
sql_project temp1 CNT



ANSI SQL

select count(*) as N from SPJ where S_NO = "S1" 

RESULT           CNT 2



Example Query: 6.28, 8.11.28 [126]



Query

Get the total quantity of P1 supplied by S1.



(S) S# SNAME STATUS CITY (SPJ) S# P# J# QTY
S1 Smith 20 London S1 P1 J1 \fbox{200}
S2 Jones 10 Paris S1 P1 J4 \fbox{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.28, 8.11.28 (Cont.) [127]



Query

Get the total quantity of P1 supplied by S1.



Algebra

$\pi_{SUM}\Sigma_{QTY}\sigma_{(S\char93 =''S1'')\wedge(P\char93 =''P1'')}(SPJ)$



Shell SQL

sql_restrict SPJ where '(S# == "S1")&&(P# == "P1")' >temp.tbl
sql_summarize temp QTY >temp1.tbl
sql_project temp1 SUM



Select

sql_select SPJ.QTY from SPJ
where '(SPJ.S# == "S1")&&(SPJ.P# == "P1")' >temp.tbl
sql_summarize temp SPJ.QTY >temp1.tbl
sql_project temp1 SUM



ANSI SQL

select sum(QTY) as X from SPJ
where S_NO = "S1" and P_NO = "P1" 

RESULT           SUM 900.0



Example Query: 6.31, 8.11.31 [128]



Query

Get JNAME for projects 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 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 \fbox{Sorter} Paris S5 P2 J4 100
J2 Display Rome S5 P5 J5 500
J3 OCR Athens S5 P5 J7 100
J4 \fbox{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.31, 8.11.31 (Cont.) [129]



Query

Get JNAME for projects supplied by S1.



Algebra

$\pi_{JNAME}\sigma_{S\char93 =''S1''}(J \Join SPJ)$



QBE

SPJ S# P# J# QTY
"S1" _$x$
J J# JNAME CITY
_$x$ .P



Shell SQL

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



Select

sql_select unique J.JNAME from J SPJ
where '(J.J# == SPJ.J#)&&(SPJ.S# == "S1")'



ANSI SQL

select distinct JNAME from J, SPJ
where J.J_NO = SPJ.J_NO and SPJ.S_NO = "S1" 

RESULT           JNAME Sorter Console

next up previous contents
Next: Example Queries: Part C Up: Relational Databases Previous: Example Queries: Part A   Contents
Ted Billard 2001-10-31