1> /* In-class practice exercises, 10/21/1999 (Q.1-5) */
2> /* */
3> use pubs2
1> /* */
2> /* Q.1 Check the results of the four SQL statements on pp. */
3> /* 157-158 (top statement) by including avg(price). */
4> /* */
5> select count(price), sum(price), avg(price)
6> from titles
----------- ------------------------ ------------------------
16 236.26 14.77
(1 row affected)
1> /* */
2> select count(price), sum(distinct price), avg(price)
3> from titles
----------- ------------------------ ------------------------
16 161.35 14.77
(1 row affected)
1> /* */
2> select count(distinct price), sum(price), avg(price)
3> from titles
----------- ------------------------ ------------------------
11 236.26 14.77
(1 row affected)
1> /* */
2> select count(distinct price), sum(distinct price), avg(price)
3> from titles
----------- ------------------------ ------------------------
11 161.35 14.77
(1 row affected)
1> /* Results of 1st and 4th queries are correct; others are not. */
2> /* */
3> /* Q.2. List all the different states where authors live; */
4> /* then again, sorted by city. Why are the results different? */
5> /* */
6> select distinct state
7> from authors
state
-----
CA
IN
KS
MD
MI
OR
TN
UT
(8 rows affected)
1> /* */
2> select distinct state
3> from authors
4> order by city
state
-----
MI
CA
OR
CA
IN
KS
CA
TN
CA
CA
MD
UT
CA
CA
CA
CA
(16 rows affected)
1> /* */
2> /* When "ordering by", that column is included implicitly */
3> /* in the select list. "Distinct" finds each different */
4> /* state-city combination. */
5> /* */
6> /* Q.3. Use the salesdetail table for these questions: */
7> /* a. How many copies of each title have been ordered, */
8> /* regardless of which store? */
9> /* b. How many copies of any title has each store ordered? */
10> /* */
11> select title_id, sum(qty) as Copies_Sold
12> from salesdetail
13> group by title_id
title_id Copies_Sold
-------- -----------
BU1032 4095
BU1111 3876
BU2075 18722
BU7832 4095
MC2222 2032
MC3021 22246
PC1035 8780
PC8888 4095
PS1372 375
PS2091 2045
PS2106 111
PS3333 4072
PS7777 3336
TC3218 375
TC4203 15096
TC7777 4095
(16 rows affected)
1> /* */
2> select stor_id, sum(qty) as Copies_Sold
3> from salesdetail
4> group by stor_id
stor_id Copies_Sold
------- -----------
5023 82674
6380 2430
7066 2430
7067 2900
7131 2097
7896 1182
8042 3733
(7 rows affected)
1> /* */
2> /* Q.4. More on grouping with aggregate functions: */
3> /* a. Show the average advance and price set by each publisher */
4> /* for each type of book. */
5> /* b. If not done already, eliminate nulls from results in 4a. */
6> /* c. Sort results from 4b in order of increasing avg advance. */
7> /* d. Same as c, but only books with price > average price. */
8> /* */
9> select pub_id, type, avg(advance) as AvgAdv, avg(price) as AvgPrice
10> from titles
11> group by pub_id, type
pub_id type AvgAdv AvgPrice
------ ------------ ------------------------ ------------------------
0736 business 10,125.00 2.99
0736 psychology 3,568.75 11.48
0877 UNDECIDED NULL NULL
0877 mod_cook 7,500.00 11.49
0877 psychology 7,000.00 21.59
0877 trad_cook 6,333.33 15.96
1389 business 5,000.00 17.31
1389 popular_comp 7,500.00 21.48
(8 rows affected)
1> /* */
2> select pub_id, type, avg(advance) as AvgAdv, avg(price) as AvgPrice
3> from titles
4> where (advance is not null or price is not null)
5> group by pub_id, type
pub_id type AvgAdv AvgPrice
------ ------------ ------------------------ ------------------------
0736 business 10,125.00 2.99
0736 psychology 3,568.75 11.48
0877 mod_cook 7,500.00 11.49
0877 psychology 7,000.00 21.59
0877 trad_cook 6,333.33 15.96
1389 business 5,000.00 17.31
1389 popular_comp 7,500.00 21.48
(7 rows affected)
1> /* */
2> select pub_id, type, avg(advance) as AvgAdv, avg(price) as AvgPrice
3> from titles
4> where (advance is not null or price is not null)
5> group by pub_id, type
6> order by 3
pub_id type AvgAdv AvgPrice
------ ------------ ------------------------ ------------------------
0736 psychology 3,568.75 11.48
1389 business 5,000.00 17.31
0877 trad_cook 6,333.33 15.96
0877 psychology 7,000.00 21.59
0877 mod_cook 7,500.00 11.49
1389 popular_comp 7,500.00 21.48
0736 business 10,125.00 2.99
(7 rows affected)
1> /* */
2> select pub_id, type, avg(advance) as AvgAdv, avg(price) as AvgPrice
3> from titles
4> group by pub_id, type
5> having (advance is not null or price is not null)
6> and price > avg(price)
7> order by avg(advance)
pub_id type AvgAdv AvgPrice
------ ------------ ------------------------ ------------------------
0736 psychology 3,568.75 11.48
1389 business 5,000.00 17.31
1389 business 5,000.00 17.31
0877 trad_cook 6,333.33 15.96
0877 mod_cook 7,500.00 11.49
1389 popular_comp 7,500.00 21.48
(6 rows affected)
1> /* */
2> /* Q.5. Which title has the maximum total sales and how many */
3> /* copies of that title have sold? */
4> /* */
5> select title_id, title, total_sales
6> from titles
7> having total_sales = max(total_sales)
title_id
title
total_sales
--------
------------------------------------------------------------------------
--------
-----------
MC3021
The Gourmet Microwave
22246
(1 row affected)