structured query language -...
TRANSCRIPT
![Page 1: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/1.jpg)
11
Structured Query Language
Yanlei Diao
Slides Courtesy of R. Ramakrishnan, J. Gehrke, and G. Miklau
![Page 2: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/2.jpg)
2
Structured Query Language (SQL)
v Data Definition Language (DDL)§ operating on tables/views
v Data Manipulation Language (DML)§ posing queries, operating on tuples
v Extension from Relational Algebra / Calculus①From a set to a multi-set (bag) based model②Extending first order expressive power with
aggregation and recursion
![Page 3: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/3.jpg)
3
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation & Grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 4: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/4.jpg)
4
Example Instances
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
bid sname 101 red 103 green
sid bid day22 101 10/10/9658 103 11/12/96
Reserves
Sailor
Boats
![Page 5: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/5.jpg)
5
Creating Tables
CREATE TABLE Sailors( sid INTEGER,
sname CHAR(50) NOT NULL,rating INTEGER,age REAL,PRIMARY KEY (sid));
CREATE TABLE Reserves( sid INTEGER,
bid INTEGER,day DATE,PRIMARY KEY (sid,bid,day),FOREIGN KEY (sid) REFERENCES Sailors (sid)
ON DELETE NO ACTION ON UPDATE CASCADEFOREIGN KEY (bid) REFERENCES Boats (bid)
ON DELETE SET DEFAULT ON UPDATE CASCADE);
CREATE TABLE Boats( bid INTEGER,
bname CHAR(20),color CHAR(20),
PRIMARY KEY (bid)UNIQUE (bname));
![Page 6: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/6.jpg)
6
Destroying and Altering Tables
v Destroys the Sailors relation, including schema and data.
DROP TABLE Sailors;
v The schema is altered by adding a new field; every tuple in the current instance is extended with a nullvalue in the new field.
ALTER TABLE Sailors ADD COLUMN credit_card:CHAR(40);
![Page 7: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/7.jpg)
7
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation and grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 8: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/8.jpg)
8
Example Instances
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0
sid bid day22 101 10/10/9658 103 11/12/96
R1
S1
S2
![Page 9: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/9.jpg)
9
Basic SQL Query
v relation-list: a list of input relation names, possibly each with a range-variable.
v qualification: predicates combined with AND, OR and NOT§ predicate: attr op const or attr1 op attr2, op is <, >, >=, <=, =, <>
v target-list: a list of attributes to display in output§ DISTINCT indicates no duplicates in the answer. Default is
that duplicates are not eliminated!
SELECT [DISTINCT] target-listFROM relation-listWHERE qualification;
![Page 10: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/10.jpg)
10
Conceptual Evaluation Strategy
v relation-list: cross-product ( ´ )
v qualification: selection ( s )§ includes join predicates and restrictions on individual tuples
v target-list: projection ( p )§ duplicate elimination if DISTINCT
v This is possibly the least efficient way to execute the query! Leave the issue to Query Optimization…
SELECT [DISTINCT] target-listFROM relation-listWHERE qualification;
![Page 11: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/11.jpg)
11
Example of Conceptual Evaluation SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103;
(sid) sname rating age (sid) bid day22 dustin 7 45.0 22 101 10/10/9622 dustin 7 45.0 58 103 11/12/9631 lubber 8 55.5 22 101 10/10/9631 lubber 8 55.5 58 103 11/12/9658 rusty 10 35.0 22 101 10/10/9658 rusty 10 35.0 58 103 11/12/96
XXXXX
EWhat is the relational algebra for this query?
X
X
![Page 12: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/12.jpg)
12
Relational Algebra for the Query
SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103;
π σsname bid serves Sailors(( Re ) )=103 ▹◃
![Page 13: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/13.jpg)
13
A Note on Range Variables
v Really needed only if the same relation appears twicein the FROM clause.
SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND bid=103;
SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid
AND bid=103;
It is good style,however, to userange variablesalways!
OR
![Page 14: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/14.jpg)
14
Find sailors who’ve reserved some (at least one) boat
E Would adding DISTINCT to this query change the answer set?
SELECT S.sidFROM Sailors S, Reserves RWHERE S.sid=R.sid;
E What if we replace S.sid by S.sname in the SELECT clause and then add DISTINCT?
![Page 15: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/15.jpg)
15
String Pattern Matching
v Find the ages of sailors whose names begin with ‘A’, end with ‘M’, and contain at least three characters.
v LIKE is used for string matching. § ‘_’ stands for any one character. § ‘%’ stands for 0 or more arbitrary characters.
SELECT S.ageFROM Sailors SWHERE S.sname LIKE ‘A_%M’;
![Page 16: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/16.jpg)
16
Arithmetic Expressions
v For sailors whose names begin with ‘A’ and end with ‘M’, return triples (of ages of sailors and two fields defined by expressions)
v Arithmetic expressions create derived attributes in SELECT.§ AS and = are two ways to name fields in the result.
v They can also appear in the predicates in WHERE.
SELECT S.age, age1 = S.age-5, 2*S.age AS age2FROM Sailors SWHERE S.sname LIKE ‘A%M’;
![Page 17: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/17.jpg)
17
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation and grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 18: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/18.jpg)
18
Find sid’s of sailors who’ve reserved a red or a green boat
v If we replace OR by AND in this query, what do we get?
v UNION: computes the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).
SELECT DISTINCT R.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’);
SELECT DISTINCT S.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND B.color=‘red’UNIONSELECT DISTINCT S.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND B.color= ‘green’;
Why do we need DISTINCT in the query?
![Page 19: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/19.jpg)
19
Set Operations on MultiSets
v Consider a multiset set A, for each element, x A, define an indicator function:
1A(x) = # occurrences of x in A
v Both regular sets and multisets use consistent definitions:
1A B (x) = max (1A(x), 1B(x))
1A B (x) = min (1A(x), 1B(x))
∈
∪
∩
![Page 20: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/20.jpg)
20
Find sid’s of sailors who’ve reserved a red and a green boat
v INTERSECT: computes the intersection of any two union-compatible sets of tuples.
SELECT DISTINCT S.sidFROM Reserves R1, Boats B1,
Reserves R2, Boats B2 WHERE R1.bid=B1.bid AND R2.bid=B2.bid
AND (B1.color=‘red’ AND B2.color=‘green’ )AND R1.sid=R2.sid;
SELECT DISTINCT S.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND B.color=‘red’INTERSECTSELECT DISTINCT S.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND B.color= ‘green’;
Need DISTINCT to be equivalent!
![Page 21: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/21.jpg)
21
Find sid’s of sailors who’ve reserved …
v Also available: EXCEPT (What does this query return?)
SELECT DISTINCT S.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND B.color=‘red’EXCEPTSELECT DISTINCT S.sidFROM Reserves R, Boats BWHERE R.bid=B.bid
AND B.color= ‘green’;
![Page 22: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/22.jpg)
22
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation & Grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 23: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/23.jpg)
23
Postgres Setup Instructionsv Visit
https://piazza.com/umass/spring2020/cmpsci645/resources
v Download “SetupInstructions.txt” given under the “General” section.
v Follow the steps in the file.
v Test query: Find the names of the sailors who have reserved the boat 103.
![Page 24: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/24.jpg)
24
Nested Queriesv A nested query has another query embedded within it. v The embedded query is called the subquery.
v The subquery often appears in the WHERE clause:
v Subqueries are also possible in the FROM clause.
SELECT S.snameFROM Sailors SWHERE S.sid IN ( SELECT R.sid
FROM Reserves RWHERE R.bid = 103 );
![Page 25: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/25.jpg)
25
Conceptual Evaluation, extended
SELECT S.snameFROM Sailors SWHERE S.sid IN ( SELECT R.sid
FROM Reserves RWHERE R.bid = 103 );
v For each row in the cross-product of the outer query, evaluate the WHERE condition by re-computing the subquery.
SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103;
However, this query is equivalent to (can be simplified to):
![Page 26: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/26.jpg)
26
Correlated Subquery
SELECT S.snameFROM Sailors SWHERE EXISTS ( SELECT *
FROM Reserves RWHERE R.bid = 103
AND R.sid = S.sid );
v A subquery that depends on the table(s) mentioned in the outer query is a correlated subquery.
v In conceptual evaluation, must recompute subquery for each row of the outer query.
Correlation
![Page 27: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/27.jpg)
27
Set Comparison Operators in WHERE
v Set comparison, optionally with a preceeding NOT:§ EXISTS R -- true if R is non-empty§ attr IN R -- true if R contains attr§ UNIQUE R -- true if no duplicates in R
v Arithmetic operator op {<,<=,=,< >, >=,>} and ALL/ANY:§ attr op ALL R-- all elements of R satisfy condition§ attr op ANY R -- some element of R satisfies condition
‘attr IN R’ equivalent to ‘attr = ANY R’‘attr NOT IN R’ equivalent to ‘attr < > ALL R’
![Page 28: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/28.jpg)
28
Example Instances (Review)
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
bid sname 101 red 103 green
sid bid day22 101 10/10/9658 103 11/12/96
Reserves
Sailors1
Boats
![Page 29: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/29.jpg)
29
Finding Extreme Values
v Find the sailors with the highest rating
SELECT S.sidFROM Sailors1 SWHERE S.rating >= ALL ( SELECT S2.rating
FROM Sailors1 S2 );
![Page 30: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/30.jpg)
30
v Find sailors whose rating is higher than some sailor named Dustin.
v Find sailors whose rating is higher than all sailors named Dustin.
Please Write SQL
SELECT S.sidFROM Sailors1 SWHERE S.rating > ANY (SELECT S2.rating
FROM Sailors1 S2 WHERE S2.sname = ‘dustin’);
SELECT S.sidFROM Sailors1 SWHERE S.rating > ALL (SELECT S2.rating
FROM Sailors1 S2 WHERE S2.sname = ‘dustin’);
![Page 31: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/31.jpg)
31
Find sailors who’ve reserved all boats.
SELECT S.snameFROM Sailors1 SWHERE NOT EXISTS (
SELECT B.bidFROM Boats B WHERE NOT EXISTS (
SELECT R.bidFROM Reserves RWHERE R.bid=B.bid
AND R.sid=S.sid));
(2)
(1)
SELECT S.snameFROM Sailors1 SWHERE NOT EXISTS
((SELECT B.bidFROM Boats B)EXCEPT(SELECT R.bidFROM Reserves RWHERE R.sid=S.sid));
![Page 32: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/32.jpg)
32
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation and grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 33: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/33.jpg)
33
Example Aggregate Operators
SELECT AVG (S.age)FROM Sailors SWHERE S.rating=10;
SELECT COUNT(*)FROM Sailors S;
SELECT AVG(DISTINCT S.age)FROM Sailors SWHERE S.rating=10;
SELECT S.snameFROM Sailors SWHERE S.rating=
(SELECT MAX(S2.rating)FROM Sailors S2);
SELECT COUNT(DISTINCT S.rating)FROM Sailors SWHERE S.sname=‘Bob’;
![Page 34: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/34.jpg)
34
Aggregate Operators
v Take a relation (single column or multiple columns), return a value.
v Significant extension of relational algebra.
COUNT (*)COUNT ( [DISTINCT] A )SUM ( [DISTINCT] A )AVG ( [DISTINCT] A)MAX (A)MIN (A)
single column
multiple columns
![Page 35: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/35.jpg)
35
Find name and age of the oldest sailor(s)
v The first query is illegal! (We’ll look into the reason a bit later, when we discuss GROUP BY.)
SELECT S.sname, MAX (S.age)FROM Sailors1 S;
SELECT S.sname, S.ageFROM Sailors1 SWHERE S.age =
(SELECT MAX (S2.age)FROM Sailors1 S2);
![Page 36: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/36.jpg)
36
Motivation for Grouping
v What if we want to apply aggregate operators to each group (subset) of tuples?
v Find the age of the youngest sailor for each rating level.§ If we know that rating values Î[1, 10], write 10 queries like:
§ In general, we don’t know how many rating levels exist, and what the rating values for these levels are!
SELECT MIN (S.age)FROM Sailors SWHERE S.rating = i
For i = 1, 2, ... , 10:
![Page 37: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/37.jpg)
37
Queries with GROUP BY and HAVING
v A group is a set of tuples that have the same value for all attributes in grouping-list.
v Query returns a single answer tuple for each group!v The target-list can only contain:
(i) attributes in the grouping-list (e.g., S.rating), or (ii) aggregate operations on other attributes, e.g., MIN (S.age).
SELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-list[HAVING group-qualification];
![Page 38: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/38.jpg)
38
Conceptual Evaluation, extended
v The cross-product of relation-list is computed. v Tuples that fail qualification are discarded. v The remaining tuples are partitioned into groups by the
value of attributes in grouping-list. v The group-qualification, if present, eliminates some groups.
§ Group-qualification must have a single value per group!v A single answer tuple is produced for each qualifying
group.
![Page 39: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/39.jpg)
39
Find age of the youngest sailor with age ³ 18, for each rating with at least 2 such sailors
SELECT S.rating, MIN (S.age) AS minage
FROM Sailors2 SWHERE S.age >= 18GROUP BY S.ratingHAVING COUNT (*) > 1;
sid sname rating age 22 dustin 7 45.0 29 brutus 1 33.0 31 lubber 8 55.5 32 andy 8 25.5 58 rusty 10 35.0 64 horatio 7 35.0 71 zorba 10 16.0 74 horatio 9 35.0 85 art 3 25.5 95 bob 3 63.5 96 frodo 3 25.5
Answer relation:
Sailors2 instance:
rating minage 3 25.5 7 35.0 8 25.5
![Page 40: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/40.jpg)
40
Find age of the youngest sailor with age ³ 18, for each rating with at least 2 such sailors.
rating age 7 45.0 1 33.0 8 55.5 8 25.5 10 35.0 7 35.0 10 16.0 9 35.0 3 25.5 3 63.5 3 25.5
rating minage 3 25.5 7 35.0 8 25.5
rating age 1 33.0 3 25.5 3 63.5 3 25.5 7 45.0 7 35.0 8 55.5 8 25.5 9 35.0 10 35.0
![Page 41: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/41.jpg)
41
Find those ratings for which the average age is the minimum over all ratings
SELECT Temp.rating, Temp.avgageFROM (SELECT S.rating, AVG (S.age) AS avgage
FROM Sailors1 SGROUP BY S.rating) AS Temp
WHERE Temp.avgage = (SELECT MIN (Temp.avgage)FROM Temp);
v Derived table: result of an SQL query as input to the FROM clause of another query§ Computed once before the other query is evaluated.
![Page 42: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/42.jpg)
42
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation & Grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 43: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/43.jpg)
43
ORDER BY
v Return the name and age of sailors rated level 8 or above in increasing (decreasing) order of age.
SELECT S.sname, S.ageFROM Sailors1 SWHERE S.rating > 8ORDER BY S.age [ASC|DESC];
![Page 44: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/44.jpg)
44
TOP-K Queries
v Return the name and age of the ten youngest sailors rated level 8 or above.
SELECT S.sname, S.ageFROM Sailors2 SWHERE S.rating >= 8ORDER BY S.age ASCLIMIT 10;
![Page 45: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/45.jpg)
45
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation & Grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 46: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/46.jpg)
46
NULL Values in SQL
v Whenever we don’t have a value, put a NULL.v Can mean many things:
§ Value does not exist§ Value exists but is unknown§ Value not applicable
v The schema specifies for each attribute whether it can be null (e.g., NOT NULL)
v How does SQL cope with tables that have NULLs ?
![Page 47: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/47.jpg)
47
Null Values
v If x= NULL, then 4*(3-x)/7 is still NULL
v If x= NULL, then x=“Joe” is UNKNOWN
v In SQL there are three boolean values:§ FALSE = 0§ UNKNOWN = 0.5§ TRUE = 1
![Page 48: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/48.jpg)
48
v C1 AND C2 = min(C1, C2)v C1 OR C2 = max(C1, C2)v NOT C1 = 1 – C1
v Rule in SQL: include only tuples that yield TRUE
Coping with Unknown Values
E.g.age=20heigth=NULLweight=200
SELECT *FROM PersonWHERE (age < 25) AND
(height > 6 OR weight > 190);
![Page 49: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/49.jpg)
49
Anomaly Associated with Null’s
v Unexpected behavior:
v Some person is not included!
SELECT *FROM PersonWHERE age < 25 OR age >= 25;
E.g.John’s age is NULL
![Page 50: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/50.jpg)
50
Null Values
v Can test for NULL explicitly:§ x IS NULL§ x IS NOT NULL
v Now it includes all people.
SELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL;
![Page 51: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/51.jpg)
51
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation & Grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 52: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/52.jpg)
52
Modifying the Database
Three kinds of modifications:v Insert - create new tuple(s)v Delete - remove existing tuple(s)v Update - modify existing tuple(s)
v Sometimes they are all called “updates”.
![Page 53: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/53.jpg)
53
InsertionsGeneral form:
Can omit attributes; a missing attribute is NULL.May drop attribute names if give values of all attributes in order.
Example: Insert a new sailor to the database:
INSERT INTO R(A1,…., An)VALUES (v1,…., vn);
INSERT INTO Sailors(sid, sname, rating, age)VALUES (3212, ‘Fred’, 9, 44);
![Page 54: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/54.jpg)
54
Insertions
The query replaces the VALUES keyword.
INSERT INTO Sailors(sid, sname)
SELECT B.id, B.nameFROM Boaters BWHERE Boaters.rank = ‘captain’;
Example: Insert multiple tuples to Sailors:
![Page 55: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/55.jpg)
55
Deletions
Fact about SQL: there is no way to delete only a single occurrence of a tuple that appears twice in a relation.
Example: delete all tuples that satisfy a condition
DELETEFROM SailorsWHERE S.sname = ‘Harry’;
![Page 56: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/56.jpg)
56
UpdatesExamples:
UPDATE Employees SET salary = salary * 1.1;
UPDATE Sailors SSET S.rating = s.rating + 1WHERE S.sid IN
(SELECT sidFROM Reserves RWHERE R.date =‘Oct, 25’);
![Page 57: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/57.jpg)
57
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation & Grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 58: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/58.jpg)
58
Creating Tables
CREATE TABLE Sailors( sid INTEGER,
sname CHAR(50) NOT NULL,rating INTEGER,age REAL,PRIMARY KEY (sid));
CREATE TABLE Reserves( sid INTEGER,
bid INTEGER,day DATE,PRIMARY KEY (sid,bid,day),FOREIGN KEY (sid) REFERENCES Sailors
ON DELETE NO ACTION ON UPDATE CASCADEFOREIGN KEY (bid) REFERENCES Boats
ON DELETE SET DEFAULT ON UPDATE CASCADE);
CREATE TABLE Boats( bid INTEGER,
bname CHAR (20),color CHAR(20),PRIMARY KEY (bid)UNIQUE (bname));
![Page 59: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/59.jpg)
59
Destroying and Altering Tables
v Destroys the Sailors relation, including schema and data.
DROP TABLE Sailors;
v The schema is altered by adding a new field; every tuple in the current instance is extended with a nullvalue in the new field.
ALTER TABLE Sailors ADD COLUMN credit_card:CHAR(40);
![Page 60: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/60.jpg)
60
Viewsv A view is like a relation, but we store a definition,
rather than a set of tuples.
CREATE VIEW RedBoatLovers (sid, name, bid)AS SELECT S.sid, S.sname, B.bid
FROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid and R.bid = B.bid
and B.color=‘red’ ;
v Views can be dropped using DROP VIEW command.§ DROP TABLE if there’s a view on the table?
![Page 61: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/61.jpg)
61
Uses of Views
v Views can be used to present necessary information (or a summary), while hiding details in underlying relation(s).
v Security/Privacy§ E.g., hiding sailors’ credit card from the boat repair dept.
v Logical data independence§ User application defined on a view is unchanged when
underlying table changesv Computational benefits
§ Result of a complex query is frequently used; materialize it.§ Online Analytical Processing (OLAP)
![Page 62: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/62.jpg)
62
SQL Overview
v Table definitionv Query capabilities
§ SELECT-FROM-WHERE blocks § Set operations (union, intersect, except)§ Nested queries (correlation)§ Aggregation and grouping§ Ordering§ Null values
v Database updatesv Tables and viewsv Integrity constraints
![Page 63: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/63.jpg)
63
Integrity Constraints (Review)
v Types of integrity constraints in SQL: § Attribute constraints: domain, NOT NULL§ Key constraints: PRIMARY KEY, UNIQUE§ Foreign key constraints: FOREIGN KEY§ General constraints: CHECK, ASSERTION
v Inserts/deletes/updates that violate IC’s are disallowed.
![Page 64: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/64.jpg)
64
General Constraintsv Two forms: CHECK (single table constraint) and ASSERTION
(multiple-table constraint).
CREATE TABLE Sailors( sid INTEGER,
sname CHAR(50),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( rating >= 1
AND rating <= 10));
![Page 65: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/65.jpg)
65
Constraints over Multiple Relations
v ASSERTION is a constraint over both tables; checked whenever one of the table is modified.
CREATE ASSERTION smallClubCHECK( (SELECT COUNT (S.sid) FROM Sailors S) +(SELECT COUNT (B.bid) FROM Boats B) < 100 );
Number of boats plus number of sailors is < 100:
![Page 66: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/66.jpg)
66
Questions
![Page 67: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/67.jpg)
67
Find sid’s of sailors who’ve reserved a red or a green boat
v If we replace OR by AND in this query, what do we get?
v UNION: computes the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’);
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’UNIONSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’;
![Page 68: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/68.jpg)
68
Find sid’s of sailors who’ve reserved a red and a green boat
v INTERSECT: computes the intersection of any two union-compatible sets of tuples.
SELECT S.sidFROM Sailors S, Boats B1, Reserves R1,
Boats B2, Reserves R2WHERE S.sid=R1.sid AND R1.bid=B1.bid
AND S.sid=R2.sid AND R2.bid=B2.bidAND (B1.color=‘red’ AND B2.color=‘green’);;
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’INTERSECTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’;
Need DISTINCT to be equivalent!
![Page 69: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/69.jpg)
69
Find sid’s of sailors who’ve reserved …
v Also available: EXCEPT (What does this query return?)
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’EXCEPTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’;
![Page 70: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/70.jpg)
70
Simulating INTERSECTv Suppose we have tables R(a,b) and S(a,b)v The following computes R ∩ S:
SELECT DISTINCT *FROM RWHERE (R.a, R.b) IN (SELECT *
FROM S );
SELECT DISTINCT R.a, R.bFROM R, SWHERE R.a = S.a AND R.b = S.b;Intersection!
• Given R(a,b), S(a,b), what is R S ?
This can be expressed without nesting:
![Page 71: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/71.jpg)
71
Find the names of sailors who reserved a red and a green boat.
SELECT snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’INTERSECTSELECT snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘green’
using INTERSECT
SELECT snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’
AND S.sid IN(SELECT S2.sidFROM Sailors S2, Reserves R2, Boats B2WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = ‘green’ )
without INTERSECT (1)
“Find all sailors who have reserved a red boat and, further, have sids that are included in the set of sids of sailors who have reserved a green boat.”
![Page 72: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/72.jpg)
72
Find the names of sailors who reserved a red and a green boat.
SELECT S1.snameFROM (SELECT S.sid, S.name
FROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘green’ ) AS S1,
(SELECT S.sid, SnameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’ ) AS S2
WHERE S1.sid = S2.sid AND S1.sname = S2.sname
without INTERSECT (2)
![Page 73: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/73.jpg)
73
Simulating EXCEPT (set difference)v What does this query compute?
SELECT B.bidFROM Boats BWHERE B.bid NOT IN (SELECT R.bid
FROM Reserves RWHERE R.sid = 100 );
v Inner on R: boats reserved by sailor with sid=100v All boats − inner is what we want.
Find boats not reserved by sailor with sid = 100.
![Page 74: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/74.jpg)
74
Find those ratings for which the average age is the minimum over all ratings
SELECT Temp.rating, Temp.avgageFROM (SELECT S.rating, AVG (S.age) AS avgage
FROM Sailors SGROUP BY S.rating) AS Temp
WHERE Temp.avgage = (SELECT MIN (Temp.avgage)FROM Temp);
v Derived table: result of an SQL query as input to the FROM clause of another query§ Computed once before the other query is evaluated.
![Page 75: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/75.jpg)
75
Integrity Constraints (Review)
v Types of integrity constraints in SQL: § Attributes constraints: domain, NOT NULL, CHECK age > 16§ Key constraints: PRIMARY KEY, UNIQUE§ Foreign key constraints: FOREIGN KEY§ General constraints: CHECK, ASSERTION
v Inserts/deletes/updates that violate IC’s are disallowed.
![Page 76: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/76.jpg)
76
General Constraintsv Two forms: CHECK (single table constraint) and ASSERTION
(multiple-table constraint).
CREATE TABLE Sailors( sid INTEGER,
sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( rating >= 1
AND rating <= 10));
![Page 77: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/77.jpg)
77
General Constraints (contd.)v Can use queries to express constraints. Can also name
constraints.
CREATE TABLE Reserves( sname CHAR(10),
bid INTEGER,day DATE,PRIMARY KEY (bid,day),FOREIGN KEY (bid) REFERENCES Boats
ON DELETE CASCADE,CONSTRAINT noInterlakeResCHECK (`Interlake’ <>
( SELECT B.bnameFROM Boats BWHERE B.bid=bid)));
![Page 78: Structured Query Language - avid.cs.umass.eduavid.cs.umass.edu/courses/645/s2020/lectures/Lec3-SQL.pdf · Structured Query Language (SQL) vData Definition Language (DDL) §operating](https://reader030.vdocuments.us/reader030/viewer/2022040214/5ebe57c992eade5018121bd6/html5/thumbnails/78.jpg)
78
Constraints Over Multiple RelationsCREATE TABLE Sailors
( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) +(SELECT COUNT (B.bid) FROM Boats B) < 100 );
v Wrong! Table constraints are required to hold only if the associated table is nonempty.§ What if Sailors
is empty?
v ASSERTION is the right solution; not associated with either table.
CREATE ASSERTION smallClubCHECK ( (SELECT COUNT (S.sid) FROM Sailors S) +(SELECT COUNT (B.bid) FROM Boats B) < 100 );
Number of boatsplus number of sailors is < 100