Introduction to SQL.ppt

1、1,Introduction to SQL,Select-From-Where Statements Subqueries Grouping and Aggregation,2,Why SQL?,SQL is a very-high-level language. Say “what to do” rather than “how to do it.” Avoid a lot of data-manipulation details needed in procedural languages like C+ or Java. Database management system figure

2、s out “best” way to execute query. Called “query optimization.”,3,Select-From-Where Statements,SELECT desired attributesFROM one or more tablesWHERE condition about tuples ofthe tables,4,Our Running Example,All our SQL queries will be based on the following database schema. Underline indicates key a

3、ttributes.Beers(name, manf)Bars(name, addr, license)Drinkers(name, addr, phone)Likes(drinker, beer)Sells(bar, beer, price)Frequents(drinker, bar),5,Example,Using Beers(name, manf), what beers are made by Anheuser-Busch?SELECT nameFROM BeersWHERE manf = Anheuser-Busch;,6,Result of Query,nameBudBud Li

4、teMichelob. . .,The answer is a relation with a single attribute, name, and tuples with the name of each beer by Anheuser-Busch, such as Bud.,7,Meaning of Single-Relation Query,Begin with the relation in the FROM clause. Apply the selection indicated by the WHERE clause. Apply the extended projectio

5、n indicated by the SELECT clause.,8,Operational Semantics,name,manf,Bud,Anheuser-Busch,9,Operational Semantics,To implement this algorithm think of a tuple variable ranging over each tuple of the relation mentioned in FROM. Check if the “current” tuple satisfies the WHERE clause. If so, compute the

6、attributes or expressions of the SELECT clause using the components of this tuple.,10,* In SELECT clauses,When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.” Example using Beers(name, manf):SELECT *FROM BeersWHERE manf = Anheuser-Busch;

7、,11,Result of Query:,name manfBud Anheuser-BuschBud Lite Anheuser-BuschMichelob Anheuser-Busch. . . . . .,Now, the result has each of the attributes of Beers.,12,Renaming Attributes,If you want the result to have different attribute names, use “AS ” to rename an attribute. Example based on Beers(nam

8、e, manf):SELECT name AS beer, manfFROM BeersWHERE manf = Anheuser-Busch,13,Result of Query:,beer manfBud Anheuser-BuschBud Lite Anheuser-BuschMichelob Anheuser-Busch. . . . . .,14,Expressions in SELECT Clauses,Any expression that makes sense can appear as an element of a SELECT clause. Example: from

9、 Sells(bar, beer, price):SELECT bar, beer,price * 114 AS priceInYenFROM Sells;,15,Result of Query,bar beer priceInYenJoes Bud 285Sues Miller 342 ,16,Another Example: Constant Expressions,From Likes(drinker, beer) :SELECT drinker,likes Bud AS whoLikesBudFROM LikesWHERE beer = Bud;,17,Result of Query,

10、drinker whoLikesBudSally likes BudFred likes Bud ,18,Complex Conditions in WHERE Clause,From Sells(bar, beer, price), find the price Joes Bar charges for Bud:SELECT priceFROM SellsWHERE bar = Joes Bar ANDbeer = Bud;,19,Patterns,WHERE clauses can have conditions in which a string is compared with a p

11、attern, to see if it matches. General form: LIKE or NOT LIKE Pattern is a quoted string with % = “any string”; _ = “any character.”,20,Example,From Drinkers(name, addr, phone) find the drinkers with exchange 555:SELECT name FROM Drinkers WHERE phone LIKE %555-_ _ _ _;,21,NULL Values,Tuples in SQL re

12、lations can have NULL as a value for one or more components. Meaning depends on context. Two common cases: Missing value : e.g., we know Joes Bar has some address, but we dont know what it is. Inapplicable : e.g., the value of attribute spouse for an unmarried person.,22,Comparing NULLs to Values,Th

13、e logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN. When any value is compared with NULL, the truth value is UNKNOWN. But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).,23,Three-Valued Logic,To understand h

14、ow AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = . AND = MIN; OR = MAX, NOT(x) = 1-x. Example: TRUE AND (FALSE OR NOT(UNKNOWN) = MIN(1, MAX(0, (1 - ) =MIN(1, MAX(0, ) = MIN(1, ) = .,24,Surprising Example,From the following Sells relation:bar beer priceJoes Bar

15、Bud NULLSELECT barFROM SellsWHERE price = 2.00;,25,Reason: 2-Valued Laws != 3-Valued Laws,Some common laws, like commutativity of AND, hold in 3-valued logic. But not others, e.g., the “law of the excluded middle”: p OR NOT p = TRUE. When p = UNKNOWN, the left side is MAX( , (1 ) = != 1.,26,Multirel

16、ation Queries,Interesting queries often combine data from more than one relation. We can address several relations in one query by listing them all in the FROM clause. Distinguish attributes of the same name by “.”,27,Example,Using relations Likes(drinker, beer) and Frequents(drinker, bar), find the

17、 beers liked by at least one person who frequents Joes Bar.SELECT beerFROM Likes, FrequentsWHERE bar = Joes Bar ANDFrequents.drinker = Likes.drinker;,28,Formal Semantics,Almost the same as for single-relation queries: Start with the product of all the relations in the FROM clause. Apply the selectio

18、n condition from the WHERE clause. Project onto the list of attributes and expressions in the SELECT clause.,29,Operational Semantics,Imagine one tuple-variable for each relation in the FROM clause. These tuple-variables visit each combination of tuples, one from each relation. If the tuple-variable

19、s are pointing to tuples that satisfy the WHERE clause, send these tuples to the SELECT clause.,30,Example,drinker bar drinker beertv1 tv2Sally BudSally JoesLikesFrequents,31,Explicit Tuple-Variables,Sometimes, a query needs to use two copies of the same relation. Distinguish copies by following the

20、 relation name by the name of a tuple-variable, in the FROM clause. Its always an option to rename relations this way, even when not essential.,32,Example,From Beers(name, manf), find all pairs of beers by the same manufacturer. Do not produce pairs like (Bud, Bud). Produce pairs in alphabetic order

21、, e.g. (Bud, Miller), not (Miller, Bud).SELECT b1.name, b2.nameFROM Beers b1, Beers b2WHERE b1.manf = b2.manf ANDb1.name b2.name;,33,Subqueries,A parenthesized SELECT-FROM-WHERE statement (subquery ) can be used as a value in a number of places, including FROM and WHERE clauses. Example: in place of

22、 a relation in the FROM clause, we can place another query, and then query its result. Better use a tuple-variable to name tuples of the result.,34,Subqueries That Return One Tuple,If a subquery is guaranteed to produce one tuple, then the subquery can be used as a value. Usually, the tuple has one

23、component. A run-time error occurs if there is no tuple or more than one tuple.,35,Example,From Sells(bar, beer, price), find the bars that serve Miller for the same price Joe charges for Bud. Two queries would surely work: Find the price Joe charges for Bud. Find the bars that serve Miller at that

24、price.,36,Query + Subquery Solution,SELECT barFROM SellsWHERE beer = Miller ANDprice = (SELECT priceFROM SellsWHERE bar = Joes BarAND beer = Bud);,37,The IN Operator, IN is true if and only if the tuple is a member of the relation.NOT IN means the opposite. IN-expressions can appear in WHERE clauses

25、. The is often a subquery.,38,Example,From Beers(name, manf) and Likes(drinker, beer), find the name and manufacturer of each beer that Fred likes.SELECT *FROM BeersWHERE name IN (SELECT beerFROM LikesWHERE drinker = Fred);,39,The Exists Operator,EXISTS( ) is true if and only if the is not empty. Ex

26、ample: From Beers(name, manf) , find those beers that are the unique beer by their manufacturer.,40,Example Query with EXISTS,SELECT nameFROM Beers b1WHERE NOT EXISTS(SELECT *FROM BeersWHERE manf = b1.manf ANDname b1.name);,41,The Operator ANY,x = ANY( ) is a boolean condition true if x equals at le

27、ast one tuple in the relation. Similarly, = can be replaced by any of the comparison operators. Example: x = ANY( ) means x is not the smallest tuple in the relation. Note tuples must have one component only.,42,The Operator ALL,Similarly, x ALL( ) is true if and only if for every tuple t in the rel

28、ation, x is not equal to t. That is, x is not a member of the relation. The can be replaced by any comparison operator. Example: x = ALL( ) means there is no tuple larger than x in the relation.,43,Example,From Sells(bar, beer, price), find the beer(s) sold for the highest price.SELECT beerFROM Sell

29、sWHERE price = ALL(SELECT priceFROM Sells);,44,Union, Intersection, and Difference,Union, intersection, and difference of relations are expressed by the following forms, each involving subqueries: ( subquery ) UNION ( subquery ) ( subquery ) INTERSECT ( subquery ) ( subquery ) EXCEPT ( subquery ),45

30、,Example,From relations Likes(drinker, beer), Sells(bar, beer, price), and Frequents(drinker, bar), find the drinkers and beers such that: The drinker likes the beer, and The drinker frequents at least one bar that sells the beer.,46,Solution,(SELECT * FROM Likes)INTERSECT (SELECT drinker, beerFROM

31、Sells, FrequentsWHERE Frequents.bar = Sells.bar );,47,Bag Semantics,Although the SELECT-FROM-WHERE statement uses bag semantics, the default for union, intersection, and difference is set semantics. That is, duplicates are eliminated as the operation is applied.,48,Motivation: Efficiency,When doing

32、projection, it is easier to avoid eliminating duplicates. Just work tuple-at-a-time. For intersection or difference, it is most efficient to sort the relations first. At that point you may as well eliminate the duplicates anyway.,49,Controlling Duplicate Elimination,Force the result to be a set by S

33、ELECT DISTINCT . . . Force the result to be a bag (i.e., dont eliminate duplicates) by ALL, as in . . . UNION ALL . . .,50,Example: DISTINCT,From Sells(bar, beer, price), find all the different prices charged for beers:SELECT DISTINCT priceFROM Sells; Notice that without DISTINCT, each price would b

34、e listed as many times as there were bar/beer pairs at that price.,51,Example: ALL,Using relations Frequents(drinker, bar) and Likes(drinker, beer):(SELECT drinker FROM Frequents)EXCEPT ALL(SELECT drinker FROM Likes); Lists drinkers who frequent more bars than they like beers, and does so as many ti

35、mes as the difference of those counts.,52,Join Expressions,SQL provides several versions of (bag) joins. These expressions can be stand-alone queries or used in place of relations in a FROM clause.,53,Products and Natural Joins,Natural join:R NATURAL JOIN S; Product:R CROSS JOIN S; Example:Likes NAT

36、URAL JOIN Serves; Relations can be parenthesized subqueries, as well.,54,Theta Join,R JOIN S ON Example: using Drinkers(name, addr) and Frequents(drinker, bar):Drinkers JOIN Frequents ONname = drinker;gives us all (d, a, d, b) quadruples such that drinker d lives at address a and frequents bar b.,55

37、,Outerjoins,R OUTER JOIN S is the core of an outerjoin expression. It is modified by: Optional NATURAL in front of OUTER. Optional ON after JOIN. Optional LEFT, RIGHT, or FULL before OUTER. LEFT = pad dangling tuples of R only. RIGHT = pad dangling tuples of S only. FULL = pad both; this choice is t

38、he default.,56,Aggregations,SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column. Also, COUNT(*) counts the number of tuples.,57,Example: Aggregation,From Sells(bar, beer, price), find the average price of Bud:SELECT AVG(price)FROM Sel

39、lsWHERE beer = Bud;,58,Eliminating Duplicates in an Aggregation,Use DISTINCT inside an aggregation. Example: find the number of different prices charged for Bud:SELECT COUNT(DISTINCT price)FROM SellsWHERE beer = Bud;,59,NULLs Ignored in Aggregation,NULL never contributes to a sum, average, or count,

40、 and can never be the minimum or maximum of a column. But if there are no non-NULL values in a column, then the result of the aggregation is NULL.,60,Example: Effect of NULLs,SELECT count(*) FROM Sells WHERE beer = Bud;SELECT count(price) FROM Sells WHERE beer = Bud;,61,Grouping,We may follow a SELE

41、CT-FROM-WHERE expression by GROUP BY and a list of attributes. The relation that results from the SELECT-FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is applied only within each group.,62,Example: Grouping,From Sells(bar, beer, price), find the average p

42、rice for each beer:SELECT beer, AVG(price)FROM SellsGROUP BY beer;,63,Example: Grouping,From Sells(bar, beer, price) and Frequents(drinker, bar), find for each drinker the average price of Bud at the bars they frequent:SELECT drinker, AVG(price)FROM Frequents, SellsWHERE beer = Bud ANDFrequents.bar

43、= Sells.barGROUP BY drinker;,64,Restriction on SELECT Lists With Aggregation,If any aggregation is used, then each element of the SELECT list must be either: Aggregated, or An attribute on the GROUP BY list.,65,Illegal Query Example,You might think you could find the bar that sells Bud the cheapest

44、by:SELECT bar, MIN(price)FROM SellsWHERE beer = Bud; But this query is illegal in SQL.,66,HAVING Clauses,HAVING may follow a GROUP BY clause. If so, the condition applies to each group, and groups not satisfying the condition are eliminated.,67,Example: HAVING,From Sells(bar, beer, price) and Beers(

45、name, manf), find the average price of those beers that are either served in at least three bars or are manufactured by Petes.,68,Solution,SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) = 3 ORbeer IN (SELECT nameFROM BeersWHERE manf = Petes);,69,Requirements on HAVING Conditions,These conditions may refer to any relation or tuple-variable in the FROM clause. They may refer to attributes of those relations, as long as the attribute makes sense within a group; i.e., it is either: A grouping attribute, or Aggregated.,