Administrivia Carnegie Mellon Univ. HW2 is due next Monday. Dept. - PowerPoint PPT Presentation

CMU SCS CMU SCS Administrivia Carnegie Mellon Univ. • HW2 is due next Monday. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos – A. Pavlo Lecture#7: Fun with SQL (Part 2) Faloutsos/Pavlo CMU SCS 15-415/615 2 CMU SCS CMU SCS Last Class Today's Jam • SELECT/INSERT/UPDATE/DELETE • Complex Joins • Table Definition (DDL) • Views • NULLs • Nested Queries • String/Date/Time/Set/Bag Operations • Common Table Expressions • Output Redirection/Control • Triggers • Aggregates/Group By • Database Application Example Faloutsos/Pavlo CMU SCS 15-415/615 3 Faloutsos/Pavlo CMU SCS 15-415/615 4

CMU SCS CMU SCS Example Database Join Query Grammar SELECT ... STUDENT ENROLLED FROM table-name1 join-type table-name2 sid name login age gpa sid cid grade ON qualification 53666 Trump trump@cs 45 4.0 53666 Pilates101 C [ WHERE ... ] 53688 Bieber jbieber@cs 21 3.9 53688 Reggae203 D 53655 Tupac shakur@cs 26 3.5 53688 Topology112 A 53666 Massage105 D • Join-Type : The type of join to compute. COURSE • Qualification : Expression that determines cid name whether a tuple from table1 can be joined with Pilates101 Pilates Reggae203 20 th Century Reggae table2. Comparison of attributes or constants Topology112 Topology + Squirrels using operators =, ≠, <, >, ≤, and ≥. Massage105 Massage & Holistic Therapy Faloutsos/Pavlo CMU SCS 15-415/615 5 Faloutsos/Pavlo CMU SCS 15-415/615 6 CMU SCS CMU SCS INNER JOIN OUTER JOIN sid name login age gpa sid cid grade sid name login age gpa sid cid grade 53666 Trump trump@cs 45 4.0 53666 Pilates101 C 53666 Trump trump@cs 45 4.0 53666 Pilates101 C 53688 Bieber jbieber@cs 21 3.9 53688 Reggae203 D 53688 Bieber jbieber@cs 21 3.9 53688 Reggae203 D 53655 Tupac shakur@cs 26 3.5 53688 Topology112 A 53677 Tupac shakur@cs 26 3.5 53688 Topology112 A 53666 Massage105 D 53666 Massage105 D SELECT name, cid, grade SELECT name, cid, grade SELECT name, cid, grade FROM student INNER JOIN enrolled FROM student , enrolled FROM student LEFT OUTER JOIN enrolled ON student.sid = enrolled.sid WHERE student.sid = enrolled.sid ON student.sid = enrolled.sid name cid grade name cid grade Bieber Reggae203 D Bieber Reggae203 D Bieber Topology112 A Bieber Topology112 A Trump Massage105 D Trump Massage105 D Trump Pilates101 C Trump Pilates101 C 7 8 Tupac NULL NULL

CMU SCS CMU SCS OUTER JOIN Join Types SELECT * FROM A JOIN B ON A.id = B.id sid name login age gpa sid cid grade 53666 Trump trump@cs 45 4.0 53666 Pilates101 C Join Type Description 53688 Bieber jbieber@cs 21 3.9 53688 Reggae203 D 53677 Tupac shakur@cs 26 3.5 53688 Topology112 A INNER JOIN Join where A and B have same value 53666 Massage105 D LEFT OUTER JOIN Join where A and B have same value SELECT name, cid, grade AND where only A has a value FROM enrolled RIGHT OUTER JOIN student RIGHT OUTER JOIN Join where A and B have same value ON student.sid = enrolled.sid AND where only B has a value FULL OUTER JOIN Join where A and B have same value name cid grade AND where A or B have unique values Bieber Reggae203 D Bieber Topology112 A CROSS JOIN Cartesian Product Trump Massage105 D Trump Pilates101 C 9 Faloutsos/Pavlo CMU SCS 15-415/615 10 Shakur NULL NULL CMU SCS CMU SCS Today's Jam • Complex Joins • Views • Nested Queries • Common Table Expressions • Triggers • Database Application Example Faloutsos/Pavlo CMU SCS 15-415/615 11 Faloutsos/Pavlo CMU SCS 15-415/615 12

CMU SCS CMU SCS Views View Example • Creates a “virtual” table containing the • Create a view of the CS student records output from a SELECT query. with just their id, name, and login. CREATE VIEW CompSciStudentInfo AS • Mechanism for hiding data from view of SELECT sid, name, login certain users. FROM student • Can be used to simplify a complex query WHERE login LIKE ‘%@cs’; that is executed often. sid name login age gpa – Won’t make it faster though! Original Table 53666 Trump trump@cs 45 4.0 53688 Bieber jbieber@cs 21 3.9 sid name login View 53666 Trump trump@cs Faloutsos/Pavlo CMU SCS 15-415/615 13 14 53688 Bieber jbieber@cs CMU SCS CMU SCS View Example Views vs. SELECT INTO CREATE VIEW AvgGPA AS • Create a view with the average age of the SELECT AVG( gpa ) AS avg_gpa FROM student students enrolled in each course. WHERE login LIKE ‘%@cs’ CREATE VIEW CourseAge AS SELECT cid, AVG (age) AS avg_age SELECT AVG(gpa) AS avg_gpa INTO AvgGPA FROM student, enrolled FROM student WHERE login LIKE ‘%@cs’ WHERE student.sid = enrolled.sid • INTO → Creates static table that does not get GROUP BY enrolled.cid; updated when student gets updated. cid avg_age • VIEW →Dynamic results are only materialized Massage105 45.0 when needed. Pilates101 45.0 Topology112 21.0 Faloutsos/Pavlo 15 16 Reggae203 21.0

CMU SCS CMU SCS Materialized Views Today's Jam • Creates a view containing the output from a • Complex Joins SELECT query that is automatically • Views updated when the underlying tables change. • Nested Queries • Common Table Expressions CREATE MATERIALIZED VIEW AvgGPA AS • Triggers SELECT AVG( gpa ) AS avg_gpa FROM student WHERE login LIKE ‘%@cs’ • Database Application Example Faloutsos/Pavlo CMU SCS 15-415/615 17 Faloutsos/Pavlo CMU SCS 15-415/615 18 CMU SCS CMU SCS Nested Queries Nested Queries • Queries containing other queries • Find the names of students in ‘Massage105’ “outer query” “inner query” SELECT name FROM student • Inner query: WHERE ... – Can appear in FROM or WHERE clause “sid in the set of people that take Massage105” SELECT cname FROM customer WHERE acctno IN (SELECT acctno FROM account ) cname Johnson Think of this as a function Smith that returns the result of Jones the inner query Faloutsos/Pavlo CMU SCS 15-415/615 20 Smith

CMU SCS CMU SCS Nested Queries Nested Queries • Find the names of students in ‘Massage105’ • Find the names of students in ‘Massage105’ SELECT name FROM student SELECT name FROM student WHERE ... WHERE sid IN ( SELECT sid FROM enrolled SELECT sid FROM enrolled WHERE cid = ‘Massage105’ WHERE cid = ‘Massage105’ ) name Trump Faloutsos/Pavlo CMU SCS 15-415/615 21 Faloutsos/Pavlo CMU SCS 15-415/615 22 CMU SCS CMU SCS Nested Queries Nested Queries • ALL →Must satisfy expression for all rows • Find the names of students in ‘Massage105’ in sub-query SELECT name FROM student • ANY →Must satisfy expression for at least WHERE sid = ANY( SELECT sid FROM enrolled one row in sub-query. WHERE cid = ‘Massage105’ • IN → Equivalent to ‘ =ANY() ’. ) • EXISTS → At least one row is returned. name • Nested queries are difficult to optimize. Try to Trump avoid them if possible. Faloutsos/Pavlo CMU SCS 15-415/615 23 Faloutsos/Pavlo CMU SCS 15-415/615 24

CMU SCS CMU SCS Nested Queries Nested Queries • Find student record with the highest id. • Find student record with the highest id. • This won’t work in SQL-92 : SELECT sid, name FROM student SELECT MAX(sid), name FROM student ; X WHERE ... “is greater than every other sid” • Runs in MySQL , but you get wrong answer: sid name 53688 Tupac Faloutsos/Pavlo CMU SCS 15-415/615 25 Faloutsos/Pavlo CMU SCS 15-415/615 26 CMU SCS CMU SCS Nested Queries Nested Queries • Find student record with the highest id. • Find student record with the highest id. SELECT sid, name FROM student SELECT sid, name FROM student WHERE sid WHERE sid => ALL( is greater than every SELECT sid FROM enrolled SELECT sid FROM enrolled ) sid name 53688 Bieber Faloutsos/Pavlo CMU SCS 15-415/615 27 Faloutsos/Pavlo CMU SCS 15-415/615 28

CMU SCS CMU SCS Nested Queries Nested Queries • Find student record with the highest id. • Find all courses that nobody is enrolled in. SELECT sid, name FROM student SELECT * FROM course WHERE sid IN ( WHERE ... SELECT MAX (sid) FROM enrolled “with no tuples in the ‘enrolled’ table” ) SELECT sid, name FROM student cid name sid cid grade WHERE sid IN ( Pilates101 Pilates 53666 Pilates101 C Reggae203 20 th Century Reggae 53688 Reggae203 D SELECT sid FROM enrolled Karate101 Karate Kid Aerobics 53688 Topology112 A ORDER BY sid DESC LIMIT 1 Topology112 Topology + Squirrels 53666 Massage105 D Massage105 Massage & Holistic Therapy ) 29 Faloutsos/Pavlo CMU SCS 15-415/615 30 CMU SCS CMU SCS Nested Queries Nested Queries • Find all courses that nobody is enrolled in. • Find all courses that nobody is enrolled in. SELECT * FROM course SELECT * FROM course WHERE NOT EXISTS( WHERE NOT EXISTS( SELECT * FROM enrolled tuples in the ‘enrolled’ table ) WHERE course.cid = enrolled.cid ) cid name Karate101 Karate Kid Aerobics Faloutsos/Pavlo CMU SCS 15-415/615 31 Faloutsos/Pavlo CMU SCS 15-415/615 32