SELECT name, SUM $($length$)$

FROM MovieExec, Movies

WHERE producerC# $=$ cert#

GROUP BY name

HAVING MIN$($year$)<mtext\; id="EditorElement\_4359225"></mtext><mn\; id="EditorElement\_4359226">1</mn><mn\; id="EditorElement\_4359227">9</mn><mn\; id="EditorElement\_4359228">3</mn><mn\; id="EditorElement\_4359229">0</mn>$;

Figure $6\mathrm{.}14$: Computing the total length of film for early producers

$6\mathrm{.}4\mathrm{.}8$ Exercises for Section $6\mathrm{.}4$

Exercise $6\mathrm{.}4\mathrm{.}1$: Write each of the queries in Exercise $2\mathrm{.}4\mathrm{.}1$ in SQL$,$ making

sure that duplicates are eliminated.

Exercise $6\mathrm{.}4\mathrm{.}2$: Write each of the queries in Exercise $2\mathrm{.}4\mathrm{.}3$ in SQL$,$ making

sure that duplicates are eliminated.

$!$ Exercise $6\mathrm{.}4\mathrm{.}3$: For each of your answers to Exercise $6\mathrm{.}3\mathrm{.}1,$ determine whether

or not the result of your query can have duplicates. If so$,$ rewrite the query

to eliminate duplicates. If not, write a query without subqueries that has the

same, duplicate$-$free answer.

$!$ Exercise $6\mathrm{.}4\mathrm{.}4$ : Repeat Exercise $6\mathrm{.}4\mathrm{.}3$ for your answers to Exercise $6\mathrm{.}3\mathrm{.}2.$

$!$ Exercise $6\mathrm{.}4\mathrm{.}5$ : In Example $6\mathrm{.}27,$ we mentioned that different versions of the

query "find the producers of Harrison Ford's movies" can have different answers

as bags, even though they yield the same set of answers. Consider the version

of the query in Example $6\mathrm{.}22,$ where we used a subquery in the FROM clause.

Does this version produce duplicates, and if so$,$ why?

Exercise $6\mathrm{.}4\mathrm{.}6$ : Write the following queries, based on the database schemaPC$($model$,$ speed, ram, hd$,$ price$)$

Laptop$($model$,$ speed, ram, hd$,$ screen, price$)$

Printer$($model$,$ color, type, price$)$of Exercise $2\mathrm{.}4\mathrm{.}1,$ and evaluate your queries using the data of that exercise.

a$)$ Find the average speed of PC$\text{'}$s$.$

b$)$ Find the average speed of laptops costing over $$1000.$

c$)$ Find the average price of PC$\text{'}$s made by manufacturer $"$A$."$

$!$d$)$ Find the average price of PC $\text{'}$s and laptops made by manufacturer $"$D$."$

e$)$ Find, for each different speed, the average price of a PC $.$