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 #:

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 (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

 

 

 

Exercise 6, 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>

 

 

 

Needs help with similar assignment?

We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper

Get Answer Over WhatsApp Order Paper Now