$1$

$.$

$$Create a database called "abc"

$2$

$.$

$$Use "abc" database to create all

$6$

$$tables$.$ Provide SQL queries to

create all tables.

$3$

$.$

$$Insert all samples values into the tables.

Questions : Write SQL queries and their corresponding Relational Algebra

$($

clearly state the subscript and superscript

$)$

Tables:

$1$

$.$

$$List all courses taught by Professor Taylor.

$2$

$.$

$$Find the total number of students enrolled in each course

$3$

$.$

$$List the names of all faculty members who teach at least one course with

$4$

$$credits$.$

$4$

$.$

$$Find the average salary of all faculty members who are department heads.

$5$

$.$

$$List all courses with enrollment counts ordered by the number of enrolled

students

$($

descending order

$)$

$.$

$6$

$.$

$$Find the names of all faculty members who have never taught any course

$7$

$.$

$$List all courses along with their departments and the number of students

enrolled in each course in Spring

$2$

$0$

$1$

$8$

$.$

$8$

$.$

Find the names of all students who have not enrolled in any course

$9$

$.$

List all departments along with the total number of faculty members in

each department.

$1$

$0$

$.$

$$Find the names of all students who are enrolled in more than one course.

$1$

$1$

$.$

$$List all courses that have not been taught yet.

$1$

$2$

$.$

$$Find the names of all faculty members who teach courses with more than

$3$

$$credits

$1$

$3$

$.$

$$Find the average salary of faculty members in each department.

$1$

$4$

$.$

$$List all courses along with the number of students enrolled in each course taught by Professor Watson.

$1$

$5$

$.$

$$Find the names of all students who have earned an

$$

A

$$

$$grade in at least one course. Table $6$: Faculty$\_$Teaching Table

$\backslash$table$[[$faculty$\_$id$,$course$\_$id$,$section,semester,year$],[10101,$CS$-101,1,$Fall,$2017],[10101,$CS$-315,1,$Spring,$2018],[10101,$CS$-347,1,$Fall,$2017],[12121,$FIN$-201,1,$Spring,$2018],[15151,$MU$-199,1,$Spring,$2018],[22222,$PHY$-101,1,$Fall,$2017],[32343,$HIS$-351,1,$Spring,$2018],[45565,$CS$-101,1,$Spring,$2018],[45565,$CS$-319,1,$Spring,$2018],[76766,$BIO$-101,1,$Summer,$2017],[76766,$BIO$-301,1,$Summer,$2018],[83821,$CS$-190,1,$Spring,$2017],[83821,$CS$-190,2,$Spring,$2017],[83821,$CS$-319,2,$Spring,$2018],[98345,$EE$-181,1,$Spring,$2017]]$ Table $3$: Department$\_$Heads Table

$\backslash$table$[[$department$,$head$\_\_$name,salary$],[$Biology$,$Watson,$90000],[$Comp$.$ Sci.,Taylor,$100000],[$Elec$.$ Eng.,Taylor,$85000],[$Finance$,$Painter,$120000],[$History$,$Painter,$50000],[$Music$,$Packard,$80000],[$Physics$,$Watson,$70000]]$

Table $4$: Prerequisites Table

$\begin{array}{cc}cours{e}_{i}d& prere{q}_{i}d\\ \text{B}\text{I}\text{O}-301& \text{B}\text{I}\text{O}-101\\ \text{B}\text{I}\text{O}-399& \text{B}\text{I}\text{O}-101\\ CS-190& CS-101\\ CS-315& CS-101\\ CS-319& CS-101\\ CS-347& CS-101\\ EE-181& PHY-101\end{array}$

Table $5$: Enrollment Table

$\backslash$table$[[$course$\_$id$,$student$\_$id$,$semester,year,instructor,room$\_\_$number,grade$],[$BIO$-101,1,$Summer,$2017,$Painter,$514,$B$],[$BIO$-301,1,$Summer,$2018,$Painter,$514,$A$],[$CS$-101,1,$Fall,$2017,$Packard,$101,$H$],[$CS$-101,1,$Spring,$2018,$Packard,$101,$F$],[$CS$-190,1,$Spring,$2017,$Taylor,$3128,$E$],[$CS$-190,2,$Spring,$2017,$Taylor,$3128,$A$],[$CS$-315,1,$Spring,$2018,$Watson,$120,$D$],[$CS$-319,1,$Spring,$2018,$Watson,$100,$B$],[$CS$-319,2,$Spring,$2018,$Taylor,$3128,4],[$CS$-347,1,$Fall,$2017,$Taylor,$3128,$A$],[$EE$-181,1,$Spring,$2017,$Taylor,$3128,4],[$FIN$-201,1,$Spring,$2018,$Packard,$101,$B$],[$HIS$-351,1,$Spring,$2018,$Painter,$514,$C$],[$MU$-199,1,$Spring,$2018,$Packard,$101,$D$],[$PHY$-101,1,$Fall,$2017,$Watson,$100,$A$]]$ Tables:

Table $1$: Faculty Table

Table $2$: Courses Table