$5$G

${}^{{?}^{?}}$:$$

HW$4.$docx

FDB

$502372-3$

HW$4$

Specify the following queries in SQL on the database schema of Figure below

$($a$)$ Retrieve the names of all senior students majoring in 'COSC $($computer science$).$

$($b$)$ Retrieve the names of all courses taught by professor King in $85$ and $86.$

$($c$)$ For each section taught by professor King, retrieve the course number, semester, year, and number of students who took the section.

$($d$)$ Retrieve the name and transcript of each senior student $($Class$=5)$ majoring in COSC. Transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.

$($e$)$ Retrieve the names and major departments of all straight A students $($students who have a grade of $A$ in all their courses$).$

$($f$)$ Retrieve the names and major departments of all students who do not have any grade of $A$ in any of their courses.

STUDENT

$\backslash$table$[[$Name$,$Student$\_$number,Class,Major$],[$Smith$,17,1,$CS$],[$Brown$,8,2,$CS$]]$

COURSE

$\backslash$table$[[$Course$\_$name,Course$\_$number,Credit$\_$hours,Department$],[$Intro to Computer Science,CS$1310,4,$CS$],[$Data Structures,CS$3320,4,$CS$],[$Discrete Mathematics,MATH$2410,3,$MATH$],[$Database$,$CS$3380,3,$CS$]]$

SECTION

$\backslash$table$[[$Section$\_$identifier,Course$\_$number,Semester,Year,Instructor$],[85,$MATH$2410,$Fall,$07,$King$],[92,$CS$1310,$Fall,$07,$Anderson$],[102,$CS$3320,$Spring,$08,$Knuth$],[112,$MATH$2410,$Fall,$08,$Chang$],[119,$CS$1310,$Fall,$08,$Anderson$],[135,$CS$3380,$Fall,$08,$Stone$]]$

GRADE REPORT

$\backslash$table$[[$Student$\_$number,Section$\_$identifier,Grade$],[17,112,$B$],[17,119,$C$],[8,85,$A$],[8,92,$A$],[8,102,$B$],[8,135,$A$],[,]]$

PREREQUISITE

$\backslash$table$[[$Course$\_$number,Prerequisite$\_$number$],[$CS$3380,$CS$3320],[$CS$3380,$MATH$2410],[$CS$3320,$CS$1310]]$