Problems: 8.3.1, 8.3.4
Query
COLOR, CITY for ``nonParis'' parts, WT10
SELECT DISTINCT P.COLOR,P.CITY FROM P WHERE P.CITY <> 'Paris' AND P.WEIGHT > 10 ORDER BY CITY DESC;
Query
Supplier/Part info that are colocated
SELECT S.S#, S.SNAME, S.STATUS, S.CITY,
P.P#, P.PNAME, P.COLOR, P.WEIGHT
FROM S, P
WHERE S.CITY = P.CITY ;
S JOIN P USING CITY ;
S NATURAL JOIN P ;
SQL Aggregates: 8.3.7, 8.3.8, 8.3.9 [180]
Query
Get total number of suppliers
SELECT COUNT(*) AS N FROM S ;
Query
Get max and min quantity for part P2
SELECT MAX (SP.QTY) AS MAXQ, MIN (SP.QTY) AS MINQ FROM SP WHERE SP.P# = 'P2' ;
Query
For each part, get P# and total QTY
SELECT SP.P#, SUM (SP.QTY) AS TOTQTY
FROM SP
GROUP BY SP.P# ;
SELECT P.P#, ( SELECT SUM ( SP.QTY )
FROM SP
WHERE SP.P# = P.P# ) AS TOTQTY
FROM P ;
SQL Aggregates: 8.3.10 [181]
Query
Get P# for all parts supplied by more than one supplier
SELECT SP.P# FROM SP GROUP BY SP.P# HAVING COUNT ( SP.S# ) > 1 ;
WHERE clause operates on a row.
HAVING clause operates on a GROUP of rows.
SQL IN, EXISTS: 8.3.11, 8.3.14 [182]
Query
Get SNAME for suppliers who supply part P2
SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# IN
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' );
SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# IN ( 'S1', 'S2', 'S3', 'S4' ) ;
SELECT DISTINCT S.SNAME
FROM S, SP
WHERE S.S# = SP.S# /* join */
AND SP.P# = 'P2' ;
SELECT DISTINCT S.SNAME
FROM S
WHERE EXISTS
( SELECT * FROM SP
WHERE SP.S# = S.S# AND SP.P# = 'P2' ) ;
SQL NOT IN, NOT EXISTS: 8.3.15 [183]
Query
Get SNAME for suppliers who do NOT supply P2
SELECT DISTINCT S.SNAME
FROM S
WHERE NOT EXISTS
( SELECT *
FROM SP
WHERE SP.S# = S.S#
AND SP.P# = 'P2' ) ;
SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# NOT IN
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' ) ;
SQL UNION: 8.3.17 [184]
Query
Get P# where WT
SELECT P.P# FROM P WHERE P.WEIGHT > 16 UNION SELECT SP.P# FROM SP WHERE SP.S# = 'S2' ;
SQL UPDATE: 8.4.1..5 [185]
INSERT INTO P ( P#, PNAME, COLOR, WEIGHT, CITY )
VALUES ('P8', 'Sprocket', 'Pink', 14, 'Nice' ) ;
INSERT INTO TEMP ( S#, CITY)
SELECT S.S#, S.CITY
FROM S
WHERE S.STATUS > 15 ;
UPDATE P
SET COLOR = 'Yellow', WEIGHT = P.WEIGHT + 5
WHERE P.CITY = 'Paris' ;
UPDATE P
SET CITY = ( SELECT S.CITY
FROM S
WHERE S.S# = 'S5' )
WHERE P.COLOR = 'Red' ;
DELETE FROM SP
WHERE 'London' =
( SELECT S.CITY
FROM S
WHERE S.S# = SP.S# ) ;
SQL Comprehensive Example [186]
Query
For all red and blue parts such that the total quantity supplied is greater than 350 (excluding from the total all shipments for which the quantity is less than or equal to 200), get the part number, the weight in grams, the color, and the maximum quantity supplied of that part.
SELECT P.P#,
'Weight in grams =' AS TEXT1,
P.WEIGHT * 454 AS GMWT,
P.COLOR,
'Max quantity=' AS TEXT2,
MAX (SP.QTY) AS MQY
FROM P, SP
WHERE P.P# = SP.P#
AND ( P.COLOR = 'Red' OR P.COLOR = 'Blue')
AND SP.QTY > 200
GROUP BY P.P#, P.WEIGHT, P.COLOR
HAVING SUM (SP.QTY ) > 350 ;
SQL Comprehensive Result [187]
P# TEXT1 GMWT COLOR TEXT2 MQY ------------------------------------------------------- P1 Weight in grams = 5448 Red Max quantity = 300 P5 Weight in grams = 5448 Blue Max quantity = 400 P3 Weight in grams = 7718 Blue Max quantity = 400
Embedded SQL [188]
Inside of COBOL, C, etc.
EXEC SQL BEGIN DECLARE SECTION ;
DCL SQLSTATE CHAR(5) ;
DCL P# CHAR(6) ;
DCL WEIGHT FIXED DECIMAL(3) ;
EXEC SQL END DECLARE SECTION ;
P# = 'P2' ; /* for example */
EXEC SQL SELECT P.WEIGHT
INTO :WEIGHT
FROM P
WHERE P.P# = :P# ;
IF SQLSTATE == '00000'
THEN PRINT WEIGHT ; /* WEIGHT = retrieved value */
ELSE PRINT 'error' ; /* some exception occurred */
SQL: Data Dictionary [189]
CREATE DOMAIN S# CHAR(5);
CREATE DOMAIN NAME CHAR(20);
...
CREATE TABLE SP
( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL,
PRIMARY KEY ( S#, P# )
FOREIGN KEY ( S# ) REFERENCES S
ON DELETE CASCADE
ON UPDATE CASCASE,
FOREIGN KEY ( P# ) REFERENCES P
ON DELETE CASCADE
ON UPDATE CASCASE,
CHECK ( QTY > 0 AND QTY < 5001 )
);
CHECK: Integrity Constraint
SQL: Integrity Rules [190]
CREATE DOMAIN QTY NUMERIC (9)
FOR QTY ( QTY > 0 AND QTY < 5001 AND MOD (QTY,50) = 0 );
CREATE DOMAIN COLOR CHAR (6)
VALUES ( 'Red', 'Yellow', 'Blue', 'Green' );
CREATE INTEGRITY RULE SR7
FORALL S ( IF S.CITY = 'London' THEN S.STATUS = 20 )
ON ATTEMPTED VIOLATION REJECT ;
CREATE INTEGRITY RULE C95
IF SX.STATUS < 20 AND SX.S# = SPX.S# THEN SPX.QTY <= 500 ;
SQL: Security Rules [191]
Mandatory Control
Discretionary Control
CREATE SECURITY RULE SR3 GRANT RETRIEVE ( S#, SNAME, CITY ), DELETE ON S WHERE S.CITY <> 'London' TO Jim, Fred, Mary ON ATTEMPTED VIOLATION REJECT;
SQL: Views [192]
Allow user to see a particular logical view of the data.
View is a window to the underlying data.
View is a named expression of a pre-defined query.
The relation is virtual but can be treated as a normal relation.
SQL: Views [193]
CREATE VIEW GOOD_SUPPLIERS AS
( S WHERE STATUS > 15 ) [ S#, STATUS, CITY ];
UPDATE GOOD_SUPPLIERS WHERE CITY = 'Paris'
STATUS := STATUS + 10;
UPDATE S WHERE STATUS > 15 AND CITY = 'Paris'
STATUS := STATUS + 10;
CREATE VIEW REDPARTS AS
( ( P WHERE COLOR = 'Red') [ P#, PNAME, WEIGHT, CITY ] )
RENAME WEIGHT AS WT ;
CREATE VIEW LONDON_REDPARTS AS
REDPARTS WHERE CITY = 'LONDON' ;
CREATE VIEW PQ AS
SUMMARIZE SP BY ( P# ) ADD SUM (QTY) AS TOTQTY ;
CREATE VIEW CITY_PAIRS AS
( ( S RENAME CITY AS SCITY ) JOIN SP JOIN
( P RENAME CITY AS PCITY ) ) [ SCITY, PCITY ] ;