Lab 03 - SQL Part 2

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:

All 22 Aggregates Exercises

2. The database

For this assignment, you'll reuse the database you created and populated as part of lab 02.

If you completed sections 2 and 3 of lab 02 correctly, you shouldn't need to do anything to the database to continue.

3. Use the database

Write the necessary SQL query to answer the following questions.

Points for these questions work 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

Each question shows the expected result below.

1. What is the total number of credits taken in 2014?

 total_credits
---------------
           179
(1 row)

2. What is the total number of credits taken each term in the years 2007, 2008, and 2009?

  name  | year | total_credits_taken
--------+------+---------------------
 Fall   | 2007 |                  36
 Spring | 2007 |                  50
 Summer | 2007 |                  62
 Fall   | 2008 |                  84
 Spring | 2008 |                  71
 Summer | 2008 |                  70
 Fall   | 2009 |                  58
 Spring | 2009 |                  60
 Summer | 2009 |                  51
(9 rows)

3. What is the total number of credits that Culley Shadfourth took each semester?

  name  | year | total_credits
--------+------+---------------
 Spring | 2015 |             4
 Summer | 2005 |             3
(2 rows)

4. What was the total number of students enrolled in each department in 2010?

(Be careful not to double-count students enrolled in more than one class per department)

 department | enrollment_count
------------+------------------
 CSC        |               23
 MTH        |               22
(2 rows)

5. What was the average course enrollment of the math department in 2009?

average_enrollment
--------------------
 4.2500000000000000
(1 row)

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

Put this file and your sql file from step 3 into a zip file, and upload that zip file to mySVU.

CSC 350