220 likes | 230 Views
C20.0046: Database Management Systems Lecture #15. M.P. Johnson Stern School of Business, NYU Spring, 2005. Summary: SQL queries. Only SELECT , FROM required Can’t have HAVING without GROUP BY Can have GROUP BY without HAVING Any clauses used must appear in this order:.
E N D
C20.0046: Database Management SystemsLecture #15 M.P. Johnson Stern School of Business, NYU Spring, 2005 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Summary: SQL queries • Only SELECT, FROM required • Can’t have HAVING without GROUP BY • Can have GROUP BY without HAVING • Any clauses used must appear in this order: SELECT LFROM Rs WHERE s GROUP BY L2 HAVING s2 ORDER BY L3 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Deletions DELETE FROM Table WHERE condition • General form: • E.g.: INSERT INTO Knights VALUES('R. Giuliani', 'n', 'KBE'); INSERT INTO Knights VALUES('Bernard Kerik', 'n', 'CBE'); DELETE FROM Knights WHERE name = 'Bernard Kerik'; M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Updates UPDATE Product SET field1 = value1, field2 = value2 WHERE condition • General form: • Example: UPDATE Product SET price = price/2 WHERE Product.name IN (SELECT product FROM Purchase WHERE Date = DATE'Oct, 25, 1999') M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Default Values • Specify defaults when creating table: • The default default: NULL CREATE TABLE People ( name VARCHAR(30), ssn CHAR(9), age SHORTINT DEFAULT 100, city VARCHAR(30) DEFAULT 'New York', gender BIT(1), dob DATE DEFAULT DATE '1900-01-01' ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Deleting and modifying schemata • Delete data, indices, schema: • Delete data and indices: • Either way, exercise extreme caution! • Add or delete attributes: DROP TABLE Person TRUNCATE TABLE Person ALTER TABLE Person ADD phone CHAR(12) Q: What’s put in the new fields? ALTER TABLE Person DROP age M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Creating Indices • Syntax: • Here: • Searches are speed up logarithmically • If all New Yorkers, #comparisons: • 8000000 log2(8000000) ~= 23 • (i.e., 223 ~= 8000000) • In general: • Indices speed up queries a lot • They slow down modifications somewhat • They also speed up some modifications… CREATE INDEX index-name ON R(fields) CREATE INDEX nameIndex ON Person(name) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Another BST example M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Constraints in SQL simplest Constraints in SQL: • Keys • foreign keys • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions The more complex the constraint, the harder it is to check and to enforce Mostcomplex M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Primary Keys & Unique Keys • There is at most one PRIMARY KEY; there can be many UNIQUE • Primary key v. candidate keys • Key fields get indices automatically (why?) CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (productID), UNIQUE (name, category) ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Foreign Key Constraints • prodName is a foreign key to Product(name) • name should be a key in Product • Purchase ~ Product is many-one • NB: referenced field specified with (), not dot • Referenced and referring fields should/must be indexed (why?) Referentialintegrityin SQL CREATE TABLE Purchase ( prodName CHAR(30) REFERENCES Product(name), date DATETIME ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Constraints on Attributes and Tuples • Constraints on attributes: • NOT NULL -- obvious meaning... • Can combine NOT NULL with foreign key constraints • CHECK condition -- any condition on row itself • Some DBMS support subqueries here, but many don’t • Constraints on tuples • CHECK condition M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Tuple-level constraint How is this different from aForeign Key? CREATE TABLE Purchase ( prodName CHAR(30) CHECK (prodName IN SELECT Product.name FROM Product), date DATETIME NOT NULL ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
General Assertions • Defined in latest SQL standard, but doesn’t really exist • Implemented/approximated in MySQL and Oracle as stored procedures • PL/SQL in Oracle CREATE ASSERTION myAssert CHECK (NOT EXISTS( SELECT Product.name FROM Product, Purchase WHERE Product.name = Purchase.prodName GROUP BY Product.name HAVING count(*) > 200) ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Final Comments on Constraints • Can give them names, and alter later or drop • Still possible if unnamed, but more work to find • Can define constraints • On fields – NOT NULL, CHECK • On tuples • As triggers that look at multiple tables • On whole databases (in theory) M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Final Comments on Constraints • Each more powerful than last, but more expensive to enforce • Like indices • On the other hand: • The more mistakes you make impossible, the better • The more mistakes you make impossible on the DBMS side, the fewer you have to check on the client side • Think of data validation M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Live examples • Q: produce a list of employees and their bosses • What if no boss? Or no subordinate? • Joins on emp, emp man: • Comma-based • Cross • Natural • Inner • Must use INNER JOIN in MySQL • If want non-managers, do outer join… • No FULL OUTER JOIN in MySQL yet M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Live examples • Q: produce list of bosses and underling-counts, for bosses with >1 underling • Just add HAVING clause… M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Finally: R.A./SQL has limitations • Can easily find Alice’s direct subordinates: • But: find all of King’s underlings • Cannot compute “transitive closure” • Cannot express in R.A./SQL! • SQL is not “Turing-Complete” M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Midterm • Know all steps of project so far: • Draw E/R from dataset description • Convert E/R to relations • Normalize badly designed relations • Indices: • Why important • How BSTs work • Constraints • Why important • Basic concepts • SQL! • http://pages.stern.nyu.edu/~mjohnson/dbms/dbmsmtinfo.html M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Midterm course evaluations • Please do midterm evals on Blackboard! • Worth 50 points on the midterm • Longish, but very helpful for me • Vote on interest levels for remaining possible topics • “We’re in touch, so you be in touch.” • Thanks! M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Live examples • Examples from sqlzoo.net SELECT L FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn) M.P. Johnson, DBMS, Stern/NYU, Spring 2005