1> /* In-class practice exercises, 10/21/1999 (Q. 6-7) */
2> /* */
3> use pubs2
1> /* */
2> /* Develop a single report on total sales, total revenue, and */
3> /* advances paid, by publisher and by type for each publisher, */
5> /* including grand totals for all publishers and types. */
6> /* */
7> select pub_id, type, total_sales, total_sales*price, advance
8> from titles
9> order by pub_id, type
10> compute sum(total_sales), sum(total_sales*price), sum(advance)
11> by pub_id, type
12> compute sum(total_sales), sum(total_sales*price), sum(advance)
13> by pub_id
14> compute sum(total_sales), sum(total_sales*price), sum(advance)
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0736 business 18722 55,978.78
10,125.00
Compute Result:
----------- ------------------------ ------------------------
18722 55,978.78 10,125.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0736 psychology 111 777.00
6,000.00
0736 psychology 2045 22,392.75
2,275.00
0736 psychology 3336 26,654.64
4,000.00
0736 psychology 4072 81,399.28
2,000.00
Compute Result:
----------- ------------------------ ------------------------
9564 131,223.67 14,275.00
Compute Result:
----------- ------------------------ ------------------------
28286 187,202.45 24,400.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 UNDECIDED NULL NULL
NULL
Compute Result:
----------- ------------------------ ------------------------
NULL NULL NULL
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 mod_cook 2032 40,619.68
0.00
0877 mod_cook 22246 66,515.54
15,000.00
Compute Result:
----------- ------------------------ ------------------------
24278 107,135.22 15,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 psychology 375 8,096.25
7,000.00
Compute Result:
----------- ------------------------ ------------------------
375 8,096.25 7,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 trad_cook 375 7,856.25
7,000.00
0877 trad_cook 4095 61,384.05
8,000.00
0877 trad_cook 15096 180,397.20
4,000.00
Compute Result:
----------- ------------------------ ------------------------
19566 249,637.50 19,000.00
Compute Result:
----------- ------------------------ ------------------------
44219 364,868.97 41,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
1389 business 3876 46,318.20
5,000.00
1389 business 4095 81,859.05
5,000.00
1389 business 4095 81,859.05
5,000.00
Compute Result:
----------- ------------------------ ------------------------
12066 210,036.30 15,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
1389 popular_comp NULL NULL
NULL
1389 popular_comp 4095 81,900.00
8,000.00
1389 popular_comp 8780 201,501.00
7,000.00
Compute Result:
----------- ------------------------ ------------------------
12875 283,401.00 15,000.00
Compute Result:
----------- ------------------------ ------------------------
24941 493,437.30 30,000.00
Compute Result:
----------- ------------------------ ------------------------
97446 1,045,508.72 95,400.00
(30 rows affected)
1> /* */
2> /* Same as previous, but eliminate unknown quantities. */
3> /* */
4> select pub_id, type, total_sales, total_sales*price, advance
5> from titles
6> where
7> (total_sales is not null or price is not null or advance is not null)
8> order by pub_id, type
9> compute sum(total_sales), sum(total_sales*price), sum(advance)
10> by pub_id, type
11> compute sum(total_sales), sum(total_sales*price), sum(advance)
12> by pub_id
13> compute sum(total_sales), sum(total_sales*price), sum(advance)
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0736 business 18722 55,978.78
10,125.00
Compute Result:
----------- ------------------------ ------------------------
18722 55,978.78 10,125.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0736 psychology 111 777.00
6,000.00
0736 psychology 2045 22,392.75
2,275.00
0736 psychology 3336 26,654.64
4,000.00
0736 psychology 4072 81,399.28
2,000.00
Compute Result:
----------- ------------------------ ------------------------
9564 131,223.67 14,275.00
Compute Result:
----------- ------------------------ ------------------------
28286 187,202.45 24,400.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 mod_cook 2032 40,619.68
0.00
0877 mod_cook 22246 66,515.54
15,000.00
Compute Result:
----------- ------------------------ ------------------------
24278 107,135.22 15,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 psychology 375 8,096.25
7,000.00
Compute Result:
----------- ------------------------ ------------------------
375 8,096.25 7,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
0877 trad_cook 375 7,856.25
7,000.00
0877 trad_cook 4095 61,384.05
8,000.00
0877 trad_cook 15096 180,397.20
4,000.00
Compute Result:
----------- ------------------------ ------------------------
19566 249,637.50 19,000.00
Compute Result:
----------- ------------------------ ------------------------
44219 364,868.97 41,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
1389 business 3876 46,318.20
5,000.00
1389 business 4095 81,859.05
5,000.00
1389 business 4095 81,859.05
5,000.00
Compute Result:
----------- ------------------------ ------------------------
12066 210,036.30 15,000.00
pub_id type total_sales
advance
------ ------------ ----------- ------------------------
------------------------
1389 popular_comp 4095 81,900.00
8,000.00
1389 popular_comp 8780 201,501.00
7,000.00
Compute Result:
----------- ------------------------ ------------------------
12875 283,401.00 15,000.00
Compute Result:
----------- ------------------------ ------------------------
24941 493,437.30 30,000.00
Compute Result:
----------- ------------------------ ------------------------
97446 1,045,508.72 95,400.00
(27 rows affected)