Download Introduction to Database Systems-Lecture 06 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!
SQL: Part I
CPS 116
Introduction to Database Systems
Announcements
Homework #1 due tonight (Sept. 9)
Sample solution will be available next Tuesday
Sign up for student presentations
Optional; “replaces” your lowest homework grade Two slots: last lecture before midterm and last lecture before final Three students per slot
3
SQL
SQL: Structured Query Language
Pronounced “S-Q-L” or “sequel” The standard query language support by most commercial DBMS
A brief history
IBM System R ANSI SQL ANSI SQL92 (SQL2) SQL3 (still under construction after years!)
4
Creating and dropping tables
CREATE TABLE table_name ( … , column_namei column_typei , … ); DROP TABLE table_name ; Examples create table Student (SID integer, name varchar(30), email varchar(30), age integer, GPA float); create table Course (CID char(10), title varchar(100)); create table Enroll (SID integer, CID char(10)); drop table Student; drop table Course; drop table Enroll; -- lines starting with -- are ignored. -- SQL is insensitive to white space. -- SQL is case insensitive; writing ...Course... is equivalent to -- writing ...COURSE...
5
Basic queries: SFW statement
SELECT A 1 , A 2 , …, A n
FROM R 1 , R 2 , …, R m
WHERE condition ;
Also called an SPJ (select-project-join) query
Equivalent (not really!) to relational algebra query
π A 1 , A 2 , …, A n ( σ condition ( R 1 × R 2 × … × R m ))
6
Example: reading a table
SELECT * FROM Student;
Single-table query, so no cross product here WHERE clause is optional * is a short hand for “all columns”
Example: selection and projection
Name of students under 18
SELECT name FROM Student WHERE age < 18;
When was Lisa born?
SELECT 2004 – age FROM Student WHERE name = ’Lisa’; SELECT list can contain expressions
- Can also use built-in functions such as SUBSTR, ABS, etc. String literals (case sensitive) are enclosed in single quotes
Example: join
SID’s and names of students taking courses with the
word “Database” in their titles
SELECT Student.SID, Student.name FROM Student, Enroll, Course WHERE Student.SID = Enroll.SID AND Enroll.CID = Course.CID AND title LIKE ’%Database%’; LIKE matches a string against a pattern
- % matches any sequence of 0 or more characters Okay to omit table_name in table_name. column_name if column_name is unique
9
Example: rename
SID’s of all pairs of classmates
Relational algebra query: π e 1. SID , e 2. SID ( ρ e 1 Enroll e 1. CID = e 2. CID ∧ e 1. SID > e 2. SID ρ e 2 Enroll ) SQL: SELECT e1.SID AS SID1, e2.SID AS SID FROM Enroll AS e1, Enroll AS e WHERE e1.CID = e2.CID AND e1.SID > e2.SID; AS keyword is completely optional
10
A more complicated example
Titles of all courses that Bart and Lisa are taking
together
FROM Student sb, Student sl, Enroll eb, Enroll el, Course c WHERE sb.name = ’Bart’ AND sl.name = ’Lisa’ AND eb.SID = sb.SID AND el.SID = sl.SID AND eb.CID = el.CID AND eb.CID = c.CID;
SELECT c.title
Tip: Write the FROM clause first, then WHERE, and then SELECT
11
Why SFW statements?
Out of many possible ways of structuring SQL
statements, why did the designers choose SELECT-
FROM-WHERE?
A large number of queries can be written using only selection, projection, and cross product (or join) Any query that uses only these operators can be written in a canonical form: π L (σ p ( R 1 × … × R (^) m ))
- Example: π R. A , S. B ( R p 1 S ) p 2 (π T. C σ p 3 T ) = π R. A , S. B , T. C σ p 1 ∧ p 2 ∧ p 3 ( R × S × T ) SELECT-FROM-WHERE captures this canonical form
12
Set versus bag semantics
Set
No duplicates Relational model and algebra use set semantics
Bag
Duplicates allowed Number of duplicates is significant SQL uses bag semantics by default
Examples of set versus bag operations
Enroll ( SID , CID ), ClubMember ( club , SID )
(SELECT SID FROM ClubMember) EXCEPT (SELECT SID FROM Enroll);
- SID’s of students who are in clubs but not taking any classes (SELECT SID FROM ClubMember) EXCEPT ALL (SELECT SID FROM Enroll);
- SID’s of students who are in more clubs than classes
Summary of SQL features covered so far
SELECT-FROM-WHERE statements (select-project-join
queries)
Set and bag operations
) Next: how to nest SQL queries
21
Table expression
Use query result as a table
In set and bag operations, FROM clauses, etc. A way to “nest” queries
Example: names of students who are in more clubs
than classes
(SELECT SID FROM ClubMember) EXCEPT ALL (SELECT SID FROM Enroll)
SELECT DISTINCT name FROM Student, ( ) AS S WHERE Student.SID = S.SID;
22
Scalar subqueries
A query that returns a single row can be used as a
value in WHERE, SELECT, etc.
Example: students at the same age as Bart
SELECT * FROM Student WHERE age = (
);
SELECT age FROM Student WHERE name = ’Bart’
What’s Bart’s age?
Runtime error if the subquery returns more than
one row
23
IN subqueries
x IN ( subquery ) checks if x is in the result of
subquery
Example: students at the same age as (some) Bart
SELECT * FROM Student WHERE age IN (
);
SELECT age FROM Student WHERE name = ’Bart’
What’s Bart’s age?
24
EXISTS subqueries
EXISTS ( subquery ) checks if the result of subquery is
non-empty
Example: students at the same age as (some) Bart
SELECT * FROM Student AS s WHERE EXISTS (SELECT * FROM Student WHERE name = ’Bart’ AND age = s.age); It is a correlated subquery—a subquery that references tuple variables in surrounding queries
Operational semantics of subqueries
SELECT * FROM Student AS s WHERE EXISTS (SELECT * FROM Student WHERE name = ’Bart’ AND age = s.age);
For each row s in Student Evaluate the subquery with the appropriate value of s.age If the result of the subquery is not empty, output s.*
The DBMS query optimizer may choose to process the query in an equivalent, but more efficient way (example?)
Scoping rule of subqueries
To find out which table a column belongs to
Start with the immediately surrounding query If not found, look in the one surrounding that; repeat if necessary
Use table_name. column_name notation and AS
(renaming) to avoid confusion
27
Another example
SELECT * FROM Student s
WHERE EXISTS
(SELECT * FROM Enroll e
WHERE SID = s.SID
AND EXISTS
(SELECT * FROM Enroll
WHERE SID = s.SID
AND CID <> e.CID));
Students who are taking at least two courses
28
Quantified subqueries
A quantified subquery can be used as a value in a WHERE condition Universal quantification (for all): … WHERE x op ALL ( subquery ) … True iff for all t in the result of subquery , x op t Existential quantification (exists): … WHERE x op ANY ( subquery ) … True iff there exists some t in the result of subquery such that x op t )Beware
- In common parlance, “any” and “all” seem to be synonyms
- In SQL, ANY really means “some”
29
Examples of quantified subqueries
Which students have the highest GPA?
SELECT *
FROM Student WHERE GPA >= ALL (SELECT GPA FROM Student); SELECT * FROM Student WHERE NOT (GPA < ANY (SELECT GPA FROM Student); )Use NOT to negate a condition
30
More ways of getting the highest GPA
Which students have the highest GPA?
SELECT *
FROM Student AS s WHERE NOT EXISTS (SELECT * FROM Student WHERE GPA > s.GPA); SELECT * FROM Student WHERE SID NOT IN (SELECT s1.SID FROM Student AS s1, Student AS s WHERE s1.GPA < s2.GPA);
Aggregates with no GROUP BY
An aggregate query with no GROUP BY clause
represent a special case where all rows go into one
group
SELECT AVG(GPA) FROM Student;
SID name age GPA 142 Bart 10 2. 857 Lisa 8 4. 123 Milhouse 10 3. 456 Ralph 8 2. ... ... ... ...
SID name age GPA 142 Bart 10 2. 857 Lisa 8 4. 123 Milhouse 10 3. 456 Ralph 8 2. ... ... ... ...
Group all rows into one group AVG_GPA 3
Compute aggregate over the group
Restriction on SELECT
If a query uses aggregation/group by, then every
column referenced in SELECT must be either
Aggregated, or A GROUP BY column
) This restriction ensures that any SELECT expression
produces only one value for each group
39
Examples of invalid queries
SELECT SID, age FROM Student GROUP BY age;
Recall there is one output row per group There can be multiple SID values per group
SELECT SID, MAX(GPA) FROM Student;
Recall there is only one group for an aggregate query with no GROUP BY clause There can be multiple SID values Wishful thinking (that the output SID value is the one associated with the highest GPA) does NOT work
40
HAVING
Used to filter groups based on the group properties
(e.g., aggregate values, GROUP BY column values)
SELECT … FROM … WHERE … GROUP BY …
HAVING condition ;
Compute FROM (×) Compute WHERE (σ) Compute GROUP BY: group rows according to the values of GROUP BY columns Compute HAVING (another σ over the groups) Compute SELECT for each group that passes the HAVING condition (π)
41
HAVING examples
Find the average GPA for each age group over 10 SELECT age, AVG(GPA) FROM Student GROUP BY age HAVING age > 10; Can be written using WHERE without table expressions List the average GPA for each age group with more than a hundred students SELECT age, AVG(GPA) FROM Student GROUP BY age HAVING COUNT(*) > 100; Can be written using WHERE and table expressions
42
Summary of SQL features covered so far
SELECT-FROM-WHERE statements
Set and bag operations
Table expressions, subqueries
Aggregation and grouping
More expressive power than relational algebra
) Next: ordering output rows
ORDER BY
SELECT [DISTINCT] ...
FROM … WHERE … GROUP BY … HAVING …
ORDER BY output_column [ASC | DESC], …;
ASC = ascending, DESC = descending
Operational semantics
After SELECT list has been computed and optional duplicate elimination has been carried out, sort the output according to ORDER BY specification
ORDER BY example
List all students, sort them by GPA (descending)
and name (ascending)
SELECT SID, name, age, GPA FROM Student ORDER BY GPA DESC, name; ASC is the default option Strictly speaking, only output columns can appear in ORDER BY clause (although some DBMS support more) Can use sequence numbers of output columns instead ORDER BY 4 DESC, 2;
45
Summary of SQL features covered so far
SELECT-FROM-WHERE statements
Set and bag operations
Table expressions, subqueries
Aggregation and grouping
Ordering
) Next: NULL’s, outerjoins, data modification,
constraints, …
