1 / 20

SQL Queries Basics and Examples

Learn about standard SQL queries with examples, including SELECT, WHERE clause, expressions, and multirelational queries. Understand SQL semantics and operations in relational algebra.

tanya-butler
Download Presentation

SQL Queries Basics and Examples

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQLPart I: Standard Queries

  2. Example Instances R Reserves S Sailors sid bid day sid age sname rating 101 2005/24/07 103 2005/07/30 103 2005/07/28 58 103 2005/07/31 22 debby 7 35 31 debby 8 55 58 lilly 10 35 B Boat bid bname color Interlake green 103 Snapper red

  3. Principle Form of a Query sid age sname rating rating age • SELECT desired attributes FROM list of relations WHERE qualification (whereclause is optional) • SELECT rating,age FROM Sailors WHERE rating <= 6 OR age < 40 • Operational Semantics • Imagine a tuple variable ranging over all tuples of the relation • For each tuple: check if is satisfies the WHERE clause. If so, print the attributes in SELECT. • Conversion to Relational Algebra • rating,age (rating<=6  age<40 (Sailors)) • Start with the relation in the FROM clause • Apply , using condition in WHERE clause (selection) • Apply ∏, using attributes in SELECT clause (projection) • Difference: duplicates not eliminated 22 debby 7 35 31 debby 8 55 58 lilly 10 35 35 10 35

  4. The WHERE Clause • Comparison: • attr1 op const, or • attr1 op attr2, • op is one of , , , , , , LIKE • We may apply the usual arithmetic operations +, *, etc. to numeric values before we compare • Qualification/Condition: Comparisons combined using AND, OR and NOT • name =‘Cheng’ AND NOT age = 18 • name LIKE ‘%e_g’ (%: any string, _:any character) • Further string operations, e.g., concatenation, string-length, etc. • As default for most statements, SQL uses ‘multiset’ semantic, i.e., duplicates are allowed and not eliminated (as long as they do not violate a primary key / unique constraint) • DISTINCT is an optional keyword indicating that the answer shouldnot contain duplicates.

  5. Attribute Lists age sid sname rating • Distinct SELECT DISTINCT sname FROM Sailors (no WHERE clause OK) • Star as list of all attributes SELECT * FROM Sailors WHERE rating < 9 • Renaming columns SELECT sid, sname AS sailor FROM Sailors WHERE rating < 9 22 debby 7 35 31 debby 8 55 58 lilly 10 35 sname debby lilly age sname sid rating 22 debby 7 35 31 debby 8 55 sid sailor 22 debby 31 debby

  6. Attribute Lists (contd) sname upgrade • Expressions as values in columns SELECT sname, rating+1 AS upgrade FROM Sailors • Constants as attribute values SELECT rating AS reality, ‘10’ AS dream FROM Sailors WHERE sname LIKE ‘_e%y’ • Ordered Output SELECT * FROM Sailors ORDER BY age,rating debby 8 debby 9 lilly 11 reality dream 7 10 8 10 age sid sname rating 22 debby 7 35 lilly 10 35 31 debby 8 55

  7. Multirelational Queries • List of relations in FROM clause • Relation-dot-attribute disambiguates attributes from several relations. • Cross-Product: SELECT * FROM Sailors, Reserves Sailors X Reserves • Join: • Have to indicate comparison even with natural join • Example: “give me the names of all sailors that have reserved boat #103” SELECT sname FROM Sailors, Reserves WHERE Sailors.sid = Reserves.sid AND bid = 103 sname (bid=103 (Reserves) Sailors)

  8. Semantics • Conversion to Relational Algebra • Same as for single relation, but start with the product (X) of all the relations mentioned in the FROM clause • Parallel assignment of tuple variables • Consider a tuple variable for each relation in the FROM • Consider all possible assignments • For each such assignment of tuple variables to tuples that makes the WHERE true, output the attributes of the SELECT sid bid day sname sid age sname rating 101 2005/24/07 103 2005/07/30 103 2005/07/28 58 103 2005/07/31 debby Lilly lilly 22 debby 7 35 31 debby 8 55 58 lilly 10 35

  9. Semantics (contd). • Nested Loop Assignment LET the tuple variables in the from clause range over relations R1, R2, …., Rn FOR each tuple t1 in relation R1 DO FOR each tuple t2 in relation R2 DO … FOR each tuple tn in relation Rn DO If the where clause is satisfied when the values from t1, t2, … tn are substituted for all attribute references THEN evaluate the attributes of the select clause according to t1, t2, …. tn and produce the tuple of values that results.

  10. Range Variables • Optional use of range variables SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid = R.sid AND R.bid = 103 • Use of range variable required when the same relation appears twice in the FROM clause • Example: “find pairs of sailors that have rented the same boat” SELECT r1.sid, r2.sid FROM Reserves r1, Reserves r2 WHERE r1.bid = r2.bid AND r1.sid < r2.sid (note that r1.sid < r2.sid is needed to avoid producing (58,58) and to avoid producing a pair in both directions.

  11. Union, Intersection, Difference • Input relations for set operators must be set-compatible, I.e. they must have • Same number of attributes • The attributes, taken in order, must have same type • As default, result relation is a set!!! (no multiset) • Many systems do not provide primitives for intersection and difference

  12. Sailors(sid,sname,rating,age) Reserves(sid,bid,day) Boats(bid,bname,color) Find sailors that have reserved a red or a green boat SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND (B.color = ‘red’ OR B.color = ‘green’) SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘red’ UNION SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘green’ Union

  13. Intersection • Find sailors that have reserved a red and a green boat (1) SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘red’ INTERSECT SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘green’ (2) SELECT R1.sid FROM Reserves R1, Reserves R2, Boats B1, Boats B2 WHERE (R1.bid = B1.bid AND B1.color = ‘red’) AND (R2.bid = B2.bid AND B2.color = ‘green’) AND R1.sid = R2.sid) sid bid day sid bid day 101 2005/24/07 103 2005/07/30 22 103 2005/07/28 58 103 2005/07/31 101 2005/24/07 103 2005/07/30 22 103 2005/07/28 58 103 2005/07/31 color bid color bid green 103 red green 103 red

  14. Difference • Find sailors that have reserved a red but not a green boat SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘red’ EXCEPT (Oracle: MINUS) SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘green’

  15. Multiset Semantic • A multiset (bag) may contain the same tuple more than once, although there is no specified order (unlike a list). • Example: {1, 2, 1, 3} is a multiset, but not a set • Multiset Union: • Sum the times an element appears in the two multisets • Example: {1, 2, 2}  {1, 2, 3, 3} = {1, 1, 2, 2, 2, 3, 3} • Multiset Intersection: • Take the minimum of the number of occurrences in each multiset. • Example: {1, 2, 2}  {1, 1, 2, 2, 3, 3} = {1, 2,2} • Multiset Difference: • Subtract the number of occurrences in the two multisets • Examples: {1, 2, 2} - {1, 2, 3, 3} = {2} • Some familiar laws for sets also hold for multisets (e.g., union is commutative); but other laws do not hold (e.g., R  (S  T)  (R  S)  (R  T)

  16. Multiset Semantic in SQL • Although SQL generally works with multisets, it uses set semantic for union/intersection/difference • To enforce multiset semantic for these operators use • UNION ALL, INTERSECT ALL, EXCEPT ALL SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘red’ UNION ALL SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘green’

  17. Nested queries: The IN operator • A where clause can itself contain an SQL query. The inner query is called a subquery • Find names of sailors who have reserved boat #103 SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid = 103) • To find sailors who have NOT reserved boat #103 use NOT IN • Semantics best understood by nested loop assignment • Multiple attributes: • WHERE (a1,a2) IN (SELECT a3, a4…

  18. EXISTS (relation) is true iff the relation is non-empty Find names of sailors who have reserved boat #103 SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid = 103 AND R.sid = S.sid) Scoping rule: to refer to outer Sailors in the inner subquery, we need to give a range variable to the outer relation. A subquery that refers to values from a surrounding query is called a correlated subquery. Since the inner query depends on the row of the outer query it must be reevaluated for each row in the outer query Exists Operator

  19. Quantifiers • ANYand ALL behave as existential and universal quantifiers, respectively. • Syntax • WHERE attr op ANY (SELECT … • WHERE attr op ALL (SELECT • op is one of , , , , ,  • Find the sailors with the highest rating SELECT * FROM Sailors WHERE rating  ALL (SELECT rating FROM Sailors)

  20. Complex queries (Division) • Find sailors who have reserved all boats SELECT sname FROM Sailors S WHERE NOT EXISTS ((SELECT B.bid FROM Boats B) EXCEPT (SELECT R.bid FROM Reserves R WHERE R.sid=S.sid)) SELECT sname FROM Sailors S WHERE NOT EXISTS (SELECT B.bid FROM Boats B WHERE NOT EXISTS (SELECT R.bid FROM Reserves R WHERE R.bid = B.bid AND R.sid = S.sid))

More Related