------Vitalie Bancu @vitallybankou
Listing 8.7a This
statement uses a subquery to list
the authors who live in
the same state as author A04
(Klee Hull). See Figure
8.7 for the result.
*/
SELECT au_id, au_fname,
au_lname, state
FROM authors
WHERE state IN
(SELECT
state
FROM authors
WHERE au_id = 'A04');
------Vitalie Bancu @vitallybankou
Listing 8.7b This
statement is equivalent to
Listing 8.7a but uses an
inner join instead of a
subquery. See Figure 8.7
for the result.
*/
SELECT a1.au_id,
a1.au_fname,
a1.au_lname, a1.state
FROM authors a1
INNER JOIN
authors a2
ON
a1.state = a2.state
WHERE a2.au_id =
'A04';
------Vitalie Bancu @vitallybankou
Listing 8.8 List all
books whose price equals the
highest book price. See
Figure 8.8 for the result.
*/
SELECT title_id, price
FROM titles
WHERE price
=
(SELECT
MAX(price)
FROM titles);
SELECT *
FROM TITLES;
------Vitalie Bancu @vitallybankou
Listing 8.9 List the
authors who live in
the same city
in which a publisher is
located,
and include the
publisher in the result.
See Figure
8.9 for the result.
*/
SELECT a.au_id, a.city,
p.pub_id
FROM authors a
INNER JOIN
publishers p
ON a.city
= p.city;
------Vitalie Bancu @vitallybankou
Listing 8.10 List the
authors who live in the same city
in which a publisher is
located. See Figure 8.10 for the
result.
*/
SELECT au_id, city
FROM authors
WHERE city IN
(SELECT
city
FROM publishers);
SELECT
candidate.title_id,
candidate.type,
candidate.sales
FROM titles candidate
WHERE sales >=
(SELECT AVG(sales)
FROM titles average
WHERE average.type =
candidate.type);
SELECT AVG(sales)
FROM titles average
WHERE average.type =
'history';
SELECT AVG(sales)
FROM titles average
WHERE average.type =
'history';
SELECT DISTINCT
au_id
FROM title_authors
WHERE royalty_share =
1.0;
SELECT au_id, au_fname,
au_lname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM title_authors
WHERE royalty_share =
1.0);
SELECT au_id, au_fname,
au_lname
FROM authors
WHERE 1.0 IN
(SELECT
royalty_share
FROM title_authors
WHERE title_authors.au_id =
authors.au_id);
------Vitalie Bancu @vitallybankou
Listing 8.15a The tables
publishers and
titles both
contain a column named
pub_id,
but you don’t have
to qualify pub_id in
this query
because of the implicit
assumptions about table
names that SQL makes.
See Figure 8.15 for the
result.
*/
SELECT pub_name
FROM publishers
WHERE pub_id IN
(SELECT
pub_id
FROM titles
WHERE type = 'biography');
------Vitalie Bancu @vitallybankou
Listing 8.15b This query
is equivalent
to Listing 8.15a,
but with explicit
qualification
of pub_id. See
Figure
8.15 for the result.
*/
SELECT pub_name
FROM publishers
WHERE
publishers.pub_id IN
(SELECT
titles.pub_id
FROM titles
WHERE type = 'biography');
------Vitalie Bancu @vitallybankouListing
8.17 List each biography, its price, the average
price of all books, and
the difference between the price
of the biography and the
average price of all books.
See Figure 8.17 for the
result.*/
SELECT
title_id,
price,
(SELECT
AVG(price) FROM titles)
AS
"AVG(price)",
price -
(SELECT AVG(price) FROM titles)
AS
"Difference"
FROM titles
WHERE
type='biography';
