Qualified Names
SELECT S.S#, S.STATUS
FROM S
WHERE S.CITY = 'Paris' ;
SIMPLE QUERIES
Simple retrieval (projection)
SELECT P#
FROM SP ;
Eliminate duplicates
SELECT DISTINCT P#
FROM SP ;
Computed values
SELECT P.P#, 'Weight in grams =', P.WEIGHT * 234
FROM P ;
Select all columns
SELECT *
FROM S ;
SELECT S.*
FROM S ;
SELECT S.S#, S.SNAME, S.STATUS, S.CITY
FROM S ;
QUALIFIED RETRIEVAL
SELECT S#
FROM S
WHERE CITY = 'Paris'
AND STATUS > 20 ;
The Condition is a logical expression
involving AND, OR, NOT, =, <>, >, >=, <, <=
Retrieval with ordering
SELECT S#, STATUS
FROM S
WHERE CITY = 'Paris'
ORDER BY STATUS DESC ;
Select all columns with condition.
(Restriction)
SELECT *
FROM S
WHERE CITY = 'Paris' ;
JOIN QUERIES
Join operation is the most powerful feature
of the relational system.
Use "join" to combine tables.
SIMPLE EQUIJOIN
SELECT S.*, P.*
FROM S, P
WHERE S.CITY = P.CITY ;
S TABLE
S# SNAME STAT CITY
P TABLE
P# PNAME COLOR WGT CITY
RESULT TABLE
S# SNAME STAT S.CITY P# PNAME COLOR WGT P.CITY
S.CITY=P.CITY is called the "join condition"
If one of the two IDENTICAL columns is
eliminated, it is a "natural join".
SELECT S#,SNAME,STAT,S.CITY
P#,PNAME,COLOR,WGT
FROM S, P
WHERE S.CITY = P.CITY ;
RESULT TABLE
S# SNAME STAT S.CITY P# PNAME COLOR WGT
GENERALIZED JOINS
Greater-than Join
SELECT S.*, P.*
FROM S, P
WHERE S.CITY > P.CITY ;
Additional conditions
SELECT S.*, P.*
FROM S, P
WHERE S.CITY = P.CITY
AND S.STATUS <> 20 ;
Selected fields
SELECT S.S#, P.P#
FROM S, P
WHERE S.CITY = P.CITY ;
Multiple tables
SELECT DISTINCT S.CITY, P.CITY
FROM S, SP, P
WHERE S.S# = SP.S#
AND SP.P# = P.P# ;
Joining a table with itself
SELECT FIRST.S#, SECOND.S#
FROM S FIRST, S SECOND
WHERE FIRST.CITY = SECOND.CITY
AND FIRST.S# < SECOND.S# ;
(get all supplier pairs in same city)
Notice the use of "range variables"
AGGREGATE FUNCTIONS
COUNT - number of values in the column
SUM - sum of the values in the column
AVG - average of the values in the column
MIN - minimum of the values in the column
MAX - maximum of the values in the column
Get the total number of suppliers.
SELECT COUNT(*)
FROM S ;
Get the total quantity of part P2 supplied.
SELECT SUM(QTY)
FROM SP
WHERE P# = 'P2' ;
GROUP BY
Get the part # and total shipment quantity
for each part.
SELECT P#, SUM(QTY)
FROM SP
GROUP BY P# ;
Get part numbers for all parts supplied
by more than one supplier
SELECT P#
FROM SP
GROUP BY P#
HAVING COUNT(*) > 1 ;
HAVING is to groups what WHERE is to rows.
HAVING is used to eliminate groups, just as
WHERE is used to eliminate rows.
SUBQUERIES
A subquery is an expression of the form
SELECT-FROM-WHERE-GROUP BY-HAVING
which is nested inside another such
expression.
We usually use subquery to represent a
set of possible values to be searched in
an "IN" condition.
Get supplier names for suppliers who
supply part P2.
SELECT SNAME
FROM S
WHERE S# IN
( SELECT S#
FROM SP
WHERE P# = 'P2' ) ;
SELECT SNAME
FROM S
WHERE S# IN {S1, S2, S3, S4} ;
QUERY EXAMPLE
Get supplier numbers for suppliers supplying at
least one part supplied by at least one supplier
who supplies at least one read part.
SQL Query:
SELECT DISTINCT S#
FROM SPJ
WHERE P# IN
( SELECT P#
FROM SPJ
WHERE S# IN
( SELECT S#
FROM SPJ
WHERE P# IN
( SELECT P#
FROM P
WHERE COLOR = "Red") ) )
STEP #1
( SELECT P#
FROM P
WHERE COLOR = "Red") ) )
P TABLE
P# PNAME COLOR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
S# SNAME STATUS CITY
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P TABLE
P# PNAME COLOR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
J TABLE
J# JNAME CITY
J1 Sorter Paris
J2 Punch Rome
J3 Reader Athens
J4 Console Athens
J5 CollatorLondon
J6 TerminalOslo
J7 Tape London
ADVANCED FEATURES
Retrieval using LIKE for string matching
Get all parts whose names begin with 'C'.
SELECT P.*
FROM P
WHERE P.PNAME LIKE 'C%' ;
'_' any single character
'%' any sequence of characters
RETRIEVAL INVOLVING NULL
Get supplier numbers for suppliers
with status greater than 25.
SELECT S#
FROM S
WHERE STATUS > 25;
If a record has STATUS equal to NULL,
it will NOT qualify, because
NULL does NOT match ANYTHING.
QUERY USING EXISTS
EXISTS represents the existential
quantifier in logic
Get supplier names for suppliers who
supply part P2.
SELECT SNAME
FROM S
WHERE EXISTS
( SELECT *
FROM SP
WHERE S# = S.S#
AND P# = 'P2' ) ;
UPDATE OPERATIONS
UPDATE table
SET field = scalar-expression
[, field = scalar-expression ] ...
[ WHERE condition ] ;
Change the color of part P2 to yellow,
and increase weight by 5, and
set city to "unknown".
UPDATE P
SET COLOR = 'Yellow',
WEIGHT = WEIGHT + 5,
CITY = NULL
WHERE P# = 'P2' ;
DELETE AND INSERT
Delete supplier S5.
DELETE
FROM S
WHERE S# = 'S5' ;
Insert a new record to table P.
INSERT
INTO P ( P#, CITY, WEIGHT )
VALUES ( 'P7', 'Athens', 24 );