Query-by-Example (QBE)QBE: Intro`Example Tables’ in QBEBasicsAnd/Or QueriesDuplicatesJoin QueriesJoin Queries (Contd.)Slide 9Unnamed Columns“Negative Tables”AggregatesConditions BoxFind sailors who’ve reserved all boatsInserting TuplesDelete and UpdateRestrictions on Update CommandsFind sailors who’ve reserved all boats (Again!)A Solution Using ViewsA Peek at MS AccessSummaryDatabase Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 1Query-by-Example (QBE)Online ChapterExample is the school of mankind,and they will learn at no other. -- Edmund Burke (1729-1797)Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 2QBE: IntroA “GUI” for expressing queries.Based on the DRC!Actually invented before GUIs.Very convenient for simple queries.Awkward for complex queries.QBE an IBM trademark.But has influenced many projectsEspecially PC Databases: Paradox, Access, etc.Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 3`Example Tables’ in QBEBoats bid bname colorS ailors sid sname rating ageReserves sid bid dayUsers specify a query by filling in example tables, or skeletons; we will use these skeletons in our examples.Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 4BasicsSailors sid sname rating ageP._N P._ATo print names and ages of all sailors:Print all fields for sailors with rating > 8, in ascending order by (rating, age):Sailors sid sname rating ageP. AO(1). >8 AO(2).QBE puts unique new variables in blank columns. Above query in DRC (no ordering): I N T A I N T A Sailors T, , , | , , ,    8Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 5And/Or QueriesSailors sid sname rating ageP. < 30P. > 20Names of sailors younger than 30 or older than 20:Names of sailors younger than 30 and older than 20:Sa ilors sid sname rating age_Id P. < 30_Id P. > 20Names of sailors younger than 30 and rating > 4:S ailors sid sname rating age_Id P. > 4 < 30Note: MiniQBEuses a slightly different syntax!Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 6DuplicatesSailors sid sname rating ageUNQ. P. < 30Single row with P: Duplicates not eliminated by default; can force elimination by using UNQ.S ailors sid sname rating ageALL. _Id P. < 30_Id P. > 20Multiple rows with P: Duplicates eliminated by default! Can avoid elimination by using ALL.Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 7Join QueriesSailors sid sname rating age_Id P._S > 25Reserves sid bid day_Id ‘8/24/96’Names of sailors who’ve reserved a boat for 8/24/96 and are older than 25 (note that dates and strings with blanks/special chars are quoted):Joins accomplished by repeating variables. Note: MiniQBE uses double quotesDatabase Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 8Join Queries (Contd.)Colors of boats reserved by sailors who’ve reserved a boat for 8/24/96 and are older than 25 :Sa ilors sid sname rating age_Id _S > 25Reserves sid bid day_Id _B ‘8/24/96’Boats bid bname color_B ‘Interlake’ P.Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 9Join Queries (Contd.)Sailors sid sname rating age_Id P. P.Reserves sid bid day22 _B_Id _BNames and ages of sailors who’ve reserved some boat that is also reserved by the sailor with sid = 22:Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 10Unnamed ColumnsSail ors sid sname rating age_Id P. _R _A P._D P.(_R/_A)Res erves sid bid day_Id _DUseful if we want to print the result of an expression, or print fields from 2 or more relations.QBE allows P. to appear in at most one table!MiniQBE allowsP. in multiple tablesDatabase Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 11“Negative Tables”Can place a negation marker in the relation column:Sailors sid sname rating age_Id P._SReserves sid bid day_Id _BNote:MiniQBEuses NOTor ~.Variables appearing in a negated table must also appear in a positive table!Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 12AggregatesSailors sid sname rating age_Id G. G.P.AO _A P.AVG._AQBE supports AVG, COUNT, MIN, MAX, SUMNone of these eliminate duplicates, except COUNTAlso have AVG.UNQ. etc. to force duplicate eliminationThe columns with G. are the group-by fields; all tuples in a group have the same values in these fields.—The (optional) use of .AO orders the answers.—Every column with P. must include G. or an aggregate operator.Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 13Conditions BoxSail ors sid sname rating ageG.P. _ACONDITIONSAVG._A > 30Used to express conditions involving 2 or more columns, e.g., _R/_A > 0.2. Can express a condition that involves a group, similar to the HAVING clause in SQL: Express conditions involving AND and OR:Sailors sid sname rating ageP. _ACONDITIONS20 < _A AND _A < 30Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 14Find sailors who’ve reserved all boatsSailors sid sname rating ageP.G._IdCONDITIONSCOUNT._B1= COUNT._B2A division query; need aggregates (or update operations, as we will see later) to do this in QBE.Reserv e s sid bid day_Id _B1How can we modify this query to print the names of sailors who’ve reserved all boats?Boats bid bname color_B2Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 15Inserting TuplesSa ilors sid sname rating ageI. _Id _N _ACONDITIONS_A > 18 OR_N LIKE ‘C%’Single-tuple insertion:S tudents sid name login age_Id _N _AInserting multiple tuples (rating is null in tuples inserted below):Sailors sid sname rating ageI. 74 Janice 7 14Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 16Delete and UpdateSa il ors sid sname rating age_Id < 4Delete all reservations for sailors with rating < 4Reserves sid bid dayD. _IdIncrement the age of the sailor with sid = 74Sailors sid sname rating age74 U._A+1Database Management Systems 3ed, Online chapter, R. Ramakrishnan and J. Gehrke 17Restrictions on Update CommandsCannot mix I., D. and U. in a single example table, or combine them with P. or G.Cannot insert, update or modify tuples using values from fields of other tuples in the