1 / 49

Oracle SQL and PL/SQL Guide

A comprehensive guide to Oracle SQL and PL/SQL, including examples, tips, and common pitfalls. Learn how to write efficient queries and manage data effectively.

scallas
Download Presentation

Oracle SQL and PL/SQL Guide

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. OCL1 Oracle 8i:SQL & PL/SQLSession #4 Matthew P. Johnson CISDD, CUNY Fall, 2004 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  2. Review • Examples from sqlzoo.net SELECT L FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  3. Questions from last time • 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); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  4. Questions from last time • 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 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  5. Case-sensitivity • By default, all matches and comparisons are case-sensitive • If want case-insensitive, some options: • Convert all to upper or lower case: • SQL> select * from emp where upper(ename) like upper('%la%'); • Create a function index • Maybe later… • Modify the nls_sort setting: • SQL> alter session set nls_sort=binary_ci; • The other values: binary, binary_ai Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  6. 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 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  7. Disambiguation in Oracle 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 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  8. SQL e.g. • Acc(name,ssn,balance) • Q: Who has the largest balance? • Conceptually: Pname(Acc) - Pa2.name(sa2.bal < Acc.bal(Acc x ra2(Acc))) • In SQL? Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  9. Nulls in SQL • If we don’t have a value, can put a NULL • Null can mean several things: • Value does not exists • Value exists but is unknown • Value not applicable • The schema specifies whether null is allowed for each attribute • not null if not allowed • Otherwise, null is allowed Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  10. Null Values • x = NULL  4*(3-x)/7 = NULL • x = NULL  x + 3 – x = NULL • x = NULL  3 + (x-x) = NULL • x = NULL x = ‘Joe’ is UNKNOWN • In general: no row use null fields appear in the selection test will pass the test • Pace Boole, SQL has three boolean values: • FALSE = 0 • TRUE = 1 • UNKNOWN = 0.5 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  11. Null values in boolean expressions • C1 AND C2 = min(C1, C2) • C1 OR C2 = max(C1, C2) • NOT C1 = 1 – C1 • height > 6 = UNKNOWN •  UNKNOWN OR weight > 190 = UNKOWN •  (age < 25) AND UNKNOWN = UNKNOWN SELECT * FROM Person WHERE (age < 25) AND (height > 6 OR weight > 190) E.g.age=20height is NULLweight=200 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  12. Comparing null and non-nulls • Unexpected behavior: • Some Persons are not included! • The “trichotomy law” does not hold! SELECT * FROM Person WHERE age < 25 OR age >= 25 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  13. Testing for null values • Can test for NULL explicitly: • x IS NULL • x IS NOT NULL • But: • x=NULLis always null • Now it includes all Persons SELECT * FROM Person WHERE age < 25 OR age >= 25 OR age IS NULL Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  14. Example with nulls • look at emp table • Select names, salaries, commissions, total salaries • What if commission is null? • nvl Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  15. Evaluation strategies for SQL queries • Semantics of a SQL query defined in terms of the following conceptual evaluation strategy: • Compute the cross-product of relation-list in FROM clause • Discard resulting tuples if they fail WHERE clause • Delete attributes that are not in SELECT clause • If DISTINCT is specified, eliminate duplicate rows • Often the least efficient way to compute a query! • Optimizer finds better ways, but result is the same Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  16. Case-sensitivity redux • By default, all matches and comparisons are case-sensitive • If want case-insensitive, some options: • Convert all to upper or lower case: • 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 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  17. Subqueries • Powerful feature of SQL: one clause can contain other SQL queries! • So can FROM and HAVING clauses • Several ways: • Selection  single constant (scalar) in WHERE • Selection  relation in WHERE • Selection  relation in FROM • Etc. Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  18. Subquery motivation • Consider standard multi-table example: • Purchase(prodname, buyerssn, etc.) • Person(name, ssn, etc.) • What did Conrad buy? • As usual, need to AND on equality identifying ssn’s row and buyerssn’s row SELECT Purchase.prodnameFROM Purchase, PersonWHERE buyerssn = ssn AND name = ‘Conrad’ Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  19. Subquery motivation • Purchase(prodname, buyerssn, etc.) • Person(name, ssn, etc.) • What did Conrad buy? • Natural intuition: 1. Go find Conrad’s ssn 2. Then find purchases SELECT ssnFROM PersonWHERE name = ‘Conrad’ SELECT Purchase.prodnameFROM Purchase, PersonWHERE buyerssn = Conrad’s-ssn Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  20. Subqueries • Subquery: copy in Conrad’s selection for his ssn: • The subquery returns one value, so the = is valid • If it returns more (or fewer), we get a run-time error SELECT Purchase.prodname FROM Purchase WHERE buyerssn = (SELECT ssn FROM Person WHERE name = ‘Conrad’) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  21. SQL e.g. • People(ssn, name, street, city, state) • assume for clarity that cities are unique • Q: Who lives on George’s street? • Conceptually: sstreet=s2 AND city=c2(rp2(s2,c2)(People) x Pstreet,city(sname=‘George’(People))) • In SQL? • Another way, conceptually: People ⋈Pstreet,city(sname='George’(People)) • In SQL? Later on… Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  22. Operators on selections • Several new operators applied to (unary) selections: • EXISTS R • s > ALL R • s > ANY R • > is just an example op • Each expression can be negated with NOT Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  23. Subqueries returning relations • Q: Find companies Martha bought from • Intuition: • Find Martha’s ssn • Find Martha’s products • Find those products’ companies SELECT Product.maker FROM Product WHERE Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase.buyerssn = (SELECT ssn FROM Person WHERE name = ‘Martha’)) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  24. Subqueries returning relations • Equivalent to: • But are they really equivalent? • Make both distinct to be sure SELECT Product.maker FROM Product, Purchase, People WHERE Product.name = Purchase.product AND Purchase.buyerssn = ssn AND name = ‘Martha’ Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  25. Subqueries returning relations You can also use: s > ALL R s > ANY R EXISTS R Product (pname, price, category, maker) Find products that are more expensive than all Gizmo-Works products SELECT name FROM Product WHERE price > ALL (SELECT price FROM Purchase WHERE maker=‘Gizmo-Works’) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  26. Correlated Queries • So far, subquery executed once; • result used for higher query • More complicated: correlated queries • “[T]he subquery… [is] evaluated many times, once for each assignment of a value to some term in the subquery that comes from a tuple variable outside the subquery” (Ullman, p286). • Q: What does this mean? • A: That subqueries refer to vars from outer queries Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  27. Correlated Queries • Movie (title, year, director, length) • Q: Find titles that are titles of multiple movies • Note (1) scope of variables (2) this can still be expressed as single SFW SELECTDISTINCT title FROM Movie AS x WHERE year <> ANY (SELECT year FROM Movie WHERE title = x.title); correlation Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  28. Complex Correlated Query • Product (pname, price, category, maker, year) • Find products (and their manufacturers) that are more expensive than all products made by the same manufacturer before 1972 • Powerful, but much harder to optimize! SELECT DISTINCT pname, maker FROM Product AS x WHERE price > ALL (SELECT price FROM Product AS y WHERE x.maker = y.maker AND y.year < 1972); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  29. FROM subqueries • Recall Q: Which companies did Martha buy from? • Before: found ssn, found products, found companies SELECT Product.maker FROM Product WHERE Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase.buyerssn = (SELECT ssn FROM Person WHERE name = ‘Martha’)) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  30. FROM subqueries • Motivation for another way: suppose we’re given Martha’s purchases • Then could just cross with Products and select identified rows •  Substitute (named) subquery for Martha’s purchases SELECT Product.maker FROM Product, (SELECT Purchase.product FROM Purchase WHERE Purchase.buyerssn = (SELECT ssn FROM Person WHERE name = ‘Martha’)) Marthas WHERE Product.name = Martha.product Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  31. Existential/Universal Conditions Product (pname, price, company) Company(cname, city) Find all companies s.t. some of their products have price < 100 SELECT DISTINCT Company.cname FROM Company, Product WHERE Company.cname = Product.company and Produc.price < 100 Existential: easy! Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  32. Existential/Universal Conditions Product (pname, price, company) Company(cname, city) Find all companies s.t. all of their products have price < 100 Universal: hard! Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  33. Existential/universal with IN 1. Find the other companies: i.e. s.t. some product  100 SELECT DISTINCT Company.cname FROM Company WHERE Company.cname IN (SELECT Product.companyFROM ProductWHERE Produc.price >= 100 2. Find all companies s.t. all their products have price < 100 SELECT DISTINCT Company.cname FROM Company WHERE Company.cname NOT IN (SELECT Product.companyFROM ProductWHERE Produc.price >= 100 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  34. More on Set-Comparison Operators • We’ve already seen IN R, NOT IN R. • Can also use EXISTS R, NOT EXISTS R • Also available: op ANY R, op ALL R • Find sailors whose rating is greater than that of some sailor called Horatio: SELECT R.SID FROM Reserves R WHERE R.rating> ANY(SELECT R2.rating FROM Reserves R2 WHERE R2.sname=‘Horatio’) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  35. Joins operations • Variations: • Cross join (Cartesian product) • Join … On • Natural join • Outer join • Apply to relations appearing in selections Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  36. Cross join - example MovieStar MovieExec Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  37. Cross join – example • Select * From MovieStar Cross Join MovieExec Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  38. Join … On: example • Select * From MovieStar Join MovieExec On MovieStar.Name <> MovieExec. Name Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  39. Natural Joins MovieStar(name, address, gender, birthdate) MovieExec(name, address, networth) Natural Join: MovieStar Natural Join MovieExec; • Results in: list of individuals who are movie-stars as well as executives: (Name, address, gender, birthdate, networth) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  40. Example - Natural join MovieStar MovieExec Select *from MovieStar Natural Join MovieExec Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  41. Outer Join - Example MovieStar MovieExec Select *from MovieStar NATURAL FULL OUTER JOIN MovieExec Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  42. Outer Join - Example • Select *from MovieStar LEFT OUTER JOIN MovieExec Select *from MovieStar RIGHT OUTER JOIN MovieExec Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  43. New-style join syntax • Old-style syntax simply lists tables separated by commas: • SELECT * FROM A, B WHERE …; • New-style makes the join explicit: • SELECT * FROM A JOIN B ON … WHERE … Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  44. New-style join syntax • Functionally equivalent to old-style, but perhaps more elegant • Introduced in Oracle 8i • Older versions / other DBMSs may only support old-style syntax Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  45. 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 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  46. New-style outer joins • Outer joins may be left, right, or middle • FROM A LEFT [OUTER] JOIN B; • FROM A RIGHT [OUTER] JOIN B; • FROM A FULL [OUTER] JOIN B; • “OUTER” is optional • If “OUTER” is included, then “FULL” is the default • Q: How to remember left v. right? • A: It indicates the side whose rows are always included Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  47. Old-style outer joins • Outer joins can also be done with the old-style syntax, but with the (+) • …WHERE A.att=B.att(+) corresponds to: • …FROM A LEFT JOIN B; • The (+) is applied to all B attributes referred to in the WHERE clause • Q: How to remember which side gets the (+)? • A: The side that gets null rows “added” Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  48. SQL e.g. • People(ssn, name, street, city, state) • assume for clarity that cities are unique • Q: Who lives on George’s street? • Now, the second way, conceptually: People ⋈Pstreet,city(sname=“George”(People)) • In SQL? Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  49. Live Examples • Examples from sqlzoo.net Matthew P. Johnson, OCL1, CISDD CUNY, F2004

More Related