用sql（Oracle）语句实现：[右外连接] 查询课程的选课情况（未选课程的课号和成绩为空）

假设有两个表：课程表（course）和选课表（select_course），课程表中包含课程号（course_id）、课程名称（course_name）等字段，选课表中包含学号（student_id）、课程号（course_id）和成绩（score）等字段。

可以使用以下 Oracle SQL 语句进行右外连接查询：

SELECT c.course_id, s.score
FROM course c
RIGHT OUTER JOIN select_course s ON c.course_id = s.course_id

该语句会将课程表和选课表进行右外连接，并只返回选课表中已选课程的成绩和课程表中所有课程的课号（未选课程的成绩为空）。如果想要查询未选课程的课号和成绩为空，可以使用以下语句：

SELECT c.course_id, s.score
FROM course c
LEFT OUTER JOIN select_course s ON c.course_id = s.course_id
WHERE s.course_id IS NULL

该语句会将课程表和选课表进行左外连接，并只返回未选课程的课号和成绩为空。

相关问题

sql（Oracle）语句查询每门课号的选课人数。（无学生的课程为0） 右连接，选课表有空值 选课表(sc)包括学号，课程号和成绩 课程表(c)包括课程号，课程名，学分

可以使用以下SQL语句查询每门课号的选课人数：

SELECT c.课程号, COUNT(sc.学号) AS 选课人数 
FROM 课程表 c 
RIGHT JOIN 选课表 sc ON c.课程号 = sc.课程号 
GROUP BY c.课程号 
ORDER BY c.课程号;

其中，使用了右连接（RIGHT JOIN）来保证即使没有学生选修某门课程，该课程也会被显示出来，并且将选修该门课程的学生数量作为结果展示。使用了COUNT函数来计算选修每门课程的学生数量，GROUP BY语句来对结果按照课程号进行分组，ORDER BY语句来按照课程号进行升序排列。

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