More about sql
Download
1 / 36

More about SQL - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

More about SQL. More about SELECT, Nested selects GROUP BY, HAVING, ORDER BY Other joins Aggregate functions Views. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' More about SQL' - amal


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
More about sql

More about SQL

More about SELECT, Nested selects

GROUP BY, HAVING, ORDER BY

Other joins

Aggregate functions

Views

FEN 2014-04-27


Sql2 dml
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 <>).

FEN 2014-04-27


Sql2 dml1
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.

FEN 2014-04-27


Sql2 dml2
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)

FEN 2014-04-27


Sql2 dml3
SQL2 - DML

(Q6): NOT EXISTS:

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE NOT EXISTS (SELECT *

FROM DEPENDENT

WHERE SSN =ESSN)

FEN 2014-04-27


Sql2 dml for all i sql
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 ----

FEN 2014-04-27


A side predicate logic
A Side: Predicate Logic

  • Let x 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 ----

FEN 2014-04-27


Sql2 dml4
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))

FEN 2014-04-27


Sql2 dml5
SQL2 - DML

SELECT DISTINCT ESSN

FROM WORKS_ON

WHERE PNO IN (1,2,3)

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE SUPERSSN IS NULL

FEN 2014-04-27


Sql2 dml6
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.

FEN 2014-04-27


Sql2 dml7
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.

FEN 2014-04-27


Sql2 dml8
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).

FEN 2014-04-27


Sql2 dml9
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.

FEN 2014-04-27


Sql2 dml10
SQL2 - DML

FEN 2014-04-27



Here they are!

FEN 2014-04-27


Sql2 dml11
SQL2 - DML

FEN 2014-04-27


Sql2 dml12
SQL2 - DML

FEN 2014-04-27


Sql2 dml13
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

  • FEN 2014-04-27


    Sql2 dml select
    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.

    FEN 2014-04-27


    Sql2 dml14
    SQL2 - DML

    Aggregate Functions:

    • COUNT

    • SUM

    • MAX

    • MIN

    • AVG

    How are NULLs treated?

    AVG(-) == SUM(-)/COUNT(-)

    ???

    FEN 2014-04-27


    Sql2 dml15
    SQL2 - DML

    Ex.: ”Number of Employees in the research department”

    SELECT COUNT(*)

    FROM EMPLOYEE, DEPARTMENT

    WHERE DNO = DNUMBER

    AND DNAME = ’Research’

    FEN 2014-04-27


    Sql2 dml q24
    SQL2 - DML(Q24)

    Try this one with at least one employee with a null value in salary.

    Compare the result with the query:

    Select DNO, COUNT(*), SUM(SALARY)/COUNT(*)

    FEN 2014-04-27


    Result of q24
    Result of Q24

    FEN 2014-04-27


    Sql2 dml q26
    SQL2 - DML(Q26)

    FEN 2014-04-27


    Result of q26 1
    Result of Q26, 1

    FEN 2014-04-27


    Result of q26 2
    Result of Q26, 2

    FEN 2014-04-27


    Sql2 dml16
    SQL2 - DML

    FEN 2014-04-27


    Break for exercises
    Break for Exercises

    • Elmasri: 5.5a

    FEN 2014-04-27


    Sql views
    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.

    FEN 2014-04-27


    Sql views1
    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

    FEN 2014-04-27


    Sql views2
    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’

    FEN 2014-04-27


    Sql views3
    SQL - VIEWS

    Which update of the base tables should be executed?

    This?

    Or this?

    FEN 2014-04-27


    Sql views4

    Not updatable vies

    Gray zone

    Updatable

    views

    SQL - VIEWS

    Views and update:

    FEN 2014-04-27


    Sql views5
    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.

    FEN 2014-04-27


    Exercises
    Exercises

    • From session03

    • Investigate how MS SQL Server implements some of the SQL constructs treated to day

    • Elmasri 5.7

    • Elmasri 5.8, 5.9

    • Try out some of the queries in solutions

    • Extra: FlereSQLOpgaver.pdf

    FEN 2014-04-27


    ad