1> /* In-class practice exercises, 10/21/1999 (Q. 9-12) */
2> /* */
3> use pubs2
1> /* */
2> /* Q.9 Queries on pp. 182 */
3> /* */
4> /* First query: */
5> /* */
6> select pub_id, sum(advance), avg(price)
7> from titles
8> where price >= 5
9> group by pub_id
10> having sum(advance) > 15000
11> and avg(price) < 20
12> and pub_id > '0800'
13> order by pub_id
pub_id
------ ------------------------ ------------------------
0877 26,000.00 17.89
1389 30,000.00 18.98
(2 rows affected)
1> /* */
2> /* Second query, as shown in the text book: */
3> /* */
4> select pub_id, sum(advance), avg(price)
5> from titles
6> group by pub_id
7> having sum(advance) > 15000
8> and avg(price) < 20
9> and pub_id > '0800'
10> and price >= 5
11> order by pub_id
pub_id
------ ------------------------ ------------------------
0877 41,000.00 15.41
0877 41,000.00 15.41
0877 41,000.00 15.41
0877 41,000.00 15.41
0877 41,000.00 15.41
1389 30,000.00 18.98
1389 30,000.00 18.98
1389 30,000.00 18.98
1389 30,000.00 18.98
1389 30,000.00 18.98
(10 rows affected)
1> /* */
2> /* Second query, including price in select list to show why */
3> /* rows are repeated in previous query: */
4> select pub_id, sum(advance), avg(price), price
5> from titles
6> group by pub_id
7> having sum(advance) > 15000
8> and avg(price) < 20
9> and pub_id > '0800'
10> and price >= 5
11> order by pub_id
pub_id
price
------ ------------------------ ------------------------
------------------------
0877 41,000.00 15.41
11.95
0877 41,000.00 15.41
14.99
0877 41,000.00 15.41
19.99
0877 41,000.00 15.41
20.95
0877 41,000.00 15.41
21.59
1389 30,000.00 18.98
11.95
1389 30,000.00 18.98
19.99
1389 30,000.00 18.98
19.99
1389 30,000.00 18.98
20.00
1389 30,000.00 18.98
22.95
(10 rows affected)
1> /* */
2> /* Q.10. On what day of the week, by name, does Christmas */
3> /* fall this year? */
4> /* */
5> select datename(dw, '12/25/1999') as Christmas
Christmas
------------------------------
Saturday
(1 row affected)
1> /* */
2> /* Q.11. How many days have passed since Sept. 9th? */
3> /* */
4> select datediff(dd, convert(datetime, 'Sep 9 1999'), getdate()) as
NumDays
NumDays
-----------
48
(1 row affected)
1> /* */
2> /* Q.12 Create a list of authors and the city and state */
3> /* they live in, using this format: */
4> /* J. Smith, author from City, State. */
5> /* */
/* Note use of convert() to eliminate extra spacing. */
6> select convert(char(50), (substring(au_fname, 1, 1) + '. ' + au_lname
+
7> ', author from ' + city + ', ' + state + '.'))
8> as "Authors with city and state"
9> from authors
Authors with city and state
--------------------------------------------------
J. White, author from Menlo Park, CA.
M. Green, author from Oakland, CA.
C. Carson, author from Berkeley, CA.
M. O'Leary, author from San Jose, CA.
D. Straight, author from Oakland, CA.
M. Smith, author from Lawrence, KS.
A. Bennet, author from Berkeley, CA.
A. Dull, author from Palo Alto, CA.
B. Gringlesby, author from Covelo, CA.
C. Locksley, author from San Francisco, CA.
M. Greene, author from Nashville, TN.
R. Blotchet-Halls, author from Corvallis, OR.
A. Yokomoto, author from Walnut Creek, CA.
I. del Castillo, author from Ann Arbor, MI.
M. DeFrance, author from Gary, IN.
D. Stringer, author from Oakland, CA.
S. MacFeather, author from Oakland, CA.
L. Karsen, author from Oakland, CA.
S. Panteley, author from Rockville, MD.
S. Hunter, author from Palo Alto, CA.
H. McBadden, author from Vacaville, CA.
A. Ringer, author from Salt Lake City, UT.
A. Ringer, author from Salt Lake City, UT.
(23 rows affected)