INF 282 – Sp 2017 SQL Assignment

The flat table of data in the spreadsheet contains data that needs to be converted into a database.  As we learned in Chapter 1, storing data in such a manner has disadvantages, including data redundancy that eventually leads to data anomalies.

As we have seen in Chapter 3, Codd’s solution with a relational database reduces redundancy by controlling it.  Separate tables are created which are linked together by primary and foreign keys.  Understanding how tables are joined back together and queried in SQL is a primary goal of our class.

The data is based upon one of the questions in the Chapter 2 and Chapter 4 homework.

Based upon the final ERD solution above and the data that is available to you in the spreadsheet, create a multiple table database to store this information correctly.  You will have to choose and create PKs and FKs as needed.  Ignore the Class_Time and Student_Address fields.

Include all DDL-related code and write the SQL code to answer the following questions.

For each query, Include 1) your prediction of the output (rows, columns, data) 2) the source code 3) output from the Oracle server.

1. Write a query that displays all information from all the tables joined together

2. Write a query that shows the student names and grades for students in the Database class

3. Write a query that shows the class name and calculates the average grade of all the students in each class

4. Write a query that shows the student(s) name and grades that belong on the Dean’s List (have an overall average grade of > 90)

5. Write a query that shows each class name, and counts the number of students in each class (use the COUNT function)

Leave a Reply

Your email address will not be published. Required fields are marked *