In this step, you will learn how to list the names of all students who have not taken courses taught by the "Daniel" teacher. Follow the steps below to complete this step:
-
Open the answer.sql
file in the /home/labex/project
directory.
-
Add the SQL statement below the comment:
-
Locate the comment -- List the names of all students who have not taken courses taught by the "Daniel" teacher.
SELECT s.sname
FROM S s
WHERE NOT EXISTS (
SELECT 1
FROM SC sc
JOIN C c ON sc.cno = c.cno
WHERE c.tname = 'Daniel' AND sc.sno = s.sno
);
This SQL statement first selects the sname
column from the S
table. It then uses a NOT EXISTS
clause to check if there are any records in the SC
table that have a course ID (cno
) that matches a course taught by the "Daniel" teacher, and the student ID (sno
) matches the current student. If no such records exist, the student's name will be included in the result.
-
Locate the comment -- List the student numbers and names of students who have failed (grade < 60) in two or more courses.
SELECT s.sno, s.sname
FROM S s
JOIN SC sc ON s.sno = sc.sno
WHERE sc.grade < 60
GROUP BY s.sno, s.sname
HAVING COUNT(sc.sno) >= 2;
This SQL statement first joins the S
and SC
tables to get the student information and their course grades. It then filters the records where the grade is less than 60, groups the results by student number and name, and finally selects only the students who have failed in two or more courses using the HAVING
clause.
-
Locate the comment -- List the names of all students who have taken both course "11" and course "12".
SELECT s.sname
FROM S s
JOIN SC sc ON s.sno = sc.sno
WHERE sc.cno IN (11, 12)
GROUP BY s.sno
HAVING COUNT(DISTINCT sc.cno) = 2
ORDER BY s.sno;
This SQL statement first joins the S
and SC
tables to get the student information and their course IDs. It then filters the records where the course ID is either 11 or 12, groups the results by student number, and selects only the students who have taken both courses using the HAVING
clause. Finally, it orders the results by student number.
-
Locate the comment -- List the student numbers of all students whose grades in course "11" are higher than those in course "12", and sort them in ascending order.
SELECT sc11.sno
FROM SC sc11
JOIN SC sc12 ON sc11.sno = sc12.sno AND sc12.cno = 12
WHERE sc11.cno = 11 AND sc11.grade > sc12.grade
ORDER BY sc11.sno;
This SQL statement first joins the SC
table with itself to get the grade information for both course 11 and course 12. It then filters the records where the course ID is 11 and the grade is higher than the grade for course 12, and selects the student number. Finally, it orders the results by student number.
-
Locate the comment -- List the names of all students who are older than "John Davis".
SELECT s.sname
FROM S s, (SELECT sage FROM S WHERE sname = 'John Davis') AS zs
WHERE s.sage > zs.sage;
This SQL statement first selects the sage
value for the student with the name "John Davis" and stores it in a subquery zs
. It then selects the sname
from the S
table where the student's age (sage
) is greater than the age of "John Davis".
-
Locate the comment -- List the student number of the student with the highest grade among all students who have taken course "12".
SELECT sc.sno
FROM SC sc
WHERE sc.cno = 12 AND sc.grade = (
SELECT MAX(grade)
FROM SC
WHERE cno = 12
);
This SQL statement first selects the sno
from the SC
table where the course ID is 12 and the grade is equal to the maximum grade among all students who have taken course 12. This will give us the student number of the student with the highest grade in course 12.
-
Locate the comment -- List the names of all students, along with their selected course IDs and grades.
SELECT s.sname, sc.cno, sc.grade
FROM S s
LEFT JOIN SC sc ON s.sno = sc.sno;
This SQL statement performs a left join between the S
and SC
tables, which will include all students from the S
table, even if they have no records in the SC
table. This will ensure that all student names are included in the result, along with their course IDs and grades (if they have any).
-
Locate the comment -- List the student numbers and total grades (alias as: SG) of students who have taken four or more courses, sorted in descending order of total grades.
SELECT sc.sno, SUM(sc.grade) AS SG
FROM SC sc
GROUP BY sc.sno
HAVING COUNT(sc.cno) >= 4
ORDER BY SG DESC;
This SQL statement first groups the SC
table by student number (sno
) and calculates the sum of the grades for each student (aliased as SG
). It then filters the results to include only the students who have taken four or more courses using the HAVING
clause. Finally, it orders the results by the total grades in descending order.