1 / 46

C20.0046: Database Management Systems Lecture #7

C20.0046: Database Management Systems Lecture #7. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Basic SQL Joins Hw1 probably soon (email from Blackboard)…. Current topic: SQL. Standard language for querying and manipulating data Structured Query Language

elysia
Download Presentation

C20.0046: Database Management Systems Lecture #7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C20.0046: Database Management SystemsLecture #7 M.P. Johnson Stern School of Business, NYU Spring, 2008 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  2. Agenda • Basic SQL • Joins • Hw1 probably soon (email from Blackboard)… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  3. Current topic: SQL • Standard language for querying and manipulating data Structured Query Language • Many standards: ANSI SQL, SQL92/SQL2, SQL3/SQL99 • Originally: Structured English Query Language (SEQUEL) • Vendors support various subsets/extensions • We’ll do Oracle/MySQL/generic • “No one ever got fired for buying Oracle.” • Basic form (many more bells and whistles in addition): SELECTattributes FROM relations (possibly multiple, joined) WHERE conditions (selections) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  4. Next (parallel) topic: relational algebra • Projection • Selection • Cartesian Product • Joins: natural joins, theta joins • Set operations: union, intersection, difference • Combining operations to form queries • Dependent and independent operations M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  5. What is relational algebra? • An algebra for relations • “High-school” algebra: an algebra for numbers • Algebra = formalism for constructing expressions • Operations • Operands: Variables, Constants, expressions • Expressions: • Vars & constants • Operators applied to expressions • They evaluate to values M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  6. Why do we care about relational algebra? • The exprs are the form that questions about the data take • The relations these exprs cash out to are the answers to our questions • RA ~ more succinct rep. of many SQL queries • DBMS parse SQL into something like RA • First proofs of concept for RDBMS/RA: • System R at IBM • Ingress at Berkeley • “Modern” implementation of RA: SQL • Both state of the art, mid-70s M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  7. Relation operators • Basic operators: • Selection: s • Projection: P • Cartesian Product:  • Other set-theoretic ops: • Union:  • Intersection: • Difference: - • Additional operators: • Joins (natural, equijoin, theta join, semijoin) • Renaming: r • Grouping… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  8. Selection op • Selects all tuples satisfying a condition • Notation: sc(R) • Examples • ssalary > 100000(Employee) • sname = “Smith”(Employee) • The condition c can have • comparison ops:=, <, , >,, <> • boolean ops: and, or M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  9. Theater N’hood Title Sunshine Village Annie Hall Sunshine Village Bad Edu. Theater N’hood Title Film Forum Village Masc. Fem. Sunshine Village Annie Hall Sunshine Village Bad Edu. Selection example • Select the movies at Angelica: • sTheater=“Sunshine”(Showings) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  10. Projection op • Keep only certain columns • Projection: op we used for decomposition • Eliminates other columns, then removes duplicates • Notation: PA1,…,An(R) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  11. Join op • Corresponds to SQL query doing cross & equality test • Specifically: R1 R2 = Pevery att once(sshared atts =(R1 R2)) • I.e., first compute the cross product R1 x R2 • Next, select the rows in which shared fields agree • Finally, project onto the union of R1 and R2’s fields (remove duplicates) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  12. Rename op • Changes the schema, not the instance • Notation: rB1,…,Bn(R) • r is spelled “rho”, pronounced “row” • Example: • Employee(ssn,name) • rE2(social, name)(Employee) • Or just: rE(Employee) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  13. RA  SQL • SQL SELECT RA Projection P • SQL WHERE  RA Selection s • SQL FROM  RA Join/cross • Comma-separated list… • SQL renaming  RA rho r • More ops later • Keep RA in the back of your mind… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  14. Next: Joins in SQL • Connect two or more tables: Product Company What is the connection between them? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  15. Joinbetween Productand Company Joins in SQL Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all products under $200 manufactured in Japan;return their names and prices. SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  16. Joins in SQL Product Company SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  17. Joins in SQL Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all countries that manufacture some product in the ‘Gadgets’ category. SELECT CountryFROM Product, CompanyWHEREManufacturer=CName AND Category='Gadgets' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  18. Joins in SQL Product Company SELECT CountryFROM Product, CompanyWHEREManufacturer=CName AND Category='Gadgets' What is the problem? What’s thesolution? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  19. Joins Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(name, phone, city) Find names of Seattleites who bought Gadgets, and the names of the stores they bought such product from. SELECT DISTINCT name, storeFROM Person, Purchase, ProductWHEREpersname=buyer AND product = pname AND city='Seattle' AND category='Gadgets' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  20. SQL Query Semantics Parallel assignment – all tuples Doesn’t impose any order SELECT a1, a2, …, ak FROM R1 AS x1, R2 AS x2, …, Rn AS xn WHERE Conditions Answer = {} for all assignments x1 in R1, …, xn in Rn do if Conditions then Answer = Answer  {(a1,…,ak)} return Answer M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  21. SQL Query Semantics Nested loops: SELECT a1, a2, …, ak FROM R1 AS x1, R2 AS x2, …, Rn AS xn WHERE Conditions Answer = {} for x1 in R1 do for x2 in R2 do ….. for xn in Rn do if Conditions then Answer = Answer  {(a1,…,ak)} return Answer M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  22. Multiple join syntaxes • Old-style syntax simply lists tables separated by commas • New-style makes the join explicit: • Functionally equivalent to old-style, but perhaps more elegant • Introduced in Oracle 8i, MySQL 3.x/4.x • Older versions / other DBMSs may not support this SELECT * FROM A,B WHERE …; SELECT * FROM A JOIN B ON … WHERE …; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  23. New-style join types • Cross joins (simplest): • FROM A CROSS JOIN B • Inner joins (regular joins): • FROM A [INNER] JOIN B ON … • Natural join: • FROM A NATURAL JOIN B; • Joins on common fields and merges • Outer joins (later) • No dangling rows M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  24. CROSS JOIN e.g. MovieStar MovieExec M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  25. CROSS JOIN e.g. SELECT * FROM MovieStar CROSS JOIN MovieExec M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  26. JOIN … ON e.g SELECT * FROM MovieStar JOIN MovieExec ON MovieStar.Name <> MovieExec.Name M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  27. NATURAL JOIN • MovieStar(name, address, gender, birthdate) • MovieExec(name, address, networth) • Natural Join syntax: • FROM MovieStar NATURAL JOIN MovieExec • Results: list of movie stars who are also execs: • (Name, address, gender, birthdate, networth) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  28. NATURAL JOIN e.g. MovieStar MovieExec SELECT * FROM MovieStar NATURAL JOIN MovieExec M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  29. Disambiguating Attributes • Sometimes two relations have the same attr:Person(pname, address, worksfor)Company(cname, address) Whichaddress? SELECT DISTINCT pname, addressFROM Person, CompanyWHEREworksfor = cname SELECT DISTINCT Person.pname, Company.addressFROM Person, CompanyWHEREPerson.worksfor = Company.cname M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  30. Tuple Var e.g. Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phoneNumber, city) Find all stores that sold at least one product that the storeBestBuy also sold: SELECT DISTINCTx.store FROM Purchase AS x, Purchase AS y WHEREx.product = y.product AND y.store = 'BestBuy' Result: (store) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  31. Details: Disambiguation in SQL • Every selected field must be unambiguous • For R(A,B), • Select A from R, R •  Select R1.A from R R1, R R2 • Consider: • Prob:* is shorthand for all fields • each must be unambiguous • Soln: Select * from R R1, R R2 SQL> Select * from R, R; Select * from R, R * ERROR at line 1: ORA-00918: column ambiguously defined M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  32. Details: Disambiguation in Oracle SQL • Depending on DBMS, can rename fields by: • Select name as n … • Select name n … • Select name=n… (not in Oracle) • Can rename relations only by • … from tab t1, tab t2 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  33. SQL e.g. with tuple vars • Reps(ssn, name, etc.) • Clients(ssn, name, rssn) • Q: Who are George’s clients, in SQL? • Conceptually: • PClients.name(sReps.name=“George” and Reps.ssn=rssn(Reps x Clients)) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  34. Ordering the Results • Ordering is ascending, unless you specify the DESC keyword per attribute: • Ordering is ascending, unless you specify the DESC keyword per attribute: SELECT pname, price, manufacturer FROM Product WHERE category='gizmo' AND price > 50 ORDER BY price, pname SELECT pname, price, manufacturer FROM Product WHERE category='gizmo' AND price > 50 ORDER BY price DESC, pname ASC M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  35. Ordering the Results SELECT Category FROM Product ORDER BY PName ? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  36. Details: Case-sensitivity • In Oracle, compares are case-sensitive by default • If want case-insensitive, some options: • Create a function index • Maybe later… • Manually convert vals to upper/lower case • SQL> select * from emp where upper(ename) = upper(‘Blake'); • Modify the nls_sort setting: • SQL> alter session set nls_sort=binary_ci; • SQL> alter session set nls_comp=ansi; • The other values: binary, binary_ai M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  37. The LIKE operator • s LIKE p: pattern matching on strings • p may contain two special symbols: • _ = any single character • % = zero or more chars • Product(Name, Price, Category, Manufacturer) • Find all products whose name contains 'gizmo': SELECT *FROM ProductsWHERE Name LIKE '%gizmo%' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  38. The LIKE operator • Q: How to search the actual '%' char? • The usual meta-char issue • PName LIKE '%%%' won’t work • Instead, must use escape chars • In C/C++/J, prepend \ • In SQL, prepend an arbitrary escape char: • PName LIKE '%x%%' ESCAPE 'x' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  39. Details: more on escape chars • SQL: no official default escape char • In Oracle’s SQL*Plus: default escape char = '\' • Can set with SQL> set escape x • Other tools, DBMSs: your mileage may vary • SQL string literals put in ' ': • 'mystring' • Single-quote literals escaped with single-quotes: • 'George''s string' • No distinction between strings and single chars M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  40. Details: more on escape chars • Q: Can an escape char be an escape string? • A: No. SQL> select * from newtable where a like '%\%%' escape '\'; A B ---------- ---------- h%i there SQL> select * from newtable where a like '%\%%' escape '\\'; select * from newtable where a like '%\%%' escape '\\' * ERROR at line 1: ORA-01425: escape character must be character string of length 1 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  41. Details: more on single-quotes • Dates with DATE: • DATE '1948-05-14' • Timestamps with TIMESTAMP: • TIMESTAMP '1948-05-14 12:00:00' • Details may vary by DBMS… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  42. Details: more on quotes • Q: What about double quotes? • A: Can’t be used in place of single quotes • But are used… • But can be used when Oracle would otherwise misparse your command, e.g.: • Names with spaces: • create table bad table name (a int, b int); • Reserved words as names: • create table badfieldname(from int, b int); M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  43. Another complex example • People(ssn, name, street, city, state, state) • Q: Who lives on George’s street? • A: First, generate pairs of (renamed) people: • rp1(People) x rp2(People) • Then pick out pairs with George: • sp1.name='George'(rp1(People) x rp2(People)) • And refine to rows with George and someone else: • sp1.name='George‘ AND p1.name<>p2.name(rp1(People) x rp2(People)) • Finally, project out the names: • Pp2.name(sp1.name='George‘ AND p1.name<>p2.name(rp1(People) x rp2(People)) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  44. Live examples • Q: produce a list of employees and their bosses • What if no boss? Or no subordinate? • Joins on emp, emp man: • Comma-based • Inner • Natural • Cross • Outer – left, right, full M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  45. More live examples • Inner joins require an ON clause • Like a where clause • Arbitrary boolean expression • If always true (1=1), reduces to cross join • New compar op: BETWEEN • a between 5 and 10 a >= 5 and a <= 10 • Q: produce a list of employees with their salary grades • emp, salgrade M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  46. Review • Examples from sqlzoo.net SELECTL FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

More Related