FEN 2014-04-27 1

• More about SELECT, Nested selects

• GROUP BY, HAVING, ORDER BY

• Other joins

• Aggregate functions

• Views

More about SQL

2

SQL2 - DML

(Q16): Sub queries (nested SELECTs)

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE EWHERE E.SSN IN (SELECT ESSN

FROM DEPENDENTWHERE ESSN = E.SSN

ANDE.FNAME = DEPENDENT_NAME

AND SEX = E.SEX)

Also ANY (SOME) and ALL in combination with comparison operators(>, >=, <, >= and <>).

FEN 2014-04-27

FEN 2014-04-27 3

SQL2 - DML(Q16): Sub queries (nested SELECTs)

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE EWHERE E.SSN IN (SELECT ESSN

FROM DEPENDENTWHERE 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 4

SQL2 - DML(Q7): Existential quantifier - EXISTS:

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE EXISTS (SELECT *

FROM DEPENDENTWHERE SSN = ESSN)

ANDEXISTS (SELECT *

FROM DEPARTMENT

WHERE SSN = MGRSSN)

FEN 2014-04-27 5

SQL2 - DML(Q6): NOT EXISTS:

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT EXISTS (SELECT *

FROMDEPENDENT

WHERE SSN =ESSN)

FEN 2014-04-27 6

SQL2 - DML For 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 7

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

A Side: Predicate Logic

FEN 2014-04-27 8

SQL2 - DML

(Q3B): ”Retrieve the name of each employee who works on all projects controlled by department number 5”

SELECT LNAME, FNAMEFROM EMPLOYEEWHERE NOT EXISTS

(SELECT * FROM WORKS_ON B WHERE (B.PNO IN (SELECT PNUMBERFROM PROJECTWHERE DNUM = 5))ANDNOT EXISTS (SELECT * FROM WORKS_ON C WHERE C.ESSN = SSNAND C.PNO=B.PNO))

FEN 2014-04-27 9

SQL2 - DML

SELECT DISTINCT ESSNFROM WORKS_ONWHERE PNO IN (1,2,3)

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SUPERSSN IS NULL

FEN 2014-04-27 10

SQL2 - DML

SELECT E.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAMEFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.SUPERSSN = S.SSN

New coulomb names in the resulting table.AS may be omitted.

FEN 2014-04-27 11

SQL2 - DML

Alternative notations for join:

SELECT FNAME, LNAME, ADDRESSFROM (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER)WHERE DNAME = ’Research’

Provides a more clear syntax and opens for more specialised joins.

FEN 2014-04-27 12

SQL2 - DML

Natural join (not MS SQL Server):(Q1B):SELECT FNAME, LNAME, ADDRESSFROM (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 13

SQL2 - DML

Outer join:

SELECT E.LNAME AS EMP_NAME, S.LNAME AS SUPER_NAMEFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE 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_NAMEFROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN = S.SSN)

Also RIGTH OUTER JOIN and FULL OUTER JOIN.

FEN 2014-04-27 14

SQL2 - DML

FEN 2014-04-27 15

What about employees with no

supervisor?

FEN 2014-04-27 16

Here they are!

FEN 2014-04-27 17

SQL2 - DML

FEN 2014-04-27 18

SQL2 - DML

FEN 2014-04-27 19

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 20

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 21

SQL2 - DML

Aggregate Functions:

COUNTSUMMAXMINAVG How are NULLs

treated?AVG(-) ==

SUM(-)/COUNT(-)???

FEN 2014-04-27 22

SQL2 - DMLEx.: ”Number of Employees in the research department”

SELECT COUNT(*)FROM EMPLOYEE, DEPARTMENTWHERE DNO = DNUMBER

AND DNAME = ’Research’

FEN 2014-04-27 23

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 24

Result of Q24

FEN 2014-04-27 25

SQL2 - DML(Q26)

FEN 2014-04-27 26

Result of Q26, 1

FEN 2014-04-27 27

Result of Q26, 2

FEN 2014-04-27 28

SQL2 - DML

Break for Exercises

Elmasri: 5.5a

FEN 2014-04-27 29

FEN 2014-04-27 30

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 31

SQL - VIEWSCREATE VIEW WORKS_ON1

AS SELECT FNAME, LNAME, PNAME, HOURSFROM EMPLOYEE, PROJECT, WORKS_ONWHERE SSN=ESSN AND PNO=PNUMBER;

Using this view, the query:

SELECT FNAME, LNAME, PNAMEFROM EMPLOYEE, PROJECT, WORKS_ONWHERE PNAME = 'ProductX'

AND SSN = ESSN AND PNO = PNUMBER;

May written as

SELECT FNAME, LNAME, PNAMEFROM WORKS_ON1WHERE PNAME = 'ProductX';

And hence saving the join

FEN 2014-04-27 32

SQL - VIEWSUpdating through views is problematic:FX:

Transfer John Smith from the project 'ProductX' to the project 'ProductY’

UPDATE WORKS_ON1SET PNAME = ’ProductY’WHERE LNAME = ’Smith’

AND FNAME = ’John’AND PNAME = ’ProductX’

FEN 2014-04-27 33

SQL - VIEWS

Which update of the base tables should be executed?This?

Or this?

FEN 2014-04-27 34

SQL - VIEWS

Views and update:

Updatableviews

Gray zone

Not updatable vies

FEN 2014-04-27 35

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 36

Exercises

1. From session032. Investigate how MS SQL Server implements

some of the SQL constructs treated to day3. Elmasri 5.74. Elmasri 5.8, 5.95. Try out some of the queries in solutions6. Extra: FlereSQLOpgaver.pdf