Lab Task Nested Query
Here are the SQL QUERY: queries for each of the tasks:
1. Names of students whose GPA is above the average GPA of all students:
SQL Query:
SELECT Name FROM students WHERE GPA > (SELECT AVG(GPA) FROM students);
2. Subjects taught by teachers whose monthly salary is higher than the average
monthly salary of all teachers:
SQL Query:
SELECT DISTINCT Subject FROM teachers WHERE MonthlySalary > (SELECT
AVG(MonthlySalary) FROM teachers);
3. Number of students in classes taught by teachers who teach History or
English:
SQL Query:
SELECT COUNT([Link]) FROM students AS s JOIN classes AS c ON [Link] =
[Link] JOIN teachers AS t ON [Link] = [Link] WHERE [Link] IN
('History', 'English');
4. Names of students who are enrolled in classes with a grade level higher than
the average grade level of all classes:
SQL Query:
SELECT [Link] FROM students AS s JOIN classes AS c ON [Link] = [Link]
WHERE [Link] > (SELECT AVG(GradeLevel) FROM classes);
5. Names of teachers who teach classes with more students than the average
number of students in all classes:
SQL Query:
SELECT DISTINCT [Link] FROM teachers AS t JOIN classes AS c ON [Link] =
[Link] GROUP BY [Link] HAVING COUNT([Link]) > (SELECT
AVG(StudentCount) FROM (SELECT COUNT(StudentID) AS StudentCount FROM
classes GROUP BY TeacherID) AS avg_student_count);
6. GPA of students in classes where the teacher's subject is not Literature:
SQL Query:
SELECT [Link] FROM students AS s JOIN classes AS c ON [Link] = [Link] JOIN
teachers AS t ON [Link] = [Link] WHERE [Link] != 'Literature';
7. Number of students in classes where the teacher's monthly salary is greater
than the average monthly salary of teachers teaching Mathematics:
SQL Query:
SELECT COUNT([Link]) FROM students AS s JOIN classes AS c ON [Link] =
[Link] JOIN teachers AS t ON [Link] = [Link] WHERE [Link] >
(SELECT AVG(MonthlySalary) FROM teachers WHERE Subject = 'Mathematics');
8. Names of students who have a GPA higher than the GPA of any student in
class 12:
SQL Query:
SELECT [Link] FROM students AS s WHERE [Link] > (SELECT MAX(GPA) FROM
students WHERE ClassID = 12);
9. Subjects taught by teachers whose monthly salary is greater than the
monthly salary of any teacher teaching History:
SQL Query:
SELECT DISTINCT Subject FROM teachers WHERE MonthlySalary > (SELECT
MAX(MonthlySalary) FROM teachers WHERE Subject = 'History');
10. Number of students in classes where the teacher's subject is Mathematics or
History:
SQL Query:
SELECT COUNT([Link]) FROM students AS s JOIN classes AS c ON [Link] =
[Link] JOIN teachers AS t ON [Link] = [Link] WHERE [Link] IN
('Mathematics', 'History');
11. Names of students who have a GPA higher than the GPA of all students in
class 10:
SQL Query:
SELECT [Link] FROM students AS s WHERE [Link] > ALL (SELECT GPA FROM students
WHERE ClassID = 10);
12. Names of students whose GPA is above the average GPA of students in
classes with more than 25 students:
SQL Query:
SELECT [Link] FROM students AS s JOIN classes AS c ON [Link] = [Link]
WHERE [Link] > (SELECT AVG(GPA) FROM students WHERE ClassID IN (SELECT ClassID
FROM classes GROUP BY ClassID HAVING COUNT(StudentID) > 25));
13. Number of students in classes where the teacher's subject is not English:
SQL Query:
SELECT COUNT([Link]) FROM students AS s JOIN classes AS c ON [Link] =
[Link] JOIN teachers AS t ON [Link] = [Link] WHERE [Link] !=
'English';
14. Names of students who are enrolled in classes with a grade level higher than
the grade level of any class taught by a teacher with a monthly salary less
than $2500:
SQL Query:
SELECT [Link] FROM students AS s JOIN classes AS c ON [Link] = [Link]
WHERE [Link] > ANY (SELECT GradeLevel FROM classes WHERE TeacherID IN
(SELECT TeacherID FROM teachers WHERE MonthlySalary < 2500));
15. Subjects taught by teachers whose monthly salary is greater than the
monthly salary of any teacher teaching Mathematics or History:
SQL Query:
SELECT DISTINCT Subject FROM teachers WHERE MonthlySalary > (SELECT
MAX(MonthlySalary) FROM teachers WHERE Subject IN ('Mathematics', 'History'));
END
