1> /* In-class practice exercises, 10/21/2000 (Q.1-3) */
2> /* */
3> use pubs2
1> /* */
2> /* Check the results of the four SQL statements on pp. 157-158 */
3> /* 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 match sum/count (OK); others do not. */
2> /* */
3> /* List the different states where authors live; then again, */
4> /* 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 in */
3> /* the select list. "Distinct" finds each different state- */
4> /* city combination. */
1> /* */
1> /* Combinations of grouping and aggregate functions. */
2> /* Average advance and price for each type of book. */
3> /* */
4> select type, avg(advance) as AvgAdvT, avg(price) as AvgPriceT
5> from titles
6> group by type
type AvgAdvT AvgPriceT
------------ ------------------------ ------------------------
UNDECIDED NULL NULL
business 6,281.25 13.73
mod_cook 7,500.00 11.49
popular_comp 7,500.00 21.48
psychology 4,255.00 13.50
trad_cook 6,333.33 15.96
(6 rows affected)
1> /* */
2> /* Average advance and price for each publisher. */
3> /* */
4> select pub_id, avg(advance) as AvgAdvP, avg(price) as AvgPriceP
5> from titles
6> group by pub_id
pub_id AvgAdvP AvgPriceP
------ ------------------------ ------------------------
0736 4,880.00 9.78
0877 6,833.33 15.41
1389 6,000.00 18.98
(3 rows affected)
1> /* */
2> /* Average advance and price for each publisher and type. */
3> select pub_id, type, avg(advance) as AvgAdvPT,
4> avg(price) as AvgPricePT
5> from titles
6> group by pub_id, type
pub_id type AvgAdvPT AvgPricePT
------ ------------ ------------------------ ------------------------
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> /* Eliminate any (remaining) nulls in previous statement. */
3> select pub_id, type, avg(advance) as AvgAdvPT,
4> avg(price) as AvgPricePT
5> from titles
6> where advance is not null or price is not null
7> group by pub_id, type
pub_id type AvgAdvPT AvgPricePT
------ ------------ ------------------------ ------------------------
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> /* Sort previous result by average advance. */
3> /* */
4> select pub_id, type, avg(advance) as AvgAdvPT,
5> avg(price) as AvgPricePT
6> from titles
7> where advance is not null or price is not null
8> group by pub_id, type
9> order by avg(advance)
pub_id type AvgAdvPT AvgPricePT
------ ------------ ------------------------ ------------------------
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)