Introduction

On Monday, we began introducing the DML (data manipulation language) associated with SQL.
We showed how to do simple queries that would correspond to selection and projection in the relational algebra.
We begin today by looking at queries which would correspond to joins.

Products and Joins in SQL

One way to couple many relations in SQL is to list them in the FROM clause. For example, suppose we want to know the name of the producer of Star Wars. We have two tables:
```
Movies(title, year, length, genre, studioName, producerC#)
MovieExec(name, address, cert#, netWorth)
```
We could select on Movies to get the producerC# of the producer of Star Wars then use that number to look up the name in MovieExec for that producer.
In relational algebra this is equivalent to `pi_{mbox(name)}(sigma_{mbox(producerC#=cert# AND title='Star Wars')}(Movies times MovieExec))`.

In SQL, we could do this as:

SELECT name 
FROM Movies, MovieExec 
WHERE title = 'Star Wars' AND producerC# = cert#;

Here the FROM clause plays the role of Cartesian Product, the WHERE clause plays the role of the relational algebra select, and the SELECT clause corresponds to the projection.
One can also view the above query as: $$\pi_{\mbox{name}}(Movies \bowtie_{\mbox{producer#=cert# AND title='Star Wars'}} MovieExec).$$ So the FROM and WHERE clauses together correspond to a `\theta`-join.

Disambiguating Attributes

Sometimes we have a query involving several relations and some of these relations share the names of their attributes.
SQL provides a means to disambiguate the attributes in this case by use the notation `R.A` to refer to the attribute `A` of relation `R`.
For example, suppose we had relations:
```
MovieStar(name, address, gender, birthdate)
MovieExec(name, address, cert#, netWorth)
```
and we want to find pairs of stars and movie execs who share an address.

We could do this with the query:

SELECT MovieStar.name, MovieExec.name
FROM MovieStar, MovieExec 
WHERE MovieStar.address = MovieExec.address;

Tuple Variables

Sometimes we want to do a query where the two tables we join are the same. For example, we want to compute the grandparents query using only a Parent(x,y) relation.
SQL allows one to create tuple variables in the FROM clause to refer to the different copies.
For example, suppose we want to output the names of two distinct stars that share the same address.

We could do this with the query:

SELECT Star1.name, Star2.name 
FROM MovieStar Star1, MovieStar Star2 
WHERE Star1.address = Star2.address AND Star1.name < Star2.name;

In the above, Star1 and Star2 are the tuple variables and serve as aliases for MovieStar.

Semantics of Multirelation Queries

There are several different ways to interpret a SQL SELECT FROM WHERE query that all yield the same results:
1. Nested For Loop:
```
LET the tuple variables in the FROM-clause range
    over relations R_1 , R_2, ..., R_n ;
FOR each tuple t_1 in relation R_1 DO
    FOR each tuple t_2 in relation R_2 DO
            ... 
        FOR each tuple t_n in relation R_n DO 
            IF the WHERE-clause is satisfied when the values
            from t_1, t_2, ... t_n are substituted for all 
            attribute references THEN 
                evaluate the expressions of the SELECT-clause
                according to t_1, t_2, ... , t_n and produce the 
                tuple of values that results.
```
2. Parallel Assignment: The idea is basically to replace the FOR above with parallel FORs. So in parallel, in arbitrary order, consider all possible tuples from each of the relations in the FROM clause. For each such assignment, we consider whether the WHERE clause becomes TRUE, if it does, we output the attributes of the SELECT clause.
3. Conversion to Relational Algebra. Rewrite the SQL query in relation algebra as `pi_L(sigma_{mbox(cond)}(R_1 times cdots times R_n))` where `L` comes from the SELECT-clause of the SQL statement, `c\o\n\d` comes from the WHERE-clause, and the `R_i` are what's listed in the FROM-clause.

Union, Intersection, and Difference of Queries

SQL has the keywords UNION, INTERSECT, and EXCEPT to express the set operations `cup`, `cap`, and `-`.
For example, suppose we have the MovieStar and MovieExec relations of a couple slides back and we want to find all female movie stars who are also movie exec's with a net worth over $10,000,000.

We can write this as a SQL query using:

(SELECT name, address
 FROM MovieStar
 WHERE gender = 'F')
     INTERSECT
(SELECT name, address
 FROM MovieExec
 WHERE netWorth > 10000000);

In-Class Exercise

Consider the relations:

Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price) 
Printer(model, color, type, price)

Express the following queries using SQL:
1. The manufacturer, price, and model of laptops which have more than 16Gb of ram.
2. The manufacturer's of printers that do not sell PCs under $800.
Post your solutions to the Nov. 7 In-Class Exercise Thread.

Subqueries

It is often useful to be able to use one query to help in the evaluation of another query.
A query that is part of another query is called a subquery.
One example we have seen so far of the subqueries are the arguments to the UNION, INTERSECT, EXCEPT SQL operations.
We now consider the following other ways that a subquery may be useful:
1. To return a single constant which can then be compared with another value in a WHERE clause.
2. To return relations that can be used in various ways in WHERE clauses.
3. For use in FROM-clauses followed by a tuple variable so that one can refer to the tuples it produces.
Let's look at each of these in turn.

Subqueries that Produce Scalar Values

Recall the query we had earlier:

SELECT name
FROM Movies, MovieExec 
WHERE title = 'Star Wars' AND producerC# = cert#;

If we were to code the answer to this query directly, one might imagine, first search for the movie with title Star Wars and find its certificate#, then use that number to find the name of the movie exec.
We can write each of these steps in SQL and imagine the first step as producing a value used by the second query.

SQL let's us express this approach via the following nested subquery syntax:

SELECT name
FROM MovieExec
WHERE cert# =
    (SELECT producerC#
     FROM Movies
     WHERE title = 'Star Wars' );

Conditions Involving Relations

There are a number of SQL operators that can be applied to a relation R or the results of a subquery and which produce a boolean result.
These operators can hence be used in a WHERE clause.
We list here the most common of these operations:
1. EXISTS R is a condition that is true iff R is not empty. Can also have NOT EXISTS.
2. s IN R is true iff s is one of the values in R. s NOT IN R is true iff s is not one of the values in R.
3. s op ALL R where op is a binary connective `<`, `>`, <=, >=, <>, etc. is true iff for every choice of tuple t in R, we have s op t holding.
4. s op ANY R where op is a binary connective `<`, `>`, <=, >=, <>, etc.is true iff there exists a choice of tuple t in R, we have s op t holding.

Example of Using Conditions Involving Relations

Suppose we wanted the names of movie execs that produced a movie that starred Harrison Ford.

One way to do this using subqueries with conditions involving relations is:

SELECT name 
FROM MovieExec
WHERE cert# IN
    (SELECT producerC#
     FROM Movies
     WHERE (title, year) IN
         (SELECT movieTitle, movieYear
          FROM StarsIn
          WHERE starName = 'Harrison Ford' ) );

This is equivalent to the following SQL query not using subqueries:

SELECT name 
FROM MovieExec, Movies, StarsIn 
WHERE cert# = producerC# 
   AND title = movieTitle 
   AND year = movieYear 
   AND starName = 'Harrison Ford';

Correlated Subqueries

So far our subqueries are evaluated once and for all and the result used in a higher level query.
For example, consider the query from the previous slide there is a fixed list of movies Harrison Ford stars in that doesn't change as we execute the outer query. I.e., We could imagine making it into a temporary table that is used for the duration of the query.
One can also have nested subqueries that require the subquery to be evaluated many times, once for each assignment of a value to some term in the subquery.
This kind of subquery is called a correlated subquery.
For example, suppose we wanted to find the titles of all movies that have been remade.

For way to do this is with the following correlated subquery:

SELECT title
FROM Movies Old
WHERE year < ANY 
   (SELECT year 
    FROM Movies 
    WHERE title = Old.title );

Notice the inner query changes as we cycle over different titles.

More Complicated Queries in SQL

Outline