Homework 6 -- QBE
It's the Return of Homework 4, but
this time with QBE.
Preparation
You know the drill by now.
- cd
- mkdir -p csci/343/home6
- chmod go-rx csci/343/home6
The database
It's in Access. 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
Both files are write-protected, so you'll have to copy them before
attempting to add the queries.
The problem
Design ten, no not seven, SQL queries to display the
following information
- The years and terms in which CSCI 343 has been offered
- The courses and sections offered in the Fall 1993 semester
- The courses taught in the Fall 1993 semester
- The year, term, course number, section, and enrollment of any section
with an enrollment greater than 29
- The names and course numbers of all courses offered in the Fall 90
semester
- The names and course numbers of all courses offered in both the Fall 90
and Spring 91 semester -- Note "offered in both semesters" means that you
could have taken the course in either the Fall or Spring
- The number and total enrollment, computed by summing enrollment in all
sections, of courses offered in the Spring 1996 semester
- The year, term, and number of students taking all courses in each
semester
- The number and name of any course that contains the word
COMPUTER in its title
- The number and total credit hours generated, computed by multiplying
enrollment by credit hours, of each course in the Fall 97 semester
For each query, print only the requested columns and do not print
any row more than once.
Turning in your solution
Store your queries under the names query1, query2,
etc. Then FTP your solution onto the CSCI machines and store it at
~/csci/343/home6/home6.mbd
Due dates
At noon on Monday, 30 March, a script will be run to copy your
home6.sql to some place where Access runs. 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 six.
Back to the CSCI 363 homework page
Back to the CSCI 363 home page