UNIVERSITY OF WISCONSIN--MADISON

COMPUTER SCIENCES DEPARTMENT

CS 564: DATABASE MANAGEMENT SYSTEMS

Assignment 1: SQL Queries
Due: Wednesday, September 27, 1996, at 5 p.m.
Instructor: Raghu Ramakrishnan

The Database Schema

The relations for this assignment are in a database called raghu564, and have the following schemas:

Student(Snum, Name, Major, Level, Age)
Class(Name, Time, Room, Fid)
Enrolled(Snum, ClassName)
Faculty(Fid, Name, Dept)
The keys are in italics. The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that student is enrolled in class. They were created using the following SQL statements:

CREATE TABLE Student (
Snum int,
Name char(10),
Major char(10),
Level char(2),
Age int );

CREATE TABLE Class (
Name char(10),
Time char(10),
Room char(10),
Fid int );

CREATE TABLE Enrolled (
Snum int,
ClassName char(10)
);

CREATE TABLE Faculty (
Fid int,
Name char(10),
Dept char(10)
);

The Queries

Using the database raghu564, write the following queries in SQL. No duplicates should be printed in any of the answers.

  1. Find the names of all CS Majors (Major = "CS") who are enrolled in the course "Math92".
  2. Find the names of all CS Majors (Major = "CS") who are enrolled in the course "Math92" and are older than some History freshman (Level="FR").
  3. Find the names of all classes that either meet in room R128 or are taught by "H.Merlin".
  4. Find the names of all pairs of students who are enrolled in some class together.
  5. Find the names of all pairs of students who are enrolled in two classes that meet at the same time (including pairs of students who are enrolled in the same class).
  6. Find the names of faculty members who teach in every room in which some class is taught in the time period "MW9-10".
  7. Find the names of faculty members such that the combined enrollment of the courses that they teach is less than 5.
  8. Print the Level and the average age of students for that Level, for each Level.
  9. Find the name of the student who is enrolled in the most classes.
  10. Find the names of all students who are not enrolled in any class taught by "H.Merlin".

What to Turn In

You are to hand in a script of a session on SYBASE with your queries and the answers. To do this, you will start script to generate a script file and pipe the query file to the SQL interpreter. The queries and results will be recorded in the script file which you will then hand in. (See the accompanying information sheet on using SYBASE for more details on how to do this.)