Homework 4 -- SQL

Preparation

Execute the following commands

The database

There is a miniSql 2.0 database CSCI343 stored on the server woodfin.cs.unca.edu. This database contains two tables: CourseEnroll with fields

Field Type Length
Year int 4
Term char 1
Number int 4
Section char 3
Enrollment int 4
CH int 4

which describes CSCI course offerings at UNCA over the last several years and CourseNamewith fields

Field Type Length
Number int 4
Title char 30

which gives the titles of CSCI courses. By the way, in the Termfield of CourseEnroll, 1 is Spring, 3 is Summer, and 6 is Fall. Yep, that's how they really do it.

To look at the contents of these tables, log into woodfin.cs.unca.edu and start Mini SQL by typing the command

then type the following commands to msql. The \gsends an SQL statement to the database server and the \qquits msql.

The problem

Design ten 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
  7. The number and name of any course that contains the word COMPUTER in its title

For each query, print only the requested columns and do notprint any row more than once.

Turning in your solution

Use your favorite text editor to create a file home4.sqlin the home4directory you created above. Your file should contain ten lines, one for each of the above queries. Each line mustbe of the form

You should be able to run all seven queries with the following command

Due dates

At noon on Tuesday, 3 March, a script will be run to execute your home4.sqlcommands in mini SQL. 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 four.

Back to the CSCI 363 homework page
Back to the CSCI 363 home page