C20.0046: Database Management Systems Lecture #10

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

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

3. SQL Query Semantics SELECTa1, a2, …, ak FROMR1 AS x1, R2 AS x2, …, Rn AS xn WHEREConditions Parallel assignment – all tuples Doesn’t impose any order! 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 2005

4. SQL Query Semantics SELECTa1, a2, …, ak FROMR1 AS x1, R2 AS x2, …, Rn AS xn WHEREConditions Nested loops: 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 2005

5. Details: 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 2005

6. 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: • Why? • * is shorthand for all fields, each must be unambiguous •  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 2005

7. Details: Tuple Variables 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 store'BestBuy' also sold: SELECT DISTINCTx.store AS competitor FROM Purchase AS x, Purchase AS y WHERE x.product = y.product AND y.store = 'BestBuy' Answer (store) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

8. Details: Disambiguation in Oracle SQL • Can rename fields by • Select name as n … • Select name n … • But not by • Select name=n… • Can rename relations only by • … from tab t1, tab t2 • Lesson: if you get errors, remove all =s, ASs M.P. Johnson, DBMS, Stern/NYU, Spring 2005

9. R.A. & SQL • Reps(ssn, name, etc.) • Clients(ssn, name, rssn) • Q: Who are George’s clients, in R.A.? • PClients.name(sReps.name='George' and Reps.ssn=rssn(Reps x Clients)) • In SQL? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

10. 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 2005

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

12. Details: Case-sensitivity • By default, all matches and comparisons are case-sensitive • If want case-insensitive, some options: • Convert all to upper or lower case - slow • SQL> select * from emp where upper(ename) = upper(‘Blake'); • Create a function index • Maybe later… • 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 2005

13. 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 PName LIKE ‘%gizmo%’ M.P. Johnson, DBMS, Stern/NYU, Spring 2005

14. The LIKE operator • Q: What it want to search for values containing a ‘%’? • 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 2005

15. Details: More on escape chars • SQL: no official default escape char • In 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' M.P. Johnson, DBMS, Stern/NYU, Spring 2005

16. 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 2005

17. 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 2005

18. Details: More on quotes • Q: What about double quotes? • A: Can’t be used in place of single quotes • 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 2005

19. Complex RA/SQL Expressions • Reps(ssn, name, etc.) • Clients(ssn, name, rssn) • Q: Who are George’s clients? • PClients.name(sReps.name='George' and Reps.ssn=rssn( Reps x Clients)) • Or: PClients.name(Reps.ssn=rssn( sReps.name='George'(Reps) x Clients)) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

20. Complex RA Expressions • People(ssn, name, street, city, state, state) • Q: Who lives on George’s street? • A: First, find George: • sname='George'(People) • Get George’s street/city/state: • Pstreet,city,state(sname='George'(People)) • Join with People: • People x Pstreet,city,state(sname='George'(People)) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

21. Complex RA Expressions • How to specify street = street? Rename • rp2(s2,c2)(People) x Pstreet,city(sname='George'(People)) • Now can select: • sstreet=s2 AND city=c2(rp2(s2,c2)(People) x Pstreet,city(sname='George'(People))) • Then project names… • Only way? No. Join! • People Pstreet,city(sname='George'(People)) • Q: Would the following work? • Pstreet,city(sname='George'(People ⋈ People)) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

22. R.A.  SQL • People(ssn, name, street, city, state) • assume for clarity that cities are unique • Q: Who lives on George’s street? • In R.A.: sstreet=s2 AND city=c2(rp2(s2,c2)(People) x Pstreet,city(sname='George'(People))) • In SQL? • The other way in R.A.: People Pstreet,city(sname='George'(People)) • In SQL? Later on… M.P. Johnson, DBMS, Stern/NYU, Spring 2005

23. Complex RA Expressions • Scenario: • Purchase(pid, seller-ssn, buyer-ssn, etc.) • Person(ssn, name, etc.) • Product(pid, name, etc.) • Q: Who (give names) bought gizmos from Dick? • Where to start? • Purchase uses pid, ssn, so must get them… M.P. Johnson, DBMS, Stern/NYU, Spring 2005

24. seller-ssn=ssn pid=pid buyer-ssn=Person.ssn Complex RA Expressions Pname Person Purchase Person Product Pssn Ppid sname='Dick' sname='Gizmo' M.P. Johnson, DBMS, Stern/NYU, Spring 2005