1 / 39

More about SQL

More about SQL. Nested SELECT Other Joins Views Index. SQL2 - DML. (Q16): Sub queries (nested SELECTs) SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN = E.SSN AND E.FNAME = DEPENDENT_NAME AND SEX = E.SEX)

megan
Download Presentation

More about 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. More about SQL Nested SELECT Other Joins Views Index NOEA/IT FEN - Databases/SQL

  2. SQL2 - DML (Q16): Sub queries (nested SELECTs) SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN = E.SSN AND E.FNAME = DEPENDENT_NAME AND SEX = E.SEX) Also ANY (SOME) and ALL in combination with comparison operators (>, >=, <, >= and <>). NOEA/IT FEN - Databases/SQL

  3. SQL2 - DML (Q16): Sub queries (nested SELECTs) SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN = E.SSN AND E.FNAME = DEPENDENT_NAME AND SEX = E.SEX) For each row in outer table (E), the inner SELECT is executed. If E.SSN is contained in the result of the inner SELECT, then E is included in the result table for the outer SELECT. NOEA/IT FEN - Databases/SQL

  4. SQL2 - DML (Q7): Existential quantifier - EXISTS: SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN = ESSN) AND EXISTS (SELECT * FROM DEPARTMENT WHERE SSN = MGRSSN) NOEA/IT FEN - Databases/SQL

  5. SQL2 - DML (Q6): NOT EXISTS: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN =ESSN) NOEA/IT FEN - Databases/SQL

  6. SQL2 - DMLFor All i SQL • Although SQL is supposed to be an implementation of first order predicate logic, it does not support the universal qualifier (FORALL), only the existential quantifier (EXISTS) is supported. • A well known (?) result from predicate logic can be used in a workaround: • Retrieving all elements satisfying some predicate is equivalent to retrieving elements that are not in the set of elements that do not satisfy the predicate: SELECT * FROM --- WHERE NOT EXISTS (SELECT * FROM --- WHERE NOT EXISTS ---- NOEA/IT FEN - Databases/SQL

  7. A Side: Predicate Logic • Let x be an arbitrary element in some set and p a predicate stating some condition on x: • De Morgan’s Law: (x: p(x))  x: p(x) Apply to p(x): (x: p(x))  x: (p(x)) Reduce the right hand side: x: p(x)  (x: p(x)) “it is not true that there exists x, so p(x) is not true” – that is: “p is true for all x” SELECT * FROM --- WHERE NOT EXISTS (SELECT * FROM --- WHERE NOT EXISTS ---- NOEA/IT FEN - Databases/SQL

  8. SQL2 - DML (Q3B): ”Retrieve the name of each employee who works on all projects controlled by department number 5” SELECT LNAME, FNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM WORKS_ON B WHERE (B.PNO IN (SELECT PNUMBER FROM PROJECT WHERE DNUM = 5)) AND NOT EXISTS (SELECT * FROM WORKS_ON C WHERE C.ESSN = SSN AND C.PNO=B.PNO)) NOEA/IT FEN - Databases/SQL

  9. SQL2 – DML: SELECT Queries: SELECT <attribute-list> FROM <tables> [WHERE <condition>] [GROUP BY <attribute-list>] [HAVING <condition>] [ORDER BY <attribute-list>] [...]: WHERE, GROUP BY, HAVING and ORDER BY may be omitted. NOEA/IT FEN - Databases/SQL

  10. SQL2 - DML SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1,2,3) SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL NOEA/IT FEN - Databases/SQL

  11. SQL2 - DML SELECT E.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAME FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN = S.SSN New coulomb names in the resulting table. AS may be omitted in FROM part. NOEA/IT FEN - Databases/SQL

  12. SQL2 - DML Alternative notations for join: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER) WHERE DNAME = ’Research’ Provides a more clear syntax and opens for more specialised joins. NOEA/IT FEN - Databases/SQL

  13. SQL2 - DML Natural join (not MS SQL Server): (Q1B): SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT (DNAME,DNO,MSSN,MSDATE))) WHERE DNAME = ’Research’ DEPARTMENT.DNUMBER must be rename to DNO in order to match EMPLOYEE.DNO. Natural join is over two attributes with the same name (EMPLOYEE.DNO = DEPT.DNO). NOEA/IT FEN - Databases/SQL

  14. SQL2 - DML Outer join: SELECT E.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAME FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN = S.SSN Retrieves only employees who have a supervisor. Left Outer Join retrieves all employees and inserts NULL in the S-attributes for employees with no supervisor. SELECT E.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAME FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN = S.SSN) Also RIGTH OUTER JOIN and FULL OUTER JOIN. NOEA/IT FEN - Databases/SQL

  15. SQL2 - DML NOEA/IT FEN - Databases/SQL

  16. What about employees with no supervisor? NOEA/IT FEN - Databases/SQL

  17. Here they are! NOEA/IT FEN - Databases/SQL

  18. SQL2 - DML NOEA/IT FEN - Databases/SQL

  19. SQL2 - DML NOEA/IT FEN - Databases/SQL

  20. SQL2 - DML Also: • CROSS JOIN (Cartesian Product) • UNION JOIN • SQL2 provides many different ways of expressing the same join: • This can be view as an advantage: • More simple expressions • Or as an disadvantage: • More complicated language NOEA/IT FEN - Databases/SQL

  21. SQL2 - DML Aggregate Functions: • COUNT • SUM • MAX • MIN • AVG NOEA/IT FEN - Databases/SQL

  22. SQL2 - DML Ex.: ”Number of Employees in the research department” SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNO = DNUMBER AND DNAME = ’Research’ NOEA/IT FEN - Databases/SQL

  23. SQL2 - DML(Q24) NOEA/IT FEN - Databases/SQL

  24. Result of Q24 NOEA/IT FEN - Databases/SQL

  25. SQL2 - DML(Q26) NOEA/IT FEN - Databases/SQL

  26. Result of Q26, 1 NOEA/IT FEN - Databases/SQL

  27. Result of Q26, 2 NOEA/IT FEN - Databases/SQL

  28. SQL2 - DML NOEA/IT FEN - Databases/SQL

  29. SQL - VIEWS • A view is virtual table which is created from one or more existing base tables. • Views may be used in a layered architecture to provide different view of the database to different users. • May also be used to increase efficiency of frequent queries, for instance to avoid JOINs. NOEA/IT FEN - Databases/SQL

  30. SQL - VIEWS CREATE VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER; Using this view, the query: SELECT FNAME, LNAME, PNAME FROM EMPLOYEE, PROJECT, WORKS_ON WHERE PNAME = 'ProductX' AND SSN = ESSN AND PNO = PNUMBER; May written as SELECT FNAME, LNAME, PNAME FROM WORKS_ON1 WHERE PNAME = 'ProductX'; And hence saving the join NOEA/IT FEN - Databases/SQL

  31. SQL - VIEWS • Updating through views is problematic: • FX: Transfer John Smith from the project 'ProductX' to the project 'ProductY’ UPDATE WORKS_ON1 SET PNAME = ’ProductY’ WHERE LNAME = ’Smith’ AND FNAME = ’John’ AND PNAME = ’ProductX’ NOEA/IT FEN - Databases/SQL

  32. SQL - VIEWS Which update of the base tables should be executed? This? Or this? NOEA/IT FEN - Databases/SQL

  33. Not updatable views Gray zone Updatable views SQL - VIEWS Views and update: NOEA/IT FEN - Databases/SQL

  34. SQL - VIEWS Generally: • Views defined over one base table can be updated, if the primary key (ore some candidate key) is included in the view. • Views defined by joining more base tables are generally not updatable. • Some joined view are in principle updatable: all primary keys from the base tables must be included in the view. • Views defined using aggregate or grouping functions are not updatable. • SQL2 standard establishes that joined view are not updatable. NOEA/IT FEN - Databases/SQL

  35. Index • Index is often considered part of the DDL of SQL • index is about internal physical storage access, and • as such NOT a part of the SQL2 standard • many SQL versions include index anyway • Previous versions of SQL includes a CREATE INDEX statement • Is used for speeding up queries, for instance: CREATE INDEX LNAME_INDEX ON EMPLOYEE(LNAME); • Index is in some older SQL implementations also used to simulate primary keys: CREATE UNIQUE INDEX SSN_INDEX ON EMPLOYEE(SSN); NOEA/IT FEN - Databases/SQL

  36. Index Why is that? • Often it is possible to define one (and only one) clustering index: CREATE INDEX DNO_INDEX ON EMPLOYEE(DNO) CLUSTER; • The effect is that employees in the same department are stored physically close • Hereby queries concerning employees in the same department are speeded up. NOEA/IT FEN - Databases/SQL

  37. Databaser (SQL) og programudviklingsværktøjer ODBC/JDBC giver standardiseret tilgang til RDBMS NOEA/IT FEN - Databases/SQL

  38. Databaseprogrammering fra 3GLs og OOPLs • SQL og typiske programmeringssprog som Java, C, Cobol etc. er ikke ”kompatible”: • Impedans-mismatch: • Programmeringssprogets datatyper er ikke afstemt med databasens domæner • SQL er mængde- (tabel-) orienteret: • alle resultater er en tabel med et antal tupler. • 3GLs og OOPLs er tuple- (objekt-)orienteret, dvs. • der er behov for en datastruktur i programmeringssproget NOEA/IT FEN - Databases/SQL

  39. Embedded SQL Databaseaccess vha. SQL fra 3GL/OOPL (værtssprog): Er baseret Cursor-begrebet: • En cursor kan opfattes som en pointer, der udpeger aktiv række i tabellen, som SQL-sætningen resulterer i • En cursor er nødvendig, når resultatet af en SQL-sætning er mere end een tuple (værtssproget er record- (objekt-) orienteret) • Er leverandørafhængig • Visse moderne værktøjer (fx Visual Studio.NET) kan tilsyneladende returnere en mængde af forekomster (dataset eller ResultSet, men reelt er der tale om en indpakket cursor • ODBC (Open Data Base Connectivity): Resultset svarer til en cursor. • I OOPLs kaldes en cursor ofte en iterator (Enumerator i C#). NOEA/IT FEN - Databases/SQL

More Related