Analysis and Design of Data Systems. Complex SQL Queries (Lecture 13) презентация

SELECT Fname FROM EMPLOYEE;

First names of all employees are retrieved

BUT

If more than one relation is specified in the FROM clause and there is no WHERE clause, then the CROSS PRODUCT—all possible tuple combinations—of these relations is selected

SELECT Fname, Dname FROM EMPLOYEE e, DEPARTMENT d
WHERE e.Dno = d.Dnumber;

For expected result we have to add
WHERE clause:

1)

2)

3)

Try these examples at home on MySQL

SQL does not automatically eliminate duplicate tuples in the results of queries, for the following reasons:

Duplicate elimination is an expensive operation.
The user may want to see duplicate tuples in the result of a query.
When an aggregate function (will learn later) is applied to tuples, in most cases we do not want to eliminate duplicates.

In that context table is a multiset rather than a set

The relations resulting from these set operations are sets of tuples; that is, duplicate tuples are eliminated from the result.

These set operations apply only to union-compatible relations, so we must make sure that the two relations on which we apply the operation have the same attributes and that the attributes appear in the same order in both relations.

UNION ALL, EXCEPT ALL, INTERSECT ALL: read in section 4.3.4

A B

A B

UNION

EXCEPT

INTERSECTION

Tables as Sets in SQL (UNION)

LIKE, AS, BETWEEN, ORDER BY: read in sections 4.3.5 – 4.3.6

(SELECT DISTINCT Pnumber
FROM PROJECT p, DEPARTMENT d, EMPLOYEE e
WHERE p.Dnum = d.Dnumber AND d.Mgr_ssn = e.Ssn
AND e.Lname = ‘Wong’)
UNION
(SELECT DISTINCT Pnumber
FROM WORKS_ON w, PROJECT p, EMPLOYEE e
WHERE w.Essn = e.Ssn AND w.Pno = p.Pnumber
AND e.Lname = 'Wong');

We can understand a correlated query better by considering that the nested query is evaluated once for each tuple (or combination of tuples) in the outer query

Example: Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee.

DEPENDENT (Essn, Dependent_name, Sex, Bdate, Realtionship);

DEPENDENT (Essn, Dependent_name, Sex, Bdate, Realtionship);

The result of EXISTS is a Boolean value TRUE if the nested query result contains at least one tuple, or FALSE if the nested query result contains no tuples .

Example from previous slide:

Let’s rephrase the query:

Retrieve the name of each employee who works on all the projects controlled by department number 5

Before:

After: