1> /* In-class practice exercises, 10/21/2000 (Q. 5, 8-10) */
2> /* */
3> use pubs2
1> /* */
2> /* 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> /* */
1> /* Eliminate repeated rows */
1> /* */
2> select distinct pub_id, sum(advance), avg(price)
3> from titles
4> group by pub_id
5> having sum(advance) > 15000
6> and avg(price) < 20
7> and pub_id > '0800'
8> and price >= 5
9> order by pub_id
pub_id
------ ------------------------ ------------------------
0877 41,000.00 15.41
1389 30,000.00 18.98
(2 rows affected)
1> /* */
2> /* Make au_ids "anonymous" by replacing part of ID. */
3> /* */
4> select au_id, stuff(au_id, 8, 4, 'xxxx') as anon_ID
5> from authors
au_id anon_ID
----------- -----------
172-32-1176 172-32-xxxx
213-46-8915 213-46-xxxx
238-95-7766 238-95-xxxx
267-41-2394 267-41-xxxx
274-80-9391 274-80-xxxx
341-22-1782 341-22-xxxx
409-56-7008 409-56-xxxx
427-17-2319 427-17-xxxx
472-27-2349 472-27-xxxx
486-29-1786 486-29-xxxx
527-72-3246 527-72-xxxx
648-92-1872 648-92-xxxx
672-71-3249 672-71-xxxx
712-45-1867 712-45-xxxx
722-51-5454 722-51-xxxx
724-08-9931 724-08-xxxx
724-80-9391 724-80-xxxx
756-30-7391 756-30-xxxx
807-91-6654 807-91-xxxx
846-92-7186 846-92-xxxx
893-72-1158 893-72-xxxx
899-46-2035 899-46-xxxx
998-72-3567 998-72-xxxx
(23 rows affected)
1> /* */
2> /* Write name backwards. */
3> /* */
4> select reverse('Evelyn Stevens')
--------------
snevetS nylevE
(1 row affected)
1> /* */
2> /* Minutes only for current time, class start time, 12:01 am. */
3> /* */
4> select substring(convert(char(8), getdate(), 8), 4, 2)
--
59
(1 row affected)
1> select right('12:01', 2)
--
01
(1 row affected)
1> select substring(convert(char(8), convert(smalldatetime, '9 am'), 8), 4, 2)
--
00
(1 row affected)