A. Write a SELECT statement that joins the Departments, Courses, StudentCourses, and Students tables. This statement should return these columns: DepartmentName, CourseDescription, LastName, and FirstName.
Return all courses in the English department.
Sort the result set by DepartmentName and then by CourseDescription in ascending sequence
B. Write a SELECT statement that joins the Instructors and Courses tables and returns these columns: LastName, FirstName, and CourseDescription.
Return at least one row for each instructor, even if that instructor isnt teaching any courses. Sort the result set by LastName and then by FirstName.
C. Use the UNION operator to generate a result set consisting of five columns from the Students table:
Status
FirstName LastName EnrollmentDate GraduationDate
A calculated column that contains a value of UNDERGRAD or GRADUA TED
The FirstName column The LastName column The EnrollmentDate column The GraduationDate column
If the student doesnt have a value in the GraduationDate column, the Status column should contain a value of UNDERGRAD. Otherwise, it should contain a value of GRADUATED.
Sort the final result set by EnrollmentDate.
Select * from courses Departments Instructorld 1 1 4 5 2 8 6 16 5 7 9 1 11 1 4 4 5 1 1 1 4 6 16 100 % Results i Messages CourselD CourseNumber Course Description CourseUnits 1 1 36598 Beginning Accounting 3 2 2 48926 Abstract Algebra 3 3 3 14862 Primary Education 3 4 4 54321 Anatomy 3 5 5 82754 Social Psychology 3 6 6 13524 Statistical Analysis 3 7 7 24653 Intro to Marketing 3 8 8 22679 Intro to Calculus 3 9 9 98765 Intermediate Accounting 3 10 10 96032 Social Media 3 11 11 58230 Physiology 3 12 12 81256 Intro to Management 3 13 13 64321 Secondary Education 3 14 14 32751 Business Writing 2 15 15 46972 Biology 4 16 16 15487 Music Theory 3 17 17 28177 Classic Literature 3 18 18 90908 Educational Theory 3 19 19 55783 Shakespeare 3 20 20 63284 Population and Demo... 3 21 21 74832 Creative Writing 3 22 22 33218 Marching Band 2 23 23 37645 Composition 3 24 24 84937 Microbiology 4 25 25 44386 Trigonometry 3 1 7 8 2 1 10 3 12 15 13 15 O W U W W NW U10 7 9 2 12 10 3 4 14 SQL Query15.sql - L..J3Q8DS\smitt (73))* + x Select * FROM Departments 100 % Results i Messages DepartmentID DepartmentName 1 1 Business 2 Education 3 English Mathematics 5 5 Music 6 8 Political Science 7 6 Science 8 Sociology w N Nm 4 4 5 7 SQL Query17.sql - L...J3Q8DS\smitt (51))* + x SELECT * FROM Student Courses 100 % Results i Messages StudentlD CourselD 1 5 10 : 2 12 3 5 15 4 5 21 5 1 6 o 00 00 7 7 19 8 9 6 9 9 8 10 9 10 11 10 2 10 24 11 7 12 13 14 15 11 13 11 14 16 13 1 17 13 7 18 13 12 19 15 6 20 15 8 21 15 15 22 16 2 23 16 24 24 17 7 25 17 14 26 17 25 27 18 1 18 12 28 29 30 18 25 19 3 19 11 31 32 19 17 33 20 4 34 20 16 21 7 35 36 21 12 37 21 14 38 21 22 Results StudentID Graduation Date 2019-12-14 1 1 2 2 3 2019-12-14 2019-05-07 3 4 4 2019-12-14 5 5 6 NULL 2019-12-14 2019-05-07 7 8 NULL 9 10 10 NULL NULL NULL 11 11 12 12 13 13 14 14 15 15 16 16 17 NULL 2019-12-14 NULL NULL NULL NULL NULL NULL NULL NULL 17 18 18 19 Messages LastName FirstName Howard Amber White George MacNamara Tony Welch Jonathan Taylor Donna Price Rose Rodriguez Jesse Williams Bonnie Kent Thomas Kramer Maggie DeLorean Cameron Sanchez Frank Smith Roberta Hoffman Wilma Bonwell Brian Clement Cal Patrick Charles Landry William Morrisey Monica Butler George Yount Anderson Rincon Anthony Hallowell Jimmy Flores Jesus Camden James Easton Barney Sommers Tanya Jones Andrew Jackson Floyd Geary Annette Osborne Letitia Manning Vincent Goodell Conner Griffin Gerald Gardner Faye Franks Karen Johnson Timothy Walker Andrew Cramsden Walter 19 EnrollmentDate 2015-12-18 16:44:26 2015-12-20 11:12:26 2015-12-21 09:21:55 2015-12-21 13:23:10 2015-12-28 10:32:16 2016-01-02 12:37:31 2016-01-03 13:08:37 2016-01-03 15:44:56 2016-07-15 11:14:23 2016-07-15 17:02:45 2016-07-18 12:48:43 2016-07-20 09:37:53 2016-07-22 11:18:25 2016-12-10 15:31:28 2016-12-12 14:22:53 2016-12-14 16:42:11 2016-12-22 08:43:48 2017-01-02 11:28:49 2017-01-04 10:42:06 2017-07-12 13:05:41 2017-07-18 14:21:07 2017-12-08 09:55:15 2017-12-19 13:44:25 2018-01-03 16:23:47 2018-01-04 11:12:31 2018-01-04 14:14:02 2018-07-22 15:41:12 2018-07-24 10:53:26 2018-07-25 09:27:53 2018-07-12 09:33:47 2018-12-12 17:14:22 2018-12-14 15:37:43 2019-01-02 14:21:58 2019-01-02 16:04:04 2019-07-22 08:15:57 2019-07-23 10:42:03 2019-08-04 09:01:04 2019-08-05 13:48:26 2019-12-15 10:18:37 20 20 21 21 22 22 NULL NULL 23 23 24 24 NULL NULL 25 25 26 26 NULL NULL 27 27 28 28 29 29 30 30 31 31 NULL NULL NULL NULL NULL NULL NULL 32 32 33 33 34 34 35 35 NULL NULL 36 36 37 37 NULL NULL 38 38 39 39 NULL Select * from courses Departments Instructorld 1 1 4 5 2 8 6 16 5 7 9 1 11 1 4 4 5 1 1 1 4 6 16 100 % Results i Messages CourselD CourseNumber Course Description CourseUnits 1 1 36598 Beginning Accounting 3 2 2 48926 Abstract Algebra 3 3 3 14862 Primary Education 3 4 4 54321 Anatomy 3 5 5 82754 Social Psychology 3 6 6 13524 Statistical Analysis 3 7 7 24653 Intro to Marketing 3 8 8 22679 Intro to Calculus 3 9 9 98765 Intermediate Accounting 3 10 10 96032 Social Media 3 11 11 58230 Physiology 3 12 12 81256 Intro to Management 3 13 13 64321 Secondary Education 3 14 14 32751 Business Writing 2 15 15 46972 Biology 4 16 16 15487 Music Theory 3 17 17 28177 Classic Literature 3 18 18 90908 Educational Theory 3 19 19 55783 Shakespeare 3 20 20 63284 Population and Demo... 3 21 21 74832 Creative Writing 3 22 22 33218 Marching Band 2 23 23 37645 Composition 3 24 24 84937 Microbiology 4 25 25 44386 Trigonometry 3 1 7 8 2 1 10 3 12 15 13 15 O W U W W NW U10 7 9 2 12 10 3 4 14 SQL Query15.sql - L..J3Q8DS\smitt (73))* + x Select * FROM Departments 100 % Results i Messages DepartmentID DepartmentName 1 1 Business 2 Education 3 English Mathematics 5 5 Music 6 8 Political Science 7 6 Science 8 Sociology w N Nm 4 4 5 7 SQL Query17.sql - L...J3Q8DS\smitt (51))* + x SELECT * FROM Student Courses 100 % Results i Messages StudentlD CourselD 1 5 10 : 2 12 3 5 15 4 5 21 5 1 6 o 00 00 7 7 19 8 9 6 9 9 8 10 9 10 11 10 2 10 24 11 7 12 13 14 15 11 13 11 14 16 13 1 17 13 7 18 13 12 19 15 6 20 15 8 21 15 15 22 16 2 23 16 24 24 17 7 25 17 14 26 17 25 27 18 1 18 12 28 29 30 18 25 19 3 19 11 31 32 19 17 33 20 4 34 20 16 21 7 35 36 21 12 37 21 14 38 21 22 Results StudentID Graduation Date 2019-12-14 1 1 2 2 3 2019-12-14 2019-05-07 3 4 4 2019-12-14 5 5 6 NULL 2019-12-14 2019-05-07 7 8 NULL 9 10 10 NULL NULL NULL 11 11 12 12 13 13 14 14 15 15 16 16 17 NULL 2019-12-14 NULL NULL NULL NULL NULL NULL NULL NULL 17 18 18 19 Messages LastName FirstName Howard Amber White George MacNamara Tony Welch Jonathan Taylor Donna Price Rose Rodriguez Jesse Williams Bonnie Kent Thomas Kramer Maggie DeLorean Cameron Sanchez Frank Smith Roberta Hoffman Wilma Bonwell Brian Clement Cal Patrick Charles Landry William Morrisey Monica Butler George Yount Anderson Rincon Anthony Hallowell Jimmy Flores Jesus Camden James Easton Barney Sommers Tanya Jones Andrew Jackson Floyd Geary Annette Osborne Letitia Manning Vincent Goodell Conner Griffin Gerald Gardner Faye Franks Karen Johnson Timothy Walker Andrew Cramsden Walter 19 EnrollmentDate 2015-12-18 16:44:26 2015-12-20 11:12:26 2015-12-21 09:21:55 2015-12-21 13:23:10 2015-12-28 10:32:16 2016-01-02 12:37:31 2016-01-03 13:08:37 2016-01-03 15:44:56 2016-07-15 11:14:23 2016-07-15 17:02:45 2016-07-18 12:48:43 2016-07-20 09:37:53 2016-07-22 11:18:25 2016-12-10 15:31:28 2016-12-12 14:22:53 2016-12-14 16:42:11 2016-12-22 08:43:48 2017-01-02 11:28:49 2017-01-04 10:42:06 2017-07-12 13:05:41 2017-07-18 14:21:07 2017-12-08 09:55:15 2017-12-19 13:44:25 2018-01-03 16:23:47 2018-01-04 11:12:31 2018-01-04 14:14:02 2018-07-22 15:41:12 2018-07-24 10:53:26 2018-07-25 09:27:53 2018-07-12 09:33:47 2018-12-12 17:14:22 2018-12-14 15:37:43 2019-01-02 14:21:58 2019-01-02 16:04:04 2019-07-22 08:15:57 2019-07-23 10:42:03 2019-08-04 09:01:04 2019-08-05 13:48:26 2019-12-15 10:18:37 20 20 21 21 22 22 NULL NULL 23 23 24 24 NULL NULL 25 25 26 26 NULL NULL 27 27 28 28 29 29 30 30 31 31 NULL NULL NULL NULL NULL NULL NULL 32 32 33 33 34 34 35 35 NULL NULL 36 36 37 37 NULL NULL 38 38 39 39 NULL
