DOC PREVIEW
UW CSE 444 - Relational Algebra (end) SQL

This preview shows page 1-2-3-18-19-36-37-38 out of 38 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

UW CSE 444 - Relational Algebra (end) SQL

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Relational Algebra (end) SQL
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Relational Algebra (end) SQL and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Relational Algebra (end) SQL 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?