1 / 43

SQL

SQL. SELECT-FROM-WHERE. SELECT * FROM Student WHERE sName=“Greg” AND address=“320 FL”. Student.  (sName=“Greg” AND address=“320 FL”) (Student). Project. SELECT sNumber, sName FROM Student. Student.  (sNumber, sName) (Student). Extended Projection. SELECT sNumber || sName AS info

akeem-hardin
Download Presentation

SQL

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. SQL Murali Mani

  2. SELECT-FROM-WHERE SELECT * FROM Student WHERE sName=“Greg” AND address=“320 FL” Student  (sName=“Greg” AND address=“320 FL”) (Student) Murali Mani

  3. Project SELECT sNumber, sName FROM Student Student  (sNumber, sName) (Student) Murali Mani

  4. Extended Projection SELECT sNumber || sName AS info FROM Student WHERE address=“320 FL” Student  (sNumber||sNameinfo) ( (address=“320 FL”) (Student)) Murali Mani

  5. SQL and Relational Algebra In short,  L ( C (R)) becomes SELECT L FROM R WHERE C Murali Mani

  6. Renaming SELECT s1.sNumber AS num FROM Student S1 WHERE s1.sNumber >= 1; Student  (s1.sNumbernum) ( (s1.sNumber >= 1) ( S1 (Student))) Murali Mani

  7. String operators • Comparison Operators based on lexicographic ordering: =, <, >, <>, >=, <= • Concatenation operator: || • ‘ represented in strings with two consecutive ‘ • Pattern match: s LIKE p • p = pattern • % : any sequence of 0 or more characters • - : matches 1 character • Patterns can explicitly declare escape characters as: s LIKE ‘x%%am%’ ESCAPE ‘x’ Murali Mani

  8. Comparison with NULL values • Arithmetic operations on NULL return NULL. • Comparison operators on NULL return UNKNOWN. • We can explicitly check whether a value is null or not, by IS NULL, IS NOT NULL. Murali Mani

  9. Truth table with UNKNOWN UNKNOWN AND TRUE = UNKNOWN UNKNOWN OR TRUE = TRUE UNKNOWN AND FALSE = FALSEUNKNOWN OR FALSE = UNKNOWN UNKNOWN AND UNKNOWN = UNKNOWN UNKNOWN OR UNKNOWN = UNKNOWN NOT UNKNOWN = UNKNOWN A WHERE clause is satisfied only when it evaluates to TRUE. Murali Mani

  10. UNION, INTERSECT, EXCEPT • UNION, INTERSECT, EXCEPT have set semantics. • For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL (SELECT sName FROM Student) EXCEPT (SELECT sName FROM Student WHERE address=‘320 FL’) Murali Mani

  11. EXCEPT - Example (SELECT sName FROM Student) EXCEPT (SELECT sName FROM Student WHERE address=‘320 FL’) Student Murali Mani

  12. Joins SELECT sName FROM Student, Professor WHERE pName=‘MM’ AND pNumber=professor; (sName)(Student ⋈(pName=‘MM’ and pNumber=professor) Professor) Murali Mani

  13. Joins - example Student SELECT sName FROM Student, Professor WHERE pName=‘MM’ AND pNumber=professor; Professor Murali Mani

  14. Cross Product (Cartesian Product) SELECT * FROM Student CROSS JOIN Professor; Student X Professor can also be written as: SELECT * FROM Student, Professor Murali Mani

  15. Cross Product - Example Student Professor Murali Mani

  16. Theta Join SELECT * FROM Student JOIN Professor ON professor=pNumber; Student ⋈(professor=pNumber) Professor SELECT * FROM Student, Professor WHERE professor=pNumber; Murali Mani

  17. Theta Join Example Murali Mani

  18. Natural Join SELECT * FROM Student NATURAL JOIN Professor (Note: This requires the columns on which the join should be done should have the same names for Student and Professor). Student ⋈ Professor Murali Mani

  19. Natural Join - Example Student Professor Murali Mani

  20. Outer Joins SELECT * FROM Student NATURAL FULL OUTER JOIN Professor Student ⋈oProfessor SELECT * FROM Student NATURAL LEFT OUTER JOIN Professor Student ⋈oLProfessor Murali Mani

  21. Outer Joins SELECT * FROM Student NATURAL RIGHT OUTER JOIN Professor Student ⋈oRProfessor Murali Mani

  22. Outer Joins - Example Student Professor Murali Mani

  23. Sorting: ORDER BY clause SELECT * FROM Student WHERE sNumber >= 1 ORDER BY sNumber, sName  (sNumber, sName) ( (sNumber >= 1) (Student)) Murali Mani

  24. Subqueries SELECT * FROM Student WHERE professor = (SELECT pName FROM Professor WHERE pNumber=1) Note: the inner subquery returns a relation, but SQL runtime ensures that the subquery returns a relation with one column and with one row, otherwise it is a run-time error. Murali Mani

  25. Subqueries - Example Student SELECT * FROM Student WHERE professor = (SELECT pName FROM Professor WHERE pNumber=1) Professor Murali Mani

  26. Subqueries • We can use IN, EXISTS (also NOT IN, NOT EXISTS) • ALL, ANY can be used with comparisons SELECT * FROM Student WHERE (sNumber, professor) IN (SELECT pNumber, pName FROM Professor) Murali Mani

  27. Subqueries - Example Student Professor SELECT * FROM Student WHERE (sNumber, professor) IN (SELECT pNumber, pName FROM Professor) Murali Mani

  28. Subqueries: EXISTS Professor SELECT * FROM Student WHERE EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName) Student Murali Mani

  29. Subqueries with negation Professor SELECT * FROM Student WHERE (sNumber, professor) NOT IN (SELECT pNumber, pName FROM Professor) Student Murali Mani

  30. Subqueries with negation Student Professor SELECT * FROM Student WHERE NOT EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName) Murali Mani

  31. Subqueries: ALL, ANY SELECT * FROM Student WHERE sNumber > ALL (SELECT pNumber FROM Professor) SELECT * FROM Student WHERE sNumber = ANY (SELECT pNumber FROM Professor) Murali Mani

  32. Subqueries: ALL - Example Student Professor SELECT * FROM Student WHERE sNumber > ALL (SELECT pNumber FROM Professor) Murali Mani

  33. Subqueries: ANY - Example Student Professor SELECT * FROM Student WHERE sNumber = ANY (SELECT pNumber FROM Professor) Murali Mani

  34. Subqueries: NOT ALL - Example Student Professor SELECT * FROM Student WHERE NOT sNumber > ALL (SELECT pNumber FROM Professor) Murali Mani

  35. Subqueries: NOT ANY - Example Student Professor SELECT * FROM Student WHERE NOT sNumber = ANY (SELECT pNumber FROM Professor) Murali Mani

  36. Subqueries: Tip Murali Mani

  37. Subqueries in FROM clause Professor SELECT sName, pName FROM Student, (SELECT * FROM Professor WHERE pNumber=1) WHERE professor=pName; Student Murali Mani

  38. Duplicate Elimination SELECT DISTINCT address FROM Student WHERE sNumber >= 1; SELECT DISTINCT * FROM Student; • (Student) • ( (address) ( (sNumber >= 1) (Student))) Student Murali Mani

  39. Aggregation SELECT COUNT (*) FROM Student; SELECT COUNT (sNumber) FROM Student; SELECT MIN (sNumber) FROM Student; SELECT MAX (sNumber) FROM Student; SELECT SUM (sNumber) FROM Student; SELECT AVG (sNumber) FROM Student; We can have distinct such as: SELECT COUNT (DISTINCT sNumber) FROM Student Murali Mani

  40. Grouping SELECT COUNT (sName) FROM Student GROUP BY address; • (COUNT (sName)) ( (address, COUNT (sName)) (Student)) Student Murali Mani

  41. Grouping SELECT address, COUNT (sNumber) FROM Student WHERE sNumber > 1 GROUP BY address HAVING COUNT (sNumber) > 1; Student Murali Mani

  42. Aggregation and NULLs • NULLs are ignored in any aggregation; except COUNT (*) • However if the set of attributes to be grouped on has null values, then grouping is done on the null values as well. Murali Mani

  43. SQL Queries - Summary SELECT [DISTINCT] a1, a2, …, an FROM R1, R2, …, Rm [WHERE C1] [GROUP BY g1, g2, …, gl [HAVING C2]] [ORDER BY o1, o2, …, oj] Murali Mani

More Related