Administrivia Carnegie Mellon Univ. HW1 is due today. Dept. of - PowerPoint PPT Presentation

CMU SCS CMU SCS Administrivia Carnegie Mellon Univ. • HW1 is due today. Dept. of Computer Science • HW2 is out. 15-415/615 - DB Applications C. Faloutsos – A. Pavlo Lecture#6: Fun with SQL (Part 1) Faloutsos/Pavlo CMU SCS 15-415/615 2 CMU SCS CMU SCS Homework #2: Bike-Share Data Relational Languages • For each question, generate a SQL query • A major strength of the relational model: that computes the answer. supports simple, powerful querying of data. – It will test automatically when you submit. • User only needs to specify the answer that – Column names are not important but order is they want, not how to compute it. • You can use Postgres on your laptop or on • The DBMS is responsible for efficient one of the Andrews machines. evaluation of the query. – Check the “Grade Center” on Blackboard for – Query optimizer: re-orders operations and your machine and port number. generates query plan Faloutsos/Pavlo CMU SCS 15-415/615 3 CMU SCS 15-415/615 4

CMU SCS CMU SCS Relational Languages History • Standardized DML / DDL • Originally “SEQUEL” from IBM’s – DML → Data Manipulation Language System R prototype. – DDL → Data Definition Language – S tructured E nglish Que ry L anguage – Adopted by Oracle in the 1970s. • Also includes: – View definition • ANSI Standard in 1986, ISO in 1987 – Integrity & Referential Constraints – S tructured Q uery L anguage – Transactions CMU SCS 15-415/615 5 CMU SCS 15-415/615 6 CMU SCS CMU SCS History Today's Class: OLTP • Current standard is SQL:2011 • Basic Queries – SQL:2011 → Temporal DBs, Pipelined DML • Table Definition (DDL) – SQL:2008 → TRUNCATE, Fancy ORDER • NULLs – SQL:2003 → XML, windows, sequences, auto-generated IDs. • String/Date/Time/Set/Bag Operations – SQL:1999 → Regex, triggers, OO • Output Redirection/Control • Most DBMSs at least support SQL-92 • System Comparison: – http://troels.arvin.dk/db/rdbms/ CMU SCS 15-415/615 7 Faloutsos/Pavlo CMU SCS 15-415/615 8

CMU SCS CMU SCS Example Database First SQL Example sid cid grade STUDENT ENROLLED Find the course ids where SELECT cid 53666 15-415 C 53688 15-721 A FROM enrolled students received a grade of ‘C’ sid name login age gpa sid cid grade 53688 15-826 B WHERE grade = ‘C’ 53655 15-415 C 53666 Kayne kayne@cs 39 4.0 53666 15-415 C in the course. 53688 Bieber jbieber@cs 22 3.9 53688 15-721 A 53666 15-721 C 53655 Tupac shakur@cs 26 3.5 53688 15-826 B Similar to… 53655 15-415 C π cid ( σ grade=‘C’ ( enrolled )) 53666 15-721 C But not quite…. cid cid 15-415 15-415 Duplicates 15-721 15-415 15-721 Faloutsos/Pavlo CMU SCS 15-415/615 9 CMU SCS 15-415/615 10 CMU SCS CMU SCS First SQL Example Multi-Relation Queries sid cid grade sid cid grade SELECT name, cid 53666 15-415 C 53666 15-415 C SELECT DISTINCT cid Get the name of the student and FROM student, enrolled 53688 15-721 A 53688 15-721 A FROM enrolled WHERE student.sid = the corresponding course ids 53688 15-826 B 53688 15-826 B WHERE grade = ‘C’ enrolled.sid 53655 15-415 C 53655 15-415 C where they received a grade of 53666 15-721 C 53666 15-721 C AND enrolled.grade = ‘C’ ‘C’ in that course. Now we get the same result sid name login age gpa as the relational algebra 53666 Kayne kayne@cs 39 4.0 Same as 53688 Bieber jbieber@cs 22 3.9 53655 Tupac shakur@cs 26 3.5 cid Why preserve duplicates? π name, cid ( σ grade=‘C’ (student ⋈ enrolled )) 15-415 • Eliminating them is costly name cid 15-721 • Users often don’t care. Kayne 15-415 Tupac 15-415 Kayne 15-721 CMU SCS 15-415/615 11 12

CMU SCS CMU SCS SELECT Clause Basic SQL Query Grammar SELECT [ DISTINCT | ALL ] target-list • Use * to get all attributes FROM relation-list SELECT * FROM student [ WHERE qualification ] SELECT student.* FROM student • Relation-List : A list of relation names • Use DISTINCT to eliminate dupes • Target-List : A list of attributes from the tables referenced in relation-list SELECT DISTINCT cid FROM enrolled • Qualification : Comparison of attributes or • Target list can include expressions constants using operators =, ≠, <, >, ≤, and ≥. SELECT name, gpa*1.05 FROM student CMU SCS 15-415/615 13 CMU SCS 15-415/615 14 CMU SCS CMU SCS FROM Clause WHERE Clause • Complex expressions using AND , OR , and NOT • Binds tuples to variable names SELECT * FROM enrolled SELECT * FROM student, enrolled WHERE grade = ‘C’ WHERE student. sid = enrolled. sid AND (cid = ‘15-415’ OR NOT cid = ‘15-826’) • Define what kind of join to use SELECT student.*, enrolled.grade • Special operators BETWEEN , IN : FROM student LEFT OUTER JOIN enrolled WHERE student.sid = enrolled.sid SELECT * FROM enrolled WHERE (sid BETWEEN 56000 AND 57000) AND cid IN (‘15-415’, ‘15-721’) CMU SCS 15-415/615 15 CMU SCS 15-415/615 16

CMU SCS CMU SCS Renaming Renaming – Table Variables • Get the name of the students that took 15-415 • The AS keyword can also be used to rename and got an ‘A’ or ‘B’ in the course. tables and columns in SELECT queries. SELECT student.name, enrolled.grade • Allows you to target a specific table FROM student, enrolled instance when you reference the same table WHERE student.sid = enrolled.sid multiple times. AND enrolled.cid = ‘15-415’ AND enrolled.grade IN (‘A’, ‘B’) CMU SCS 15-415/615 17 CMU SCS 15-415/615 18 CMU SCS CMU SCS Renaming – Table Variables Renaming – Self-Join sid cid grade • Get the name of the students that took 15-415 • Find all unique students 53666 15-415 C and got an ‘A’ or ‘B’ in the course. 53688 15-721 A that have taken more than 53688 15-826 B 53655 15-415 C SELECT S.name, E.grade AS egrade one course. 53666 15-721 C FROM student AS S, enrolled AS E WHERE S.sid = E.sid AND E.cid = ‘15-415’ SELECT DISTINCT e1.sid AND E.grade IN (‘A’, ‘B’) FROM enrolled AS e1, enrolled AS e2 WHERE e1.sid = e2.sid AND e1.cid != e2.cid CMU SCS 15-415/615 19 CMU SCS 15-415/615 20

CMU SCS CMU SCS INSERT More SQL • INSERT • Provide target table, columns, and values for new tuples: • UPDATE INSERT INTO student • DELETE ( sid, name, login, age, gpa ) • TRUNCATE VALUES ( 53888 , ‘Drake’, ‘drake@cs’, 29, 3.5 ) • Short-hand version: INSERT INTO student VALUES ( 53888 , ‘Drake’, ‘drake@cs’, 29, 3.5 ) CMU SCS 15-415/615 21 CMU SCS 15-415/615 22 CMU SCS CMU SCS UPDATE DELETE • UPDATE must list what columns to update and • Similar to single-table SELECT statements. their new values (separated by commas). • The WHERE clause specifies which tuples will • Can only update one table at a time. deleted from the target table. • WHERE clause allows query to target multiple • The delete may cascade to children tables. tuples at a time. DELETE FROM enrolled WHERE grade = ‘F’ UPDATE student SET login = ‘kwest@cs’, age = age + 1 WHERE name = ‘Kayne’ CMU SCS 15-415/615 23 CMU SCS 15-415/615 24

CMU SCS CMU SCS TRUNCATE Today's Party: OLTP • Remove all tuples from a table. • Basic Queries • This is usually faster than DELETE , unless it • Table Definition (DDL) needs to check foreign key constraints. • NULLs TRUNCATE student • String/Date/Time/Set/Bag Operations • Output Redirection/Control CMU SCS 15-415/615 25 Faloutsos/Pavlo CMU SCS 15-415/615 26 CMU SCS CMU SCS Table Definition (DDL) Table Definition Example CREATE TABLE student ( CREATE TABLE <table-name> ( sid INT , [ column-definition ]* name VARCHAR( 16 ) , [ constraints ]* Integer Range ) [ table-options ]; login VARCHAR( 32 ) , age SMALLINT , • Column-Definition : Comma separated list gpa FLOAT ); Variable String Length of column names with types. CREATE TABLE enrolled ( • Constraints : Primary key, foreign key, and sid INT , other meta-data attributes of columns. cid VARCHAR( 32 ) , Fixed String Length • Table-Options : DBMS-specific options for grade CHAR( 1 ) ); the table (not SQL-92 ). 27 28

CMU SCS CMU SCS Common Data Types Useful Non-standard Types • CHAR( n ) , VARCHAR( n ) • TEXT • TINYINT , SMALLINT , INT , BIGINT • BOOLEAN • NUMERIC( p,d ) , FLOAT , DOUBLE , REAL • ARRAY • DATE , TIME • Geometric primitives • BINARY( n ) , VARBINARY( n ) , BLOB • XML/JSON • Some systems also support user-defined types. Faloutsos/Pavlo CMU SCS 15-415/615 29 Faloutsos/Pavlo CMU SCS 15-415/615 #30 CMU SCS CMU SCS Integrity Constraints Primary Keys CREATE TABLE student ( sid INT PRIMARY KEY , • Single-column primary key: name VARCHAR( 16 ) , PKey Definition CREATE TABLE student ( login VARCHAR( 32 ) UNIQUE , sid INT PRIMARY KEY , age SMALLINT CHECK ( age > 0 ) , ⋮ gpa FLOAT Type Attributes ); • Multi-column primary key: CREATE TABLE enrolled ( CREATE TABLE enrolled ( sid INT REFERENCES student ( sid ) , ⋮ cid VARCHAR( 32 ) NOT NULL , PRIMARY KEY (sid, cid) FKey Definition grade CHAR( 1 ), PRIMARY KEY ( sid, cid ) ); 31 Faloutsos/Pavlo CMU SCS 15-415/615 32