From Relational Algebra to the Structured Query LanguageReview – Relational IntegrityReview – Relational AlgebraWhy Relational Algebra?Homework Problem 6.18Sets versus BagsWhy Bags? Efficiency!Bag UnionBag IntersectionBag DifferenceBag SelectionBag ProjectionBag Theta-JoinIntroducing SQLRunning Example – The SodaBaseThe Basic SQL QueryExampleSlide 18Select *Slide 20Renaming AttributesSlide 22Expressions in SELECT ClausesSlide 24Slide 25Another Example: ConstantsExample: Complex ConditionsSlide 28Pattern MatchingSlide 30Slide 31Dealing with NullThree-Valued LogicConsiderCombining RelationsTuple VariablesSolutionThe Story Thus FarFrom Relational Algebra to the Structured Query LanguageRose-Hulman Institute of TechnologyCurt CliftonReview – Relational IntegrityEntity Integrity Constraints:Primary key values cannot be nullReferential Integrity Constraints:Foreign key values must either:Match the primary key values of some tuple, orBe nullReview – Relational AlgebraIntersection: R1  R2Union: R1  R2Difference: R1 - R2Selection: BDATE < 1970-1-1 (EMPLOYEE)Projection: FNAME, BDATE(EMPLOYEE)Theta-Join:Natural Join: R1 * R2Why Relational Algebra?Foundational knowledgeUsed by query optimizersFiner grained than SQLCan be formally reasoned aboutFormal basis for semantics of SQLHomework Problem 6.18Parts a–d and gBegin in class, may work in groups of 2–3Please note your partners on the sheetSets versus BagsSetsOrder doesn’t matterNo duplicatesExamples{1,2,3} = {2,1,3}{1,2}  {2} = {1,2}Bags (or multi-sets)Order doesn’t matterDuplicates allowedExamples{1,2,3} = {2,1,3}{1,2}  {2} = {1,2,2}{1,2,3} ≠ {1,2,2,3}Why Bags? Efficiency!Eliminating duplicates can be expensiveBy default SQL uses bagsBag Union“Just dump all the elements into a single bag”An element appears in the union of two bags the sum of the number of times it appears in each bagBag Intersection“Whichever bag has the fewest, has the answer”An element appears in the intersection of two bags the minimum number of times it appears in either.Bag Difference“Take elements out of the first if they’re in the second”An element appears in the difference of two bags as many times as it appears in the first, minus the number of times it appears in the second, but no less than 0 timesBag SelectionA B1 25 61 2RLike set selectionBut input and output can be bagsBag ProjectionA B1 25 61 2RUnlike set project, can turn a set into a bagBag Theta-JoinA B1 25 61 2RPair each tuple of first table with each tuple of secondCheck conditionDon’t eliminate duplicatesB C3 47 8SIntroducing SQLPronounced:“ess queue ell”Or “sequel”Benefits:Designed for the relational modelEasily optimized by DBMSStandard (well, sort of, a little bit, sometimes)http://sqlfairy.sourceforge.netRunning Example – The SodaBaseSoda(name, manf)Rest(name, addr, contract)Customer(name, addr, phone)Likes(customer, soda)Sells(rest, soda, price)Frequents(customer, rest)The Basic SQL QuerySELECT attributes FROM tableWHERE conditionSemantics: attributes(condition(table))ExampleFind all the names of all sodas made by PepsiCoExampleFind all the names of all sodas made by PepsiCoSELECT name FROM Soda WHERE manf = 'PepsiCo'(note single quotes)Select *For getting all attributes…SELECT *FROM tableWHERE conditionSemantics: condition(table)ExampleSELECT * FROM Soda WHERE manf = 'PepsiCo'Renaming AttributesSELECT attribute1 AS newName1, …FROM tableWHERE conditionSemantics: newName1, …(attribute1, …(condition(table)))ExampleSELECT name AS soda, manf AS makerFROM SodaWHERE manf = 'PepsiCo'Expressions in SELECT ClausesCan use expressions on attributes in SELECTSELECT f(attributes), …FROM tableWHERE conditionMore powerful than the relational algebra we’ve seenWould need functions on tuplesExampleShow selling prices in YenExampleShow selling prices in YenSELECT rest, soda, price * 115 AS priceInYen FROM SellsAnother Example: ConstantsSELECT customer, 'likes Pepsi' AS promotionFROM LikesWHERE soda = 'Pepsi'Example: Complex ConditionsFind the price that Joe’s Sushi charges for PepsiExample: Complex ConditionsFind the price that Joe’s Sushi charges for PepsiSELECT price FROM Sells WHERE rest = 'Joe''s Sushi' AND soda = 'Pepsi'Note:Double apostrophe inside stringAND, OR, NOTCase insensitivePattern MatchingWHERE clauses can compare string to patternAttribute LIKE patternAttribute NOT LIKE patternPattern syntax:Pattern is a string% in string represents any number of characters_ in string represent any single characterExampleFind the customers with exchange 555, regardless of area codeExampleFind the customers with exchange 555, regardless of area codeSELECT name FROM CustomerWHERE phone LIKE '%555-____'-- That’s four underscoresDealing with NullWhy might a tuple have a null value?SQL uses three-valued logic to handle nullA boolean expression can be true, false, or unknownComparison with null yields unknown instead of errorWHERE clause must be true to matchThree-Valued LogicTrue = 1False = 0Unknown = 1/2x AND y = min(x, y)x OR y = max(x, y)NOT x = 1 - xConsiderSELECT restFROM SellsWHERE price < 2.00 OR price >= 2.00If the Sells relation has the value:Beware of nulls!rest soda priceJoe's Pepsi nullCombining RelationsList multiple tables in FROMUse Relation.Attribute to distinguishSELECT sodaFROM Likes, FrequentsWHERE Frequents.customer = Likes.customerAND rest = 'Joe''s'Semantics:Tuple VariablesCan distinguish two copies of same relationExample: Find all pairs of sodas by the same manufacturer…Omitting trivial pairs like (Pepsi, Pepsi)Omitting permutations of same sodas by listing members of pair alphabeticallySolutionSELECT s1.name, s2.nameFROM Soda s1, Soda s2WHERE s1.manf = s2.manfAND s1.name < s2.nameThe Story Thus FarSELECT … FROM … WHERESELECT * …SELECT Foo AS Bar …SELECT expression …SELECT … FROM … WHERE … LIKE …SELECT … FROM Foo, Bar …SELECT … FROM Foo f1, Foo f2