CS3095D Database Management Systems Laboratory/
CS4083E - Database System Design Laboratory
SQL Practice Questions
Note: Questions of this lab session can be answered by learning following concepts in SQL. Please use MySQL
relational database management system to write and execute the SQL queries.
➢ JOIN ➢ DESC ➢ DISTINCT
➢ GROUP BY HAVING ➢ LIMIT ➢ IN
➢ ORDER BY ➢ EXISTS ➢ Aggregation Operations
➢ Nested Query ➢ Date ➢ String Operators
Given the following tables:
Student (Student_ID (Primary Key) VARCHAR(10), Name VARCHAR(50), Date_of_Birth DATE, Email
VARCHAR(50), Phone_Number INTEGER)
Course (Course_ID (Primary Key) VARCHAR(10), Course_Name VARCHAR(50), Department_ID (Foreign Key
referencing Department) VARCHAR(10), Faculty_ID (Foreign Key referencing Faculty) VARCHAR(10))
Department(Department_ID (Primary Key) VARCHAR(10), Department_Name VARCHAR(50),
Head_of_Dept_ID (Foreign Key referencing Faculty) VARCHAR(10))
Faculty(Faculty_ID (Primary Key) VARCHAR(10), Name VARCHAR(50), Department_ID (Foreign Key
referencing Department) VARCHAR(10))
Enrollment(Student_ID (Foreign Key referencing Student) VARCHAR(10), Course_ID (Foreign Key referencing
Course) VARCHAR(10), Grade INTEGER)
Grade column can take integer values in the range 0-10.
Questions:
1. Find the courses offered by the 'Computer Science' department.
2. List all departments along with the names of their respective heads.
3. Retrieve the names of all faculty members who teach courses in any 'Engineering' department.
4. Calculate the average grade for each course.
5. Count the number of students enrolled in each course.
6. Find the courses with the highest and lowest enrollment.
7. Retrieve the names of students who are enrolled in at least two courses.
8. Calculate the total number of courses taken by each student.
9. Find the courses with the highest average grade.
10. List the names of courses that have not been assigned to any students.
11. Calculate the total number of students enrolled in each department.
12. List the names of students who have enrolled in both 'Mathematics' and 'Physics' courses.
13. Retrieve the names of students who have not enrolled in any courses.
14. Calculate the average grade for each department.
15. Find the courses with at least one student scoring grade below 6.
16. Retrieve the names of students who are enrolled in all courses with 'Engineering' in their names.
17. Find the names of students who have the highest grade in each course.
18. Find the course(s) with the maximum difference between the highest and lowest grades.
19. List the names of students who have taken all courses taught by a specific faculty member.
20. Find the courses with the lowest average grade among those with more than 10 enrollments.
21. List the names of students who have taken courses in both 'Mathematics' and 'Physics' departments but not in
'Computer Science'.
22. Retrieve the names of students who have taken every course offered by the department with the lowest average
grade.
23. Calculate the number of students who have taken more courses than the average number of courses taken by all
students.
24. Calculate the percentage of students who have failed in at least one course.
25. Retrieve the names of students who have taken courses in more than one department.