Class News • HW2 due on Monday – email SQL scripts to Xueyuan CSPP 53001: Databases • HW3 will be out this weekend • Midterm in first half of class on November 5 (5:15pm - 6:45pm) Svetlozar Nestorov – open book/notes Overview Multirelation Queries • Multirelation SQL queries • List of relations in the FROM clause • Subqueries • Relation-dot-attribute disambiguates attributes with the same name from • Aggregation several relations. • Schema declarations • Example: find all beers that Hopleaf • Modifications regulars like. Frequents(drinker,bar) Likes(drinker,beer) Equivalent Operational Formal Semantics Semantics 1. Start with the product of all relations in • Consider a tuple variable for each relation the FROM clause. in the FROM clause 2. Apply σ (bag), using condition in WHERE • Imagine a series of nested loops over these clause. variables that produces every possible 3. Apply π (extended, bag) using attributes in combination of tuples, one from each of the SELECT clause. the relations in the FROM clause. 1. For each tuple combination, check if it The same as single-relation query with the satisfies the WHERE clause addition of product of all relations. 2. If so, print the values of terms in the SELECT clause 1
Explicit Tuple Variables Example Query • Sometimes we need to refer to two or SELECT A.beer, B.beer more copies of the same relation. FROM Likes A, Likes B • Use tuple variables as aliases of the WHERE A.drinker = B.drinker relations. AND A.beer <> B.beer • Example: find pairs of beers liked by the same drinker. Likes(drinker,beer) More Examples Subqueries • Find all bars that sell two different • Result of a query can be used in the beers at the same price. WHERE clause of another query. • Find all bars that sell three different • The former is called a subquery. beers at the same price. • Simplest case: subquery returns a • Find all drinkers that frequent a bar single unary tuple (a single atomic that serves their favorite beer. value). Example Scoping Rules • Find bars that serve Bud at the same price • Attribute name refers to the most Hopleaf charges for Stella. closely nested relation with that attribute. SELECT bar FROM Sells • Parenthesis around subquery are essential. WHERE beer = “Bud” AND price = (SELECT price FROM Sells WHERE beer = “Stella” AND bar = “Hopleaf”) 2
Problems with Example Solution? The IN Operator • What can go wrong? • tuple IN relation • Returns true if the tuple is in the relation. • How do we fix it? • Find the names and manufactures of beers that Janos likes. Likes(drinker,beer) Beers(name,manf) The EXISTS Operator Unique Beers • EXISTS(relation) SELECT name • Returns true if the relation contains at FROM Beers B least one tuple (nonempty) WHERE NOT EXISTS • Find the beers that are the unique beer (SELECT * by their manufacturer in the Beers FROM Beers relation. WHERE manf = B.manf AND name <> B.name) Correlated Subquery Quantifiers • Refer to outer Beers in the subquery by • ANY is existential quantifier. giving it a tuple variable (alias) to distinguish attribute condition ANY (subquery) from Beers in the subquery. At least one tuple in the relation satisfies the • A suqbquery that refers to values of a condition. surrounding query is called a correlated • ALL is universal quantifier. subquery. attribute condition ALL (subquery) • A correlated subquery must be evaluated (by All tuples in the relation satisfy the condition. the database system) for every tuple in the outer query. 3
Quantifier Example 1 Quantifier Example 2 • Find the beer(s) sold for the highest • Find the beer(s) not sold for the lowest price (among all bars). price (among all bars). Set Operators Intersect Example • UNION, INTERSECT, EXCEPT • Find the drinkers and beers such that the drinker likes the beer and frequents a bar • (subquery) UNION (subquery) that serves it. • Only UNION is supported in MySQL (SELECT * FROM Likes) but you can write equivalent queries INTERSECT for the the other two set operators. (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar) Forcing Set or Bag Semantics Distinct Example • Default for select-from-where queries • Find all different prices charged for is bag; default for union is set. beers. – Bag is chosen for performance. • Force set semantics (no duplicates) with DISTINCT after SELECT. • Force bag semantics with ALL after UNION. 4
Aggregation Average Price of Bud • Aggregate-function(attribute) • Find the average price of Bud. • SUM, AVG, MIN, MAX, COUNT SELECT AVG(price) • COUNT(*) applies to tuples FROM Sells • Use them in SELECT clause. WHERE beer = “Bud” Eliminating Duplicates Grouping Before Aggregation • Find the number of different prices at • Aggregation applied to several subsets which Bud is sold. of your relation grouped together by some condition. SELECT COUNT(DISTINCT price) – So far, the entire relation was aggregated. FROM Sells • GROUP BY clause with a list of WHERE beer = “Bud” attributes. • DISTINCT can be used in any aggregation but typically only makes sense with COUNT. Grouping Example 1 Grouping Explanation Find the average price for each beer. • The relation that is the result of the FROM and WHERE clauses is grouped according to the values of the SELECT beer, AVG(price) attributes of the GROUP BY clause FROM Sells and aggregations take place within GROUP BY beer each group. • One result tuple is produced from each group. 5
Restrictions on SELECT Grouping Example 2 Lists with Aggregation • For each drinker, find the average • If aggregation with grouping is used, price of Bud at the bars they frequent. then each element of the SELECT clause must be either: 1. Aggregated 2. Appear in the GROUP BY clause Cheapest Place for Bud HAVING clause • First attempt: • Selections on groups just as WHERE clauses are selections on tuples. • Conditions can use tuple variables or SELECT bar, MIN(price) relations in the FROM clause and their FROM Sells attributes. WHERE beer = “Bud” – Tuple variables range only over the group. – Attributes must be grouping attributes or be • Illegal query in SQL! aggregated. • How do we find the bar? HAVING example Another HAVING example • Find the average price of those beers that are either served in at least 3 bars or • For each manufacturer, find the beer manufactured by InBev. with the highest average price. SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(*) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = “InBev”) 6
Defining a Database Schema Create/Drop Example CREATE TABLE name (list-of-elements) DROP TABLE IF EXISTS Sells; • Principal elements are attributes and their CREATE TABLE Sells ( types but key declarations and constraints bar CHAR(20), also appear. beer VARCHAR(20), • Similar CREATE X commands for other schema elements X: indexes, views, price REAL assertions, triggers. ) • DROP TABLE name DROP X name for schema elements X. Data Types Date and Time Types • INT or INTEGER • Default date format is – Varying precision DATE’yyyy-mm-dd’ but can be changed. • REAL or FLOAT • Default time format is • CHAR(n) fixed length character string. TIME ‘hh:mm:ss[.ss…]’ • VARCHAR(n) variable length character • DATETIME string with up to n characters • TIMESTAMP • TEXT, BLOB – In MySQL, the first TIMESTAMP column in a • ENUM(val1,val2,…) table is automatically set to the date and time of the most recent operation except when the operation explicitly changed it. Declaring Keys Where to Declare Keys • PRIMARY KEY or UNIQUE • Two places to declare keys: • Only one primary key is allowed, but 1. After an attribute’s type, if the there can be many uniques. attribute is a key by itself. • SQL does not allow nulls in primary 2. As a separate element key, but allows them in unique • Essential for multi-attribute keys. attributes (more than one null value is allowed). 7
Key/Unique Example Attribute Properties • NOT NULL the attribute value cannot DROP TABLE IF EXISTS Sells; be null. CREATE TABLE Sells ( • DEFAULT value, the value is used bar CHAR(20), whenever no other value of the beer VARCHAR(20), attribute is known (without default null price REAL, will be used). invoice INT UNIQUE, PRIMARY KEY (bar, beer) ) Database Modifications Insert • Change the current relation instance! INSERT INTO relation VALUES(lits-of-values) • Results of modifications last beyond your current session! • Insert the tuple defined by the list of values, associating values with attributes in the • Insert a new tuple order the attributes were declared. • Delete a current tuple – You can also list the attributes as arguments of the relation. • Update a current tuple INSERT INTO Likes(beer,drinker) – Change the value of one or more of its VALUES(“Bud”,”Jane”) attributes Insert the Result of a Query Potential Buddies INSERT INTO relation INSERT INTO PotBuddies (subquery) (SELECT DISTINCT B.drinker • Example: Find potential drinking buddies FROM Frequents A, Frequents B for Jane by selecting all drinkers who WHERE A.drinker = “Jane” frequent a bar that Jane frequents. AND B.drinker <> “Jane” CREATE TABLE PotBuddies ( AND A.bar = B.bar) name char(30) ) 8
Recommend
More recommend
