用sql(Oracle)语句实现:[右外连接] 查询课程的选课情况(未选课程的课号和成绩为空)
时间: 2024-01-25 18:02:28 浏览: 32
假设有两张表:课程表(course)和选课表(selection),其中课程表包含课程号(cid)和课程名称(cname),选课表包含学生号(sid)、课程号(cid)和成绩(score)三个字段。
以下是实现右外连接查询课程的选课情况的 Oracle SQL 语句:
```
SELECT c.cid, c.cname, s.sid, s.score
FROM course c
RIGHT OUTER JOIN selection s
ON c.cid = s.cid
ORDER BY c.cid;
```
解释:
1. 使用 RIGHT OUTER JOIN 实现右外连接,将选课表作为主表,课程表作为从表。
2. ON 子句中使用 cid 字段进行关联。
3. 查询结果包括课程表的课程号和课程名称,以及选课表的学生号和成绩。
4. 如果某个课程没有学生选,对应的学生号和成绩字段值为空。
5. 使用 ORDER BY 子句按课程号排序。
相关问题
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);
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)