1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* Cumulative statistics - examples */
4> /* */
5> use pubs2
1> /* */
2> /* Rank pubs2 titles in order of publication. */
3> /* */
4> select t1.title_id, t1.pubdate,
5> (select count(*)
6> from titles t2
7> where t2.pubdate < t1.pubdate) + 1 LineNum
8> from titles t1
9> order by t1.pubdate
title_id pubdate LineNum
-------- -------------------------- -----------
TC4203 Jun 12 1985 12:00AM 1
MC3021 Jun 18 1985 12:00AM 2
BU2075 Jun 30 1985 12:00AM 3
BU1032 Jun 12 1986 12:00AM 4
PC1035 Jun 30 1986 12:00AM 5
PC8888 Jun 12 1987 12:00AM 6
TC7777 Jun 12 1987 12:00AM 6
BU7832 Jun 22 1987 12:00AM 8
BU1111 Jun 9 1988 12:00AM 9
PS3333 Jun 12 1988 12:00AM 10
PS7777 Jun 12 1988 12:00AM 10
MC2222 Jun 9 1989 12:00AM 12
PS2091 Jun 15 1989 12:00AM 13
PS2106 Oct 5 1990 12:00AM 14
PS1372 Oct 21 1990 12:00AM 15
TC3218 Oct 21 1990 12:00AM 15
MC3026 Sep 15 1999 2:26PM 17
PC9999 Sep 15 1999 2:26PM 18
(18 rows affected)
1> /* */
2> /* Create numbered, alphabetically sorted list of authors. */
3> /* */
4> select
5> (select count(*)
6> from authors a2
7> where a2.au_lname < a1.au_lname) + 1 Author#,
8> convert(char(25), rtrim(a1.au_fname + ' ' + a1.au_lname)) AuthName,
9> substring(a1.city, 1, 15) City, a1.state
10> from authors a1
11> order by a1.au_lname
Author# AuthName City state
----------- ------------------------- --------------- -----
1 Abraham Bennet Berkeley CA
2 Reginald Blotchet-Halls Corvallis OR
3 Cheryl Carson Berkeley CA
4 Michel DeFrance Gary IN
5 Ann Dull Palo Alto CA
6 Marjorie Green Oakland CA
7 Morningstar Greene Nashville TN
8 Burt Gringlesby Covelo CA
9 Sheryl Hunter Palo Alto CA
10 Livia Karsen Oakland CA
11 Chastity Locksley San Francisco CA
12 Stearns MacFeather Oakland CA
13 Heather McBadden Vacaville CA
14 Michael O'Leary San Jose CA
15 Sylvia Panteley Rockville MD
16 Anne Ringer Salt Lake City UT
16 Albert Ringer Salt Lake City UT
18 Meander Smith Lawrence KS
19 Dick Straight Oakland CA
20 Dirk Stringer Oakland CA
21 Johnson White Menlo Park CA
22 Akiko Yokomoto Walnut Creek CA
23 Innes del Castillo Ann Arbor MI
(23 rows affected)
1> /* */
2> /* As previous, but correct "same last name" problem. */
3> /* */
4> select
5> (select count(*)
6> from authors a2
7> where
8> a2.au_lname + a2.au_fname < a1.au_lname + a1.au_fname) + 1 Author#,
9> convert(char(25), rtrim(a1.au_fname + ' ' + a1.au_lname)) AuthName,
10> substring(a1.city, 1, 15) City, a1.state
11> from authors a1
12> order by a1.au_lname, a1.au_fname
Author# AuthName City state
----------- ------------------------- --------------- -----
1 Abraham Bennet Berkeley CA
2 Reginald Blotchet-Halls Corvallis OR
3 Cheryl Carson Berkeley CA
4 Michel DeFrance Gary IN
5 Ann Dull Palo Alto CA
6 Marjorie Green Oakland CA
7 Morningstar Greene Nashville TN
8 Burt Gringlesby Covelo CA
9 Sheryl Hunter Palo Alto CA
10 Livia Karsen Oakland CA
11 Chastity Locksley San Francisco CA
12 Stearns MacFeather Oakland CA
13 Heather McBadden Vacaville CA
14 Michael O'Leary San Jose CA
15 Sylvia Panteley Rockville MD
16 Albert Ringer Salt Lake City UT
17 Anne Ringer Salt Lake City UT
18 Meander Smith Lawrence KS
19 Dick Straight Oakland CA
20 Dirk Stringer Oakland CA
21 Johnson White Menlo Park CA
22 Akiko Yokomoto Walnut Creek CA
23 Innes del Castillo Ann Arbor MI
(23 rows affected)
1> /* */
2> /* Assign order numbers by sales date. */
3> /* */
4> select s1.ord_num, s1.date,
5> (select count(*)
6> from sales s2
7> where s2.date < s1.date) + 1 OrderNum
8> from sales s1
9> order by s1.date
ord_num date OrderNum
-------------------- -------------------------- -----------
BA27618 Oct 12 1985 12:00AM 1
AB-123-DEF-425-1Z3 Oct 31 1985 12:00AM 2
AB-872-DEF-732-2Z1 Nov 6 1985 12:00AM 3
342157 Dec 13 1985 12:00AM 4
12-F-9 Jul 13 1986 12:00AM 5
124152 Aug 14 1986 12:00AM 6
Asoap132 Nov 16 1986 12:00AM 7
BS-345-DSE-860-1F2 Dec 12 1986 12:00AM 8
NB-1.142 Jan 2 1987 12:00AM 9
GH-542-NAD-713-9F9 Mar 15 1987 12:00AM 10
NF-123-ADS-642-9G3 Jul 18 1987 12:00AM 11
Fsoap867 Sep 8 1987 12:00AM 12
234518 Sep 30 1987 12:00AM 13
BA52498 Oct 27 1987 12:00AM 14
13-J-9 Jan 13 1988 12:00AM 15
ZA-000-ASD-324-4D1 Jul 27 1988 12:00AM 16
BA71224 Aug 5 1988 12:00AM 17
13-E-7 May 23 1989 12:00AM 18
NB-3.142 Jun 13 1990 12:00AM 19
AX-532-FED-452-2Z7 Dec 1 1990 12:00AM 20
Asoap432 Dec 20 1990 12:00AM 21
234518 Feb 14 1991 12:00AM 22
356921 Feb 17 1991 12:00AM 23
55-V-7 Mar 20 1991 12:00AM 24
91-A-7 Mar 20 1991 12:00AM 24
91-V-7 Mar 20 1991 12:00AM 24
XS-135-DER-432-8J2 Mar 21 1991 12:00AM 27
ZD-123-DFG-752-9G8 Mar 21 1991 12:00AM 27
ZS-645-CAT-415-1B2 Mar 21 1991 12:00AM 27
ZZ-999-ZZZ-999-0A0 Mar 21 1991 12:00AM 27
(30 rows affected)
1> /* */
2> /* Keep a running total of titles published: Start with */
3> /* a ranking query (variation on first query above), and */
4> /* use it to find max(count) for each row. */
5> /* */
6> select t1.title_id, datepart(yy, t1.pubdate) PubYear,
7> (select count(*)
8> from titles t2
9> where t2.pubdate < t1.pubdate) + 1 PubOrder
10> from titles t1
11> order by t1.pubdate
title_id PubYear PubOrder
-------- ----------- -----------
TC4203 1985 1
MC3021 1985 2
BU2075 1985 3
BU1032 1986 4
PC1035 1986 5
PC8888 1987 6
TC7777 1987 6
BU7832 1987 8
BU1111 1988 9
PS3333 1988 10
PS7777 1988 10
MC2222 1989 12
PS2091 1989 13
PS2106 1990 14
PS1372 1990 15
TC3218 1990 15
MC3026 1999 17
PC9999 1999 18
(18 rows affected)
1> /* */
2> select t1.title_id, datepart(yy, t1.pubdate) PubYear,
3> (select count(*)
4> from titles t2
5> where t2.pubdate < t1.pubdate) + 1 PubOrder,
6> max(
7> (select count(*)
8> from titles t2
9> where t2.pubdate < t1.pubdate) +1) AllPublished
10> from titles t1
11> group by datepart(yy, t1.pubdate)
12> order by t1.pubdate
title_id PubYear PubOrder AllPublished
-------- ----------- ----------- ------------
TC4203 1985 1 3
MC3021 1985 2 3
BU2075 1985 3 3
BU1032 1986 4 5
PC1035 1986 5 5
PC8888 1987 6 8
TC7777 1987 6 8
BU7832 1987 8 8
BU1111 1988 9 10
PS3333 1988 10 10
PS7777 1988 10 10
MC2222 1989 12 13
PS2091 1989 13 13
PS2106 1990 14 15
PS1372 1990 15 15
TC3218 1990 15 15
MC3026 1999 17 18
PC9999 1999 18 18
(18 rows affected)
1> /* */
2> /* As previous, but better grouping because count not shown: */
3> /* */
4> select datepart(yy, t1.pubdate) PubYear,
5> max(
6> (select count(*)
7> from titles t2
8> where t2.pubdate < t1.pubdate) +1
9> ) NumPublished
10> from titles t1
11> group by datepart(yy, t1.pubdate)
12> order by datepart(yy, t1.pubdate)
PubYear NumPublished
----------- ------------
1985 3
1986 5
1987 8
1988 10
1989 13
1990 15
1999 18
(7 rows affected)
1> /* */
2> /* Keep running total, by date, of books sold. */
3> /* */
4> select convert(char(12), date, 101) OrderDate, s.ord_num,
5> sum(sd.qty) OrderTotal,
6> (select sum(sd2.qty)
7> from sales s2, salesdetail sd2
8> where s2.ord_num = sd2.ord_num
9> and s2.date <= s.date) RunningTotal
10> -- Using only " < " above moves RunningTotal down one row (starts NULL)
11> -- See separate query below.
12> from sales s, salesdetail sd
13> where s.ord_num = sd.ord_num
14> group by s.date, s.ord_num
15> order by s.date, s.ord_num
OrderDate ord_num OrderTotal RunningTotal
------------ -------------------- ----------- ------------
10/12/1985 BA27618 630 630
10/31/1985 AB-123-DEF-425-1Z3 6500 7130
11/06/1985 AB-872-DEF-732-2Z1 5000 12130
12/13/1985 342157 450 12580
07/13/1986 12-F-9 827 13407
08/14/1986 124152 417 13824
11/16/1986 Asoap132 517 14341
12/12/1986 BS-345-DSE-860-1F2 10400 24741
01/02/1987 NB-1.142 723 25464
03/15/1987 GH-542-NAD-713-9F9 6000 31464
07/18/1987 NF-123-ADS-642-9G3 14500 45964
09/08/1987 Fsoap867 950 46914
09/30/1987 234518 1920 48834
10/27/1987 BA52498 100 48934
01/13/1988 13-J-9 550 49484
07/27/1988 ZA-000-ASD-324-4D1 7600 57084
08/05/1988 BA71224 1700 58784
05/23/1989 13-E-7 1476 60260
06/13/1990 NB-3.142 2177 62437
12/01/1990 AX-532-FED-452-2Z7 3600 66037
12/20/1990 Asoap432 630 66667
02/14/1991 234518 1920 68587
02/17/1991 356921 825 69412
03/20/1991 55-V-7 31 70292
03/20/1991 91-A-7 409 70292
03/20/1991 91-V-7 440 70292
03/21/1991 XS-135-DER-432-8J2 8311 99366
03/21/1991 ZD-123-DFG-752-9G8 7150 99366
03/21/1991 ZS-645-CAT-415-1B2 9200 99366
03/21/1991 ZZ-999-ZZZ-999-0A0 4413 99366
(30 rows affected)
1> /* */
2> /* Note: OK to convert dates for display, but not for the */
3> /* comparison in the subquery: Dates don't sort correctly as */
4> /* text. */
5> /* */
6> /* Start RunningTotal at second row: */
7> /* (Using order number for sort criteria instead of date.) */
8> /* */
9> select sd2.ord_num, sum(sd2.qty) OrderTotal,
10> (select sum(sd.qty)
11> from sales s, salesdetail sd
12> where s.ord_num = sd.ord_num
13> and sd.ord_num < sd2.ord_num) RunningTotal
14> from salesdetail sd2
15> group by sd2.ord_num
16> order by sd2.ord_num
ord_num OrderTotal RunningTotal
-------------------- ----------- ------------
12-F-9 827 NULL
124152 417 827
13-E-7 1476 1244
13-J-9 550 2720
234518 1920 3270
342157 450 7110
356921 825 7560
55-V-7 31 8385
91-A-7 409 8416
91-V-7 440 8825
AB-123-DEF-425-1Z3 6500 9265
AB-872-DEF-732-2Z1 5000 15765
AX-532-FED-452-2Z7 3600 20765
Asoap132 517 24365
Asoap432 630 24882
BA27618 630 25512
BA52498 100 26142
BA71224 1700 26242
BS-345-DSE-860-1F2 10400 27942
Fsoap867 950 38342
GH-542-NAD-713-9F9 6000 39292
NB-1.142 723 45292
NB-3.142 2177 46015
NF-123-ADS-642-9G3 14500 48192
XS-135-DER-432-8J2 8311 62692
ZA-000-ASD-324-4D1 7600 71003
ZD-123-DFG-752-9G8 7150 78603
ZS-645-CAT-415-1B2 9200 85753
ZZ-999-ZZZ-999-0A0 4413 94953
(29 rows affected)
1> /* */
2> dump tran evelyn with truncate_only