Consider a database schema with the following relations: Student (ssn, name) Prof (ssn, name) Course (number, instructor-ssn, title, credits, room#) Enroll (student-ssn, course#) Room (number, capacity) Write an SQL query that finds the names of all students who are NOT enrolled in two classes held in the same room.

可以使用以下 SQL 查询语句来查找所有没有在同一教室上两门课程的学生姓名：

SELECT s.name
FROM Student s
WHERE s.ssn NOT IN (
    SELECT e1.student-ssn
    FROM Enroll e1, Enroll e2, Course c1, Course c2, Room r
    WHERE e1.course# = c1.number
        AND e2.course# = c2.number
        AND c1.room# = c2.room#
        AND c1.number != c2.number
        AND c1.instructor-ssn = c2.instructor-ssn
        AND e1.student-ssn = e2.student-ssn
        AND r.number = c1.room#
        AND e1.course# != e2.course#
    )

这个查询使用了子查询来查找所有在同一教室上两门课程的学生，然后使用 NOT IN 子句来排除这些学生。最后，查询返回所有剩余的学生姓名。

相关问题

Consider a database schema with the following relations: Student (ssn, name) Prof (ssn, name) Course (number, instructor-ssn, title, credits, room#) Enroll (student-ssn, course#) Room (number, capacity) Write a relational algebra query that finds the names of all students who are enrolled in a class taught by “Jones”

可以使用以下关系代数查询语句来查找所有选修由“Jones”教授教授的课程的学生姓名：

σ Prof.name='Jones' (Course ⨝ Enroll ⨝ Student)

这个查询使用了自然连接和选择运算符。首先，我们使用自然连接将 Course 和 Enroll 关系连接，以获得所有选修课程的学生。然后，我们再将这个结果与 Student 关系连接，以获得学生姓名。最后，我们使用选择运算符来筛选出教授姓名为“Jones”的课程。