oracle查询不选1号课程的学生学号与姓名

您可以使用以下 SQL 语句来查询不选 1 号课程的学生学号与姓名：

SELECT s.id, s.name
FROM students s
WHERE s.id NOT IN (
  SELECT sc.student_id
  FROM scores sc
  WHERE sc.course_id = 1
)

其中，`students` 表存储了所有学生的信息，包括 `id` 和 `name` 字段；`scores` 表存储了所有学生的成绩信息，包括 `student_id` 和 `course_id` 字段。以上 SQL 语句使用了子查询来查找选了 1 号课程的学生，然后在外层查询中使用 `NOT IN` 语句来排除这些学生，从而得到不选 1 号课程的学生学号与姓名。

相关问题

oracle 查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

假设您的学生和选课信息存储在以下两张表中：

- 学生表：student，包含字段 student_id（学号）和 student_name（姓名）。
- 选课表：selection，包含字段 student_id（学号）和 course_id（课程号）。

为了查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名，您可以按照以下步骤进行操作：

1. 找到“s001”号同学所选的所有课程。

SELECT course_id
FROM   selection
WHERE  student_id = 's001';

2. 找到选了上述课程的其他同学。

SELECT DISTINCT student_id
FROM   selection
WHERE  course_id IN (
         SELECT course_id
         FROM   selection
         WHERE  student_id = 's001'
       )
AND    student_id != 's001';

3. 将上述查询结果与学生表进行 JOIN，获取其他同学的学号和姓名。

SELECT s.student_id, s.student_name
FROM   student s
JOIN   (
         SELECT DISTINCT student_id
         FROM   selection
         WHERE  course_id IN (
                  SELECT course_id
                  FROM   selection
                  WHERE  student_id = 's001'
                )
         AND    student_id != 's001'
       ) t
ON     s.student_id = t.student_id;

综合以上三个查询，您可以得到和“s001”号的同学学习的课程完全相同的其他同学的学号和姓名。

以下查询请尽量多使用不同的SQL语句实现! (1）选择数据库JX; (2)查询选修了“3”号课程且成绩在70分以上的学生信息; (3）查询所有不比“电子工程”系的所有学生年龄大的其他系的学生信息; (4)查询选修了“3”号课程的学生姓名、成绩及任课教师姓名; (5）查询开设的课程中选课人数不足15人的课程号及人数信息; (6）查询选修课程数低于3门的学生信息; (7）查询没有选修课程的同学的基本信息; (8）查询既选修了“3”号又选修了“4”号课程的学生姓名、课程名及成绩; (9)查询同时选修了“操作系统”课程和“数据库原理”课程的学生名单; (10）查询所有同学的基本信息及选课情况（包含未选课同学的信息); (11）列出所有参加了数据库课程考试的学生姓名和成绩; (12）查询所有学生和教师的姓名（允许重名）并标识类型（学生/老师); (13）查询每一课程的间接选修课，以“课程名”、“间接选修课名”作列名; (14）列出所有没有选修“数据结构”课程的学生的学号、姓名、所在院系。

1. 使用MySQL语句：
(1) USE JX;
(2) SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = 3 AND score >= 70);
(3) SELECT * FROM student WHERE age > (SELECT MAX(age) FROM student WHERE department != '电子工程');
(4) SELECT student.s_name, score.score, teacher.t_name FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id LEFT JOIN teacher ON course.t_id = teacher.t_id WHERE score.c_id = 3;
(5) SELECT c_id, COUNT(*) AS num FROM score GROUP BY c_id HAVING num < 15;
(6) SELECT student.s_id, student.s_name FROM student LEFT JOIN score ON student.s_id = score.s_id GROUP BY student.s_id HAVING COUNT(*) < 3;
(7) SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score);
(8) SELECT student.s_name, course.c_name, score.score FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE score.c_id IN (3,4);
(9) SELECT student.s_name FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE course.c_name = '操作系统' AND student.s_id IN (SELECT student.s_id FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE course.c_name = '数据库原理');
(10) SELECT student.s_id, student.s_name, student.department, course.c_id, course.c_name, score.score FROM student LEFT JOIN score ON student.s_id = score.s_id RIGHT JOIN course ON score.c_id = course.c_id;
(11) SELECT student.s_name, score.score FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE course.c_name = '数据库';
(12) SELECT s_name AS name, '学生' AS type FROM student UNION SELECT t_name AS name, '老师' AS type FROM teacher;
(13) SELECT course.c_name AS course, indirect_course.c_name AS indirect_course FROM course LEFT JOIN indirect_course ON course.c_id = indirect_course.c_id;
(14) SELECT student.s_id, student.s_name, student.department FROM student WHERE student.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 2);

2. 使用Oracle语句：
(1) ALTER SESSION SET current_schema = JX;
(2) SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = 3 AND score >= 70);
(3) SELECT * FROM student WHERE age > (SELECT MAX(age) FROM student WHERE department != '电子工程');
(4) SELECT student.s_name, score.score, teacher.t_name FROM student, score, course, teacher WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.t_id = teacher.t_id AND score.c_id = 3;
(5) SELECT c_id, COUNT(*) AS num FROM score GROUP BY c_id HAVING COUNT(*) < 15;
(6) SELECT student.s_id, student.s_name FROM student, score WHERE student.s_id = score.s_id GROUP BY student.s_id HAVING COUNT(*) < 3;
(7) SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score);
(8) SELECT student.s_name, course.c_name, score.score FROM student, score, course WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND score.c_id IN (3,4);
(9) SELECT student.s_name FROM student, score, course WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.c_name = '操作系统' AND student.s_id IN (SELECT student.s_id FROM student, score, course WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.c_name = '数据库原理');
(10) SELECT student.s_id, student.s_name, student.department, course.c_id, course.c_name, score.score FROM student, score, course WHERE student.s_id = score.s_id(+) AND score.c_id = course.c_id(+);
(11) SELECT student.s_name, score.score FROM student, score, course WHERE student.s_id = score.s_id(+) AND score.c_id = course.c_id(+) AND course.c_name = '数据库';
(12) SELECT s_name AS name, '学生' AS type FROM student UNION SELECT t_name AS name, '老师' AS type FROM teacher;
(13) SELECT course.c_name AS course, indirect_course.c_name AS indirect_course FROM course, indirect_course WHERE course.c_id = indirect_course.c_id(+);
(14) SELECT student.s_id, student.s_name, student.department FROM student WHERE student.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 2);