/* Results for first 4 questions, in-class exercise 11/04/99 */
/* List the publishers who publish psychology books. */
/* 1. Using a join.... */
2>
3> select pub_name, title, type
4> from publishers, titles
5> where publishers.pub_id = titles.pub_id
6> and type = 'psychology'
pub_name
title
type
----------------------------------------
--------------------------------------------------------------------------------
------------
Binnet & Hardley
Computer Phobic and Non-Phobic Individuals: Behavior Variations
psychology
New Age Books
Is Anger the Enemy?
psychology
New Age Books
Life Without Fear
psychology
New Age Books
Prolonged Data Deprivation: Four Case Studies
psychology
New Age Books
Emotional Security: A New Algorithm
psychology
(5 rows affected)
/* Note on above result: 5 rows, but only two publishers. */
/* Using a subquery........ */
3>
4> select pub_name
5> from publishers
6> where 'psychology' in
7> (select type
8> from titles
9> where publishers.pub_id = titles.pub_id)
pub_name
----------------------------------------
New Age Books
Binnet & Hardley
(2 rows affected)
/* List all first authors who have sold more than 1000 books. */
/* Using a join..... */
1>
2>
3> select authors.au_id, au_ord, total_sales
4> from authors, titleauthor, titles
5> where
6> authors.au_id = titleauthor.au_id
7> and titles.title_id = titleauthor.title_id
8> and au_ord = 1
9> and total_sales > 1000
au_id au_ord total_sales
----------- ------ -----------
172-32-1176 1 4072
213-46-8915 1 18722
238-95-7766 1 8780
274-80-9391 1 4095
409-56-7008 1 4095
427-17-2319 1 4095
486-29-1786 1 3336
648-92-1872 1 15096
672-71-3249 1 4095
712-45-1867 1 2032
722-51-5454 1 22246
724-80-9391 1 3876
998-72-3567 1 2045
(13 rows affected)
/* Using a subquery.... */
2>
3> select authors.au_id, au_ord
4> from authors, titleauthor, titles
5> where authors.au_id = titleauthor.au_id
6> and au_ord=1
7> and 1000 <
8> (select total_sales
9> from titles
10> where titleauthor.title_id = titles.title_id)
au_id au_ord
----------- ------
172-32-1176 1
213-46-8915 1
238-95-7766 1
274-80-9391 1
409-56-7008 1
427-17-2319 1
486-29-1786 1
648-92-1872 1
672-71-3249 1
712-45-1867 1
722-51-5454 1
724-80-9391 1
998-72-3567 1
(13 rows affected)
1>
2> /* ----------- Whose books are selling for $2.99? ------------- */
3>
4> /* Using a subquery. */
5>
6> select au_lname
7> from authors a
8> where a.au_id in
9> (select ta.au_id
10> from titleauthor ta
11> where ta.title_id in
12> (select t.title_id
13> from titles t
14> where price = 2.99))
au_lname
----------------------------------------
Green
DeFrance
Ringer
(3 rows affected)
1>
2>
3> /* Using a join. */
4>
5> select au_lname, title, price
6> from authors a, titleauthor ta, titles t
7> where a.au_id = ta.au_id
8> and ta.title_id = t.title_id
9> and price = 2.99
au_lname
title
price
----------------------------------------
--------------------------------------------------------------------------------
------------------------
Green
You Can Combat Computer Stress!
2.99
DeFrance
The Gourmet Microwave
2.99
Ringer
The Gourmet Microwave
2.99
(3 rows affected)
1>
2>
3>
4> /* Titles ordered in quantities > than max. ordered by Bookbeat. */
5>
6> /* Combined subquery and joins. (Can one solve with joins only?) */
7>
8> select distinct title
9> from titles t, salesdetail d
10> where t.title_id = d.title_id
11> and qty > any
12> (select max(qty)
13> from salesdetail d, stores s
14> where d.stor_id = s.stor_id
15> and stor_name = 'Bookbeat')
title
--------------------------------------------------------------------------------
Sushi, Anyone?
Is Anger the Enemy?
The Gourmet Microwave
But Is It User Friendly?
Secrets of Silicon Valley
Straight Talk About Computers
You Can Combat Computer Stress!
Silicon Valley Gastronomic Treats
Emotional Security: A New Algorithm
The Busy Executive's Database Guide
Fifty Years in Buckingham Palace Kitchens
Prolonged Data Deprivation: Four Case Studies
Cooking with Computers: Surreptitious Balance Sheets
(13 rows affected)
(Subqueries only)
1> select title from titles
2> where title_id in
3> (select title_id from salesdetail
4> where qty >
5> (select max(qty) from salesdetail
6> where stor_id in
7> (select stor_id from stores
8> where stor_name = 'Bookbeat')))
title
--------------------------------------------------------------------------------
The Busy Executive's Database Guide
Cooking with Computers: Surreptitious Balance Sheets
You Can Combat Computer Stress!
Straight Talk About Computers
Silicon Valley Gastronomic Treats
The Gourmet Microwave
But Is It User Friendly?
Secrets of Silicon Valley
Is Anger the Enemy?
Prolonged Data Deprivation: Four Case Studies
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Sushi, Anyone?
(13 rows affected)