SQL Select [64]

Query

Get SNAMEs with shipment QTY$>=800$.

(S)	S#	SNAME	STATUS	CITY		(SPJ)	S#	P#	J#	QTY
	S1	Smith	20	London			S1	P1	J1	200
	S2		10	Paris			S1	P1	J4	700
	S3	Blake	30	Paris			S2	P3	J1	400
	S4	Clark	20	London			S2	P3	J2	200
	S5		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

SQL Select [65]

Purpose

To unique, rename, project, restrict, times, join.

Query

Get SNAMEs with shipment QTY$>=800$.

Select

sql_select unique S.SNAME from S SPJ
where '(S.S#==SPJ.S#)&&(SPJ.QTY>=800)'

Select

sql_select [UNIQUE] [RENAME] [PROJECT] from [TIMES]
where [JOIN] && [RESTRICT]

Algebra

$\delta_{SNAME\rightarrow S.SNAME}\pi_{SNAME}\sigma_{QTY\geq 800}(S \Join SPJ)$

Shell SQL

sql_join S SPJ S# S# >temp.tbl
sql_restrict temp where 'QTY>=800' >temp1.tbl
sql_project temp1 SNAME >temp2.tbl
sql_rename temp2 SNAME S.SNAME >temp3.tbl
sql_unique temp3

RESULT           S.SNAME Adams Jones

ANSI SQL [66]

select distinct SNAME
from   S, SPJ
where  S.S_NO = SPJ.S_NO
and    SPJ.QTY >= 800

sql_select (Help) [67]

SQL: SELECT
   Usage  : sql_select [unique] [all] <fld1> <fld2>... from <tbl1> <tbl2>... 
            where ["<condition>"]
   Output : stdout
   Purpose: To project all or the specified fields.
   Purpose: To multiply (times) the specified tables.
   Purpose: To join the tables based on specified fields in the condition.
   Purpose: To restrict based on other fields in the condition.'
   Example: sql_select animals.animal rooms.room from animals rooms 
            where 'animals.animal==rooms.animal'
   Comment: Animal records are joined based on the condition.
   
   animals.tbl:              rooms.tbl            stdout:
   +------+---+-----+        +------+----+---+    +------+----+
   |animal|sex|color|        |animal|room|box|    |animal|room|
   +------+---+-----+        +------+----+---+    +------+----+
   |  21  | m |brown| select |  22  | R1 | B6| -> |  21  | R1 |
   |  22  | m |black|        |  21  | R1 | B7|    |  22  | R1 | 
   |  13  | m |brown|        +------+----+---+    +------+----+
   +------+---+-----+