1 / 77

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications. C. Faloutsos – A. Pavlo Lecture#6: Fun with SQL (part2). Administrivia. HW1 is due today. HW2 is out. Last Class. SELECT/INSERT/UPDATE/DELETE NULLs Output Redirection/Control Aggregates. Today's Party. DDLs

zuzana
Download Presentation

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Carnegie Mellon Univ.Dept. of Computer Science15-415/615 - DB Applications C. Faloutsos – A. Pavlo Lecture#6: Fun with SQL (part2)

  2. Administrivia • HW1 is due today. • HW2 is out. CMU SCS 15-415/615

  3. Last Class • SELECT/INSERT/UPDATE/DELETE • NULLs • Output Redirection/Control • Aggregates CMU SCS 15-415/615

  4. Today's Party • DDLs • Complex Joins • Views • Nested Subqueries • Triggers • Database Application Example CMU SCS 15-415/615

  5. Example Database STUDENT ENROLLED CMU SCS 15-415/615

  6. Table Definition (DDL) CREATE TABLE <table-name>( [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).

  7. Table Definition Example CREATE TABLE student ( sidINT, name VARCHAR(16), login VARCHAR(32), age SMALLINT, gpaFLOAT ); CREATE TABLE enrolled ( sidINT, cid VARCHAR(32), grade CHAR(1) ); Integer Range Variable String Length Fixed String Length

  8. Common Data Types • CHAR(n), VARCHAR(n) • TINYINT, SMALLINT, INTEGER, BIGINT • NUMERIC(p,d), FLOAT DOUBLE, REAL • DATE, TIME • BINARY(n), VARBINARY(n), BLOB CMU SCS 15-415/615

  9. 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? CMU SCS 15-415/615

  10. Useful Non-standard Types • TEXT • BOOLEAN • ARRAY • Some systems also support user-defined types. CMU SCS 15-415/615

  11. Integrity Constraints CREATE TABLE student ( sidINT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT CHECK (age > 0), gpaFLOAT ); CREATE TABLE enrolled ( sidINT REFERENCESstudent (sid), cid VARCHAR(32) NOT NULL, grade CHAR(1), PRIMARY KEY (sid, cid) ); PKey Definition Type Attributes FKey Definition

  12. Primary Keys • Single-column primary key: • Multi-column primary key: CREATE TABLE student ( sid INT PRIMARY KEY, ⋮ CREATE TABLE student ( ⋮ PRIMARY KEY (sid, name) CMU SCS 15-415/615

  13. Foreign Key References • Single-column reference: • Multi-column reference: CREATE TABLE enrolled ( sid INT REFERENCES student (sid), ⋮ CREATE TABLE enrolled ( ⋮ FOREIGN KEY (sid, …) REFERENCES student (sid, …) CMU SCS 15-415/615

  14. Foreign Key References • You can define what happens when the parent table is modified: • CASCADE • RESTRICT • NO ACTION • SET NULL • SET DEFAULT CMU SCS 15-415/615

  15. Foreign Key References • Delete/update the enrollment information when a student is changed: CREATE TABLE enrolled ( ⋮ FOREIGN KEY (sid) REFERENCES student (sid) ONDELETE CASCADE ON UPDATE CASCADE CMU SCS 15-415/615

  16. Value Constraints • Ensure one-and-only-one value exists: • Make sure a value is not null: CREATE TABLE student ( login VARCHAR(32) UNIQUE, CREATE TABLE enrolled ( cid VARCHAR(32) NOT NULL, CMU SCS 15-415/615

  17. Value Constraints • Make sure that an expression evaluates to true for each row in the table: • Can be expensive to evaluate, so tread lightly… CREATE TABLE enrolled ( age SMALLINT CHECK (age > 0), CMU SCS 15-415/615

  18. 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 CMU SCS 15-415/615

  19. 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); CMU SCS 15-415/615

  20. Conditional Table Creation • IFNOTEXISTS prevents the DBMS from trying to create a table twice. CREATE TABLE IF NOT EXISTSstudent ( sidINT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT CHECK (age > 0), gpaFLOAT ); CMU SCS 15-415/615

  21. Dropping Tables • Completely removes a table from the database. Deletes everything related to the table (e.g., indexes, views, triggers, etc): • Can also use IFEXISTS to avoid errors: DROP TABLE student; DROP TABLEIF EXISTS student; CMU SCS 15-415/615

  22. Modifying Tables • SQL lets you add/drop columns in a table after it is created: • This is really expensive!!! Tread lightly… ALTER TABLE student ADDCOLUMN phone VARCHAR(32) NOT NULL; ALTER TABLE student DROPCOLUMN login; CMU SCS 15-415/615

  23. 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); CMU SCS 15-415/615

  24. 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. CMU SCS 15-415/615

  25. Accessing Table Schema • List all of the tables in the current database: SELECT * FROMINFORMATION_SCHEMA.TABLES WHEREtable_catalog = '<db name>' Postgres \d; MySQL SHOW TABLES; SQLite .tables; CMU SCS 15-415/615

  26. Accessing Table Schema • List the column info for the student table: SELECT * FROMINFORMATION_SCHEMA.COLUMNS WHEREtable_name = 'student' Postgres \d student; MySQL DESCRIBE student; SQLite .schema student; CMU SCS 15-415/615

  27. Today's Party • DDLs • Complex Joins • Views • Nested Subqueries • Triggers • Database Application Example CMU SCS 15-415/615

  28. Example Database STUDENT ENROLLED COURSE CMU SCS 15-415/615

  29. Join Query Grammar SELECT ... FROMtable-name1 join-type table-name2 ON qualification [WHERE ...] • 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 ≥. CMU SCS 15-415/615

  30. INNER JOIN SELECT name, cid, grade FROM studentINNER JOIN enrolled ONstudent.sid = enrolled.sid

  31. INNER JOIN • Short-hand version SELECT student.sid, cid, grade FROM student, enrolled WHERE student.sid = enrolled.sid CMU SCS 15-415/615

  32. OUTER JOIN SELECT student.sid, cid, grade FROM studentLEFT OUTER JOIN enrolled ONstudent.sid = enrolled.sid

  33. OUTER JOIN SELECT student.sid, cid, grade FROM enrolledRIGHT OUTER JOIN student ONstudent.sid = enrolled.sid

  34. Join Types SELECT * FROM A JOIN B ON A.id = B.id CMU SCS 15-415/615

  35. Today's Party • DDLs • Complex Joins • Views • Nested Subqueries • Triggers • Database Application Example CMU SCS 15-415/615

  36. 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! CMU SCS 15-415/615

  37. View Example • Create a view of the CS student records with just their id, name, and login. CREATE VIEW CompSciStudentInfoAS SELECT sid, name, login FROM student WHERE login LIKE ‘%@cs’; Original Table View

  38. View Example • Create a view with the average age of the students enrolled in each course. CREATE VIEW CourseAgeAS SELECT cid, AVG(age) ASavg_age FROM student, enrolled WHERE student.sid = enrolled.sid GROUP BYenrolled.cid;

  39. Views vs. SELECT INTO CREATE VIEWAvgGPA 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.

  40. Today's Party • DDLs • Complex Joins • Views • Nested Subqueries • Triggers • Database Application Example CMU SCS 15-415/615

  41. Nested Queries • Queries containing other queries • Inner query: • Can appear in FROM or WHERE clause “outer query” “inner query” SELECT cnameFROM borrower WHERE cname IN (SELECT cnameFROM depositor) Think of this as a function that returns the result of the inner query

  42. Nested Queries • Find the names of students in ‘Massage105’ SELECT name FROM student WHERE ... • “sid in the set of people that take Massage105” CMU SCS 15-415/615

  43. Nested Queries • Find the names of students in ‘Massage105’ SELECT name FROM student WHERE ... SELECT sid FROM enrolled WHERE cid = ‘Massage105’ CMU SCS 15-415/615

  44. Nested Queries • Find the names of students in ‘Massage105’ SELECT name FROM student WHERE sid IN ( SELECT sid FROM enrolled WHERE cid = ‘Massage105’ ) CMU SCS 15-415/615

  45. 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. CMU SCS 15-415/615

  46. Nested Queries • Find the names of students in ‘Massage105’ SELECT name FROM student WHERE sid = ANY( SELECT sid FROM enrolled WHERE cid = ‘Massage105’ ) CMU SCS 15-415/615

  47. Nested Queries • Find student record with the highest id. • This won’t work in SQL-92: • Runs in MySQL, but you get wrong answer: X SELECT MAX(sid), name FROM student; CMU SCS 15-415/615

  48. Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE ... • “is greater than every other sid” CMU SCS 15-415/615

  49. Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE sid SELECTsidFROM enrolled • is greater than every CMU SCS 15-415/615

  50. Nested Queries • Find student record with the highest id. SELECT sid, name FROM student WHERE sid=> ALL( SELECTsidFROM enrolled ) CMU SCS 15-415/615

More Related