Subqueries Example Find the name of the producer

Subqueries Example Find the name of the producer of ‘Star Wars’. Movie(title, year, length, in. Color, studio. Name, producer. C) Movie. Exec(name, address, cert, net. Worth) We can do: SELECT name FROM Movie, Movie. Exec WHERE title ='Star Wars' AND producer. C =cert; Or we can have a subquery: SELECT name FROM Movie. Exec WHERE cert =(SELECT producer. C FROM Movie WHERE title = 'Star Wars'); If we can deduce that there will be only a single value produced by the subquery, then we can use this expression, surrounded by parentheses, as if it were a constant.

Conditions Involving Relations There a number of SQL operators that we can apply to a relation R and produce a Boolean result. Typically used in the WHERE clause. 1. EXISTS R is a condition that is true if R is not empty. 2. s IN R is true if s is equal to one of the tuples in R. Likewise, s NOT IN R is true if and only if s is equal to no tuple in R.

Example Give all the producers of movies in which Julia Roberts stars. SELECT name FROM Movie. Exec WHERE cert IN (SELECT producer. C FROM Movie WHERE (title, year) IN (SELECT movie. Title, movie. Year FROM Stars. In WHERE star. Name = 'Julia Roberts'));

Remark The previous nested query can, like many nested queries, be written as a single SELECT-FROM-WHERE expression. SELECT name FROM Movie. Exec, Movie, Stars. In WHERE cert = producer. C AND title = movie. Title AND year = movie Year AND star. Name = 'Julia Roberts';

(Continued) 3. s > ALL R is true if s is greater than every value in the unary (one column) relation R. Similarly, the > operator could be replaced by any other comparison operators with the analogous meaning. For instance, s <> ALL R is the same as s NOT IN R. 4. s > ANY R is true if s is greater than at least one value in unary relation R. Similarly we can use any other comparison operators in place of >. For instance, s =ANY R is the same as s IN R. EXISTS, ALL, and ANY operators can be negated by putting NOT in front of the entire expression.

Bag Semantics and Union, Intersection and Difference • Although the SELECT-FROM-WHERE statement uses bag semantics, the default for union, intersection, and difference is set semantics. Motivation? • When doing projection in relational algebra, it is easier to avoid eliminating duplicates. Just work tuple-at-a-time. • When doing intersection or difference, it is most efficient to sort the relations first. At that point you may as well eliminate the duplicates anyway.

Controlling Duplicate Elimination • Force the result to be a set by – SELECT DISTINCT. . . • Force the result to be a bag (i. e. , don’t eliminate duplicates) by ALL, as in – UNION ALL. . . • Only UNION ALL supported in ORACLE. Example • Find all the different studios producing movies: Movie(title, year, length, in. Color, studio. Name, producer. C), SELECT DISTINCT studioname FROM Movie; • Notice that without DISTINCT, a studioname would be listed as many times as there were movies from that studio.

Aggregations • SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column. Example • Find the average length of movies from Disney. SELECT AVG(length) FROM Movie WHERE studio. Name = 'Disney'; Remark • We can also use COUNT(*) which counts the number of tuples in the relation constructed from the FROM and WHERE clauses of the query.

Eliminating Duplicates in an Aggregation • DISTINCT inside an aggregation causes duplicates to be eliminated before the aggregation. Example • Find the number of different producers for Disney movies. SELECT COUNT(DISTINCT producerc) FROM Movie WHERE studioname = 'Disney'; This is not the same as: DISTINCT here is useless! Why? SELECT DISTINCT COUNT(producerc) FROM Movie WHERE studioname = 'Disney';

Not only in COUNT… SELECT AVG(DISTINCT length) FROM Movie WHERE studioname = 'Disney'; • This will produce the average of only the distinct values for length.

NULL’s Ignored in Aggregation • NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column. SELECT SUM(networth) FROM moviestar NATURAL FULL OUTER JOIN movieexec; • But if there are no non-NULL values in a column, then the result of the aggregation is NULL.

Example: Effect of NULL’s SELECT count(*) FROM Movie WHERE studio. Name = 'Disney'; SELECT count(length) FROM Movie WHERE studio. Name = 'Disney'; The number of movies from Disney with a known length.

Grouping • We may follow a SELECT-FROM-WHERE expression by GROUP BY and a list of attributes. • The relation that results from the SELECT-FROM-WHERE – is grouped according to the values of all the listed attributes in GROUP BY, and – any aggregation is applied only within each group. Example • From the Movie relation, find the average length for each studio. SELECT studio. Name, AVG(length) FROM Movie GROUP BY studio. Name;

Another Example • From Movie and Movie. Exec, find the producer’s total length of film produced: SELECT name, SUM(length) FROM Movie, Movie. Exec WHERE producerc = cert GROUP BY name; Compute those tuples first, then group by name.

Restriction on SELECT Lists With Aggregation • If any aggregation is used, then each element of the SELECT list must be either: 1. 2. Aggregated, or An attribute on the GROUP BY list.

Illegal Query Example • We might think we could find the shortest movie of Disney as: SELECT title, MIN(length) FROM Movie WHERE studio. Name = 'Disney'; • But this query is illegal in SQL. • Because title is neither aggregated nor on the GROUP BY list. • We should do instead: SELECT title, length FROM Movie WHERE studio. Name = 'Disney' AND length = (SELECT MIN(length) FROM Movie WHERE studio. Name = 'Disney');

Or… SELECT title, length FROM Movie NATURAL JOIN (SELECT MIN(length) AS length FROM Movie WHERE studio. Name = 'Disney') WHERE studio. Name = 'Disney'; …resembling Relational Algebra.

HAVING Clauses • HAVING <condition> may follow a GROUP BY clause. – If so, the condition applies to each group, and groups not satisfying the condition are eliminated. Example • Consider again the query SELECT name, SUM(length) FROM Movie, Movie. Exec WHERE producerc = cert GROUP BY name; • Suppose we didn’t wish to include all the producers in our table of aggregated lengths. We want those producers – with networth less than 1, 000, and – that have at least one movie before 1973. • Solution SELECT name, SUM(length) FROM Movie. Exec, Movie WHERE producerc = cert AND networth<1000000 GROUP BY name HAVING MIN(year) < 1973;

Requirements on HAVING Conditions • • These conditions may refer to any relation in the FROM clause. They may refer to attributes of those relations, as long as the attribute makes sense within a group; i. e. , it is either: 1. A grouping attribute, or 2. Aggregated attribute.

“Having” is a special kind of • The previous query can also be written as: SELECT name, sum. Length FROM ( SELECT name, MIN(year) AS min. Year, SUM(length) AS sum. Length FROM Movie. Exec, Movie WHERE producerc = cert AND networth < 1000000 GROUP BY name) WHERE min. Year < 1973;

Correlated Subqueries • Suppose Stars. In relation has an additional attribute “salary” Stars. In(movie. Title, movie. Year, star. Name, salary) • Now, find the stars who were paid for some movie more than the average salary for that movie. SELECT star. Name, movie. Title, movie. Year FROM Stars. In X WHERE salary > (SELECT AVG(salary) FROM Stars. In WHERE movie. Title = X. movie. Title AND movie. Year=X. movie. Year); Remarks 1. Outer query cannot reference any columns in the subquery. 2. Subquery references the tuple in the outer query. 3. Value of the tuple changes by row of the outer query, so the database must rerun the subquery for each row comparison.

Another Solution (Nesting in FROM) SELECT X. star. Name, X. movie. Title, X. movie. Year FROM Stars. In X, (SELECT movie. Title, movie. Year, AVG(salary) AS avg. Salary FROM Stars. In GROUP BY movie. Title, movie. Year) Y WHERE X. salary>Y. avg. Salary AND X. movie. Title=Y. movie. Title AND X. movie. Year=Y. movie. Year;

Exercise Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) 1. Find those manufacturers that sell Laptops, but not PC's. 2. Find those hard-disk sizes that occur in two or more PC's. 3. Find those manufacturers of at least two different computers (PC or Laptops) with speed of at least 700. 4. Find the manufacturers who sell exactly three different models of PC.