(

/2

marks) Give the SQL query to list all the section information.\ ( /2 marks) Give the SQL statement to list all the student names with their department and department building. Sort the results in ascending order of department building, and then by descending order department name.\ ( /2 marks) Give the SQL statement to list only the student names, where the student received a C or less in the year 2017. (Hint: lexicographical order is C, and then

C_(-)

, and finally

C_(+)

, so be careful about the

C+

in yourlogic. Furthermore, do not repeat any student names in the result - this adds no value for the business question).\ ( /2 marks) Give the SQL statement to show the student names that have received between an Aand

A^(+)

. (Hint: lexicographical order is

A

, and then

A-

, and finally

A+

, so be careful about your logic. Note that the grade point order of letter grades (

A+,A,A-,B+,B,B-,C+,C,C-,D+,D,D-

, and

F

is different than the default lexicographic order (

A,A-,A+,B,B-,B+,C,C-,C+,D,D-,D+.F

.\ Furthermore, do not repeat any student names in the result - this adds no value for the business question).\ ( /6 marks) Give the SQL statement to list all the students that have been taught by two or more instructors, listing all the (student name, instructor count) pairs that meet the criteria. (Hint: use the WITH clause to break the problem down).\ (

/2

marks) Give the SQL statement to show all student names whose name ends in a vowel ('a', '

e

', '

i

', '

o

', '

u

'), or '

y

'. (Hint: if there are two or more students with an identical name, you should only list the student name once).\ (/4 marks) Give the SQL statement to show the set of course IDs that were offered both in 2017 and 2018.\ ( /4 marks) Give the SQL statement to show the course titles for the course IDs that were offered in 2017, but not offered in 2018. (Hint: be careful to use course IDs when doing the set membership test, which is not subject to change like course title.)

2. ( /2 marks) Give the SQL statement to list all the student names with their department and department building. Sort the results in ascending order of department building, and then by descending order department name. 3. ( /2 marks) Give the SQL statement to list only the student names, where the student received a C or less in the year 2017. (Hint: lexicographical order is C, and then C, and finally C+, so be careful about the C+ in yourlogic. Furthermore, do not repeat any student names in the result - this adds no value for the business question). 4. ( /2 marks) Give the SQL statement to show the student names that have received between an Aand A+. (Hint: lexicographical order is A, and then A, and finally A+, so be careful about your logic. Note that the grade point order of letter grades ( A+,A,A,B+,B,B,C+,C,C,D+,D,D, and F ) is different than the default lexicographic order ( A,A,A+,B,B,B+,C,C,C+,D,D,D+.F). Furthermore, do not repeat any student names in the result - this adds no value for the business question). 5. ( /6 marks) Give the SQL statement to list all the students that have been taught by two or more instructors, listing all the (student name, instructor count) pairs that meet the criteria. (Hint: use the WITH clause to break the problem down). 6. ( /2 marks) Give the SQL statement to show all student names whose name ends in a vowel ('a', ' e ', ' i ', ' o ', ' u '), or ' y '. (Hint: if there are two or more students with an identical name, you should only list the student name once). 7. (/4 marks) Give the SQL statement to show the set of course IDs that were offered both in 2017 and 2018. 8. ( /4 marks) Give the SQL statement to show the course titles for the course IDs that were offered in 2017, but not offered in 2018. (Hint: be careful to use course IDs when doing the set membership test, which is not subject to change like course title.)