CMSC424 Database Design Lecture 6 CMSC424 Spring 2005 1 SQL Introduction Standard DML DDL for relational DB s DML Data Manipulation Language queries updates DDL Data Definition Language create tables indexes Also includes View definition Security Authorization Integrity constraints Transactions CMSC424 Spring 2005 2 SQL Basic Structure SELECT FROM WHERE P A1 An r1 rm Equivalent to A1 A2 An P r1 rn CMSC424 Spring 2005 3 A Simple SELECT FROM WHERE Query SELECT FROM WHERE Similar to bname loan amt 1000 bname amt 1000 loan But not quite In general SQL will not remove duplicates unless asked to bname Redwood Perry Downtown Perry Duplicates are retained i e result not a set CMSC424 Spring 2005 4 A Simple SELECT FROM WHERE Query SELECT FROM WHERE Similar to DISTINCT bname loan amt 1000 bname amt 1000 loan Result bname Redwood Perry Downtown CMSC424 Spring 2005 5 Next Formal Semantics of SQL Bag or multiset semantics CMSC424 Spring 2005 6 Formal Semantics of SQL RA can only express SELECT DISTINCT queries To express SQL must extend RA to a bag algebra Bags aka multisets like sets but can have duplicates e g 5 3 3 e g homes cname ccity Johnson Smith Johnson Smith Brighton Perry Brighton R H Next will define RA a bag version of RA CMSC424 Spring 2005 7 Formal Semantics of SQL RA 1 p r preserves copies in r e g city Brighton homes 2 A1 An r cname ccity Johnson Johnson Brighton Brighton no duplicate elimination e g cname homes cname Johnson Smith Johnson Smith CMSC424 Spring 2005 8 Formal Semantics of SQL RA 3 r s additive union A B A B 1 1 2 A B 2 3 1 r 4 r s e g r s s 1 1 2 2 3 1 bag difference A B s r A B 3 1 CMSC424 Spring 2005 9 Formal Semantics of SQL RA 5 r s cartesian product A B C A B 1 1 2 C CMSC424 Spring 2005 1 1 1 1 2 2 10 Formal Semantics of SQL Query SELECT FROM WHERE a1 an r1 rm p Semantics A1 An p r1 rm Query SELECT DISTINCT FROM WHERE Semantics 1 a1 an r1 rm p What is the only operator to change in 1 A1 An p r1 rm CMSC424 Spring 2005 2 11 Next More SQL CMSC424 Spring 2005 12 More SQL AS 1 Using AS in FROM clause Introduces tuple variables e g SELECT DISTINCT T bname FROM branch AS T branch AS S WHERE T assets S assets returns branch names of branches with non minimal assets 2 Using AS in SELECT clause Renames columns in result p e g SELECT bname acct no balance 1 05 AS newbal FROM account returns bname acct no newbal Downtown Mianus A 101 A 215 525 735 CMSC424 Spring 2005 13 More SQL INTO Used to name query results e g SELECT DISTINCT bname INTO branchnames FROM branch Intuitively BranchNames SELECT DISTINCT bname FROM branch CMSC424 Spring 2005 14 More SQL Order by Example List in alphabetical order the names of all customers with loans at the Perry branch cname SELECT DISTINCT cname Result Adams FROM borrower AS b loan AS l Hayes WHERE b lno l lno AND bname Perry ORDER BY cname default ascending order asc Can also write ORDER BY cname DESC or ORDER BY cname ASC Like SELECT DISTINCT very expensive requires external sort cannot usually fit entire relation in memory instead must sort in chunks CMSC424 Spring 2005 15 More SQL Aggregate Operators Aggregate operators AVG col SUM col COUNT col average of values in col sum of values in col number of values in col MIN col MAX col minimun value in col maximun value in col Examples 1 Find the average acct balance Perry SELECT AVG bal FROM account WHERE bname Perry 2 Find the number of tuples in customer SELECT COUNT FROM customer 3 Find the number of unique depositors SELECT COUNT DISTINCT cname FROM customer CMSC424 Spring 2005 16 More SQL Aggregates Group By Usually aggregates used with GROUP BY Example SELECT bname COUNT DISTINCT cname FROM depositor AS d account AS a WHERE d acct no a acct no GROUP BY bname Result Extended relational grouping operator bname count Downtown Mianus Perry R H Brighton Redwood 1 1 1 1 2 1 G1 G2 Gn CMSC424 Spring 2005 g F1 A1 F2 A2 Fn An E 17 More SQL Aggregates Group By Intuition behind Group By SELECT bname COUNT DISTINCT cname FROM depositor AS d account AS a WHERE d acct no a acct no GROUP BY bname Step 1 Group result of join bname a acct no balance cname d acct no Downtown A 101 500 Johnson A 101 Mianus A 215 700 Smith A 215 Perry A 102 400 Hayes A 102 R H A 305 350 Turner A 305 Brighton Brighton A 201 A 217 900 750 Johnson Jones A 201 A 217 Redwood A 222 700 Lindsay A 222 CMSC424 Spring 2005 Step 2 Aggregate on groups and project on result bname count Downtown Mianus Perry R H Brighton Redwood 1 1 1 1 2 1 18 More SQL GROUP BY cont Another Example SELECT bname SUM assets as total FROM branch GROUP BY bcity Result Multiple names to choose from bname total Redwood 2 1M bcity Palo Alto Pownal 0 3 M bcity Bennington N Town 3 7 M bcity Rye 16 1 M bcity Brooklyn 10 1 M bcity Horseneck Above Query Not Allowed CMSC424 Spring 2005 19 More SQL GROUP BY cont Another Example SELECT bname SUM assets as total FROM branch GROUP BY bcity Above Query Not Allowed Non aggregated attributes in SELECT clause e g bname must also appear in GROUP BY clause SELECT FROM WHERE GROUP BY A1 Ak Agg1 Aggi A1 Ak CMSC424 Spring 2005 20 More SQL Having WHERE FROM as HAVING GROUP BY HAVING P selects rows from result of GROUP BY Optional missing HAVING clause HAVING TRUE Example Find names of branches and the average account balance for those branches having an account balance 1200 SELECT bname AVG balance AS avg FROM account GROUP BY bname HAVING avg 1200 same result as SELECT bname AVG balance AS avg INTO temp FROM account GROUP BY bname CMSC424 Spring 2005 SELECT FROM temp WHERE avg 1200 21 More SQL Set Bag Operations Set Operations Bag Operations UNION U INTERSECT EXCEPT UNION ALL U INTERSECT ALL EXCEPT ALL Duplicate Counting Given m copies of t in r n copies of t in s how many copies of t in r UNION ALL s A m n r INTERSECT ALL s A min m n r EXCEPT ALL s A max 0 m n CMSC424 Spring 2005 22 More SQL Set Bag Operations Example Queries SELECT cname FROM depositor SELECT cname FROM borrower UNION returns names of customers with savings accts loans or both INTERSECT returns names of customers with savings accts and loans EXCEPT returns names of customers with savings accts but not loans CMSC424 Spring 2005 23 SQL Summary Thus Far Clause Eval Order SELECT DISTINCT FROM WHERE …
View Full Document
Unlocking...