CSE384: Database Design Lab Mahjabin Sultana, Lecturer, CSE, SEU
SQL Practice Set
Table Description
Department
Column Type
dept_id NUMBER
dept_name VARCHAR2(30)
location VARCHAR2(30) Course
Column Type
Student course_id NUMBER
Column Type course_name VARCHAR2(50)
student_id NUMBER credit NUMBER
name VARCHAR2(50) dept_id NUMBER
dept_id NUMBER
cgpa NUMBER(3,2) Enrollment
admission_year NUMBER Column Type
student_id NUMBER
Evening_student course_id NUMBER
Column Type semester VARCHAR2(10)
student_id NUMBER marks NUMBER
name VARCHAR2(50)
dept_id NUMBER
Basic Filtering (WHERE)
1. Find the names and CGPA of students whose CGPA is greater than 3.70.
2. List all students admitted after 2021.
1
CSE384: Database Design Lab Mahjabin Sultana, Lecturer, CSE, SEU
3. Find courses that have more than 3 credits.
4. Display students whose CGPA is between 3.20 and 3.80.
Aggregate Functions
5. Find the average CGPA of all students.
6. Find the maximum marks obtained in any course.
7. Count the total number of students in the STUDENT table.
GROUP BY
8. Find the number of students in each department.
9. Find the average CGPA of each department.
10. Find the maximum marks obtained in each course.
11. Find the total number of students admitted each year.
HAVING
12. Find departments where the average CGPA is greater than 3.50.
13. Find courses where the maximum marks obtained is greater than 85.
14. Find departments that have more than 5 students.
Nested Queries
15. Find students whose CGPA is higher than the overall average CGPA.
16. Find students who belong to the same department as the student named
'Rahim'.
17. Find students who obtained the highest marks in any course.
18. Find students whose marks are greater than the average marks of their
course.
19. Find the department that has the highest average CGPA.
2
CSE384: Database Design Lab Mahjabin Sultana, Lecturer, CSE, SEU
WITH Clause
20. Create a temporary table that contains students with CGPA greater than
3.60, then display all of them.
21. Create a temporary table that stores average CGPA per department, then
show departments where the average CGPA is greater than 3.40.
22. Using WITH clause, calculate total enrollments per course, then display
courses with more than 10 enrollments.
Set Operations
23. List all student names from STUDENT and EVENING_STUDENT without
duplicates.
24. Find students who appear in both STUDENT and EVENING_STUDENT
tables.
25. Find students who are in STUDENT but not in EVENING_STUDENT.
Logical Queries
26. Find students who did not enroll in any course.
27. Find courses that no student has taken.
28. Find students who have taken more than 3 courses.
29. Find students whose CGPA is higher than the average CGPA of their
department.
30. Find courses taken by all students in the CSE department.