More SQL

Why is SQL sometimes hard?

Basically, it's hard because it is a non-imperative language: you don't issue a sequence of commands. Instead, you write a single big expression. It's hard to visualize big expressions, and hard to test them incrementally. Nested queries do seem to help in this regard.

(Actually, the data types of SQL are tables, and you can in fact save a table in a "table variable". But this is rare.)

Perhaps because of this non-imperative nature of the language, it is relatively easy to read several SQL examples and still have trouble knowing where to start when trying to write one from scratch.

What Joins Do

Self-join

Without a join, all you can look at is one row at a time of one table. When we join EMPLOYEE and DEPARTMENT, eg

select e.lname, d.dname from employee e, department d where e.dno = d.dnumber;

we are looking at one row at a time of each table, but two tables. The DEPARTMENT table is being used here to "extend" the EMPLOYEE table: each employee record now has additional attributes from DEPARTMENT.

Suppose we want to print a list of all employees and their supervisors. Here's the approach we might take:

select e.fname, e.lname, s.fname, s.lname
from employee e join employee s on e.super_ssn = s.ssn;

The idea here, in terms of cursors, is for e to traverse linearly the employee table. For each employee, we get e.super_ssn, and then use s to traverse the same table, looking for a row with s.ssn = e.super_ssn. Each record in the result is a combination of two rows of table EMPLOYEE, so a join is necessary.

In terms of table extension, the EMPLOYEE table is being "extended" to include additional attributes about supervisors besides just super_ssn.

This particular example is much more readable if we relabel the supervisor columns in the output

select e.fname, e.lname, s.fname AS super_fname, s.lname AS super_lname
from employee e join employee s on e.super_ssn = s.ssn;

Note that the AS here is not optional, and plays a rather different role than the AS that can be used in the FROM section (from employee AS e, employee AS s).

A tricky foreign-key constraint

MySQL

Postgres

Generally, intersections are easy to implement as joins, and the join approach may be more efficient. As a general rule, an intersection of the form

(select a.col1, a.col2 from TableA a)
intersect
(select b.col1, b.col2 from TableB b)

is equivalent to the following (which may be a self-join if TableA and TableB are the same table):

select a.col1, a.col2 from TableA a join TableB b on a.col1 = b.col1 and a.col2 = b.col2

In some cases only one of the two join conditions may be necessary; if col1 is a primary key, for example, then you can drop the col2 comparison.

The Company query above, where we find employees who have worked on project 2 and on project 3, is of this general form, though we only needed the employee table once in the join. TableA corresponds to employee join works_on w1, and TableB corresponds to employee join works_on w2.

WHERE conditions

In many cases, the where conditions are either join conditions or are simple Boolean expressions about a single row. Sometimes, however, the question being asked relates to other rows, as in "List employees who worked on more than one project"; if we form the join table of employees and their projects we still cannot answer this row-by-row. Another query might be to list employees who are part of some set of employees whose salaries add up to exactly 100,000.

The answer to the employees-who-worked-on-multiple-projects query was a "triple" join with inequality as one of the join conditions:

select e.fname, e.lname from employee e, works_on w1, works_on w2
where e.ssn = w1.essn and e.ssn = w2.essn and w1.pno <> w2.pno;

or

select e.fname, e.lname from employee e join works_on w1 on e.ssn = w1.essn
    join works_on w2 on e.ssn = w2.essn
where w1.pno <> w2.pno;

Most joins are equijoins; that is, joins involving an equality relationship. We can write this one as an equijoin, with the inequality in the where clause, but, in the first version, this isn't entirely obvious.

Actually, this particular example might be easier to understand using a subquery and the count operator (which we'll cover below):

select e.fname, e.lname from employee e
where (select count(w.pno) from works_on w where w.essn = e.ssn) > 1

In general, WHERE conditions that just select rows of a table satisfying a given Boolean condition are fundamentally trivial. Joins in WHERE conditions are a little less trivial, and WHERE conditions that select a record (or part of a record) depending on other records in the table tend to be the hardest. Example: list all students who got straight A's; this lists the student_id values from grade_report where all of the matching rows have an A in the grade column. This last category often involves grouping, or subqueries, or some other advanced feature.

Examples from above revisited

Joins again

In all the examples presented above, one of the join attributes is the primary key of the table it appears in. Here are most of those examples again, where for each join's equality condition the attribute that is the primary key of its table is in bold.

select e.lname, d.dname from employee e join department d on e.dno = d.dnumber;

select distinct p.pnumber
from project p join department d on d.dnumber = p.dnum
    join employee e on d.mgr_ssn = e.ssn
where e.lname = 'Smith'

select distinct p.pnumber
from project p join works_on w on p.pnumber = w.pno
    join employee e on w.essn = e.ssn
where e.lname = 'Smith'

select d.dname, e.lname, e.fname, p.pname
from department d join employee e on d.dnumber = e.dno
    join works_on w on e.ssn = w.essn
    join project p on w.pno = p.pnumber

select e.fname, e.lname , w.pno
from employee e join works_on w on e.ssn = w.essn
where w.pno = 2 or w.pno =3;

select e.fname, e.lname
from project p join works_on w on p.pnumber = w.pno
join employee e on w.essn = e.ssn
where p.plocation = 'Stafford';

When this is done with two tables, the other table (with join attribute that is not a key) can be thought of as being extended. For each record in the other table, we use the join attribute to look up a single record from the first table (using the primary key).

Note that in most cases only one join-condition attribute is a primary key; the other though is a foreign key.

Are there any cases where neither of the join's attributes is a key of its relation? Mathematically this can certainly happen; in fact, a join condition does not even have to be based on an equality comparison. But coming up with a meaningful example is a different thing. Here's the best I could do. Suppose we want a list of all employees who live in the same town as some department's office. We might want such a list, for example, for emergency preparedness. We can match the employee city field with the dept_locations dlocation field; neither of these is a key. Actually, in the Company database we have to use the employee address field, and do substring matching. This is what I got:

select distinct e.ssn, e.lname, e.address from employee e, dept_locations dl
where e.address LIKE concat('%', dl.dlocation, '%');

I used distinct because an employee in Houston would otherwise be listed for both departments 1 and 5 (both of which have offices in Houston).

Outer joins

Suppose we introduce the table qualification, holding a (single) qualification for each employee:

+-----------+-----------+
| ssn       | qual      |
+-----------+-----------+
| 123456789 | bachelors |
| 333445555 | masters   |
| 987654321 | masters   |
| 666884444 | doctorate |
| 987987987 | ccie      |
| 888665555 | doctorate |
+-----------+-----------+

This is created with

    create table qualification (
       ssn       char(9)  primary key not null,
       qual     varchar(20) not null,
       foreign key (ssn) references employee(ssn)
    );

What if we want to list all employees and, if they have a qualification, that too? Here is the standard join syntax:

select e.lname, q.qual from employee e join qualification q on e.ssn = q.ssn;

But employees with no qualification are not listed at all! How can we get everyone listed, whether or not they have a qualification?

"Left [outer] join" syntax (the "outer" keyword is optional)

select e.lname, q.qual from employee e left join qualification q on e.ssn = q.ssn;

What is happening here? The point is that with the left outer join, then all rows of the left table are included, even when there is no match in the right table.

Let's add an extra line to the qualification table:

insert into qualification values ('345456567', 'hacker');       // no such existing employee!

Now let's do the left and right joins (replace left with right, or reverse the order).

select e.lname, q.qual from employee e right join qualification q on e.ssn = q.ssn;
select e.lname, q.qual from qualification q left join employee e on e.ssn = q.ssn;

What changed?

To delete the row,

delete from qualification where qual='hacker';

We can do this to make the output prettier:

select e.lname, case when q.qual is null then '' else q.qual end as emp_qual
from employee e left join qualification q on e.ssn = q.ssn;

What's going on here is that we're using an expression in the select part, rather than a field name, and using the

    case when boolean_expression then expression1 else expression2 end

as our expression. We're also giving the column a name, "emp_qual".

While expressions in select columns are often important, note that some kinds of output formatting are better done in the front end rather than in SQL itself.

Outer joins may at first look like a more "complete" form of join, but this is slightly misleading. The normal "inner" join lists everything it should; it may be helpful to think of outer joins as the union of the inner join and a special case of those records that have no match in the join.

Here is an alternative use of select expressions that does not require outer joins at all.

select e.fname, e.lname, (select q.qual from qualification q where e.ssn = q.ssn)
from employee e;

We might be happier giving a name to the third column:

select e.fname, e.lname, (select q.qual from qualification q where e.ssn = q.ssn) AS qual
from employee e;

Note that this does not work:

select e.lname, q.qual from EMPLOYEE e, QUALIFICATION q
where (e.ssn = q.ssn) OR (not exists (select q.qual from QUALIFICATION q where e.ssn=q.ssn));

What goes wrong? Look at the output. If someone has no qualifications, the second half of the OR is always true. So that employee appears with all qualifications.

Finally, the following query may at first seem strange:

select e.lname, q.qual from employee e left join qualification q on e.ssn = q.ssn where q.ssn is null;

How can q.ssn ever be null? But the equijoin condition e.ssn = q.ssn does not always hold; it holds only when there actually is a matching QUALIFICATION record. When there is no q to match e, the record still enters the join, but this time with all nulls in the QUALIFICATION fields. This is known as an antijoin.

Null

• A value we just do not know at the moment (eg an unknown middle initial or phone number)
• A value that is more-or-less-permanently unavailable (eg NULL for a middle initial of someone with no middle name)
• A value that is not applicable, perhaps given some other attributes of the row (Borg's super_ssn might be an example,

• select lname from employee where super_ssn is null;
• select lname from employee where super_ssn is not null;
• select lname from employee where super_ssn = null;
• select lname from employee where not super_ssn = null;
• select lname from employee where super_ssn <> null;

Nested queries

Single-value nested queries

Aggregation functions

We can also involve a user-defined function in the calculations; here's an example with grade_num() from sql3.html#functions. It converts a student's grades to numeric form. The grade_num() function is used "internally"; we can't define our own function to replace sum:

select sum(grade_num(g.grade)) as GPA from grade_report g where g.student_number = 8;

We can find the GPA with this:

select sum(grade_num(g.grade))/count(*) as GPA from grade_report g where g.student_number = 8;
select avg(grade_num(g.grade)) as GPA from grade_report g where g.student_number = 8;

Let's take a look at E&N exercise 4.12(c)

GROUP BY

Conventional attributes (that is, columns) are known to be constant for the group if they appear in the group by clause. For years, Postgres enforced this very strictly. Now they also allow attributes in select if

the ungrouped column is functionally dependent on the grouped columns.... A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

For example, Postgres now allows the following, but formerly (prior to version 9.1) did not:

select d.dname, d.dnumber, count(*), avg(e.salary), min(bdate)
from EMPLOYEE e join DEPARTMENT d on e.dno = d.dnumber
group by d.dnumber;

Note that d.dname appears in the select clause, but not in the group-by clause. Postgres used to require d.dname in the group-by clause. However, now Postgres recognizes that d.dname "functionally depends" on d.dnumber because the latter is the key for table department, and that determines d.dname because d.dnumber is the primary key for that table.

Irritatingly, if you replace d.dnumber above in the group-by clause with e.dno, the query fails (until you add d.dname to the group-by), even though e.dno = d.dnumber.

Typically all attributes in the group by clause are used in the select as well.

Note that the grouping is by s.section_identifier, which is the primary key for table SECTION but which does not appear in the select list. A more traditional version would be to group by s.course_number, s.semester and s.year.

The COUNT(*) counts all records, and when it is present, everything else in the select clause should be "constant" (either there should be nothing else, or else the query should use group by for all the non-aggregated attributes.

1. select student_number from GRADE_REPORT;                       -- no aggregation
   
2. select count(*) from GRADE_REPORT;                                    -- no non-aggregated attributes
   
3. select count(student_number) from GRADE_REPORT;
4. select count(distinct student_number) from GRADE_REPORT;
5. select count(*) from (select distinct student_number, section_identifier from grade_report) as dummy;
   

1. select student_number, count(*) from GRADE_REPORT;                    -- needs GROUP BY
2. select count(distinct student_number, section_identifier) from GRADE_REPORT;
   
3. select count(distinct *) from GRADE_REPORT;      /* fails in mysql*/

Here's a query to list, for each student, the GPA; we're again using grade_num() from sql3.html#functions.

select s.name, avg(grade_num(g.grade)) as GPA
from student s join grade_report g on s.student_number = g.student_number
group by s.name order by s.name;

Multi-value nested queries

• IN
• EXISTS / NOT EXISTS
• UNIQUE
  
• = ANY / = SOME
  
• op ALL (op is typically a comparison like > or >=)
  

Of these, IN, EXISTS and NOT EXISTS are the most useful.

Suppose we want to find the names of employees who have worked on project 10. We can do this as a traditional join, but we can also do:

Nested queries with Exists and Unique

The inner queries here are uncorrelated.

Most queries with EXISTS are correlated, as without correlation the inner query can convey just a yes/no result. However, by using IN we can make this query uncorrelated:

select e.fname, e.lname from EMPLOYEE e
where e.ssn IN (select w.essn from WORKS_ON w join PROJECT p on w.pno = p.pnumber where p.dnum = 4)
and
e.ssn IN (select w.essn from WORKS_ON w join PROJECT p on w.pno = p.pnumber where p.dnum = 5);

How about employees who have worked on a project in a department other than their "home" department? Again, this can be done as a join but also as:

Queries with ALL (Universal Quantification)

We'll start with a "simpler" example: list the students with ALL grades B or better. This is the same as the set of students for whom there does NOT EXIST a grade (in the database) less than 'B'. We'll use the grade_less() function of sql3.html#functions to do the comparison:

How can you test this?

The next example of universal quantification is slightly harder: list the employees who have worked for ALL departments, or, in more detail, list all employees e such that for every department d, e worked for a project of d. In the first example, all grades are B or better if there does NOT EXIST a grade less than B. That's the same as saying there does NOT EXIST a grade that is NOT B or better, but the second NOT gets handled by reversing the sense of the comparison. Here, that doesn't work; there is no quick rephrasing of "employee e worked for a project of department d", or its negation.

A way to approach this problem that MySQL can handle (and which may be more efficient for Postgres) is by is asking for all employees such that there is not a department that the employee has not worked on any of that department's projects, or list all employees e such that it is there is no department d such that e did not work for any project of d.

(Compare this to the grades example: list all students s such that there is no grade_report g with grade less than B).

• { e | for all d ∈ D, foo(e,d) }
• { e | there does not exist d ∈ D for which ¬foo(e,d) }
  

Another ALL example

+---------+-----------+
|       1 | Houston   |
|       4 | Stafford  |
|       5 | Bellaire  |
|       5 | Houston   |
|       5 | Sugarland |
+---------+-----------+

• "There is no location loc1 for which there is no location loc2 of d that is equal to loc1", or
• "There is no location loc1 which is not a location of d".

Except / Minus

GROUP BY with HAVING

+---------+-----------------+
| pnumber | pname           |
+---------+-----------------+
|       1 | ProductX        |
|       1 | ProductX        |
|       2 | ProductY        |
|       2 | ProductY        |
|       2 | ProductY        |
|       3 | ProductZ        |
|       3 | ProductZ        |
|      10 | Computerization |
|      20 | Reorganization  |
+---------+-----------------+

+----------+---------+----------+
| Alicia   | Zelaya  | 25000.00 |
| Jennifer | Wallace | 43000.00 |
| Ahmad    | Jabbar  | 25000.00 |
+----------+---------+----------+

VIEWS

• query rewriting
• materialization

Triggers

1. Attribute checks

2. Record (tuple) checks

3. Assertions

4. Triggers

Advanced SQL

Relational Algebra

• choosing subset of entries
• corresponds to logical operations defining the subset
• Sel(Parts:cost >10.0)

Query trees