11.

a) Design an E-R diagram for a University Course Management System with entities: Student, Course,
Instructor, and Enrollment.
b) Convert the E-R diagram into relational schemas.

12.
a) Create all necessary tables (Student, Course, Instructor, Enrollment, Teaches) using DDL commands with
primary and foreign key constraints.
b) Insert sample data into each table using DML commands.

13.
a) Write queries using ANY, ALL, EXISTS, NOT EXISTS, UNION, and INTERSECT.
b) Add a constraint to ensure that student email contains '@' in the STUDENT table.

14.
a) Write SQL queries to find the total number of students enrolled in each course using aggregate functions
and GROUP BY.
b) Create triggers on the ENROLLMENT table for insert, delete, and update events.

15. Create a table EMPLOYEE with the following columns:

 EmployeeID (INT, Primary Key)

 Name (VARCHAR(100))

 Department (VARCHAR(50))

 Salary (DECIMAL(10,2))

 Email (VARCHAR(100), unique)

Add a CHECK constraint on the Salary column to ensure it is greater than 5000.

Modify the EMPLOYEE table to add a new column JoiningDate of type DATE.

Drop the Department column from the EMPLOYEE table.

Rename the EMPLOYEE table to EMP.

Create a table PROJECT with a foreign key constraint referencing EMPLOYEE.

16. Create a table STUDENT with the following fields and constraints:

 RollNo (INT, Primary Key)

 Name (VARCHAR(50))

 Age (INT, CHECK Age >= 18)

 CourseID (INT, foreign key referencing COURSE table)

[Link] (Data Manipulation Language) Questions

1. Insert 3 records into the EMPLOYEE table with valid data.

2. Update the salary of an employee with EmployeeID = 101 by 10%.

3. Delete all employees from the EMPLOYEE table who belong to the ‘HR’ department.

4. Write a query to retrieve all employees with a salary greater than 50,000.
 5. Insert a record into the STUDENT table where the student is enrolled in a course with CourseID =
104.

6. Update the Name of the student whose RollNo is 5 to "Ravi Kumar".

7. Delete the student record with RollNo = 10.

[Link] Function Queries for Lab Practice

1. Count the total number of students in the STUDENT table:

SELECT COUNT(*) AS Total_Students FROM STUDENT;

2. Find the average salary of employees in the EMPLOYEE table:

SELECT AVG(Salary) AS Average_Salary FROM EMPLOYEE;

3. Find the highest and lowest marks obtained in the RESULTS table:

SELECT MAX(Marks) AS Highest_Mark, MIN(Marks) AS Lowest_Mark FROM RESULTS;

4. Calculate the total fees collected from all students in the FEES table:

SELECT SUM(Amount) AS Total_Fees FROM FEES;

5. Find the number of students in each department from the STUDENT table:

SELECT Department, COUNT(*) AS No_of_Students

FROM STUDENT

GROUP BY Department;

6. Display the average salary for each department in the EMPLOYEE table:

SELECT Department, AVG(Salary) AS Avg_Salary

FROM EMPLOYEE

GROUP BY Department;

7. List departments having more than 5 employees:

SELECT Department, COUNT(*) AS Employee_Count

FROM EMPLOYEE

GROUP BY Department

HAVING COUNT(*) > 5;

8. Find the course with the highest number of enrollments:

SELECT CourseID, COUNT(*) AS Enrollments

FROM ENROLLMENT

GROUP BY CourseID

ORDER BY Enrollments DESC

LIMIT 1;

9. Get the total number of students who scored above 75 in any subject:
SELECT COUNT(*) AS High_Scorers

FROM RESULTS

WHERE Marks > 75;

10. Show the total and average fees paid per student from the FEES table:

SELECT StudentID, SUM(Amount) AS Total_Paid, AVG(Amount) AS Avg_Payment

FROM FEES

GROUP BY StudentID;

[Link] Lab Questions Using GROUP BY and HAVING

1. Find the total number of students in each department:

SELECT Department, COUNT(*) AS Total_Students

FROM STUDENT

GROUP BY Department;

2. List departments where the average salary of employees is more than 50,000:

SELECT Department, AVG(Salary) AS Avg_Salary

FROM EMPLOYEE

GROUP BY Department

HAVING AVG(Salary) > 50000;

3. Find the total fee collected from each student, but only show those who paid more than ₹10,000:

SELECT StudentID, SUM(Amount) AS Total_Fee

FROM FEES

GROUP BY StudentID

HAVING SUM(Amount) > 10000;

4. Display the number of students enrolled in each course and filter those with more than 3
students:

SELECT CourseID, COUNT(StudentID) AS No_of_Students

FROM ENROLLMENT

GROUP BY CourseID

HAVING COUNT(StudentID) > 3;

5. Show the courses with an average grade above 75:

SELECT CourseID, AVG(Grade) AS Avg_Grade

FROM ENROLLMENT

GROUP BY CourseID

HAVING AVG(Grade) > 75;

SELECT InstructorID, COUNT(CourseID) AS Courses_Taught

FROM TEACHES

GROUP BY InstructorID

HAVING COUNT(CourseID) > 2;

7. Find the minimum, maximum, and average marks obtained by students grouped by CourseID:

SELECT CourseID, MIN(Marks) AS Min_Marks, MAX(Marks) AS Max_Marks, AVG(Marks) AS Avg_Marks

FROM RESULTS

GROUP BY CourseID;

8. Find the number of students per department whose average marks are above 60:

SELECT Department, AVG(Marks) AS Avg_Marks, COUNT(*) AS Student_Count

FROM STUDENT s

JOIN RESULTS r ON [Link] = [Link]

GROUP BY Department

HAVING AVG(Marks) > 60;