Instructions:Put your SQL in the "Problem 5"


Put your SQL in the "Problem 5" location in the SQL template.

Put your row count in the green box in cell D5.

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

Need customized help? Order now
user img


12-09-20 | 11:23:58

use university;
select concat(CourseDept,' ',CourseNumber) as course,CourseDesc,
concat(stdFirstName,' ',stdLastName) as student_name,OffTerm,Format,
concat(FacFirstName,' ',FacLastName) as professor_name
from course c, enrollment e, faculty f, offering o, student s
where s.idstudent = e.Student_idStudent
and o.idOffering=e.Offering_idOffering and
c.idcourse=o.Course_idCourse and
OffTerm='Spring'and FacFirstName='Victoria'
order by course asc, concat(stdLastName,' ',stdFirstName) asc;

Related Question