Homework 6 -- QBE

It's the Return of Homework 4, but this time with QBE.

Preparation

You know the drill by now.

The database

It's in Access. On the CSCI Unix machines, you'll find it at

On the Windows 95 machines in Karpen 037, it's

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

  1. The years and terms in which CSCI 343 has been offered
  2. The courses and sections offered in the Fall 1993 semester
  3. The courses taught in the Fall 1993 semester
  4. The year, term, course number, section, and enrollment of any section with an enrollment greater than 29
  5. The names and course numbers of all courses offered in the Fall 90 semester
  6. 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
  7. The number and total enrollment, computed by summing enrollment in all sections, of courses offered in the Spring 1996 semester
  8. The year, term, and number of students taking all courses in each semester
  9. The number and name of any course that contains the word COMPUTER in its title
  10. 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