Using SQL as a Query LanguageExample InstancesBasic SQL QueryConceptual Evaluation StrategyExample of Conceptual EvaluationA Note on Range VariablesFind sailors who’ve reserved at least one boatExpressions and StringsFind sid’s of sailors who’ve reserved a red or a green boatDr. Eick’s Graphical Method to Design SQL QueriesPowerPoint PresentationFind sid’s of sailors who’ve reserved a red and a green boatFirst Summary SQLNested QueriesNested Queries with CorrelationMore on Set-Comparison OperatorsRewriting INTERSECT Queries Using INDivision in SQLAggregate OperatorsFind name and age of the oldest sailor(s)GROUP BY and HAVINGQueries With GROUP BY and HAVINGConceptual EvaluationFind the age of the youngest sailor with age 18, for each rating with at least 2 such sailorsFor each red boat, find the number of reservations for this boatFind the age of the youngest sailor with age > 18, for each rating with at least 2 sailors (of any age)Find those ratings for which the average age is the minimum over all ratingsTriggersTriggers: Example (SQL:1999)2nd Summary SQLIntroduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke1Using SQLas a Query LanguageCOSC 6340Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke2Example Instancessid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0sid bid day22 101 10/10/9658 103 11/12/96R1S1S2We will use these instances of the Sailors and Reserves relations in our examples.If the key for the Reserves relation contained only the attributes sid and bid, how would the semantics differ?Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke3Basic SQL Queryrelation-list A list of relation names (possibly with a range-variable after each name).target-list A list of attributes of relations in relation-listqualification Comparisons (Attr op const or Attr1 op Attr2, where op is one of ) combined using AND, OR and NOT.DISTINCT is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated! SELECT [DISTINCT] target-listFROM relation-listWHERE qualification     , , , , ,Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke4Conceptual Evaluation Strategy Semantics of an SQL query defined in terms of the following conceptual evaluation strategy:Compute the cross-product of relation-list.Discard resulting tuples if they fail qualifications.Delete attributes that are not in target-list.If DISTINCT is specified, eliminate duplicate rows.This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers.Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke5Example of Conceptual EvaluationSELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103(sid) sname rating age (sid) bid day22 dustin 7 45.0 22 101 10/ 10/ 9622 dustin 7 45.0 58 103 11/ 12/ 9631 lubber 8 55.5 22 101 10/ 10/ 9631 lubber 8 55.5 58 103 11/ 12/ 9658 rusty 10 35.0 22 101 10/ 10/ 9658 rusty 10 35.0 58 103 11/ 12/ 96Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke6A Note on Range VariablesReally needed only if the same relation appears twice in the FROM clause. The previous query can also be written as:SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND bid=103SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103It is good style,however, to userange variablesalways!ORIntroduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke7Find sailors who’ve reserved at least one boatWould adding DISTINCT to this query make a difference?What is the effect of replacing S.sid by S.sname in the SELECT clause? Would adding DISTINCT to this variant of the query make a difference?SELECT S.sidFROM Sailors S, Reserves RWHERE S.sid=R.sidIntroduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke8Expressions and StringsIllustrates use of arithmetic expressions and string pattern matching: Find triples (of ages of sailors and two fields defined by expressions) for sailors whose names begin and end with B and contain at least three characters.AS and = are two ways to name fields in result.LIKE is used for string matching. `_’ stands for any one character and `%’ stands for 0 or more arbitrary characters. SELECT S.age, age1=S.age-5, 2*S.age AS age2FROM Sailors SWHERE S.sname LIKE ‘B_%B’Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke9Find sid’s of sailors who’ve reserved a red or a green boatUNION: Can be used to compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).If we replace OR by AND in the first version, what do we get?Also available: EXCEPT (What do we get if we replace UNION by EXCEPT?)SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’UNIONSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke10Dr. Eick’s Graphical Method to Design SQL Queries1. Draw a node for each relation that is required to answer the query2. Write those attributes, whose values will be returned by answer the query into the node(s)3. Specify single node restrictions/selection conditions --- attach those to nodes using ‘<>’4. Assign edges that connect the involved nodes for 2-node restrictions / conditions to the graph. Label the edge with the 2-node condition5. Translate the graph into an SQL-queryIntroduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke11S-namesidSailor SReservation R1Boat B1Reservation R2Boat B2sid=sidbid=bidsid=sidbid=bidcolor=redcolor=greenExample: “Give sid and name of all sailors that have reservations for a green boat and a red boat”Remark: for correspondingSQL-query see page 12Introduction to SQL; Christoph F. Eick & R. Ramakrishnan and J. Gehrke12Find sid’s of sailors who’ve reserved a red and a green boatINTERSECT: Can be used to compute the intersection