SELECT title_id, pub_id,
pub_name
FROM publishers
INNER JOIN titles
USING (pub_id);
SELECT title_id, pub_id,
pub_name, advance
FROM publishers
INNER JOIN titles
USING (pub_id)
INNER JOIN royalties
USING (title_id)
WHERE advance <
20000;
------Vitalie Bancu @vitallybankou
Listing 7.7 List the
books that each author wrote (or
cowrote). See Figure 7.7
for the result.
*/
SELECT
a.au_id,
a.au_fname,
a.au_lname,
ta.title_id
FROM authors a
INNER JOIN
title_authors ta
ON a.au_id
= ta.au_id
ORDER BY a.au_id
ASC, ta.title_id ASC;
SELECT *
FROM AUTHORS;
SELECT *
FROM TITLE_AUTHORS
ORDER BY AU_ID ASC;
SELECT a.au_id,
a.au_fname,
a.au_lname, ta.title_id
FROM authors a,
title_authors ta
WHERE a.au_id = ta.au_id
ORDER BY a.au_id ASC,
ta.title_id ASC;
SELECT
t.title_id,
t.title_name,
t.pub_id,
p.pub_name
FROM titles t
INNER JOIN
publishers p
ON
p.pub_id = t.pub_id
ORDER BY
t.title_name ASC;
SELECT t.title_id,
t.title_name,
t.pub_id, p.pub_name
FROM titles t,
publishers p
WHERE p.pub_id =
t.pub_id
ORDER BY t.title_name
ASC;
SELECT
t.title_id,
t.title_name,
t.pub_id,
p.pub_name
FROM titles t
INNER JOIN
publishers p
ON
p.pub_id = t.pub_id
ORDER BY
t.title_name ASC;
SELECT
a.au_id,
a.au_fname,
a.au_lname,
a.city,
a.state
FROM authors a
INNER JOIN
publishers p
ON a.city
= p.city
AND
a.state = p.state
ORDER BY a.au_id
ASC;
------Vitalie Bancu @vitallybankou
SELECT a.au_id,
a.au_fname,
a.au_lname, a.city,
a.state
FROM authors a
NATURAL JOIN publishers
p
ORDER BY a.au_id ASC;
*/
SELECT t.title_id,
t.title_name,
p.state, p.country
FROM titles t,
publishers p
WHERE t.pub_id =
p.pub_id
AND (p.state = 'CA'
OR p.country NOT IN
('USA', 'Canada',
'Mexico'))
ORDER BY t.title_id ASC;
------Vitalie Bancu @vitallybankou
Listing 7.10 List the
books published in California or
outside the large North
American countries. See
Figure 7.10 for the
result.
*/
SELECT
t.title_id,
t.title_name,
p.state,
p.country
FROM titles t
INNER JOIN
publishers p
ON
t.pub_id = p.pub_id
WHERE p.state =
'CA'
OR
p.country NOT IN
('USA', 'Canada', 'Mexico')
ORDER BY
t.title_id ASC;
------Vitalie Bancu @vitallybankou
Listing 7.11 List the
number of books that each author
wrote (or cowrote). See
Figure 7.11 for the result.
*/
SELECT
a.au_id,
COUNT(ta.title_id) AS "Num books"
FROM authors a
INNER JOIN
title_authors ta
ON a.au_id
= ta.au_id
GROUP BY a.au_id
ORDER BY a.au_id
ASC;
------Vitalie Bancu @vitallybankou
SELECT a.au_id,
COUNT(ta.title_id)
AS "Num books”
FROM authors a,
title_authors ta
WHERE a.au_id = ta.au_id
GROUP BY a.au_id
ORDER BY a.au_id ASC;
*/