SQL School

UMGC IFSM 330 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 #:

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 (10 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 (15 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 (15 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 indication he did not teach anything	Some indication he did not teach anything	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 (20 points):

You want to see how your various students are doing, GPA-wise. Create a report which will give the student’s last name, student’s first name (should be concatenated into a single field as in the example), the student’s major, and the student’s average GPA.

Include only students who have actually taken at least one class (if they took it and earned 0 GPA points indicating they failed it, that record should be included.) Do not include a student who has not taken any classes.

The average GPA is the average of the GPA Points column in the enrollment table; display this to four or more decimal places. Sort alphabetically by “Last, First” name combination.

Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.

For example, if your input data is like this: (note the in the last row – indicates student Tyrone Brown is new and has not taken any classes yet, has only declared a major.)

First Name	Last Name	Major	Semester	Class	GPA Points
Arianna	Huffington	LSA	Fall	Art History	4
Blanca	Washington	FIN	Spring	Calculus	3
Blanca	Washington	FIN	Fall	English	4
Blanca	Washington	FIN	Summer	Databases	3
Charles	Lee	IS	Summer	Databases	0
Deniz	Lee	IS	Fall	Databases	4
Deniz	Lee	IS	Summer	Calculus II	0
Tyrone	Brown	FIN	–	–	–

Your results should look something like this:
Student Name	Major	Average GPA
Lee, Charles	IS	0
Lee, Deniz	IS	2
Huffington, Arianna	LSA	4
Washington, Blanca	FIN	3.3333

Exercise 4, Your Answer:

1. ~~How many rows did your query return?~~ There are many rows, so you do not need to count them manually, but please be sure your first 15 rows are legible when you post your screenshot.

<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 5 (20 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 5, 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 6 (20 points):

Start with the same problem statement as in Problem 5, except this time generate a summary report. For each course name, list the count of the students who will need to be contacted for each format. Sort your report so it’s alphabetically sorted on Course Name, then by format (online or in person). A sample output is below (your data may vary.)

Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.

Course Name	Course Description	How many students	Format	Professor (ok to concatenate; also ok to leave as two separate fields)
FIN 200	Introduction to Finance	10	In person	Victoria Emmerline
FIN 200	Introduction to Finance	20	Online	Victoria Emmerline
GEO 100	Geography for Lawyers	3	In person	Victoria Emmerline
GEO 100	Geography for Lawyers	7	Online	Victoria Emmerline