Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
An introduction to sql, the structured query language used for managing and manipulating relational databases. Topics covered include the history of sql, creating and dropping tables, basic queries using the select, from, and where clauses, and examples of selection, projection, and join queries. The document also touches on set and bag semantics, operational semantics of sfw statements, and sql set and bag operations.
Typology: Slides
1 / 14
This page cannot be seen from the preview
Don't miss anything!
Advanced Database Systems
2
Reading assignment for this week (Ailamaki et al., VLDB 2001) has been posted Due Wednesday night Hunt for related/follow-up work too!
Homework #1 due in two weeks
3
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) ANSI SQL99 (SQL3) ANSI SQL 2003 (+OLAP, XML, etc.)
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; -- everything from -- to the end of the line is ignored. -- SQL is insensitive to white space. -- SQL is case insensitive (e.g., ...Course... is equivalent to -- ...COURSE...)
5
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
SELECT * FROM Student; Single-table query, so no cross product here WHERE clause is optional * is a short hand for “all columns”
Titles of all courses that Bart and Lisa are taking together
Tip: Write the FROM clause first, then WHERE, and then SELECT
11
Out of many possible ways of structuring SQL statements, why did the designers choose SELECT- FROM-WHERE?
12
Set
No duplicates Relational model and algebra use set semantics
Bag Duplicates allowed Number of duplicates is significant SQL uses bag semantics by default
SID CID 142 CPS 142 CPS 123 CPS 857 CPS 857 CPS 456 CPS ... ...
SID 142 123 857 456 ...
π SID Enroll
Enroll
SELECT SID FROM Enroll;
SID 142 142 123 857 857 456 ...
14
Besides, SQL provides the option of set semantics with DISTINCT keyword
15
SELECT [DISTINCT] E 1 , E 2 , …, En FROM R 1 , R 2 , …, R (^) m WHERE condition ; For each t 1 in R 1 : For each t 2 in R 2 : … … For each tm in R (^) m : If condition is true over t 1 , t 2 , …, tm : Compute and output E 1 , E 2 , …, En If DISTINCT is present Eliminate duplicate rows in output
t 1 , t 2 , …, tm are often called tuple variables
Enroll ( SID , CID ), ClubMember ( club , SID ) (SELECT SID FROM ClubMember) EXCEPT (SELECT SID FROM Enroll);
(SELECT SID FROM ClubMember) EXCEPT ALL (SELECT SID FROM Enroll);
20
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;
21
Basic CREATE/DROP TABLE
SELECT-FROM-WHERE statements (select-project-join queries)
Set and bag operations
Nesting queries using table expressions
) So far, not much more than relational algebra
) Next: aggregation
Standard SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX
Example: number of students under 18, and their average GPA SELECT COUNT(), AVG(GPA) FROM Student WHERE age < 18; COUNT() counts the number of rows
23
GROUP BY list_of_columns ;
Example: find the average GPA for each age group SELECT age, AVG(GPA) FROM Student GROUP BY age;
24
Compute FROM (×)
Compute WHERE (σ)
Compute GROUP BY: group rows according to the values of GROUP BY columns
Compute SELECT for each group (π)
)One output row per group in the final output
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
29
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 HAVING
30
Find the average GPA for each age group over 10 SELECT age, AVG(GPA) FROM Student GROUP BY age HAVING age > 10;
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;
Basic CREATE/DROP TABLE
SELECT-FROM-WHERE statements
Set and bag operations
Table expressions
Aggregation and grouping More expressive power than relational algebra
) Next: NULL’s
32
Example: Student ( SID , name , age , GPA )
Value unknown
We do not know Nelson’s age
Value not applicable Nelson has not taken any classes yet; what is his GPA?
33
A dedicated special value for each domain (type)
GPA cannot be –1, so use –1 as a special value to indicate a missing or invalid GPA Leads to incorrect answers if not careful
When we compare a NULL with another value (including another NULL) using =, >, etc., the result is UNKNOWN
TRUE = 1, FALSE = 0, UNKNOWN = 0. x AND y = min( x , y )
x OR y = max( x , y ) NOT x = 1 – x
WHERE and HAVING clauses only select rows for output if the condition evaluates to TRUE UNKNOWN is insufficient
38
SELECT AVG(GPA) FROM Student; SELECT SUM(GPA)/COUNT(*) FROM Student;
SELECT * FROM Student; SELECT * FROM Student WHERE GPA = GPA;
) Be careful: NULL breaks many equivalences
39
Example: Who has NULL GPA values? SELECT * FROM Student WHERE GPA = NULL;
Introduced built-in predicates IS NULL and IS NOT NULL
Basic CREATE/DROP TABLE
SELECT-FROM-WHERE statements
Set and bag operations
Table expressions
Aggregation and grouping
NULL’s
) Next: subqueries, modifications, constraints, and views
