返回一组值的基于集合的子查询 使用sql语句查询 查询选修了S4号学生选修课程的学生学号和姓名

假设选修课程的学生信息存储在表格 `student_course` 中，其中包含以下字段：

- `student_id`: 学生学号
- `course_id`: 课程编号
- `course_name`: 课程名称

则可以使用如下 SQL 语句查询选修了 S4 号学生选修课程的学生学号和姓名：

SELECT student_id, student_name
FROM student
WHERE student_id IN (
  SELECT student_id
  FROM student_course
  WHERE course_id IN (
    SELECT course_id
    FROM student_course
    WHERE student_id = 'S4'
  )
);

其中，外层的 `SELECT` 语句查询符合条件的学生学号和姓名，内层的子查询使用了集合的子查询，查询选修了 S4 号学生选修课程的学生学号，最内层的子查询则查询 S4 号学生选修过的课程编号。

相关问题

以下查询请尽量多使用不同的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);

嵌套查询和组合查询操作。 (1) 统计选修了【数据库原理】课程的学生人数。 (2) 查询没有选修【数据库原理】课程的学生学号、姓名。 (3) 查询其他系中比计算机系学生年龄都小的学生学号、姓名和所在系。 (4) 查询被0602001 学生或0602002 学生所选修的课程的课程号(用UNION 组合查询与OR条件查询两种方法实现)。 (5) 查询0602001 学生和0602002 学生同时选修的课程的课程号(用IN 子查询与EXISTS 嵌套子查询两种方法实现)。 (6) 查询被学号0602001 学生选修，但没有被0602002 学生所选修的课程的课程号。

好的，以下是针对您提出的问题的SQL查询语句：

1. 统计选修了【数据库原理】课程的学生人数

SELECT COUNT(DISTINCT sid) AS num FROM sc WHERE cno='数据库原理';

2. 查询没有选修【数据库原理】课程的学生学号、姓名

SELECT sno, sname FROM student WHERE sno NOT IN (SELECT sid FROM sc WHERE cno='数据库原理');

3. 查询其他系中比计算机系学生年龄都小的学生学号、姓名和所在系

SELECT sno, sname, sdept FROM student WHERE sage < (SELECT MIN(sage) FROM student WHERE sdept='计算机系') AND sdept <> '计算机系';

4. 查询被0602001学生或0602002学生所选修的课程的课程号(用UNION组合查询与OR条件查询两种方法实现)

-- UNION组合查询方式
SELECT cno FROM sc WHERE sid='0602001'
UNION
SELECT cno FROM sc WHERE sid='0602002';

-- OR条件查询方式
SELECT cno FROM sc WHERE sid='0602001' OR sid='0602002';

5. 查询0602001学生和0602002学生同时选修的课程的课程号(用IN子查询与EXISTS嵌套子查询两种方法实现)

-- IN子查询方式
SELECT cno FROM sc WHERE sid='0602001' AND cno IN (SELECT cno FROM sc WHERE sid='0602002');

-- EXISTS嵌套子查询方式
SELECT cno FROM sc s1 WHERE sid='0602001' AND EXISTS (SELECT * FROM sc s2 WHERE sid='0602002' AND s1.cno=s2.cno);

6. 查询被学号0602001学生选修，但没有被0602002学生所选修的课程的课程号

SELECT cno FROM sc WHERE sid='0602001' AND cno NOT IN (SELECT cno FROM sc WHERE sid='0602002');

希望以上查询语句能够满足您的需求。