Sub Queries in SQL (Student Database)
Subquery Types
Type Description
- Simple Subquery: One value returned (Scalar)
- Multi-row Subquery: Returns multiple rows
- Subquery in FROM clause: Acts like a temporary table
- Correlated Subquery: Depends on outer query
- Subquery in WHERE/SELECT/HAVING: Filtering, conditions
- EXISTS / NOT EXISTS: Check if data exists
- Nested Subqueries: Subqueries within subqueries
1. Simple Scalar Subquery — Find Student with Highest Marks
SELECT StudentID, StudentName, Marks
FROM Student
WHERE Marks = (SELECT MAX(Marks) FROM Student);
2. Multi-Row Subquery with IN — Students Enrolled in CS or Math
SELECT StudentName, CourseID
FROM Student
WHERE CourseID IN (
SELECT CourseID FROM Course WHERE CourseName IN ('CS', 'Math')
);
3. Subquery in FROM Clause (Inline View) — Average Marks by Course
SELECT CourseName, AvgMarks
FROM (
SELECT c.CourseName, ROUND(AVG(s.Marks), 2) AS AvgMarks
FROM Student s
JOIN Course c ON s.CourseID = c.CourseID
GROUP BY c.CourseName
);
4. Correlated Subquery — Students Scoring Above Course Average
SELECT StudentID, StudentName, CourseID, Marks
FROM Student s
WHERE Marks > (
SELECT AVG(Marks)
FROM Student
WHERE CourseID = s.CourseID
);
5. Subquery with EXISTS — Courses with Students
SELECT CourseID, CourseName
FROM Course c
WHERE EXISTS (
SELECT 1
FROM Student s
WHERE s.CourseID = c.CourseID
);
6. Subquery in SELECT Clause — Student with Their Grade
SELECT
StudentID,
StudentName,
Marks,
(SELECT Grade
FROM GradeScale g
WHERE s.Marks BETWEEN g.MinMarks AND g.MaxMarks) AS Grade
FROM Student s;
7. Nested Subquery — Students in Same Course as 'Amit'
SELECT StudentName, CourseID
FROM Student
WHERE CourseID = (
SELECT CourseID
FROM Student
WHERE StudentName = 'Amit'
);
8. Subquery with NOT IN — Students Not Assigned to Any Course
SELECT StudentID, StudentName
FROM Student
WHERE CourseID NOT IN (
SELECT CourseID FROM Course
);
9. Top 1 Marks in Each Course (Correlated + RANK)
SELECT *
FROM (
SELECT
StudentID,
StudentName,
CourseID,
Marks,
RANK() OVER (PARTITION BY CourseID ORDER BY Marks DESC) AS rk
FROM Student
)
WHERE rk = 1;
10. Subquery with Aggregation in HAVING Clause — Courses with Avg Marks > 75
SELECT c.CourseName, AVG(s.Marks) AS AvgMarks
FROM Student s
JOIN Course c ON s.CourseID = c.CourseID
GROUP BY c.CourseName
HAVING AVG(s.Marks) > 75;
Practice Exercises Using Subqueries
Q1: List students who scored more than the average marks of all students.
SELECT StudentID, StudentName, Marks
FROM Student
WHERE Marks > (SELECT AVG(Marks) FROM Student);
Q2: Show names of courses where at least one student scored more than 90.
SELECT CourseName
FROM Course c
WHERE EXISTS (
SELECT 1 FROM Student s
WHERE s.CourseID = c.CourseID AND s.Marks > 90
);
Q3: List students not enrolled in any course.
SELECT StudentID, StudentName
FROM Student
WHERE CourseID NOT IN (SELECT CourseID FROM Enrollment WHERE CourseID IS NOT
NULL);
Q4: List students whose advisor’s marks are more than 95.
SELECT StudentID, StudentName
FROM Student s
WHERE AdvisorID IS NOT NULL
AND (SELECT Marks FROM Student a WHERE a.StudentID = s.AdvisorID) > 95;
Q5: Find students who belong to the course that has the lowest average marks.
SELECT StudentID, StudentName, CourseID
FROM Student
WHERE CourseID = (
SELECT CourseID
FROM (
SELECT CourseID, AVG(Marks) AS AvgMarks
FROM Student
GROUP BY CourseID
ORDER BY AvgMarks
)
WHERE ROWNUM = 1
);
Practice Questions — SQL Views
Q1. Create a simple view that shows only student names and marks.
CREATE VIEW v_student_marks AS
SELECT StudentName, Marks
FROM Student;
Q2. Create a view that shows student name, course name, and location.
CREATE VIEW v_student_course_location AS
SELECT s.StudentName, c.CourseName, c.Location
FROM Student s
JOIN Course c ON s.CourseID = c.CourseID;
Q3. Create a view to list all students with their grade (using non-equi join).
CREATE VIEW v_student_grade AS
SELECT s.StudentID, s.StudentName, s.Marks, g.Grade
FROM Student s
JOIN GradeScale g
ON s.Marks BETWEEN g.MinMarks AND g.MaxMarks;
Q4. Create a view that only shows students scoring more than 75 marks.
CREATE VIEW v_high_scorers AS
SELECT StudentID, StudentName, Marks
FROM Student
WHERE Marks > 75;
Q5. Create a view to find course-wise average marks.
CREATE VIEW v_course_avg_marks AS
SELECT c.CourseName, ROUND(AVG(s.Marks), 2) AS AvgMarks
FROM Student s
JOIN Course c ON s.CourseID = c.CourseID
GROUP BY c.CourseName;
Q6. Create a view that shows enrollment with course names.
CREATE VIEW v_enrollment_course AS
SELECT e.EnrollID, s.StudentName, c.CourseName
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID;
Q7. Create a view that shows each student’s advisor name (self join).
CREATE VIEW v_student_advisor AS
SELECT s.StudentName, a.StudentName AS AdvisorName
FROM Student s
LEFT JOIN Student a ON s.AdvisorID = a.StudentID;
Q8. Create a view that lists all students without a course assigned.
CREATE VIEW v_student_no_course AS
SELECT StudentID, StudentName
FROM Student
WHERE CourseID IS NULL;
Q9. Create a view that is read-only, showing only course names and locations.
CREATE VIEW v_courses AS
SELECT CourseName, Location
FROM Course
WITH READ ONLY;
Q10. Create a view to show the top scorer in each course (using RANK or subquery).
CREATE VIEW v_top_scorers AS
SELECT *
FROM (
SELECT StudentID, StudentName, CourseID, Marks,
RANK() OVER (PARTITION BY CourseID ORDER BY Marks DESC) AS rk
FROM Student
)
WHERE rk = 1;
Challenge Questions:
1) Create a view to show how many students are there in each course.
2) Create a view to mask marks for all courses except Math (only Math shows real marks).
3) Create a view to show only the latest enrolled student from each course.
4) Create a view to display all courses and whether they currently have enrollments.
5) Create an updatable view that allows changes to student marks and name, but not their course
or advisor.
6) List students who scored more than the average marks in their course, excluding courses with
fewer than 2 students.
7) Find courses where all students are enrolled only within their course domain.
8) For each course, show the difference between the highest and lowest marks.
9) List students who enrolled before the average enrollment date and scored below the overall
average marks.
10) Show courses where the number of enrollments exceeds the average number of enrollments
per course.
