CS 307 Lab 3

due as specified on course web page

In the jensen and ships databases, do these queries requiring aggregate functions and GROUP BY:
  1. Count how many courses are taught by each professor, and how many credits they total.
  2. (don't copy result) List all course codes and count of enrolled students, for courses that have at least 2 students enrolled
  3. List all course codes and titles and count of enrolled students, for courses that have at least 2 students enrolled (like last one, but requires a join).
Queries that could be done using a subquery (but not necessarily a grouping):
What attribues uniqely identify a "room"?
  1. List all courses taught in the rooms used by prof Jensen (code and title).
  2. List names of all students enrolled in courses taught by the prof who teaches 311.
In the Ships database,
  1. The average bore of battleship classes (type='bb')
  2. The average bore of all battleships (type='bb'), and their total number of guns. This average should be different from the 1st query, why?
  3. The number of ships involved in each battle.
  4. The number of battles in which each ship participated, if more than 1.
Submit your results in the usual way - by email or, submit cs307 yourfile

In your file, be sure to show ALL your SQL queries, and all results except where it says "don't copy result".

For reference

The order of clauses:
select		-- columns wanted, or *
from		-- tables and joins
where		-- before grouping
group by 
having		-- conditions on the grouping
order by
limit		-- max. number of rows wanted

insert into table values (...);
update table set col='value',... where

Create some tables

Your own database is set up, its name is same as your username.
Connect to it in psql using \c yourusername  -or-  at command line with psql
Please create two tables that can be joined together, for example, people, each with a set of phone numbers, or set of sports.
Put some data in your tables.

You might use these tables in future labs, if not, you can DROP them once your lab is marked.

You do not have to "submit" these tables other than having created them and put in some data.

Completion of lab:

By Friday, at the latest, please.

At the end of this lab, please submit the text file into which you will have pasted the required queries and their results.You may also put in any comments you think are appropriate:
Please put your name at the beginning of the file, so when printed I can identify it later! 
Prepared by Lin Jensen. back to Database index
Last Modified