USE CSIS$3300$M$1$;

$--$ Drop tables if they exist $($for testing purposes, only use them if you need to drop tables.$)$

IF OBJECT$\_$ID$(\text{'}$Faculty$\text{'},\text{'}$U$\text{'})$ IS NOT NULLIF OBJECT$\_$ID$(\text{'}$Courses$\text{'},\text{'}$U$\text{'})$ IS NOT NULLIF OBJECT$\_$ID$(\text{'}$Department$\_$Heads', $\text{'}$U$\text{'})$ IS NOT NULLIF OBJECT$\_$ID$(\text{'}$Prerequisites$\text{'},\text{'}$U$\text{'})$ IS NOT NULL DROP CONSTRAINT FK$\_$Prerequisites$\_$Courses;

IF OBJECT$\_$ID$(\text{'}$Enrollment$\text{'},\text{'}$U$\text{'})$ IS NOT NULL DROP CONSTRAINT FK$\_$Enrollment$\_$Courses;

IF OBJECT$\_$ID$(\text{'}$Students$\text{'},\text{'}$U$\text{'})$ IS NOT NULLUsing NoSQL to answer these below questions INSERT INTO Faculty $($faculty$\_$id$,$ name, department, salary$)$ VALUES

$(10101,$ 'Srinivasan', 'Comp. Sci.$\text{'},65000),$

$(12121,\text{'}$Wu$\text{'},$ 'Finance', $90000),$

$(15151,$ 'Mozart', 'Music', $40000),$

$(22222,$ 'Einstein', 'Physics', $95000),$

$(32343,\text{'}$El Said', 'History', $60000),$

$(33456,$ 'Gold', 'Physics', $87000),$

$(45565,$ 'Katz', 'Comp. Sci.$\text{'},75000),$

$(58583,$ 'Califieri', 'History', $62000),$

$(76543,$ 'Singh', 'Finance', $80000),$

$(76766,$ 'Crick', 'Biology', $72000),$

$(83821,$ 'Brandt', 'Comp. Sci.$\text{'},92000),$

$(98345,$ 'Kim', 'Elec. Eng.$\text{'},80000)$; INSERT INTO Courses $($course$\_$id$,$ course$\_$name, department, credits$)$ VALUES

$(\text{'}$BIO$-101\text{'},$ 'Intro. to Biology', 'Biology', $4),$

$(\text{'}$BIO$-301\text{'},$ 'Genetics', 'Biology', $4),$

$(\text{'}$BIO$-399\text{'},$ 'Computational Biology', 'Biology', $3),$

$(\text{'}$CS$-101\text{'},$ 'Intro. to Computer Science', 'Comp. Sci.$\text{'},4),$

$(\text{'}$CS$-190\text{'},$ 'Game Design', 'Comp. Sci.$\text{'},4),$

$(\text{'}$CS$-315\text{'},$ 'Robotics', 'Comp. Sci.$\text{'},3),$

$(\text{'}$CS$-319\text{'},$ 'Image Processing', 'Comp. Sci.$\text{'},3),$

$(\text{'}$CS$-347\text{'},$ 'Database System Concepts', 'Comp. Sci.$\text{'},3),$

$(\text{'}$EE$-181\text{'},$ 'Intro. to Digital Systems', 'Elec. Eng.$\text{'},3),$

$(\text{'}$FIN$-201\text{'},$ 'Investment Banking', 'Finance', $3),$

$(\text{'}$HIS$-351\text{'},$ 'World History', 'History', $3),$

$(\text{'}$MU$-199\text{'},$ 'Music Video Production', 'Music', $3),$

$(\text{'}$PHY$-101\text{'},$ 'Physical Principles', 'Physics', $4)$; $(\text{'}$Biology$\text{'},$ 'Crick', $76766),$

$(\text{'}$Comp$.$ Sci.$\text{'},$ 'Brandt', $83821),$

$(\text{'}$Elec$.$ Eng.$\text{'},$ 'Kim', $98345),$

$(\text{'}$Finance$\text{'},\text{'}$Wu$\text{'},12121),$

$(\text{'}$History$\text{'},\text{'}$El Said', $32343),$

$(\text{'}$Music$\text{'},$ 'Mozart', $15151),$

$(\text{'}$Physics$\text{'},$ 'Einstein', $22222)$;

INSERT INTO Prerequisites $($course$\_$id$,$ prereq$\_$id$)$ VALUES

$(\text{'}$BIO$-301\text{'},$ 'BIO$-101\text{'}),$

$(\text{'}$BIO$-399\text{'},$ 'BIO$-101\text{'}),$

$(\text{'}$CS$-190\text{'},\text{'}$CS$-101\text{'}),$

$(\text{'}$CS$-315\text{'},\text{'}$CS$-101\text{'}),$

$(\text{'}$CS$-319\text{'},\text{'}$CS$-101\text{'}),$

$(\text{'}$CS$-347\text{'},\text{'}$CS$-101\text{'}),$

$(\text{'}$EE$-181\text{'},\text{'}$PHY$-101\text{'})$; INSERT INTO Students $($id$,$ name, dept$\_$name, total$\_$credit$)$ VALUES

$(1,$ 'John Doe', 'Comp. Sci.$\text{'},30),$

$(2,$ 'Jane Smith', 'Biology', $40),$

$(3,$ 'Alice Johnson', 'Physics', $35),$

$(4,$ 'Robert Brown', 'History', $20),$

$(5,$ 'Emily Wilson', 'Comp. Sci.$\text{'},25),$

$(6,$ 'Michael Clark', 'Finance', $30),$

$(7,$ 'Sarah Lewis', 'Music', $28),$

$(8,$ 'David Lee', 'Elec. Eng.$\text{'},32),$

$(9,$ 'Jessica Miller', 'History', $27),$

$(10,$ 'Ryan Martinez', 'Physics', $23)$;INSERT INTO Enrollment $($course$\_$id$,$ student$\_$id$,$ semester, year, instructor, room$\_$number, grade$)$ VALUES

$(\text{'}$BIO$-101\text{'},2,$ 'Summer', $2017,$ 'Crick', $\text{'}514\text{'},\text{'}$B$\text{'}),$

$(\text{'}$BIO$-301\text{'},1,$ 'Summer', $2018,$ 'Crick', $\text{'}514\text{'},\text{'}$A$\text{'}),$

$(\text{'}$CS$-101\text{'},1,$ 'Fall', $2017,$ 'Mozart', $\text{'}101\text{'},\text{'}$B$\text{'}),$

$(\text{'}$CS$-101\text{'},3,$ 'Spring', $2018,$ 'Mozart', $\text{'}101\text{'},\text{'}$F$\text{'}),$

$(\text{'}$CS$-190\text{'},4,$ 'Spring', $2017,$ 'Brandt', $\text{'}3128\text{'},\text{'}$A$\text{'}),$

$(\text{'}$CS$-190\text{'},6,$ 'Spring', $2017,$ 'Brandt', $\text{'}3128\text{'},\text{'}$A$\text{'}),$

$(\text{'}$CS$-315\text{'},5,$ 'Spring', $2018,$ 'Singh', $\text{'}120\text{'},\text{'}$D$\text{'}),$

$(\text{'}$CS$-319\text{'},5,$ 'Spring', $2018,$ 'Singh', $\text{'}100\text{'},\text{'}$B$\text{'}),$

$(\text{'}$CS$-319\text{'},2,$ 'Spring', $2018,$ 'Brandt', $\text{'}3128\text{'},\text{'}$A$\text{'}),$

$(\text{'}$CS$-347\text{'},1,$ 'Fall', $2017,$ 'Brandt', $\text{'}3128\text{'},\text{'}$

$--$Q$1$: Write a noSQL to list all courses taught within the same department as Professor Kim.

$--$ List Course ID$,$ Course Name, Department Name and Professor's name.

$--$Q$2$: Write a NoSQL to Calculate the total number of students enrolled in each course in Fall $2017.$

$--$ List Course Id$,$ Course Name and Total No of Enrollment.

$--$Q$3$: Write a noSQL to Identify faculty members who teach at least one course with $3$ credits

$--$ List Faculty ID$,$ Faculty Name, Course ID and Course Names.

$--$Q$4$: Write a noSQL to Find the average salary of all faculty members who are not department heads

$--$ List AVG salary only.

$--$Q$5$: Write a noSQL to List all courses with enrollment counts ordered by the number of enrolled students $($ascending order$).$

$--$ List Course Id$,$ Course Name and No of Enrollment.

$--$Q$6$: Write a noSQL to Find the names of all students who have not enrolled in any course.

$--$ List Student Id and Student Name.

$--$Q$7$: Write a noSQL to List all departments along with the total number of faculty members in each department, Order by lowest to highest.

$--$ List Dept Name and No of Faculty Members

$--$Q$8$: Write a noSQL to Identify the names of all students who are enrolled in exactly one course.

$--$ List Student ID and Name

$--$Q$9$: Write a noSQL to Retrieve the courses that have no enrollment records.

$--$ List Student ID and Name

$--$Q$10$: Write a noSQL to Identify the names of all students who have earned a $\text{'}$B$\text{'}$ grade in at least one course

$--$ List Student ID$,$ Name and Course Name.

$--$Q$11$: Write a noSQL to List all courses that have not been taught in Spring $2018.--$ List Course ID$,$ Name and Dept Name.

$--$Q$12$: Write a noSQL to Find courses that have prerequisites in the same department. $--$ List course ID$,$ course name, and prerequisite ID$.$