More about sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

More about SQL PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on
  • Presentation posted in: General

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) SELECTE.FNAME, E.LNAME FROMEMPLOYEE E WHEREE.SSNIN(SELECTESSN FROMDEPENDENT WHEREESSN = E.SSN

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

SELECTE.FNAME, E.LNAME

FROMEMPLOYEE E

WHEREE.SSNIN(SELECTESSN

FROMDEPENDENT

WHEREESSN = E.SSN

ANDE.FNAME = DEPENDENT_NAME

ANDSEX = 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)

SELECTE.FNAME, E.LNAME

FROMEMPLOYEE E

WHEREE.SSN IN(SELECTESSN

FROMDEPENDENT

WHEREESSN = E.SSN

ANDE.FNAME = DEPENDENT_NAME

ANDSEX = 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:

SELECTFNAME, LNAME

FROMEMPLOYEE

WHEREEXISTS(SELECT*

FROMDEPENDENT

WHERESSN = ESSN)

AND

EXISTS(SELECT*

FROMDEPARTMENTWHERESSN = MGRSSN)

FEN 2014-04-27


Sql2 dml3

SQL2 - DML

(Q6): NOT EXISTS:

SELECTFNAME, LNAME

FROMEMPLOYEE

WHERENOT EXISTS(SELECT*

FROMDEPENDENT

WHERESSN =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”

SELECTLNAME, FNAME

FROMEMPLOYEE

WHERENOT EXISTS

(SELECT*

FROMWORKS_ON B

WHERE(B.PNO IN(SELECTPNUMBER

FROMPROJECT

WHEREDNUM = 5))

AND

NOT EXISTS(SELECT*

FROMWORKS_ON C WHEREC.ESSN = SSN

AND C.PNO=B.PNO))

FEN 2014-04-27


Sql2 dml5

SQL2 - DML

SELECT DISTINCTESSN

FROMWORKS_ON

WHEREPNO IN (1,2,3)

SELECTFNAME, LNAME

FROMEMPLOYEE

WHERESUPERSSN IS NULL

FEN 2014-04-27


Sql2 dml6

SQL2 - DML

SELECTE.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAME

FROMEMPLOYEE AS E, EMPLOYEE AS S

WHEREE.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:

SELECTFNAME, LNAME, ADDRESS

FROM(EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER)

WHEREDNAME = ’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):

SELECTFNAME, LNAME, ADDRESS

FROM(EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT

(DNAME,DNO,MSSN,MSDATE)))

WHEREDNAME = ’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:

SELECTE.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAME

FROMEMPLOYEE AS E, EMPLOYEE AS S

WHEREE.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.

SELECTE.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


More about sql

What about employees with no supervisor?

FEN 2014-04-27


More about sql

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”

    SELECTCOUNT(*)

    FROMEMPLOYEE, DEPARTMENT

    WHEREDNO = 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 VIEWWORKS_ON1

    AS SELECTFNAME, LNAME, PNAME, HOURS

    FROMEMPLOYEE, PROJECT, WORKS_ON

    WHERESSN=ESSN AND PNO=PNUMBER;

    Using this view, the query:

    SELECTFNAME, LNAME, PNAME

    FROMEMPLOYEE, PROJECT, WORKS_ON

    WHEREPNAME = 'ProductX'

    AND SSN = ESSN AND PNO = PNUMBER;

    May written as

    SELECTFNAME, LNAME, PNAME

    FROMWORKS_ON1

    WHEREPNAME = '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’

      UPDATEWORKS_ON1

      SETPNAME = ’ProductY’

      WHERELNAME = ’Smith’

      ANDFNAME = ’John’

      ANDPNAME = ’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


  • Login