Homework 9 -- Ultimate SQL
The database
It's the Homework 6 database again. On
the CSCI Unix machines, you'll find it at
- /usr/local/csci/343/home6/home6.mdb
On the Windows 95 machines in Karpen 037, it's
- T:\csci\Csci343\Home6.mdb
The problem
Design nine SQL queries (most of them pretty hard) to display the
following information, shown below with the expected result of the query:
- Courses offered in both the Fall 1995 and Spring 1996 terms.
| 126 |
| 142 |
| 173 |
| 201 |
| 202 |
| 254 |
| 351 |
| 373 |
| 462 |
- All academic years, displayed as a row consisting of the calender years
of the Fall and Spring terms, in which a section of CSCI 373 was offered in
both the Fall and Spring terms.
| 1993 |
1994 |
| 1995 |
1996 |
| 1996 |
1997 |
| 1997 |
1998 |
- The combined enrollment in all 100-level courses (numbers 100 up to 199)
in the Fall term of each year.
| 1988 |
241 |
| 1989 |
233 |
| 1990 |
209 |
| 1991 |
285 |
| 1992 |
271 |
| 1993 |
247 |
| 1994 |
255 |
| 1995 |
241 |
| 1996 |
222 |
| 1997 |
225 |
- The total enrollment in all sections of 126 in the Fall and Spring
semesters.
| 1988 |
6 |
112 |
| 1989 |
1 |
107 |
| 1989 |
6 |
121 |
| 1990 |
1 |
141 |
| 1990 |
6 |
136 |
| 1991 |
1 |
146 |
| 1991 |
6 |
159 |
| 1992 |
1 |
193 |
| 1992 |
6 |
203 |
| 1993 |
1 |
182 |
| 1993 |
6 |
180 |
| 1994 |
1 |
193 |
| 1994 |
6 |
207 |
| 1995 |
1 |
169 |
| 1995 |
6 |
127 |
| 1996 |
1 |
126 |
| 1996 |
6 |
104 |
| 1997 |
1 |
119 |
- The years and terms in which sections of both CSCI 142 and CSCI 343 were
offered.
| 1995 |
1 |
| 1996 |
1 |
| 1997 |
1 |
| 1998 |
1 |
- The years and terms in which sections of both CSCI 142 and CSCI 343 were
offered and enrollment in 343 exceeded enrollment in 142. You may
assume only one section of each was offered.
- The year and term in which CSCI 343 had its greatest enrollment. Again,
you may assume only one section of CSCI 343 was offered. You'll probably need
to do an SQL subquery to solve this problem.
- The years and numbers of CSCI 126 sections offered in the Fall of each
year.
| 1988 |
6 |
| 1989 |
8 |
| 1990 |
9 |
| 1991 |
10 |
| 1992 |
11 |
| 1993 |
10 |
| 1994 |
11 |
| 1995 |
7 |
| 1996 |
6 |
- The years and terms and counts of CSCI 126 sections in terms in which ten
or more sections of CSCI 126 were offered.
| 1991 |
6 |
10 |
| 1992 |
1 |
10 |
| 1992 |
6 |
11 |
| 1993 |
1 |
10 |
| 1993 |
6 |
10 |
| 1994 |
1 |
11 |
| 1994 |
6 |
11 |
| 1995 |
1 |
10 |
Turning in your solution
Use Access and store your queries under the names query1,
query2, etc. Finally FTP your solution onto the CSCI machines at
~/csci/343/home9/home9.mbd -- which should be protected as it has
been for all previous assignments.
Due dates
At noon on Saturday, 2 May, a script will be run to copy your
home9.mbd. This script will be run at noon for five consecutive
days. Each day your program is missing or fails to successfully solve the
problem, 20% will be deducted from your grade for homework nine.
This is a hard assignment and will count twice as much as homework 6.
Don't wait to the last minute to start.
Back to the CSCI 363 homework page
Back to the CSCI 363 home page