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 子句来排除这些学生。最后，查询返回所有剩余的学生姓名。

相关问题

create a relational database schema with SQL statement.

Sure, here is an example of creating a simple relational database schema for a blog website using SQL:

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  author_id INT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INT NOT NULL,
  author_id INT NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

In this example, we have three tables: "users", "posts", and "comments". The "users" table stores information about registered users, including their username, email, and password. The "posts" table stores information about blog posts, including the title, content, and the ID of the user who wrote the post. The "comments" table stores information about comments on blog posts, including the content of the comment, the ID of the post it refers to, and the ID of the user who wrote the comment.

Note that we have also included foreign key constraints in the "posts" and "comments" tables, which ensure that the "author_id", "post_id", and "author_id" fields reference valid IDs in the "users" and "posts" tables, respectively.

students(student_id, name, year, GPA, programs_code) instructors(instructor_id, name, title, salary, programs_code) programs(code, name, division, instructor_id) course(course_name, credit, domain, course_number) enroll(student_id, instructor_id, course_name) 1. Create tables according to the schema.

Sure, here are the SQL statements to create the tables based on the given schema:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  year INT,
  GPA DECIMAL(3,2),
  programs_code VARCHAR(10)
);

CREATE TABLE instructors (
  instructor_id INT PRIMARY KEY,
  name VARCHAR(50),
  title VARCHAR(50),
  salary DECIMAL(10,2),
  programs_code VARCHAR(10)
);

CREATE TABLE programs (
  code VARCHAR(10) PRIMARY KEY,
  name VARCHAR(50),
  division VARCHAR(50),
  instructor_id INT,
  FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
);

CREATE TABLE course (
  course_name VARCHAR(50) PRIMARY KEY,
  credit INT,
  domain VARCHAR(50),
  course_number INT
);

CREATE TABLE enroll (
  student_id INT,
  instructor_id INT,
  course_name VARCHAR(50),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id),
  FOREIGN KEY (course_name) REFERENCES course(course_name)
);

Please note that I assumed some data types for the columns, but you may need to adjust them based on your specific requirements.