Lab 02 - SQL Part 1

1. SQL Practice (Optional)

PostgresSQL Exercises is a popular site for practicing SQL commands. While this is an optional part of the assignment, I highly recommend that you complete the following:

2. Build a database

Create a database that matches this schema:

Database schema

To do so, you'll need to issue the appropriate SQL commands to create the database. Then you'll need to issue the appropriate SQL commands to create the tables along with their specified columns, primary keys, and foreign key constraints.

3. Populate the database

Once your database has been built, download this SQL file and use it to populate your database using the following command:

psql -U <your user name> -d <the database name> -f <FULL PATH TO THE SQL FILE YOU DOWNLOADED>

Alternatively, if you're already connected to the database you want to import that data to, you can use psql's \i command from the psql prompt:

university=# \i <FULL PATH TO THE SQL FILE YOU DOWNLOADED>

If you have built the schema correctly, you should receive no errors. If you haven't, you'll need to go back to step 2 and see what is wrong.

4. Use the database

Write the necessary SQL query to answer the following questions.

Points for each question are assigned as follows:

Answer the question correctly using a single query: 100%
Answer the question correctly using multiple queries: 80%
Answer the question incorrectly: 0%

After you're sure you have the correct answer, copy the question, your answer, and the necessary SQL into a SQL file called assignment02_dml.sql. Make sure your question and answers are written as comments.

For example:

/* Question 1: How many Honda cars does the dealership have in stock?
   Answer: 27
*/
SELECT COUNT(*) FROM cars WHERE make = 'Honda';

/* Question 2: etc...
*/

Questions:

How many active students are there?
How many credits are available from the Math (MTH) department?
How many active students have a last name that starts with 'S'?
What was Tedie Cosser's course schedule in Spring of 2008?
What was the roster of students for the Operating Systems class in Fall of 2004?

Submission

To submit this homework, do the following:

Create a copy of your database using the pg_dump command:

pg_dump <the database name> > assignment02_ddl.sql