Download Advanced Database Systems: SQL Subqueries and Constraints and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
SQL: Part II
CPS 216
Advanced Database Systems
2
Announcements (February 1)
Reading assignment for this week
System R paper and Lomet’s B+^ -tree tricks Due Thursday night
Homework #1 due in 7 days
No class next Thursday (February 10)
Instructor out of town for a program committee meeting
3
Summary of SQL features covered so far
Basic modeling features
Bags, NULL’s
Schema features
CREATE/DROP TABLE
Query features
SELECT-FROM-WHERE statements, set and bag operations, table expressions, aggregation and grouping )Next: subqueries
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 subquery returns more than one row Under what condition will this runtime error never occur?
What if subquery returns no rows? Return NULL
5
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?
6
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
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”
11
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
12
More ways of getting the highest GPA
Which students have the highest GPA?
Summary of SQL features covered so far
Basic modeling features
Bags, NULL’s
Schema features
CREATE/DROP TABLE
Query features
SELECT-FROM-WHERE statements, set and bag operations, table expressions, aggregation and grouping Subqueries: not much more expressive power added
) Next: modifications
14
INSERT
Insert one row
INSERT INTO Enroll VALUES (456, ’CPS216’);
Insert the result of a query
INSERT INTO Enroll (SELECT SID, ’CPS216’ FROM Student WHERE SID NOT IN (SELECT SID FROM Enroll WHERE CID = ’CPS216’));
- Force everybody to take CPS
15
DELETE
Delete everything DELETE FROM Enroll;
Delete according to a WHERE condition
Example: Student 456 drops CPS DELETE FROM Enroll WHERE SID = 456 AND CID = ’CPS216’; Example: Drop students with GPA lower than 1.0 from all CPS classes DELETE FROM Enroll WHERE SID IN (SELECT SID FROM Student WHERE GPA < 1.0) AND CID LIKE ’CPS%’;
Types of SQL constraints
NOT NULL
Key
Referential integrity (foreign key)
General assertion
Tuple- and attribute-based CHECK’s
20
NOT NULL constraint examples
CREATE TABLE Student (SID INTEGER NOT NULL, name VARCHAR(30) NOT NULL, email VARCHAR(30), age INTEGER, GPA FLOAT); CREATE TABLE Course (CID CHAR(10) NOT NULL, title VARCHAR(100) NOT NULL); CREATE TABLE Enroll (SID INTEGER NOT NULL, CID CHAR(10) NOT NULL);
21
Key declaration
At most one PRIMARY KEY per table
Typically implies a primary index Rows are stored inside the index, typically sorted by the primary key value
Any number of UNIQUE keys per table
Typically implies a secondary index Pointers to rows are stored inside the index
Key declaration examples
CREATE TABLE Student (SID INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(30) UNIQUE, age INTEGER, GPA FLOAT); CREATE TABLE Course (CID CHAR(10) NOT NULL PRIMARY KEY, title VARCHAR(100) NOT NULL); CREATE TABLE Enroll (SID INTEGER NOT NULL, CID CHAR(10) NOT NULL, PRIMARY KEY(SID, CID));
Works on Oracle but not DB2: DB2 requires UNIQUE key columns to be NOT NULL
This form is required for multi-attribute keys
23
Referential integrity example
Enroll. SID references Student. SID If an SID appears in Enroll , it must appear in Student Enroll. CID references Course. CID If a CID appears in Enroll , it must appear in Course ) That is, no “dangling pointers”
Student Enroll Course
SID name age GPA 142 Bart 10 2. 123 Milhouse 10 3. 857 Lisa 8 4. 456 Ralph 8 2. ... ... ... ...
CID title CPS216 Advanced Database Systems CPS230 Analysis of Algorithms CPS214 Computer Networks ... ...
SID CID 142 CPS 142 CPS 123 CPS 857 CPS 857 CPS 456 CPS ... ...
24
Referential integrity in SQL
Referenced column(s) must be PRIMARY KEY
Referencing column(s) form a FOREIGN KEY
Example
CREATE TABLE Enroll (SID INTEGER NOT NULL REFERENCES Student(SID), CID CHAR(10) NOT NULL, PRIMARY KEY(SID, CID), FOREIGN KEY CID REFERENCES Course(CID));
Summary of SQL features covered so far
Basic modeling features
Bags, NULL’s
Schema features
CREATE/DROP TABLE, constraints )Next: views
Query features
SELECT-FROM-WHERE statements, set and bag operations, table expressions, aggregation and grouping, subqueries
Modifications
29
Views
A view is like a “virtual” table
Defined by a query, which describes how to compute the view contents on the fly DBMS stores the view definition query instead of view contents Can be used in queries just like a regular table
30
Creating and dropping views
Example: CPS216 roster
CREATE VIEW CPS216Roster AS SELECT SID, name, age, GPA FROM Student WHERE SID IN (SELECT SID FROM Enroll WHERE CID = ’CPS216’);
To drop a view
DROP VIEW view_name ;
Called “base tables”
Using views in queries
Example: find the average GPA of CPS216 students
SELECT AVG(GPA) FROM CPS216Roster; To process the query, replace the reference to the view by its definition SELECT AVG(GPA) FROM (SELECT SID, name, age, GPA FROM Student WHERE SID IN (SELECT SID FROM Enroll WHERE CID = ’CPS216’));
32
Why use views?
To hide data from users
To hide complexity from users
Logical data independence
If applications deal with views, we can change the underlying schema without affecting applications Recall physical data independence: change the physical organization of data without affecting applications
) Real database applications use tons of views
33
Summary of SQL features covered so far
Basic modeling features
Bags, NULL’s
Schema features
CREATE/DROP TABLE, constraints, views
Query features
SELECT-FROM-WHERE statements, set and bag operations, table expressions, aggregation and grouping, subqueries
Modifications
) Next: indexes
Choosing indexes to create
More indexes = better performance?
38
Summary of SQL features covered so far
Basic modeling features
Bags, NULL’s
Schema features
CREATE/DROP TABLE, constraints, views
Query features
SELECT-FROM-WHERE statements, set and bag operations, table expressions, aggregation and grouping, subqueries
Modifications
Performance tuning features
Indexes
39
What else?
Output ordering (ORDER BY)
Triggers
SQL transactions and isolation levels
Application programming interface
Recursion
