Part $2$: Database Creation and SQL Queries $(90$ marks$)$

In this part of the assignment, you will create a database and perform a series of tasks to test your understanding of SQL$.$ Each task has specific marks assigned to it

Task $1$: Create a Database and Tables $(20$ marks$)$

$1.$ Create a new database named university. $(5$ marks$)$

$2.$ Create the following tables based on the schema:

o Student table:

$$ ID $($Primary Key$)$

$$ name

$$ dept$\_$name

$$ tot$\_$cred

o Course table:

$$ course$\_$id $($Primary Key$)$

$$ title

$$ dept$\_$name

$$ credits

$(5$ marks for each table$)$

Task $2$: Insert Data into Tables $(20$ marks$)$

$1.$ Insert $5$ rows of data into the student table. $(10$ marks$)$ Example:

$($ID$,$ name, dept$\_$name, tot$\_$cred$)$

$(101,$ 'Alice', 'Comp. Sci.$\text{'},30),$

$(102,$ 'Bob', 'Physics', $40),$

$(103,$ 'Carol', 'Mathematics', $25),$

$(104,$ 'Dave', 'Biology', $20),$

$(105,$ 'Eve', 'Chemistry', $35)$;

$2.$ Insert $5$ rows of data into the course table. $(10$ marks$)$ Example:

$($course$\_$id$,$ title, dept$\_$name, credits$)$

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

$(\text{'}$PH$101\text{'},$ 'Intro to Physics', 'Physics', $3),$

$(\text{'}$MA$101\text{'},$ 'Calculus I$\text{'},$ 'Mathematics', $4),$

$(\text{'}$BI$101\text{'},$ 'Biology Basics', 'Biology', $3),$

$(\text{'}$CH$101\text{'},$ 'Introduction to Chemistry', 'Chemistry', $3)$;

Task $3$: Simple Queries $(30$ marks$)$

Perform the following SQL queries and include the output:

$1.$ List all students from the student table. $(5$ marks$)$

$2.$ Find all courses in the 'Comp. Sci.$\text{'}$ department. $(5$ marks$)$

$3.$ Find the total number of credits for a student whose ID is $101.(5$ marks$)$

$4.$ Find all students who have earned more than $30$ total credits. $(5$ marks$)$

$5.$ List the course titles and department names of all courses. $(5$ marks$)$

$6.$ Count the number of students in the 'Physics' department. $(5$ marks$)$

Task $4$: Update and Delete Data $(20$ marks$)$

$1.$ Update the total credits for the student with ID $102$ to $45.(5$ marks$)$

$2.$ Increase the credits of all courses in the 'Comp. Sci.$\text{'}$ department by $1.(5$ marks$)$

$3.$ Delete all courses in the 'Biology' department. $(5$ marks$)$

$4.$ Delete a student from the student table whose ID is $105.(5$ marks$)$

Bonus Task: Nested Queries $(10$ marks$)$

For this bonus task, you will need to write nested queries to answer more complex questions.

Each query should demonstrate your understanding of how to use subqueries effectively in SQL$.$

Bonus Task $1$: Find Students Who Have Taken All Courses in a Specific Department $(5$ marks$)$

Write a query to find all students who have taken all the courses offered by the 'Comp. Sci.$\text{'}$ department.

$$ Hint: Use a nested query to first select all the courses from the 'Comp. Sci.$\text{'}$ department, and then compare it with the courses that each student has taken.

Bonus Task $2$: Find Courses with Enrollment Greater Than the Average Enrollment $(5$

marks$)$

Write a query to find all courses that have an enrollment greater than the average enrollment of

all courses.

$$ Hint: Use a nested query to compute the average enrollment first, and then compare it with the enrollment of each course.