Relational Algebra (end) SQLComplex QueriesExercisesOperations on Bags (and why we care)Summary of Relational AlgebraGlimpse Ahead: Efficient Implementations of OperatorsGlimpse Ahead: OptimizationsFinally: RA has Limitations !OutlineSQL IntroductionSlide 11SelectionsSlide 13The LIKE operatorProjectionsSlide 16Ordering the ResultsJoinsDisambiguating AttributesTuple VariablesSlide 21Meaning (Semantics) of SQL QueriesSlide 23Slide 24First Unintuitive SQLismUnion, Intersection, DifferenceSlide 27SubqueriesPowerPoint PresentationSubqueries Returning RelationsSlide 31Slide 32Question for Database Fans and their FriendsConditions on TuplesCorrelated QueriesComplex Correlated QueryRemoving DuplicatesConserving DuplicatesRelational Algebra (end)SQLApril 19th, 2002Complex QueriesProduct ( pid, name, price, category, maker-cid)Purchase (buyer-ssn, seller-ssn, store, pid)Company (cid, name, stock price, country)Person(ssn, name, phone number, city)Note:•in Purchase: buyer-ssn, seller-ssn are foreign keys in Person, pid is foreign key in Product; •in Product maker-cid is a foreign key in CompanyFind phone numbers of people who bought gizmos from Fred.Find telephony products that somebody boughtExercises Product ( pid, name, price, category, maker-cid)Purchase (buyer-ssn, seller-ssn, store, pid)Company (cid, name, stock price, country)Person(ssn, name, phone number, city)Ex #1: Find people who bought telephony products.Ex #2: Find names of people who bought American productsEx #3: Find names of people who bought American products and did not buy French productsEx #4: Find names of people who bought American products and they live in Seattle.Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.Operations on Bags (and why we care)•Union: {a,b,b,c} U {a,b,b,b,e,f,f} = {a,a,b,b,b,b,b,c,e,f,f}–add the number of occurrences•Difference: {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b,d}–subtract the number of occurrences•Intersection: {a,b,b,b,c,c} {b,b,c,c,c,c,d} = {b,b,c,c}–minimum of the two numbers of occurrences•Selection: preserve the number of occurrences•Projection: preserve the number of occurrences (no duplicate elimination)•Cartesian product, join: no duplicate eliminationReading assignment: 5.3Summary of Relational Algebra•Why bother ? Can write any RA expression directly in C++/Java, seems easy.•Two reasons:–Each operator admits sophisticated implementations (think of , C)–Expressions in relational algebra can be rewritten: optimizedGlimpse Ahead: Efficient Implementations of Operators• (age >= 30 AND age <= 35)(Employees)–Method 1: scan the file, test each employee–Method 2: use an index on age–Which one is better ? Well, depends…•Employees Relatives–Iterate over Employees, then over Relatives–Iterate over Relatives, then over Employees–Sort Employees, Relatives, do “merge-join”–“hash-join”–etcGlimpse Ahead: OptimizationsProduct ( pid, name, price, category, maker-cid)Purchase (buyer-ssn, seller-ssn, store, pid)Person(ssn, name, phone number, city)•Which is better:price>100(Product) (Purchase city=seaPerson)price>100(Product) Purchase) city=seaPerson•Depends ! This is the optimizer’s job…Finally: RA has Limitations !•Cannot compute “transitive closure”•Find all direct and indirect relatives of Fred•Cannot express in RA !!! Need to write C programName1 Name2 RelationshipFred Mary FatherMary Joe CousinMary Bill SpouseNancy Lou SisterOutline•Simple Queries in SQL (6.1)•Queries with more than one relation (6.2)•Subqueries (6.3)•Duplicates (6.4)SQL IntroductionStandard language for querying and manipulating data Structured Query LanguageMany standards out there: SQL92, SQL2, SQL3, SQL99Vendors support various subsets of these, but all of what we’llbe talking about.SQL IntroductionBasic form: (many many more bells and whistles in addition) Select attributes From relations (possibly multiple, joined) Where conditions (selections) Select attributes From relations (possibly multiple, joined) Where conditions (selections)SelectionsCompany(sticker, name, country, stockPrice)Find all US companies whose stock is > 50:Output schema: R(sticker, name, country, stockPrice)SELECT *FROM CompanyWHERE country=“USA” AND stockPrice > 50SELECT *FROM CompanyWHERE country=“USA” AND stockPrice > 50SelectionsWhat you can use in WHERE: attribute names of the relation(s) used in the FROM. comparison operators: =, <>, <, >, <=, >= apply arithmetic operations: stockprice*2 operations on strings (e.g., “||” for concatenation). Lexicographic order on strings. Pattern matching: s LIKE p Special stuff for comparing dates and times.The LIKE operator•s LIKE p: pattern matching on strings•p may contain two special symbols:–% = any sequence of characters–_ = any single characterCompany(sticker, name, address, country, stockPrice)Find all US companies whose address contains “Mountain”:SELECT *FROM CompanyWHERE country=“USA” AND address LIKE “%Mountain%”SELECT *FROM CompanyWHERE country=“USA” AND address LIKE “%Mountain%”ProjectionsSELECT name, stockPriceFROM CompanyWHERE country=“USA” AND stockPrice > 50SELECT name, stockPriceFROM CompanyWHERE country=“USA” AND stockPrice > 50Select only a subset of the attributesInput schema: Company(sticker, name, country, stockPrice)Output schema: R(name, stock price)Rename the attributes in the resulting tableInput schema: Company(sticker, name, country, stockPrice)Output schema: R(company, price)ProjectionsSELECT name AS company, stockprice AS priceFROM CompanyWHERE country=“USA” AND stockPrice > 50SELECT name AS company, stockprice AS priceFROM CompanyWHERE country=“USA” AND stockPrice > 50Ordering the ResultsSELECT name, stockPriceFROM CompanyWHERE country=“USA” AND stockPrice > 50ORDERBY country, nameSELECT name, stockPriceFROM CompanyWHERE country=“USA” AND stockPrice > 50ORDERBY country, nameOrdering is ascending, unless you specify the DESC keyword.Ties are broken by the second attribute on the ORDERBY list, etc.Joins Product (pname, price,
View Full Document