Class Note for CMPSCI 645 at UMass(4)
Class Note for CMPSCI 645 at UMass(4)
Popular in Course
Popular in Department
This 54 page Class Notes was uploaded by an elite notetaker on Friday February 6, 2015. The Class Notes belongs to a course at University of Massachusetts taught by a professor in Fall. Since its upload, it has received 14 views.
Reviews for Class Note for CMPSCI 645 at UMass(4)
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 02/06/15
SQL CMPSCI 645 SQL Overview SQL Preliminaries Nested queries Integrity constraints Correlat39on Null values Query capabilities M df h d b SELECTFROM o iyingt e ata ase WHERE blocks Views Basic features ordering duplicates Set ops union intersect except Aggregation amp Grouping Review in the textbook Ch 5 The SQL Query Language Structured Query Language Developed by IBM system R in the 1970s Need fora standard since it is used by many vendors Evolving standard SOL86 SOL89 minor revision SOL92 major revision SQL99 major extensions SOL2003 minor revisions Two parts of SQL Data Definition Language DDL Createaterdeete tables and their attributes estabish and modify schema Data Manipulation Language DML Query and modify database instance Creating Relations in SQL Creates the Student relation CREATE TABLE Student Observe that the type domain sid CHARQO of each field is specified and name CHARQO enforced by the DBMS loglnCHAR1039 age INTEGER whenever tuples are added or a REAL modified gp As another example the I CREATE TABLE Takes Takes table holds Information Sid CHARQO about courses that students Cid CHARQO take grade CHAR2 Characters CHAR20 Numbers MONEY VARCHAR40 Data Types in SQL fixed length variable length BIGINT INT SMALLINT TINYINT REAL FLOAT differ in precision Times and dates DATE DATETIME Others Destroying and Altering Relations DROP TABLE Student Destroys the relation Student The schema information and the tuples are deleted ALTER TABLE Student ADD COLUMN firstYear integer The schema of Student is altered by adding a new field every tuple in the current instance is extended with a null value in the new field Integrity Constraints le IC condition that must be true for any instance of the database le are specified when schema is defined le are checked when relations are modified A legal instance of a relation is one that satisfies all specified le DBMS should only allow legal instances If the DBMS checks le stored data is more faithful to realworld meaning Avoids data entry errors too Key Constraints A set of fields is a key for a relation if 1 No two distinct tuples can have same values in all key fields and 2 This is not true for any subset of the key If part 2 false then fields are a superkey If there s more than one key for a relation one of the keys is chosen by DBA to be the primary key Eg sid is a key for Students What about name The set sid gpa is a superkey Student table STUDENT 50000 Dave davecs 19 32 53666 Jones jonescs 18 33 53688 Smith smithee 18 32 53650 Smith smithmath 19 37 53831 Madayan madayanmusic 11 18 53832 Guldu guldumusic 12 20 1O Specifying Key Constraints in SQL CREATE TABLE Student Sid CHAR20 name CHAR20 login CHAR10 age INTEGER gpa REAL UNIQUE name age PRIMARY KEY Sid 11 Primary and Candidate Keys in SQL o Possibly many candidate keys specified using UNIQUE one of which is chosen as the primary key ozo For a given student and course CREATE TABLE Takes there is a single grade vs Sid CHARQO Students can take only one course Cid CHAR20 and receive a single grade for that grade CHAR2 course further no two students in a PRIMARY KEY sidcid course receive the same grade CREATE TABLE Takes to Used carelessly an IC can prevent Sid CHARQO the storage of database instances Cid CHARQO that arise in practice grade CHARQ PRIMARY KEY sid UNIQUE Cid grade Foreign Keys Referential Integrity Foreign key Set of fields in one relation that is used to refer to a tuple in another relation Must correspond to primary key of the second relation Like a logical pointer Eg sid is a foreign key referring to Students Takessid string Cid string grade string If all foreign key constraints are enforced referential intearitv is achieved ie no dangling references Can you name a data model wo referential integrity Links in HTML Foreign Keys in SQL Only students listed in the Students relation should be allowed to enroll for courses CREATE TABLE Takes sid CHAR20 cid CHAR20 grade CHAR2 PRIMARY KEY sidCid FOREIGN KEY sid REFERENCES Students STUDENT Takes 50000 Dave davecs 50000 445 A 53666 Jones jonescs 74 53688 Smith smithee 53666 435 B 53688 483 O 53650 Smith smithmath nforoing Referential Integrity o Consider Student and Takes sid in Takes is a foreign key that references Student What should be done if a Takes tuple with a nonexistent student id is inserted Reject it What should be done if a Student tuple is deleted Also delete all Takes tuples that refer to it Disallow deletion of a Students tuple that is referred to Set sid in Takes tuples that refer to it to a default sid In SQL also Set sid in Takes tuples that refer to it to a special value null denoting unknown or inapplicable Similar if primary key of Students tuple is updated Referential Integrity in SQL SOL92 and SQL1999 support all CREATE TABLE Takes 4 options on deletes and updates Sid CHARQO Default is NO ACTION delete update is rejected CASCADE also delete all tuples that refer to deleted cid CHAR20 grade CHAR2 PRIMARY KEY sidCid tuple FOREIGN KEY sid SET NULL SET DEFAULT sets REFERENCES Students foreign key value of ON DELETE CASCADE referencing tUIDIe ON UPDATE SET DEFAULT Where do le Come From le are based upon the semantics of the realworld enterprise that is being described in the database relations We can check a database instance to see if an IC is violated but we can NEVER infer that an IC is true by looking at an instance An IC is a statement about all possible instances From example we know name is not a key but the assertion that sid is a key is given to us Key and foreign key le are the most common more general le supported too SQL Overview Query capabilities SELECT FROMWHERE blocks Basic features ordering duplicates Set operations union intersect except Aggregation amp Grouping Nested queries correlation Null values 18 Example database Sailors sname rating age Boat bname color ves sid bid day Key for each table indicated by underlined attributes 19 W W 7 1 M M 1 3 g my Reserves Sailors Boats SQL Query Basic form plus many many extensions SELECT DISTINCT targetlist FROM relationlist WHERE qualification conditions For example SELECT sid sname rating age FROM Sailars WHERE age gt 21 Basic SQL Query o 2 A list of attributes of relations in relation list 0 r A list of relation names possibly with a rangevariable after each name lt Comparisons Attr op const or Attr1 opAttr2 where op is one of lt gt s 2 7 combined using AND OR and NOT 0 is an optional keyword indicating that the answer should not contain duplicates Default is that duplicates are noteliminated Simple SQL Query Sailors 22 dustin 7 45 31 lubber 8 555 58 rusty 10 35 71 zorba 10 16 E g 22 dustin 7 45 31 lubber 8 555 58 rusty 1 0 35 Conditions in the WHERE clause are like selection Cagelt21 Selection conditions What goes in the WHERE clause xyxlty xlty x y etc For number they have the usual meanings For CHAR and VARCHAR lexicographic ordering For dates and times what you expect Also pattern matching on strings s LIKE p The LIKE operator s LIKE p pattern matching on strings p may contain two special symbols 7 any sequence of Charaders 7 7 any single Charader Find aii Students wnose name begins and ends Witn b39 SELECT FROM Sailors WHERE sname LIKE bb Simple SQL Query Sailors 22 dustin 7 45 31 lubber 8 555 58 rusty 1O 35 71 zorba 1O 16 dustin 45 iii 5 Z lubber 555 rusty 35 Conditions in the SELECT clause are like projection Hsnameage Note confusing terminology Conditions in the WHERE clause are like selection Oagelt21 Conditions in the SELECT clause are like projection Hsnameage Eliminating Duplicates SELECT DISTINCT sname FROM Sailors Compare to SELECT sname FROM Sailors Default behavior does not eliminate duplicates Ordering the Results SELECT sname rating age FROM Sailors WHERE agegt18 ORDER BY rating sname Ordering is ascending unless you specify the DESC keyword Ties are broken by the second attribute on the ORDER BY list etc Conceptual Evaluation Strategy o Semantics of an SQL query defined in terms of RA a conceptual evaluation strategy equiv Compute the crossproduct of relationlist X Discard resulting tuples if they fail qualifications 0 Delete attributes that are not in targetlist H If DISTINCT is specified eliminate duplicate rows 0 Probably the least efficient way to compute a query optimizer will find more efficient plan Example of Conceptual Evaluation snaroe rating age E m d 22 mm 7 45 C 22 101 101096 31 lubber 8 555 58 rusty 10 350 58 103 111296 SELECT Ssname FROM Sailors 5 Reserves R WHERE SsidRsid AND Rbid103 sid sname rating age sid bid day 22 dustin 7 450 22 101 10 10 96 22 dustin 7 450 58 103 11 1296 31 lubber 8 555 22 101 10 10 96 31 lubber 8 555 58 103 11 12 96 58 rusty 10 350 22 101 10 10 96 58 rusty 10 350 58 103 11 12 96 Example SELECT sname FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Bcolor red What does this query compute Find the names of sailors Who have reserved a red boat 32 Please write in SQL Find the colors of boats reserved by Lubber SELECT Bcolor FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Ssname Lubber 33 Renaming Columns Sailors 22 dustin 7 45 31 lubber 8 555 58 rusty 10 35 71 zorba 10 16 dustin 45 lubber 555 rusty 35 Disambiguating Attributes Sometimes two relations have the same attr Personpname address worksfor Companycname address Which address SELECT DISTINCT pname address FROM Person Company WHERE worksfor oname SELECT DISTINCT Personpname Companyaddress FROM Person Company WHERE Personworksfor Companycname Range Variables in SQL Purchase buyer seller store product Find all stores that sold at least one product that was sold at BestBuy SELECT DISTINCT xstore FROM Purchase AS x Purchase AS y WHERE xproduct yproduct AND ystore BestBuy Please write in SQL Selfjoin on Flights The departure and arrival cities of trips consisting of two direct flights SELECT F1depart F2arrive FROM Flights as F1 Flights as F2 WHERE F1 arrive F2depart FLIGHTS NYC Reno NYC Oakland Boston Tampa Oakland Boston Tampa NYC a7 SQL Overview Query capabilities SELECT FROMWHERE blocks Basic features ordering duplicates Set operations union intersect except Aggregation amp Grouping Nested queries correlation Null values 38 Set operations UNION INTERSECTION EXCEPT sometimes called MINUS Recall schemas must match for these operations 39 UNION example Find the names of sailors Who have reserved a red or a green boat SELECT sname FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Bcolor red UNION SELECT sname FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Bcolor green 40 UNION Duplicates ARE NOT eliminated by default in basic SELECTFROMWHERE queries Duplicate ARE eliminated by default for UNION queues To preserve duplicates in UNION you must use UNION ALL 41 UNION example alternative Find the names of sailors Who have reserved a red or a green boat SELECT DISTINCT sname FROM Sailors S Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Bcolor red OR Bcolor green 42 Find the names of sailors Who have reserved a red or a green boat Find the names of sailors Who have reserved a red and a green boat SELECT sname FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Bbid Bbid AND Bcolor red AND Bcolor green This doesn t work What does this query return Find the names of sailors Who have reserved a red and a green boat SELECT sname FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Bcolor red INTERSECT SELECT sname FROM Sailors 8 Reserves R Boats B WHERE Ssid Rsid AND Rbid Bbid AND Bcolor green SQL Overview Query capabilities SELECT FROMWHERE blocks Basic features ordering duplicates Set ops union intersect except Aggregation amp Grouping 45 Aggregation SELECT AvgSage FROM Sailors WHERE Srating 10 SQL supports several aggregation operations Aggregation Count SELECT Coumm FROM Sailors WHERE rating gt 5 Except for COUNT all aggregations applyto a single attribute Aggregation Count COUNT applies to duplicates unless otherwise stated SELECT Countcategory FROM Product WHERE year gt 1995 Better m FROM Product WHERE year gt 1995 Simple Aggregation Purchaseproduct date price quantity Example 1 find total sales for the entire database SELECT Sumprice quantity FROM Purchase Example 1 find total sales of bagels SELECT Sumprice quantity FROM Purchase WHERE product bagel GROUP BY and HAVING Clauses We often want to apply aggregates to each of a number of groups of rows in a relation Find the age of the youngest sailor for each rating level SELECT MIN Sage FROM Sailors 8 WHERE Srating i Fori1210 50 Sailors Grouping SELECT Srating MNSage FROM Sailors S GROUP BY Srating New Table Queries With GROUP BY and HAVING WM M Hi i N oThe targetlistoontains i attribute names ii terms with aggregate operations eg MIN Sage The attribute list i must be a subset of grouping list Intuitively each answer tuple corresponds to a group and these attributes must have a single value per group Conceptual Evaluation oThe crossproduct of relationlist is computed tuples that fail qualification are discarded unneoessary fields are deleted and the remaining tuples are partitioned into groups by the value of attributes in groupinglist oThe groupqualification is then applied to eliminate some groups Expressions in group quaifioation must have a single value per group oOne answer tuple is generated per qualifying group 53 39 Find age of the youngest sailor with age 218 for each rating with at least 2 such sailors SELECT Srating MIN Sage sailors Instance AS minage sname rating age FROM Sailors S 22 dustin 7 450 WHERE sage gt 18 29 brutus 1 330 GROUP BY Srating 31 lubber 8 555 HAVING COUNT gt 1 32 andy 8 255 58 rusty 10 350 64 horatio 7 350 rating minage 71 zorba 10 160 Answer relation 3 255 74 horatio 9 350 7 350 85 art 3 255 8 255 95 bob 3 635 96 frodo 3 255 40
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'