$1.$ Create a database called "ASG$1"$

$2.$ Use "ASG$1"$ to create $6$ tables. Provide SQL queries to create all tables. Make sure you define he relations $($primary keys, foreign keys and other relations$)$ corectly.

$3.$ Insert all sample values in to the tables.

For SQL queries: Answer with SQL qUERIES & relational algebra

$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 $2018.$

$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.

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

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

$12.$ Find the names of all faculty members who teach courses with more than $3$ credits.

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

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

$15.$ Find the names of all students who have earned an $$A$$ grade in at least one course.

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 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]]1.$Create Database called "ASG$1"$

$2.$ Use "ASG$1"$ DB to create $6$ tables, provide SQL queries to create tables $.$ Make sure you define the relations $($primary keys, foreign keys, or any other relations$)$ correctly.

$3.$Insert all samples values into the tables.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