Additional examples from last two classes:
- Adding a row counter without "setrow" or temp table
- Room reservation problem (list latest reservation date)
- Examples using where vs. having with aggregate functions
Row counter
1> use evelyn
1>
2> drop table uniquenames
1>
2> create table uniquenames
3> (name varchar(10))
1>
2> insert into uniquenames
3> values('Evelyn')
4> insert into uniquenames
5> values('John')
6> insert into uniquenames
7> values('Jerry')
8> insert into uniquenames
9> values('Martha')
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1>
2> select * from uniquenames
name
----------
Evelyn
John
Jerry
Martha
(4 rows affected)
1>
2> select distinct a.name, count(b.name)
3> from uniquenames a, uniquenames b
4> where a.name >= b.name
5> group by a.name
6> order by a.name
name
---------- -----------
Evelyn 1
Jerry 2
John 3
Martha 4
(4 rows affected)
Room reservation problem
1> use evelyn
1>
2> /* List buildings, rooms and dates from schedule table in my */
3> /* to show which ones we want. */
4>
5> select building, room, date
6> from schedule
7> order by building, room
building room date
-------- ---- --------------------------
AA 100 Oct 1 1997 12:11AM
AA 101 Sep 27 1997 12:00AM
AA 101 Oct 1 1997 12:00AM
AA 105 Oct 1 1997 12:11AM
AA 202 Sep 30 1997 11:53PM
AA 202 Oct 7 1997 12:49AM
AB 105 Sep 30 1997 11:35PM
BB 101 Oct 7 1997 12:49AM
BB 101 May 15 1998 1:54PM
CC 101 Oct 1 1997 12:11AM
CC 101 Oct 1 1997 12:11AM
aa abc May 14 1998 8:02PM
aa abc May 14 1998 8:03PM
(13 rows affected)
1>
2> /* No "guest" table (simpler than example), so just a self-join */
3> /* to compare difference between each date and current date with */
4> /* minimum (latest) difference. */
5>
6> select a.building, a.room, a.date, a.event
7> from schedule a
8> where datediff(dd, a.date, getdate()) =
9> (select min(datediff(dd, b.date, getdate())) from schedule b
10> where b.room = a.room
11> and b.building = a.building
12> and b.date < getdate())
13> order by a.building, a.room, a.date
building room date
event
-------- ---- --------------------------
----------------------------------------
AA 100 Oct 1 1997 12:11AM
Unknown
AA 101 Oct 1 1997 12:00AM
Senior seminar
AA 105 Oct 1 1997 12:11AM
Unknown
AA 202 Oct 7 1997 12:49AM
%%% Important meeting! %%%
AB 105 Sep 30 1997 11:35PM
Database class
BB 101 May 15 1998 1:54PM
ISQL commands can be mixed case
CC 101 Oct 1 1997 12:11AM
Introduction to Computing
CC 101 Oct 1 1997 12:11AM
Introduction to Computing
aa abc May 14 1998 8:02PM
Another event
aa abc May 14 1998 8:03PM
Unknown
(10 rows affected)
/* Note above: Rooms scheduled at different times on the */
/* same date show up twice. Could refine further if needed. */
Examples using where and having
1> /* Examples to illustrate effect of where and having on */
2> /* aggregate function calculations. */
3> /* Queries are similar to those on pp. 365-366, with the */
4> /* following changes: */
5> /* - Find avg(advance) for each pub_id and type */
6> /* rather than max(advance) to show calculation */
1> use pubs2
2> go
7> /* - Include advance to make differences obvious */
8> /* */
9> /* Example using where */
10>* */
1> select pub_id, type, advance, avg(advance)
2> from titles
3> where advance > 4000
4> group by pub_id, type
5> order by pub_id, type
6> go
pub_id type advance
------ ------------ ------------------------ ------------------------
0736 business 10,125.00 10,125.00
0736 psychology 2,000.00 6,000.00
0736 psychology 2,275.00 6,000.00
0736 psychology 4,000.00 6,000.00
0736 psychology 6,000.00 6,000.00
0877 mod_cook 0.00 15,000.00
0877 mod_cook 15,000.00 15,000.00
0877 psychology 7,000.00 7,000.00
0877 trad_cook 4,000.00 7,500.00
0877 trad_cook 7,000.00 7,500.00
0877 trad_cook 8,000.00 7,500.00
1389 business 5,000.00 5,000.00
1389 business 5,000.00 5,000.00
1389 business 5,000.00 5,000.00
1389 popular_comp NULL 7,500.00
1389 popular_comp 7,000.00 7,500.00
1389 popular_comp 8,000.00 7,500.00
(17 rows affected)
1> /* Above: Values < 4000 were not included in avg(advance) */
2> /* Query should be set up correctly without advance column. */
3> /* */
4> /* Same query with having: */
5> /* */
6> select pub_id, type, advance, avg(advance)
7> from titles
8> group by pub_id, type
9> having advance > 4000
10> order by pub_id, type
11> go
pub_id type advance
------ ------------ ------------------------ ------------------------
0736 business 10,125.00 10,125.00
0736 psychology 6,000.00 3,568.75
0877 mod_cook 15,000.00 7,500.00
0877 psychology 7,000.00 7,000.00
0877 trad_cook 7,000.00 6,333.33
0877 trad_cook 8,000.00 6,333.33
1389 business 5,000.00 5,000.00
1389 business 5,000.00 5,000.00
1389 business 5,000.00 5,000.00
1389 popular_comp 7,000.00 7,500.00
1389 popular_comp 8,000.00 7,500.00
(11 rows affected)
1> /* Above: All values were included in avg(advance) */
2> /* Rows with advance < 4000 not included in result. */
3> /* */
4> /* Which to use depends on what the question is: */
5> /* What is the average advance, by pub_id and type, for */
6> /* those titles for which the the advance was greater than $4000?*/
7> /* -- use where: */
8> select pub_id, type, avg(advance)
9> from titles
10> where advance > 4000
11> group by pub_id, type
12> order by pub_id, type
13> go
pub_id type
------ ------------ ------------------------
0736 business 10,125.00
0736 psychology 6,000.00
0877 mod_cook 15,000.00
0877 psychology 7,000.00
0877 trad_cook 7,500.00
1389 business 5,000.00
1389 popular_comp 7,500.00
(7 rows affected)
1> /* Calculate the average advance for all books, but list */
2> /* only those for which the advance was > $4000, by pub_id */
3> /* and type (...presumably for some good reason). */
4> select pub_id, type, avg(advance)
5> from titles
6> group by pub_id, type
7> having advance > 4000
8> order by pub_id, type
9> go
pub_id type
------ ------------ ------------------------
0736 business 10,125.00
0736 psychology 3,568.75
0877 mod_cook 7,500.00
0877 psychology 7,000.00
0877 trad_cook 6,333.33
0877 trad_cook 6,333.33
1389 business 5,000.00
1389 business 5,000.00
1389 business 5,000.00
1389 popular_comp 7,500.00
1389 popular_comp 7,500.00
(11 rows affected)
1> /* Repetition above tells us there is something */
2> /* distinctive about each of the repeated rows - in */
3> /* this case we know it is the advance. */