Homework answers / question archive / Assignment SQL University This assignment provides additional practice with SQL queries

Assignment SQL University This assignment provides additional practice with SQL queries

Computer Science

Share With

---

Assignment SQL University

This assignment provides additional practice with SQL queries.  The assignment assumes you have prepared the SQLiteonline.com environment (see the Setting_Up_SQLiteonline_com.docx for instructions). 

 

Your basic study trajectory here will be:

•             Work through this document and write SQL to answer all the questions listed below.  As part of your SQL, you will capture screenshots and paste them where indicated below.

•             There is NO Quiz component to this assignment.  You will simply upload this document with your SQL code and screenshots. 

•             Each exercise requires that you provide

1.            the number of rows that your query returned

2.            executable SQL code

3.            and at least one legible screenshot (include more than one, as needed).

 

 

Setup:  You will need visit Sqliteonline.com and run the two attached scripts to create your "University" database.  First use the ‘create’ script, then the ‘insert’ script. It should have tables in it for courses, enrollments, faculty, offerings, and students. 

Once you have created and inserted the data into the database, complete the following exercises.

 

OPTIONAL: To confirm your database is working correctly, you can run the following SQL statements on SQLiteonline.com (note: nothing to turn in here; this is just to help verify your database works):

 

select count(course.CourseDesc) from course;

 

# The above should return a count of 11 if working correctly.

 

select sum(course.CourseUnits) from course;

 

# The above should return a count of 34 if working correctly.

 

 

 

Example Question and Response:

 

Create SQL code to display all fields from the Course table.

 

Your Answer:

 

 

1. How many rows did your query return?

                Enter #:

11

 

SELECT * FROM course;

 

 

 

 

<3. Paste an additional Screenshot here, as needed, making sure the entire screenshot is viewable>

<Add additional rows below, as needed>

 

 

Exercise 1 (20 points):

 

Create SQL code to display all fields from the students table.  Include only students whose major is Finance (‘FIN’) and who have an in-state residence.  Sort it alphabetically by student first name.

 

Exercise 1, Your Answer:

 

 

1. How many rows did your query return?

                Enter #:

 

 

<2. Paste/Enter SQL Code here, making sure the entire SQL code is viewable>

 

 

<3. Paste Screenshot here, making sure the entire screenshot is viewable>

 

 

<3. Paste an additional Screenshot here, as needed, making sure the entire screenshot is viewable>

<Add additional rows below, as needed>

 

 

Exercise 2 (20 points):

 

Write SQL which will give us the course id number (idCourse field), course description, the number of units, the offering term, whether it’s online or in person, and the capacity.  Include only those courses which are more than 3 units and have a capacity of less than 40 students.  Sort it ascending by idCourse.

 

Exercise 2, Your Answer:

 

 

1. How many rows did your query return?

                Enter #:

 

 

<2. Paste/Enter SQL Code here, making sure the entire SQL code is viewable>

 

 

<3. Paste Screenshot here, making sure the entire screenshot is viewable>

 

 

<3. Paste an additional Screenshot here, as needed, making sure the entire screenshot is viewable>

<Add additional rows below, as needed>

 

 

 

Exercise 3 (30 points):

 

Some faculty are grumbling that certain professors have an easy teaching load, defined as only a few courses with low capacity, while others have a heavy load of many courses with high seat counts.  Run a query to help answer this question.

 

Your output should contain the faculty ID, faculty name (first and last – it’s fine to have this as two separate fields, also fine to concatenate together), the largest capacity class the faculty member is assigned to teach, the maximum possible total student load (sum of all the course capacities), and the number of classes the faculty member is teaching.

 

Include all faculty members, even if they are not assigned to teach any courses.  Include courses only if they are taught by a faculty member.  Sort your output ascending by the number of courses taught, with the professors with the fewest courses at the top.

 

For example, if you had the following input data (the blank row at the bottom means Professor Poldark did not teach anything):

 

Faculty ID            Faculty Name    Term     Class      Class capacity

1              Michael Mouse Fall         Ears for the Novice          50

1              Michael Mouse Spring   Ears for the Novice          20

1              Michael Mouse Summer               Comedic Voices                10

1              Michael Mouse Summer               Calculus II            20

2              Nancy Drew       Fall         Calculus I             30

2              Nancy Drew       Spring   Calculus I             10

3              Ross Poldark      -              -              -          

                                                               

Your desired output would be something like this:                                                          

Faculty ID            Faculty Name    Max capacity      Sum of capacity How many classes

3              Ross Poldark      Some indica-tion he did not teach anything         Some indication he did not teach any-thing                Some indication he did not teach anything

2              Nancy Drew       30           40           2

1              Michael Mouse 50           100         4

 

 

 

 

 

Exercise 3, Your Answer:

 

 

1. How many rows did your query return?

                Enter #:

 

 

<2. Paste/Enter SQL Code here, making sure the entire SQL code is viewable>

 

 

<3. Paste Screenshot here, making sure the entire screenshot is viewable>

 

 

<3. Paste an additional Screenshot here, as needed, making sure the entire screenshot is viewable>

<Add additional rows below, as needed>

 

 

 

 

Exercise 4 (30 points):

 

Professor Victoria Emmerline is set to retire immediately before spring semester.  You want to generate a list of all students who are currently enrolled in any class she is teaching this spring, so you can reach out to them and let them know there will be a different professor.  Assume each class she teaches could be assigned to a different professor – i.e. if Professor Emmerline was set to teach Calculus I and Calculus II, it’s possible Calculus I will be assigned to Professor X and Calculus II will be assigned to Professor Y.

 

Generate SQL code to make a report which will do the following

•             Generate a concatenated class name/number string, such as “FIN 200” from something in the Finance Department where the course number is 200

•             Print that class name/number string in the first column of your report

•             Print the class description (such as “Introduction to Finance”)

•             List the student’s first name and then last name

•             List the offering term and the format (online or in person)

•             List the professor’s first name and last name

•             Sort your output ascending by the class name/number string, so “FIN 200”, “FIN 120” and “ART 100” would be sorted in the following order:  “ART 100”, “FIN 120”, “FIN 200.”

•             Within the same class, further sort your output so it’s alphabetical by student last name, student first name

 

Exercise 4, Your Answer:

 

 

1. How many rows did your query return?

                Enter #:

 

 

<2. Paste/Enter SQL Code here, making sure the entire SQL code is viewable>

 

 

<3. Paste Screenshot here, making sure the entire screenshot is viewable>

 

 

<3. Paste an additional Screenshot here, as needed, making sure the entire screenshot is viewable>

<Add additional rows below, as needed>