drop schema if exists ex1 ;

create schema ex1;

use ex1;

create table student( studentid int primary key, lastname char(25), firstname char(25), gpa double, major char(25), totalcredits int); create table course( courseid int primary key, name char(25), section char(4), department char(25), instructor char(25)); create table enrollment( studentid int, courseid int, semester char(25), grade char(5), units int); INSERT INTO COURSE VALUES (40123,'Calculus 1','1','MATH','Smith'); INSERT INTO COURSE VALUES (40125,'Calculus 1','2','MATH','Lupin'); INSERT INTO COURSE VALUES (40126,'Calculus 1','3','MATH','Fenwick'); INSERT INTO COURSE VALUES (40130,'Calculus 2','1','MATH','Strong'); INSERT INTO COURSE VALUES (40140,'Intro Prog','1','CS','Suesse'); INSERT INTO COURSE VALUES (40240,'Data Structures','1','CS','Suesse'); INSERT INTO COURSE VALUES (40500,'Cell Biology','1','Biology','Lynn'); INSERT INTO COURSE VALUES (40501,'Cell Biology Lab','1','Biology','Lynn'); INSERT INTO COURSE VALUES (40502,'Ecology','1','Biology','Finne'); INSERT INTO COURSE VALUES (40503,'Ecology Lab','1','Biology','Dimmer'); INSERT INTO COURSE VALUES (50100,'Chemistry 1','1','Chemistry','Lyon'); INSERT INTO COURSE VALUES (50110,'Chemistry 2','1','Chemistry','Lyon'); INSERT INTO COURSE VALUES (50500,'Politics','1','Social Science','Hanson'); INSERT INTO COURSE VALUES (50510,'Economics','1','Social Science','Snivley'); INSERT INTO COURSE VALUES (50520,'Technology & Society','1','Social Science','Lyon'); INSERT INTO COURSE VALUES (50530,'Communication','1','HCOM','Patrick'); INSERT INTO COURSE VALUES (50540,'Philosopy & Ethics','1','Social Science','Patrick'); INSERT INTO COURSE VALUES (60200,'Health and Well Being','1','Kinesioloy','Ford'); INSERT INTO COURSE VALUES (40132,'Discrete Math','1','MATH','Lupin'); INSERT INTO COURSE VALUES (40128,'Geometry','1','MATH','Fenwick'); INSERT INTO COURSE VALUES (50112,'Bio-Chemistry','1','Chemistry','Lyon'); INSERT INTO ENROLLMENT VALUES (5430,40123,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (5430,40130,'Spring2016','D',4); INSERT INTO ENROLLMENT VALUES (5430,40132,'Spring2017','D',4); INSERT INTO ENROLLMENT VALUES (5430,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (5430,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (5430,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (5430,60200,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (5430,40500,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (5430,40501,'Fall2016','C',1); INSERT INTO ENROLLMENT VALUES (1287,40125,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (1287,40130,'Spring2016','C',4); INSERT INTO ENROLLMENT VALUES (1287,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (1287,50510,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (1287,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (1287,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (1287,60200,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (1287,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (1287,40501,'Fall2016','C',1); INSERT INTO ENROLLMENT VALUES (1970,40126,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (1970,40130,'Spring2016','A',4); INSERT INTO ENROLLMENT VALUES (1970,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (1970,50510,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (1970,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (1970,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (1970,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (1970,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (1970,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (2928,40123,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (2928,40130,'Spring2016','D',4); INSERT INTO ENROLLMENT VALUES (2928,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (2928,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (2928,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (2928,50530,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (2928,60200,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (2928,40500,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (2928,40501,'Fall2016','B',1); INSERT INTO ENROLLMENT VALUES (5209,40125,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (5209,40130,'Spring2016','D',4); INSERT INTO ENROLLMENT VALUES (5209,40132,'Spring2017','D',4); INSERT INTO ENROLLMENT VALUES (5209,50510,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (5209,50520,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (5209,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (5209,60200,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (5209,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (5209,40501,'Fall2016','B',1); INSERT INTO ENROLLMENT VALUES (104,40123,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (104,40130,'Spring2016','A',4); INSERT INTO ENROLLMENT VALUES (104,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (104,50510,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (104,50520,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (104,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (104,60200,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (104,40500,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (104,40501,'Fall2016','C',1); INSERT INTO ENROLLMENT VALUES (7460,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (7460,40130,'Spring2016','B',4); INSERT INTO ENROLLMENT VALUES (7460,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (7460,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (7460,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (7460,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (7460,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (7460,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (7460,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (6848,40125,'Fall2016','D',4); INSERT INTO ENROLLMENT VALUES (6848,40130,'Spring2016','B',4); INSERT INTO ENROLLMENT VALUES (6848,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (6848,50510,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (6848,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (6848,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (6848,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (6848,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (6848,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (4456,40123,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (4456,40130,'Spring2016','C',4); INSERT INTO ENROLLMENT VALUES (4456,40132,'Spring2017','C',4); INSERT INTO ENROLLMENT VALUES (4456,50510,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (4456,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (4456,50530,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (4456,60200,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (4456,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (4456,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (3729,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (3729,40130,'Spring2016','D',4); INSERT INTO ENROLLMENT VALUES (3729,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (3729,50510,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (3729,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (3729,50530,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (3729,60200,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (3729,40500,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (3729,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (6324,40126,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (6324,40140,'Fall2016','D',4); INSERT INTO ENROLLMENT VALUES (6324,40240,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (6324,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (6324,50510,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (6324,50520,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (6324,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (6324,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (4106,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (4106,40140,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (4106,40240,'Spring2017','C',4); INSERT INTO ENROLLMENT VALUES (4106,40132,'Spring2017','D',4); INSERT INTO ENROLLMENT VALUES (4106,50510,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (4106,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (4106,50530,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (4106,60200,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (3214,40123,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (3214,40140,'Fall2016','D',4); INSERT INTO ENROLLMENT VALUES (3214,40240,'Spring2017','C',4); INSERT INTO ENROLLMENT VALUES (3214,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (3214,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (3214,50520,'Fall2016','D',3); INSERT INTO ENROLLMENT VALUES (3214,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (3214,60200,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (535,40123,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (535,40140,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (535,40240,'Spring2017','D',4); INSERT INTO ENROLLMENT VALUES (535,40132,'Spring2017','D',4); INSERT INTO ENROLLMENT VALUES (535,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (535,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (535,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (535,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (7830,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (7830,40140,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (7830,40240,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (7830,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (7830,50510,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (7830,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (7830,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (7830,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (9179,40126,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (9179,40140,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (9179,40240,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (9179,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (9179,50510,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (9179,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (9179,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (9179,60200,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (8265,40125,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (8265,40140,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (8265,40240,'Spring2017','C',4); INSERT INTO ENROLLMENT VALUES (8265,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (8265,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (8265,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (8265,50530,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (8265,60200,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (9563,40123,'Fall2016','D',4); INSERT INTO ENROLLMENT VALUES (9563,40140,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (9563,40240,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (9563,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (9563,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (9563,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (9563,50530,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (9563,60200,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (8013,40126,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (8013,40140,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (8013,40240,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (8013,40132,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (8013,50510,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (8013,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (8013,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (8013,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (565,40126,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (565,40140,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (565,40240,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (565,40132,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (565,50510,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (565,50520,'Fall2016','A',3);

There are 3 professors who teach Calculus 1: Smith, Lupin and Fenwick. We want to compare the average Calculus 2 grade of students based on their teacher in Calculus 1. If a student took Calculus 1 but did not take Calculus 2 don't count them. For example; the output below would show that 10 of Smith's calculus 1 students continued on to calculus 2 and their average grade in calculus 2 is 2.8. Fenwick had 25 students in calculus 1 and none of them went on to calculus 2. Lupin had 30 students in his calc1 class continue on to calc2 where they are received a 3.5 average grade. The data shown below is an example. Your results may be different from what is shown below. InstructorCaculus1 NumberTaking Calculus2 AverageGradeCalculus2 Smith 10 2.8 Lupin 30 3.5 Fenwick 25 0 You will need to use the SQL case function to convert letter grades into numbers that can be averaged. A letter grade of B should be 3.0 and a letter grade of C should be 2.0, etc. There are 3 professors who teach Calculus 1: Smith, Lupin and Fenwick. We want to compare the average Calculus 2 grade of students based on their teacher in Calculus 1. If a student took Calculus 1 but did not take Calculus 2 don't count them. For example; the output below would show that 10 of Smith's calculus 1 students continued on to calculus 2 and their average grade in calculus 2 is 2.8. Fenwick had 25 students in calculus 1 and none of them went on to calculus 2. Lupin had 30 students in his calc1 class continue on to calc2 where they are received a 3.5 average grade. The data shown below is an example. Your results may be different from what is shown below. InstructorCaculus1 NumberTaking Calculus2 AverageGradeCalculus2 Smith 10 2.8 Lupin 30 3.5 Fenwick 25 0 You will need to use the SQL case function to convert letter grades into numbers that can be averaged. A letter grade of B should be 3.0 and a letter grade of C should be 2.0, etc