oracle数据库练习题

以下为Oracle数据库练习题的答案:

6. 查询同时学过课程1和课程2的同学的学号和姓名:

SELECT g.sno AS "学号", g.sname AS "姓名" 
FROM student g, 
    (SELECT * FROM sc WHERE cno = 1) t 
WHERE t.sno IN (SELECT sno FROM sc WHERE cno = 2) 
    AND g.sno = t.sno;

该查询使用了子查询和多表连接，首先在子查询中选择了选修了课程1的学生的学号，然后通过连接操作找到同时选修了课程2的学生，并返回他们的学号和姓名。

7. 查询学过“李丁”老师所教的所有课程的所有同学的学号和姓名:

SELECT sno AS "学号", sname AS "姓名" 
FROM student 
WHERE sno IN (
    SELECT p.sno 
    FROM (SELECT * FROM sc WHERE cno = 4) p, 
        (SELECT * FROM sc WHERE cno = 5) k 
    WHERE p.sno = k.sno
);

该查询使用了子查询和多表连接，在子查询中先选择了李丁老师所教的课程4的学生的学号，然后通过连接操作找到同时学习了课程5的学生，并返回他们的学号和姓名。

3. 查询所有同学的学号、姓名、选课数、总成绩:

SELECT z."学号", g.sname AS "姓名", z."选课数", z."总分数" 
FROM (
    SELECT s.sno AS "学号", COUNT(s.cno) AS "选课数", SUM(s.grade) AS "总分数" 
    FROM course c, sc s 
    WHERE c.cno = s.cno 
    GROUP BY s.sno 
    ORDER BY "总分数" DESC
) z, student g 
WHERE z."学号" = g.sno;

该查询使用了子查询和表连接，首先在子查询中计算了每个学生的选课数和总成绩，并按照总成绩降序排序，然后通过连接操作将学生的学号、姓名、选课数和总成绩与student表连接起来，并返回结果。

以上是关于Oracle数据库练习题的答案。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* *2* *3* [Oracle数据库练习题](https://blog.csdn.net/WhenTheWindBlows/article/details/89403828)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"]
[ .reference_list ]