Next: Example Queries: Part B Up: Relational Databases Previous: Query-By-Example (QBE) Contents

Example Queries: Part A [70]

Problems: 6.6.1, 8.3.11

Query

Get SNAME who supply P2.

(S) S# SNAME STATUS CITY (SP) S# P# QTY

S1 $\fbox{Smith}$ 20 London S1 P1 300

S2 $\fbox{Jones}$ 10 Paris S1 P2 200

S3 $\fbox{Blake}$ 30 Paris S1 P3 400

S4 $\fbox{Clark}$ 20 London S1 P4 200

S5 Adams 30 Athens S1 P5 100

S1 P6 100

(P) P# PNAME COLOR WEIGHT CITY S2 P1 300

P1 Nut Red 12 London S2 P2 400

P2 Bolt Green 17 Paris S3 P2 200

P3 Screw Blue 17 Rome S4 P2 200

P4 Screw Red 14 London S4 P4 300

P5 Cam Blue 12 Paris S4 P5 400

P6 Cog Red 19 London

(S)	S#	SNAME	STATUS	CITY		(SP)	S#	P#	QTY
	S1	$\fbox{Smith}$	20	London			S1	P1	300
	S2	$\fbox{Jones}$	10	Paris			S1	P2	200
	S3	$\fbox{Blake}$	30	Paris			S1	P3	400
	S4	$\fbox{Clark}$	20	London			S1	P4	200
	S5	Adams	30	Athens			S1	P5	100
							S1	P6	100
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P1	300
	P1	Nut	Red	12	London		S2	P2	400
	P2	Bolt	Green	17	Paris		S3	P2	200
	P3	Screw	Blue	17	Rome		S4	P2	200
	P4	Screw	Red	14	London		S4	P4	300
	P5	Cam	Blue	12	Paris		S4	P5	400
	P6	Cog	Red	19	London

Example Query: 6.6.1, 8.3.11 (Cont.) [71]

Query

Get SNAME who supply P2.

Algebra

$\pi_{SNAME}\sigma_{P\char93 =''P2''}(S \Join SP)$

QBE

SP S# P# QTY

_ "P2"

S S# SNAME STATUS CITY

_ .P

Shell SQL

sql_restrict SP where 'P#=="P2"' >temp.tbl
sql_join temp S S# S# >temp1.tbl
sql_project temp1 SNAME >temp2.tbl
sql_unique temp2

Select

sql_select unique S.SNAME from S SP
where '(S.S#==SP.S#)&&(SP.P#=="P2")'

ANSI SQL

select distinct S.SNAME
from S, SP
where S.S_NO = SP.S_NO
and SP.P_NO = "P2"

RESULT           SNAME Blake Clark Jones Smith

Example Query: 6.6.2, 8.3.12 [72]

Query

Get SNAME who supply at least one RED part.

(S) S# SNAME STATUS CITY (SP) S# P# QTY

S1 $\fbox{Smith}$ 20 London S1 P1 300

S2 $\fbox{Jones}$ 10 Paris S1 P2 200

S3 Blake 30 Paris S1 P3 400

S4 $\fbox{Clark}$ 20 London S1 P4 200

S5 Adams 30 Athens S1 P5 100

S1 P6 100

(P) P# PNAME COLOR WEIGHT CITY S2 P1 300

P1 Nut Red 12 London S2 P2 400

P2 Bolt Green 17 Paris S3 P2 200

P3 Screw Blue 17 Rome S4 P2 200

P4 Screw Red 14 London S4 P4 300

P5 Cam Blue 12 Paris S4 P5 400

P6 Cog Red 19 London

(S)	S#	SNAME	STATUS	CITY		(SP)	S#	P#	QTY
	S1	$\fbox{Smith}$	20	London			S1	P1	300
	S2	$\fbox{Jones}$	10	Paris			S1	P2	200
	S3	Blake	30	Paris			S1	P3	400
	S4	$\fbox{Clark}$	20	London			S1	P4	200
	S5	Adams	30	Athens			S1	P5	100
							S1	P6	100
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P1	300
	P1	Nut	Red	12	London		S2	P2	400
	P2	Bolt	Green	17	Paris		S3	P2	200
	P3	Screw	Blue	17	Rome		S4	P2	200
	P4	Screw	Red	14	London		S4	P4	300
	P5	Cam	Blue	12	Paris		S4	P5	400
	P6	Cog	Red	19	London

Example Query: 6.6.2, 8.3.12 (Cont.) [73]

Query

Get SNAME who supply at least one RED part.

Algebra

$\pi_{SNAME}\sigma_{COLOR=''Red''}(S \Join SP \Join P)$ (ignore CITY)

QBE

SP S# P# QTY

_ _

S S# SNAME STATUS CITY

_ .P

P P# PNAME COLOR WEIGHT CITY

_ "Red"

Shell SQL

sql_restrict P where 'COLOR=="Red"' >temp.tbl
sql_join temp SP P# P# >temp1.tbl
sql_join temp1 S S# S# >temp2.tbl
sql_project temp2 SNAME >temp3.tbl
sql_unique temp3

Select

sql_select unique S.SNAME from S P SP
where '(S.S#==SP.S#)&&(P.P#==SP.P#)&&(P.COLOR=="Red")'

RESULT           SNAME Clark Jones Smith

ANSI SQL: 6.6.2, 8.3.12 (Cont.) [74]

               
select distinct S.SNAME
from   S, P, SP
where  S.S_NO = SP.S_NO
and    P.P_NO = SP.P_NO
and    P.COLOR = "Red"

Example Query: 6.6.3 [75]

Query

Get SNAME who supply ALL parts.

(S) S# SNAME STATUS CITY (SP) S# P# QTY

S1 $\fbox{Smith}$ 20 London S1 P1 300

S2 Jones 10 Paris S1 P2 200

S3 Blake 30 Paris S1 P3 400

S4 Clark 20 London S1 P4 200

S5 Adams 30 Athens S1 P5 100

S1 P6 100

(P) P# PNAME COLOR WEIGHT CITY S2 P1 300

P1 Nut Red 12 London S2 P2 400

P2 Bolt Green 17 Paris S3 P2 200

P3 Screw Blue 17 Rome S4 P2 200

P4 Screw Red 14 London S4 P4 300

P5 Cam Blue 12 Paris S4 P5 400

P6 Cog Red 19 London

(S)	S#	SNAME	STATUS	CITY		(SP)	S#	P#	QTY
	S1	$\fbox{Smith}$	20	London			S1	P1	300
	S2	Jones	10	Paris			S1	P2	200
	S3	Blake	30	Paris			S1	P3	400
	S4	Clark	20	London			S1	P4	200
	S5	Adams	30	Athens			S1	P5	100
							S1	P6	100
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P1	300
	P1	Nut	Red	12	London		S2	P2	400
	P2	Bolt	Green	17	Paris		S3	P2	200
	P3	Screw	Blue	17	Rome		S4	P2	200
	P4	Screw	Red	14	London		S4	P4	300
	P5	Cam	Blue	12	Paris		S4	P5	400
	P6	Cog	Red	19	London

Example Query: 6.6.3 (Cont.) [76]

Query

Get SNAME who supply ALL parts.

Algebra

$\pi_{SNAME}(S \Join (\pi_{S\char93 ,P\char93 }(SP) \div \pi_{P\char93 }(P)))$

Shell SQL

sql_project P P# >temp.tbl /*ALL parts*/
sql_project SP S# P# >temp1.tbl /*ALL pairs*/
sql_divide temp1 temp >temp2.tbl /*supply ALL parts*/
sql_join temp2 S S# S# >temp3.tbl
sql_project temp3 SNAME

RESULT           SNAME Smith

temp.tbl         P# P1 P2 P3 P4 P5 P6

temp2.tbl        S# S1

ANSI SQL: 6.6.3 (Cont.) [77]

select distinct S.SNAME
from   S
where (select count(SP.P_NO)
       from   SP
       where  SP.S_NO = S.S_NO
      ) =
      (select count(P.P_NO)
       from   P
      )

select distinct S.SNAME            /* those that supply all parts */
from   S
where  S_NO not in
     ( select S_NO                 /* those that don't supply some part */
       from   S, P                 /* all possible combinations */
       where  S_NO, P_NO not in
            ( select S_NO, P_NO
              from   SP            /* actual combinations */
            )
     )

Example Query: 6.6.4 [78]

Query

Get S# who supply at least those parts supplied by S2.

(S) S# SNAME STATUS CITY (SP) S# P# QTY

S1 Smith 20 London $\fbox{S1}$ P1 300

S2 Jones 10 Paris $\fbox{S1}$ P2 200

S3 Blake 30 Paris S1 P3 400

S4 Clark 20 London S1 P4 200

S5 Adams 30 Athens S1 P5 100

S1 P6 100

(P) P# PNAME COLOR WEIGHT CITY $\fbox{S2}$ P1 300

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

P2 Bolt Green 17 Paris S3 P2 200

P3 Screw Blue 17 Rome S4 P2 200

P4 Screw Red 14 London S4 P4 300

P5 Cam Blue 12 Paris S4 P5 400

P6 Cog Red 19 London

(S)	S#	SNAME	STATUS	CITY		(SP)	S#	P#	QTY
	S1	Smith	20	London			$\fbox{S1}$	P1	300
	S2	Jones	10	Paris			$\fbox{S1}$	P2	200
	S3	Blake	30	Paris			S1	P3	400
	S4	Clark	20	London			S1	P4	200
	S5	Adams	30	Athens			S1	P5	100
							S1	P6	100
(P)	P#	PNAME	COLOR	WEIGHT	CITY		$\fbox{S2}$	P1	300
	P1	Nut	Red	12	London		$\fbox{S2}$	P2	400
	P2	Bolt	Green	17	Paris		S3	P2	200
	P3	Screw	Blue	17	Rome		S4	P2	200
	P4	Screw	Red	14	London		S4	P4	300
	P5	Cam	Blue	12	Paris		S4	P5	400
	P6	Cog	Red	19	London

Example Query: 6.6.4 (Cont.) [79]

Query

Get S# who supply at least those parts supplied by S2.

Algebra

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

Shell SQL

sql_restrict SP where 'S#=="S2"' >temp.tbl
sql_project temp P# >temp1.tbl /*S2's parts*/
sql_project SP S# P# >temp2.tbl /*ALL pairs*/
sql_divide temp2 temp1

RESULT           S# S1 S2

temp1.tbl        P# P1 P2

ANSI SQL: 6.6.4 (Cont.) [80]

select S_NO                             /* do supply the parts   */
from   SP
where  S_NO not in
     ( select SP2.S_NO                  /* do not supply at part */
       from   SP as SP1, SP as SP2      /* cross product         */
       where  SP1.S_NO = "S2"
       and    SP2.S_NO, SP1.P_NO not in
            ( select S_NO, P_NO         /* actual combinations   */
              from   SP
            )
     )

Example Query: 6.6.5, 8.3.6 [81]

Query

Get SA#,SB# that are located in the SAME CITY.

(S) S# SNAME STATUS CITY (SP) S# P# QTY

$\fbox{S1}$ Smith 20 London S1 P1 300

S2 Jones 10 Paris S1 P2 200

S3 Blake 30 Paris S1 P3 400

$\fbox{S4}$ Clark 20 London S1 P4 200

S5 Adams 30 Athens S1 P5 100

S1 P6 100

(P) P# PNAME COLOR WEIGHT CITY S2 P1 300

P1 Nut Red 12 London S2 P2 400

P2 Bolt Green 17 Paris S3 P2 200

P3 Screw Blue 17 Rome S4 P2 200

P4 Screw Red 14 London S4 P4 300

P5 Cam Blue 12 Paris S4 P5 400

P6 Cog Red 19 London

(S)	S#	SNAME	STATUS	CITY		(SP)	S#	P#	QTY
	$\fbox{S1}$	Smith	20	London			S1	P1	300
	S2	Jones	10	Paris			S1	P2	200
	S3	Blake	30	Paris			S1	P3	400
	$\fbox{S4}$	Clark	20	London			S1	P4	200
	S5	Adams	30	Athens			S1	P5	100
							S1	P6	100
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P1	300
	P1	Nut	Red	12	London		S2	P2	400
	P2	Bolt	Green	17	Paris		S3	P2	200
	P3	Screw	Blue	17	Rome		S4	P2	200
	P4	Screw	Red	14	London		S4	P4	300
	P5	Cam	Blue	12	Paris		S4	P5	400
	P6	Cog	Red	19	London

Example Query: 6.6.5, 8.3.6 (Cont.) [82]

Query

Get SA#,SB# that are located in the SAME CITY.

Algebra

$\pi_{SA\char93 ,SB\char93 }\sigma_{SA\char93 <SB\char93 }$
$(\pi_{SA\char93 ,CITY}\delta_{S\char93 \rightarrow SA\char93 }(S)$
$\Join$
$\pi_{SB\char93 ,CITY}\delta_{S\char93 \rightarrow SB\char93 }(S))$

Shell SQL

sql_rename S S# SA# >temp.tbl
sql_rename S S# SB# >temp1.tbl
sql_join temp temp1 CITY CITY >temp2.tbl
sql_project temp2 SA# SB# >temp3.tbl
sql_restrict temp3 where 'SA# < SB#'

Select

sql_copy S SA; sql_copy S SB
sql_select SA.S# SB.S# from SA SB
where '(SA.CITY==SB.CITY)&&(SA.S#<SB.S#)'

RESULT           SA#  SB#  
                  S1   S4  
                  S2   S3

ANSI SQL: 6.6.5, 8.3.6 (Cont.) [83]

select SA.S_NO, SB.S_NO
from   S as SA, S as SB
where  SA.CITY = SB.CITY
and    SA.S_NO < SB.S_NO

Example Query: 6.6.6 [84]

Query

Get SNAME who do not supply P2.

(S) S# SNAME STATUS CITY (SP) S# P# QTY

S1 Smith 20 London S1 P1 300

S2 Jones 10 Paris S1 P2 200

S3 Blake 30 Paris S1 P3 400

S4 Clark 20 London S1 P4 200

S5 $\fbox{Adams}$ 30 Athens S1 P5 100

S1 P6 100

(P) P# PNAME COLOR WEIGHT CITY S2 P1 300

P1 Nut Red 12 London S2 P2 400

P2 Bolt Green 17 Paris S3 P2 200

P3 Screw Blue 17 Rome S4 P2 200

P4 Screw Red 14 London S4 P4 300

P5 Cam Blue 12 Paris S4 P5 400

P6 Cog Red 19 London

(S)	S#	SNAME	STATUS	CITY		(SP)	S#	P#	QTY
	S1	Smith	20	London			S1	P1	300
	S2	Jones	10	Paris			S1	P2	200
	S3	Blake	30	Paris			S1	P3	400
	S4	Clark	20	London			S1	P4	200
	S5	$\fbox{Adams}$	30	Athens			S1	P5	100
							S1	P6	100
(P)	P#	PNAME	COLOR	WEIGHT	CITY		S2	P1	300
	P1	Nut	Red	12	London		S2	P2	400
	P2	Bolt	Green	17	Paris		S3	P2	200
	P3	Screw	Blue	17	Rome		S4	P2	200
	P4	Screw	Red	14	London		S4	P4	300
	P5	Cam	Blue	12	Paris		S4	P5	400
	P6	Cog	Red	19	London

Example Query: 6.6.6 (Cont.) [85]

Query

Get SNAME who do not supply P2.

Algebra

$\pi_{SNAME}(S \Join (\pi_{S\char93 }(S) - \pi_{S\char93 }\sigma_{P\char93 =''P2''}(SP)))$

Shell SQL

sql_restrict SP where 'P#=="P2"' >temp.tbl
sql_project temp S# >temp1.tbl /*supply P2*/
sql_project S S# >temp2.tbl /*ALL suppliers*/
sql_minus temp2 temp1 >temp3.tbl /*does NOT supply*/
sql_join temp3 S S# S# >temp4.tbl
sql_project temp4 SNAME

RESULT           SNAME Adams

temp1.tbl        S# S1 S2 S3 S4

temp2.tbl        S# S1 S2 S3 S4 S5

temp3.tbl        S# S5

ANSI SQL: 6.6.6 (Cont.) [86]

select distinct S.SNAME
from   S
where  S.S_NO not in
     ( select SP.S_NO
       from SP
       where SP.P_NO = "P2"
     )

Example Query: 6.13, 8.11.13 [87]

Query

Get full details of 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 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.13, 8.11.13 (Cont.) [88]

Query

Get full details of all projects.

Algebra

QBE

J J# JNAME CITY

.P

Shell SQL

sql_restrict J

Select

sql_select all from J where

ANSI SQL

select * from J

RESULT           J#   JNAME   CITY 
                 J1  Sorter  Paris 
                 J2 Display   Rome 
                 J3     OCR Athens 
                 J4 Console Athens 
                 J5    RAID London 
                 J6     EDS   Oslo 
                 J7    Tape London

Example Query: 6.14, 8.11.14 [89]

Query

Get full details of 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 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.14, 8.11.14 (Cont.) [90]

Query

Get full details of all projects in LONDON.

Algebra

$\sigma_{CITY=''London''}(J)$

QBE

J J# JNAME CITY

.P "London"

Shell SQL

sql_restrict J where 'CITY=="London"'

Select

sql_select all from J where 'J.CITY=="London"'

ANSI SQL

select * from J where CITY = "London"

RESULT          J# JNAME   CITY 
                J5  RAID London 
                J7  Tape London

Example Query: 6.15, 8.11.15 [91]

Query

Get S# who supply J1.

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

S1 Smith 20 London $\fbox{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 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 $\fbox{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			$\fbox{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			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		$\fbox{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.15, 8.11.15 (Cont.) [92]

Query

Get S# who supply J1.

Algebra

$\pi_{S\char93 }\sigma_{J\char93 =''J1''}(SPJ)$

QBE

SPJ S# P# J# QTY

.P "J1"

Shell SQL

sql_restrict SPJ where 'J# == "J1"' >temp.tbl
sql_project temp S# >temp1.tbl
sql_unique temp1

Select

sql_select unique SPJ.S# from SPJ where 'SPJ.J# == "J1"

ANSI SQL

select distinct S_NO from SPJ where J_NO = "J1"

RESULT           S# S1 S2 S3

Example Query: 6.16, 8.11.16 [93]

Query

Get all shipments where QTY is 300 to 750.

(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 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	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.16, 8.11.16 (Cont.) [94]

Query

Get all shipments where QTY is 300 to 750.

Algebra

$\sigma_{QTY>=300\wedge QTY<=750}(SPJ)$

Shell SQL

sql_restrict SPJ where '(QTY=300)&&(QTY750)'

Select

sql_select all from SPJ
where '(SPJ.QTY300)&&(SPJ.QTY750)'

ANSI SQL

select * from SPJ where QTY >= 300 and QTY <= 750

RESULT           S1 P1 J4 700 
                 S2 P3 J1 400 
                 S2 P3 J4 500 
                 S2 P3 J5 600 
                 S2 P3 J6 400 
                 S3 P4 J2 500 
                 S4 P6 J3 300 
                 S4 P6 J7 300 
                 S5 P5 J5 500 
                 S5 P5 J4 400 
                 S5 P6 J4 500

Example Query: 6.17, 8.11.17 [95]

Query

Get all part-color/part-city combinations.

<

(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