------Vitalie Bancu
@vitallybankou
Listing 7.32 List all
authors and any
books written (or
cowritten) that sold
more than
100,000 copies. See
Figure 7.32 for the
result.
*/
SELECT a.au_id,
a.au_fname, a.au_lname,
tta.title_id, tta.title_name, tta.sales
FROM authors a
LEFT OUTER JOIN
(SELECT ta.au_id,
t.title_id,
t.title_name, t.sales
FROM
title_authors ta
INNER JOIN
titles t
ON
t.title_id = ta.title_id
WHERE
sales > 100000) tta
ON a.au_id
= tta.au_id
ORDER BY a.au_id
ASC, tta.title_id ASC;
------Vitalie Bancu @vitallybankou
Listing 7.32 combines an
inner join and a
left outer join to list
all authors and any
books they wrote (or
cowrote) that sold
more than 100,000
copies. In this example,
first I created a
filtered INNER JOIN result
and then OUTER JOINed it
with the table
authors, from which I
wanted all rows. See
Figure 7.32 for the
result.
*/
SELECT a.au_id,
a.au_fname, a.au_lname,
tta.title_id, tta.title_name, tta.sales
FROM authors a
LEFT OUTER JOIN
(SELECT ta.au_id,
t.title_id,
t.title_name, t.sales
FROM
title_authors ta
INNER JOIN
titles t
ON
t.title_id = ta.title_id
WHERE
sales > 100000) tta
ON a.au_id
= tta.au_id
ORDER BY a.au_id
ASC, tta.title_id ASC;
SELECT a.au_id,
a.au_fname,
a.au_lname,
tta.title_id,
tta.title_name,
tta.sales
FROM authors a,
(SELECT ta.au_id,
t.title_id,
t.title_name, t.sales
FROM title_authors ta,
titles t
WHERE t.title_id =
ta.title_id
AND sales > 100000)
tta
WHERE a.au_id =
tta.au_id (+)
ORDER BY a.au_id ASC,
tta.title_id ASC;
------Vitalie Bancu @vitallybankou
c
*/
SELECT
e1.emp_name AS "Employee name",
e2.emp_name AS "Boss name"
FROM employees e1
INNER JOIN employees e2
ON e1.boss_id =
e2.emp_id;
------Vitalie Bancu @vitallybankou
Listing 7.34 List the
authors who live
in the same
state as author A04
(Klee Hull).
See Figure 7.34
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';
SELECT *
FROM authors;
------Vitalie Bancu @vitallybankouListing
7.34 uses a WHERE search condition
and self-join from the
column state to itself
to find all authors who
live in the same state
as author A04 (Klee
Hull). See Figure 7.34
for the result.
*/
SELECT a1.au_id,
a1.au_fname,
a1.au_lname, a1.state
FROM authors a1, authors
a2
WHERE a1.state =
a2.state
AND a2.au_id = 'A04';
------Vitalie Bancu @vitallybankou
Self-joins often can
be
restated as subqueries
(see Chapter 8). Using a
subquery,
Listing 7.34 is
equivalent to:
*/
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 7.35 For every
biography, list
the title ID and
sales of the other
biographies that outsold it. See
Figure 7.35 for the
result.
*/
SELECT t1.title_id,
t1.sales,
t2.title_id AS "Better seller",
t2.sales
AS "Higher sales"
FROM titles t1
INNER JOIN titles
t2
ON
t1.sales < t2.sales
WHERE t1.type =
'biography'
AND
t2.type = 'biography'
ORDER BY
t1.title_id ASC, t2.sales ASC;
SELECT t1.title_id,
t1.sales,
t2.title_id AS
"Better seller",
t2.sales AS "Higher
sales"
FROM titles t1, titles
t2
WHERE t1.sales <
t2.sales
AND t1.type =
'biography'
AND t2.type =
'biography'
ORDER BY t1.title_id
ASC,
t2.sales ASC;
------Vitalie Bancu @vitallybankou
Listing 7.36 List all
pairs of authors who live
in New
York state. See Figure
7.36 for the result.
*/
SELECT
a1.au_fname, a1.au_lname,
a2.au_fname, a2.au_lname
FROM authors a1
INNER JOIN
authors a2
ON
a1.state = a2.state
WHERE a1.state =
'NY'
ORDER BY a1.au_id
ASC, a2.au_id ASC;
SELECT
a1.au_fname,
a1.au_lname,
a2.au_fname, a2.au_lname
FROM authors a1, authors
a2
WHERE a1.state =
a2.state
AND a1.state = 'NY'
ORDER BY a1.au_id ASC,
a2.au_id ASC;
------Vitalie Bancu @vitallybankou
a1.au_fname,
a1.au_lname,
a2.au_fname, a2.au_lname
FROM authors a1, authors
a2
WHERE a1.state =
a2.state
AND a1.au_id <>
a2.au_id
AND a1.state = 'NY'
ORDER BY a1.au_id ASC,
a2.au_id ASC;
*/
