Write an SQL select that would return a transcript data for a student. Test your query for student 3729. The transcript should be in chronological order and should include course id, name, semester, grade, units. Note that the values of semester do not sort in correct order. The correct chronological order would be: 2017 Spring, 2017 Summer, 2017 Fall , 2018 Spring, etc. . In your answer include the SQL statement used, and a screenshot showing the transcript list of courses and grade taken by student 3729 in the correct sequence. You will probably need to use a case function to calculate a value that can be sorted to get data in the correct sequence.

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 STUDENT VALUES (5430,'Last0','First0',2.17,'MATH',29); INSERT INTO STUDENT VALUES (1287,'Last1','First1',3.1,'MATH',29); INSERT INTO STUDENT VALUES (1970,'Last2','First2',3.62,'MATH',29); INSERT INTO STUDENT VALUES (2928,'Last3','First3',2.21,'MATH',29); INSERT INTO STUDENT VALUES (5209,'Last4','First4',2.14,'MATH',29); INSERT INTO STUDENT VALUES (104,'Last5','First5',2.59,'MATH',29); INSERT INTO STUDENT VALUES (7460,'Last6','First6',3.66,'MATH',29); INSERT INTO STUDENT VALUES (6848,'Last7','First7',3.03,'MATH',29); INSERT INTO STUDENT VALUES (4456,'Last8','First8',2.66,'MATH',29); INSERT INTO STUDENT VALUES (3729,'Last9','First9',2.28,'MATH',29); INSERT INTO COURSE VALUES (40123,'Calculus 1','1','MATH','Smith'); INSERT INTO COURSE VALUES (40125,'Calculus 1','2','MATH','Lupin'); 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 (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 (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); INSERT INTO ENROLLMENT VALUES (565,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (565,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (6240,40126,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (6240,40130,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (557,40125,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (557,40130,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (557,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (557,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (557,60200,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (557,40500,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (557,40501,'Fall2016','B',1); INSERT INTO ENROLLMENT VALUES (557,50100,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (557,50110,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (4263,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (4263,40130,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (4263,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (4263,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (4263,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (4263,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (4263,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (4263,50100,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (4263,50110,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (9524,40126,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (9524,40130,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (9524,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (9524,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (9524,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (9524,40500,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (9524,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (9524,50100,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (9524,50110,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (8843,40123,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (8843,40130,'Spring2017','D',4); INSERT INTO ENROLLMENT VALUES (8843,50520,'Fall2016','B',3); INSERT INTO ENROLLMENT VALUES (8843,50530,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (8843,60200,'Spring2017','D',3); INSERT INTO ENROLLMENT VALUES (8843,40500,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (8843,40501,'Fall2016','D',1); INSERT INTO ENROLLMENT VALUES (8843,50100,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (8843,50110,'Spring2017','B',4); INSERT INTO ENROLLMENT VALUES (4914,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (4914,40130,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (4914,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (4914,50530,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (4914,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (4914,40500,'Fall2016','B',4); INSERT INTO ENROLLMENT VALUES (4914,40501,'Fall2016','D',1); INSERT INTO ENROLLMENT VALUES (4914,50100,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (4914,50110,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (7262,40125,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (7262,40130,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (7262,50520,'Fall2016','C',3); INSERT INTO ENROLLMENT VALUES (7262,50530,'Spring2017','C',3); INSERT INTO ENROLLMENT VALUES (7262,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (7262,40500,'Fall2016','C',4); INSERT INTO ENROLLMENT VALUES (7262,40501,'Fall2016','A',1); INSERT INTO ENROLLMENT VALUES (7262,50100,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (7262,50110,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (8113,40123,'Fall2016','A',4); INSERT INTO ENROLLMENT VALUES (8113,40130,'Spring2017','A',4); INSERT INTO ENROLLMENT VALUES (8113,50520,'Fall2016','A',3); INSERT INTO ENROLLMENT VALUES (8113,50530,'Spring2017','B',3); INSERT INTO ENROLLMENT VALUES (8113,60200,'Spring2017','A',3); INSERT INTO ENROLLMENT VALUES (8113,40500,'Fall2016','A',4);