/* SQL/Sybase Programming, Fall 2000 */
/* Assignment 3 */
3>
4> /* Names of stores in same state as a publisher. ----------- */
5>
6> /* Using subquery. ------------------------------------------ */
7>
8> select stor_name, s.state from stores s
9> where s.state in
10> (select p.state from publishers p)
stor_name state
---------------------------------------- -----
Barnum's CA
News & Brews CA
Fricative Bookshop CA
Thoreau Reading Discount Chain MA
(4 rows affected)
1>
2> /* Using join. ---------------------------------------------- */
3>
4> select stor_name, s.state
5> from stores s, publishers p
6> where s.state = p.state
stor_name state
---------------------------------------- -----
Thoreau Reading Discount Chain MA
Barnum's CA
News & Brews CA
Fricative Bookshop CA
(4 rows affected)
1>
2> /* Authors living in states without a bookstore location. -- */
3>
4> /* Using subquery. ------------------------------------------ */
5>
6> select au_fname + ' ' + au_lname, a.state
7> from authors a
8> where a.state not in
9> (select s.state
10> from stores s)
state
------------------------------------------------------------- -----
Michel DeFrance IN
Meander Smith KS
Sylvia Panteley MD
Innes del Castillo MI
Morningstar Greene TN
Anne Ringer UT
Albert Ringer UT
(7 rows affected)
1>
2> /* Using join. --------------------------------------------- */
3>
4> select distinct au_fname + ' ' + au_lname, a.state, s.state
5> from authors a, stores s
6> where a.state *= s.state
7> group by au_fname + ' ' + au_lname, a.state, s.state
8> having s.state is null
state state
------------------------------------------------------------- ----- -----
Anne Ringer UT NULL
Albert Ringer UT NULL
Meander Smith KS NULL
Michel DeFrance IN NULL
Sylvia Panteley MD NULL
Innes del Castillo MI NULL
Morningstar Greene TN NULL
(7 rows affected)
1>
2> /* -- Whose books (first authors only) are selling for $19.99? ---- */
3>
4> /* Using a subquery. */
5>
6> select (au_fname + ' ' + au_lname) as "Best-selling first authors"
7> from authors a
8> where a.au_id in
9> (select ta.au_id
10> from titleauthor ta
where au_ord = 1
11> and ta.title_id in
12> (select t.title_id
13> from titles t
14> where price = 19.99))
Best-selling first authors
-------------------------------------------------------------
Johnson White
Dick Straight
Abraham Bennet
Innes del Castillo
(4 rows affected)
1>
2>
3> /* Using a join. */
4>
5> select (au_fname + ' ' + au_lname) as author, 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 = 19.99
10> and au_ord = 1
author
title
price
-------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------
Abraham Bennet
The Busy Executive's Database Guide
19.99
Dick Straight
Straight Talk About Computers
19.99
Innes del Castillo
Silicon Valley Gastronomic Treats
19.99
Johnson White
Prolonged Data Deprivation: Four Case Studies
19.99
(4 rows affected)
1>
2>
3> /* Stores that have ordered 1000 books or more. ------------ */
4>
5> /* Using subquery. ------------------------------------------*/
6>
7> select stor_name, sum(qty)
8> from stores s, salesdetail d
9> where s.stor_id in
10> (select d.stor_id
11> from salesdetail)
12> and 1000 <
13> (select sum(qty)
14> from salesdetail)
15> group by stor_name
stor_name
---------------------------------------- -----------
Barnum's 2430
Bookbeat 3733
Doc-U-Mat: Quality Laundry and Books 2097
Eric the Read Books 2430
Fricative Bookshop 1182
News & Brews 2900
Thoreau Reading Discount Chain 82674
(7 rows affected)
1>
2> /* Using a join. --------------------------------------------*/
3>
4> select stor_name, sum(qty)
5> from stores s, salesdetail d
6> where s.stor_id = d.stor_id
7> group by stor_name
8> having sum(qty) > 1000
stor_name
---------------------------------------- -----------
Barnum's 2430
Bookbeat 3733
Doc-U-Mat: Quality Laundry and Books 2097
Eric the Read Books 2430
Fricative Bookshop 1182
News & Brews 2900
Thoreau Reading Discount Chain 82674
(7 rows affected)