Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos – A. Pavlo Lecture#6: Fun with SQL (part2)
CMU SCS
Today's Party • • • • • •
DDLs Complex Joins Views Nested Subqueries Triggers Database Application Example
Faloutsos/Pavlo
CMU SCS 15-415/615
4
CMU SCS
Example Database STUDENT sid 53666 53688 53677
name login Faloutsos
[email protected] Bieber
[email protected] Tupac
[email protected]
Faloutsos/Pavlo
ENROLLED age 45 21 26
gpa 4.0 3.9 3.5
CMU SCS 15-415/615
sid 53831 53688 53688 53666
cid grade Pilates101 C Reggae203 D Topology112 A Massage105 D
5
1
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Table Definition (DDL) CREATE TABLE
( [column-definition]* [constraints]* ) [table-options];
• Column-Definition: Comma separated list of column names with types. • Constraints: Primary key, foreign key, and other meta-data attributes of columns. • Table-Options: DBMS-specific options for the table (not SQL-92). 6
CMU SCS
Table Definition Example CREATE TABLE student ( sid INT, name VARCHAR(16), login VARCHAR(32), age SMALLINT, gpa FLOAT ); CREATE TABLE enrolled ( sid INT, cid VARCHAR(32), grade CHAR(1) );
Integer Range
Variable String Length
Fixed String Length 7
CMU SCS
Common Data Types • CHAR(n), VARCHAR(n) • TINYINT, SMALLINT, INTEGER, BIGINT • NUMERIC(p,d), FLOAT DOUBLE, REAL • DATE, TIME • BINARY(n), VARBINARY(n), BLOB
Faloutsos/Pavlo
CMU SCS 15-415/615
#8
2
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Comment About BLOBs • Don‟t store large files in your database! • Put the file on the filesystem and store a URI in the database. • Many app frameworks will do this automatically for you. • More information: – To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem? Faloutsos/Pavlo
CMU SCS 15-415/615
9
CMU SCS
Useful Non-standard Types • TEXT • BOOLEAN • ARRAY • Some systems also support user-defined types.
Faloutsos/Pavlo
CMU SCS 15-415/615
#10
CMU SCS
Integrity Constraints CREATE TABLE student ( sid INT PRIMARY KEY, name VARCHAR(16), PKey Definition login VARCHAR(32) UNIQUE, age SMALLINT CHECK (age > 0), gpa FLOAT Type Attributes ); CREATE TABLE enrolled ( sid INT REFERENCES student (sid), cid VARCHAR(32) NOT NULL, FKey Definition grade CHAR(1), PRIMARY KEY (sid, cid) 11 );
3
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Primary Keys • Single-column primary key: CREATE TABLE student ( sid INT PRIMARY KEY, ⋮
• Multi-column primary key: CREATE TABLE student ( ⋮ PRIMARY KEY (sid, name) Faloutsos/Pavlo
CMU SCS 15-415/615
12
CMU SCS
Foreign Key References • Single-column reference: CREATE TABLE enrolled ( sid INT REFERENCES student (sid), ⋮
• Multi-column reference: CREATE TABLE enrolled ( ⋮ FOREIGN KEY (sid, …) REFERENCES student (sid, …) Faloutsos/Pavlo
CMU SCS 15-415/615
13
CMU SCS
Foreign Key References • You can define what happens when the parent table is modified: – CASCADE – RESTRICT – NO ACTION – SET NULL – SET DEFAULT Faloutsos/Pavlo
CMU SCS 15-415/615
14
4
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Foreign Key References • Delete/update the enrollment information when a student is changed: CREATE TABLE enrolled ( ⋮ FOREIGN KEY (sid) REFERENCES student (sid) ON DELETE CASCADE ON UPDATE CASCADE
Faloutsos/Pavlo
CMU SCS 15-415/615
15
CMU SCS
Value Constraints • Ensure one-and-only-one value exists: CREATE TABLE student ( login VARCHAR(32) UNIQUE,
• Make sure a value is not null: CREATE TABLE enrolled ( cid VARCHAR(32) NOT NULL,
Faloutsos/Pavlo
CMU SCS 15-415/615
16
CMU SCS
Value Constraints • Make sure that an expression evaluates to true for each row in the table: CREATE TABLE enrolled ( age SMALLINT CHECK (age > 0),
• Can be expensive to evaluate, so tread lightly…
Faloutsos/Pavlo
CMU SCS 15-415/615
17
5
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Auto-Generated Keys • Automatically create a unique integer id for whenever a row is inserted (last + 1). • Implementations vary wildly: – – – – – –
SQL:2003 → IDENTITY MySQL → AUTO_INCREMENT Postgres → SERIAL SQL Server → SEQUENCE DB2 → SEQUENCE Oracle → SEQUENCE
Faloutsos/Pavlo
CMU SCS 15-415/615
18
CMU SCS
Auto-Generated Keys MySQL CREATE TABLE student ( sid INT PRIMARY KEY AUTO_INCREMENT, ⋮ INSERT INTO student (sid, name, login, age, gpa) VALUES (NULL, “Christos”, “@cs”, 45, 4.0);
Faloutsos/Pavlo
CMU SCS 15-415/615
19
CMU SCS
Conditional Table Creation • IF NOT EXISTS prevents the DBMS from trying to create a table twice. CREATE TABLE IF NOT EXISTS student ( sid INT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT CHECK (age > 0), gpa FLOAT ); Faloutsos/Pavlo
CMU SCS 15-415/615
20
6
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Dropping Tables • Completely removes a table from the database. Deletes everything related to the table (e.g., indexes, views, triggers, etc): DROP TABLE student;
• Can also use IF EXISTS to avoid errors: DROP TABLE IF EXISTS student;
Faloutsos/Pavlo
CMU SCS 15-415/615
21
CMU SCS
Modifying Tables • SQL lets you add/drop columns in a table after it is created: ALTER TABLE student ADD COLUMN phone VARCHAR(32) NOT NULL; ALTER TABLE student DROP COLUMN login;
• This is really expensive!!! Tread lightly… Faloutsos/Pavlo
CMU SCS 15-415/615
22
CMU SCS
Modifying Tables • You can also modify existing columns (rename, change type, change defaults, etc): Postgres ALTER TABLE student ALTER COLUMN name TYPE VARCHAR(32); MySQL ALTER TABLE student CHANGE COLUMN name name VARCHAR(32);
Faloutsos/Pavlo
CMU SCS 15-415/615
23
7
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Accessing Table Schema • You can query the DBMS‟s internal INFORMATION_SCHEMA catalog to get info about the database. • ANSI standard set of read-only views that provide info about all of the tables, views, columns, and procedures in a database • Every DBMS also have non-standard shortcuts to do this. Faloutsos/Pavlo
CMU SCS 15-415/615
24
CMU SCS
Accessing Table Schema • List all of the tables in the current database: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_catalog = '' \d;
Postgres
SHOW TABLES;
MySQL
.tables;
SQLite
Faloutsos/Pavlo
CMU SCS 15-415/615
25
CMU SCS
Accessing Table Schema • List the column info for the student table: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'student' \d student;
Postgres
DESCRIBE student;
MySQL
.schema student;
SQLite
Faloutsos/Pavlo
CMU SCS 15-415/615
26
8
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Today's Party • • • • • •
DDLs Complex Joins Views Nested Subqueries Triggers Database Application Example
Faloutsos/Pavlo
CMU SCS 15-415/615
27
CMU SCS
Example Database STUDENT sid 53666 53688 53655
name login Faloutsos [email protected] Bieber [email protected] Tupac [email protected]
ENROLLED age 45 21 26
gpa 4.0 3.9 3.5
sid 53666 53688 53688 53666
cid grade Pilates101 C Reggae203 D Topology112 A Massage105 D
COURSE cid Pilates101 Reggae203 Topology112 Massage105
Faloutsos/Pavlo
name Pilates 20th Century Reggae Topology + Squirrels Massage & Holistic Therapy
CMU SCS 15-415/615
28
CMU SCS
Join Query Grammar SELECT FROM ON [WHERE
... table-name1 join-type table-name2 qualification ...]
• Join-Type: The type of join to compute. • Qualification: Expression that determines whether a tuple from table1 can be joined with table2. Comparison of attributes or constants using operators =, ≠, <, >, ≤, and ≥. Faloutsos/Pavlo
CMU SCS 15-415/615
29
9
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
INNER JOIN sid 53666 53688 53655
name login Faloutsos [email protected] Bieber [email protected] Tupac [email protected]
age 45 21 26
gpa 4.0 3.9 3.5
sid 53666 53688 53688 53666
cid grade Pilates101 C Reggae203 D Topology112 A Massage105 D
SELECT name, cid, grade FROM student INNER JOIN enrolled ON student.sid = enrolled.sid name Bieber Bieber Faloutsos Faloutsos
cid Reggae203 Topology112 Massage105 Pilates101
Grade D A D C 30
CMU SCS
INNER JOIN • Short-hand version SELECT student.sid, cid, grade FROM student, enrolled WHERE student.sid = enrolled.sid
Faloutsos/Pavlo
CMU SCS 15-415/615
31
CMU SCS
OUTER JOIN sid 53666 53688 53677
name login Faloutsos [email protected] Bieber [email protected] Tupac [email protected]
age 45 21 26
gpa 4.0 3.9 3.5
sid 53666 53688 53688 53666
cid grade Pilates101 C Reggae203 D Topology112 A Massage105 D
SELECT student.sid, cid, grade FROM student LEFT OUTER JOIN enrolled ON student.sid = enrolled.sid name Bieber Bieber Faloutsos Faloutsos Tupac
cid Reggae203 Topology112 Massage105 Pilates101 NULL
Grade D A D C NULL
32
10
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
OUTER JOIN sid 53666 53688 53677
name login Faloutsos [email protected] Bieber [email protected] Tupac [email protected]
age 45 21 26
gpa 4.0 3.9 3.5
sid 53666 53688 53688 53666
cid grade Pilates101 C Reggae203 D Topology112 A Massage105 D
SELECT student.sid, cid, grade FROM enrolled RIGHT OUTER JOIN student ON student.sid = enrolled.sid name Bieber Bieber Faloutsos Faloutsos Tupac
cid Reggae203 Topology112 Massage105 Pilates101 NULL
Grade D A D C NULL
33
CMU SCS
Join Types SELECT * FROM A JOIN B ON A.id = B.id Join Type
Description
INNER JOIN
Join where A and B have same value
LEFT OUTER JOIN
Join where A and B have same value AND where only A has a value
RIGHT OUTER JOIN Join where A and B have same value AND where only B has a value FULL OUTER JOIN Join where A and B have same value AND where A or B have unique values CROSS JOIN Cartesian Product Faloutsos/Pavlo
CMU SCS 15-415/615
34
CMU SCS
Today's Party • • • • • •
DDLs Complex Joins Views Nested Subqueries Triggers Database Application Example
Faloutsos/Pavlo
CMU SCS 15-415/615
35
11
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Views • Creates a “virtual” table containing the output from a SELECT query. • Mechanism for hiding data from view of certain users. • Can be used to simplify a complex query that is executed often. – Won‟t make it faster though!
Faloutsos/Pavlo
CMU SCS 15-415/615
36
CMU SCS
View Example • Create a view of the CS student records with just their id, name, and login. CREATE VIEW CompSciStudentInfo AS SELECT sid, name, login FROM student WHERE login LIKE ‘%@cs’;
Original Table View
sid name 53666 Faloutsos 53688 Bieber
login [email protected] [email protected]
sid name 53666 Faloutsos 53688 Bieber
age gpa 45 4.0 21 3.9
login [email protected] [email protected]
37
CMU SCS
View Example • Create a view with the average age of the students enrolled in each course. CREATE VIEW CourseAge AS SELECT cid, AVG(age) AS avg_age FROM student, enrolled WHERE student.sid = enrolled.sid GROUP BY enrolled.cid;
Faloutsos/Pavlo
cid
avg_age
Massage105
45.0
Pilates101
45.0
Topology112
21.0
Reggae203
21.0
38
12
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Views vs. SELECT INTO CREATE VIEW AvgGPA AS SELECT AVG(gpa) AS avg_gpa FROM student WHERE login LIKE ‘%@cs’ SELECT AVG(gpa) AS avg_gpa INTO AvgGPA FROM student WHERE login LIKE ‘%@cs’
• INTO→Creates static table that does not get updated when student gets updated. • VIEW→Dynamic results are only materialized when needed. 39
CMU SCS
Today's Party • • • • • •
DDLs Complex Joins Views Nested Subqueries Triggers Database Application Example
Faloutsos/Pavlo
CMU SCS 15-415/615
40
CMU SCS
Nested Queries • Queries containing other queries “outer query”
“inner query”
• Inner query: – Can appear in FROM or WHERE clause SELECT cname FROM borrower WHERE cname IN (SELECT cname FROM depositor) Think of this as a function that returns the result of the inner query
cname Johnson Smith Jones Smith
13
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Nested Queries • Find the names of students in „Massage105‟ SELECT name FROM student WHERE ...
“sid in the set of people that take Massage105”
Faloutsos/Pavlo
CMU SCS 15-415/615
42
CMU SCS
Nested Queries • Find the names of students in „Massage105‟ SELECT name FROM student WHERE ... SELECT sid FROM enrolled WHERE cid = ‘Massage105’
Faloutsos/Pavlo
CMU SCS 15-415/615
43
CMU SCS
Nested Queries • Find the names of students in „Massage105‟ SELECT name FROM student WHERE sid IN ( SELECT sid FROM enrolled WHERE cid = ‘Massage105’ ) name Faloutsos
Faloutsos/Pavlo
CMU SCS 15-415/615
44
14
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Nested Queries • ALL →Must satisfy expression for all rows in sub-query • ANY →Must satisfy expression for at least one row in sub-query. • IN → Equivalent to „=ANY()‟. • EXISTS → At least one row is returned. • Nested queries are difficult to optimize. Try to avoid them if possible. Faloutsos/Pavlo
CMU SCS 15-415/615
45
CMU SCS
Nested Queries • Find the names of students in „Massage105‟ SELECT name FROM student WHERE sid = ANY( SELECT sid FROM enrolled WHERE cid = ‘Massage105’ ) name Faloutsos
Faloutsos/Pavlo
CMU SCS 15-415/615
46
CMU SCS
Nested Queries • Find student record with the highest id. • This won‟t work in SQL-92: SELECT MAX(sid), name FROM student;
X
• Runs in MySQL, but you get wrong answer: sid 53688
Faloutsos/Pavlo
name Tupac
CMU SCS 15-415/615
47
15
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE ...
“is greater than every other sid”
Faloutsos/Pavlo
CMU SCS 15-415/615
48
CMU SCS
Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE sid is greater than every SELECT sid FROM enrolled
Faloutsos/Pavlo
CMU SCS 15-415/615
49
CMU SCS
Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE sid => ALL( SELECT sid FROM enrolled ) sid 53688
Faloutsos/Pavlo
name Bieber
CMU SCS 15-415/615
50
16
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE sid IN ( SELECT MAX(sid) FROM enrolled ) sid 53688
Faloutsos/Pavlo
name Bieber
CMU SCS 15-415/615
51
CMU SCS
Nested Queries • Find all courses that nobody is enrolled in. SELECT * FROM course WHERE ...
“with no tuples in the „enrolled‟ table” cid Pilates101 Reggae203 Karate101 Topology112 Massage105 Faloutsos/Pavlo
name Pilates 20th Century Reggae Karate Kid Aerobics Topology + Squirrels Massage & Holistic Therapy
sid 53666 53688 53688 53666
cid grade Pilates101 C Reggae203 D Topology112 A Massage105 D
CMU SCS 15-415/615
52
CMU SCS
Nested Queries • Find all courses that nobody is enrolled in. SELECT * FROM course WHERE NOT EXISTS(
tuples in the „enrolled‟ table
)
Faloutsos/Pavlo
CMU SCS 15-415/615
53
17
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Nested Queries • Find all courses that nobody is enrolled in. SELECT * FROM course WHERE NOT EXISTS( SELECT * FROM enrolled WHERE course.cid = enrolled.cid )
Faloutsos/Pavlo
CMU SCS 15-415/615
54
CMU SCS
Today's Party • • • • • •
DDLs Complex Joins Views Nested Subqueries Triggers Database Application Example
Faloutsos/Pavlo
CMU SCS 15-415/615
55
CMU SCS
Database Triggers • Procedural code that is automatically executed in response to certain events on a particular table or view in a database. • BEFORE/AFTER – INSERT – UPDATE – DELETE Faloutsos/Pavlo
CMU SCS 15-415/615
56
18
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Trigger Example • Set a timestamp field whenever a row in the enrolled table is updated. • First we need to add our timestamp field. ALTER TABLE enrolled ADD COLUMN updated TIMESTAMP;
Faloutsos/Pavlo
CMU SCS 15-415/615
57
CMU SCS
Trigger Example • Register a function that sets the „updated‟ column with the current timestamp. CREATE OR REPLACE FUNCTION update_col() RETURNS TRIGGER AS $$ BEGIN NEW.updated = NOW(); RETURN NEW; END; $$ language 'plpgsql'; Postgres Faloutsos/Pavlo
CMU SCS 15-415/615
58
CMU SCS
Trigger Example • Invoke the update_col function when a row in the enrolled table is updated. CREATE TRIGGER update_enrolled_modtime AFTER UPDATE ON enrolled FOR EACH ROW EXECUTE PROCEDURE update_col();
Postgres
Faloutsos/Pavlo
CMU SCS 15-415/615
59
19
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Today's Party • • • • • •
DDLs Complex Joins Views Nested Subqueries Triggers Database Application Example
Faloutsos/Pavlo
CMU SCS 15-415/615
60
CMU SCS
Outline of an DB application • • • • •
Establish connection with DB server Authenticate (user/password) Execute SQL statement(s) Process results Close connection
Faloutsos/Pavlo
CMU SCS 15-415/615
CMU SCS
CMU SCS 15-415/615
20
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Sample Python Code • http://www.cs.cmu.edu/~christos/courses/db ms.S14/PYTHON-examples/csv2sql.py • Or follow instructions at • http://www.cs.cmu.edu/~christos/courses/db ms.S14/PYTHON-examples/
Faloutsos/Pavlo
CMU SCS 15-415/615
63
CMU SCS 15-415/615
64
CMU SCS 15-415/615
65
CMU SCS
Faloutsos/Pavlo
CMU SCS
Faloutsos/Pavlo
21
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Cursors • Used to iterate through the results of query. • Enables result rows to be processed sequentially. – Runs counter to SQL‟s set‐based nature. – Very inefficient in most cases!
Faloutsos/Pavlo
CMU SCS 15-415/615
66
CMU SCS 15-415/615
67
CMU SCS 15-415/615
68
CMU SCS
Faloutsos/Pavlo
CMU SCS
Faloutsos/Pavlo
22
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
Faloutsos/Pavlo
CMU SCS 15-415/615
CMU SCS
Summary Outline of an SQL application: • Establish connection with db server • Authenticate (user/password) • Execute SQL statement(s) (using cursors) • Process results • Close connection
Faloutsos/Pavlo
CMU SCS 15-415/615
70
CMU SCS
ORM Libraries • Object-Relational Mapping • Automatically convert classes into database-backed objects. • Method calls on objects are automatically converted into SQL queries. • Removes the tediousness of writing SQL queries directly in application code. Faloutsos/Pavlo
CMU SCS 15-415/615
71
23
Faloutsos/Pavlo
CMU - 15-415/615
CMU SCS
ORM Example class Location(models.Model): zipcode = CharField(max_length=5,primary_key=True) state = USStateField() city = CharField(max_length=64) class Company(models.Model): name = CharField(max_length=64,unique=True) address1 = CharField(max_length=128) location = ForeignKey(Location) website = URLField() public = BooleanField(default=True)
72
CMU SCS
ORM Example CREATE TABLE location ( zipcode VARCHAR(5) NOT NULL, state CHAR(2) NOT NULL, city VARCHAR(64) NOT NULL, PRIMARY KEY (zipcode), ); CREATE TABLE company ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, address1 VARCHAR(128) NOT NULL, location_id VARCHAR(5) NOT NULL \ REFERENCES location (zipcode), website VARCHAR(200) NOT NULL, public TINYINT(1) NOT NULL, PRIMARY KEY (id), );
73
CMU SCS
ORM Libraries • Standalone: – Hibernate (Java) – SQLAlchemy (Python) – Doctrine (PHP)
• Integrated: – Django (Python) – ActiveRecord (Ruby on Rails) – CakePHP (PHP) Faloutsos/Pavlo
CMU SCS 15-415/615
74
24
