请教关于数据库查询的入门问题！

[tom95588]

题目是：建立一下数据库表

create table student
(name varchar2(30),
kecheng varchar2(30),
fenshu number(20)
)
然后向数据库插入以下数据
insert all
into student values('张三','语文',81)
into student values('张三','数学',75)
into student values('李四','语文',56)
into student values('李四','数学',90)
into student values('王五','语文',81)
into student values('王五','数学',100)
into student values('王五','英语',40)
select count(name) from student

问题是：
（1） 找出至少有一门课成绩的学生名单；
（2） 找出平均分比“王五”的平均分高的学生；
（3） 找出至少有一门课成绩大于80的学生；
（4） 列出各门课程的最低分学生；
（5） 列出所有学生都及格的课程

请各位大佬帮忙写个查询语句，小弟刚刚接触oracle 谢谢大家！！！！

[tom95588]

引用 6 楼 zlloct 的回复:

[quote=引用 5 楼 zlloct 的回复:] [quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 tom95588 的回复:] [quote=引用 1 楼 zlloct 的回复:] SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

谢谢楼上的大哥，问题全部解决得了。非常感谢[/quote]但是我觉得下面三个题的答案有问题。主要是针对没有课程成绩的学生该如何处理 （2） 找出平均分比“王五”的平均分高的学生 （4） 列出各门课程的最低分学生； （5） 列出所有学生都及格的课程 [/quote] 如果没成绩的学生当0分处理，修改后的SQL 如下： SELECT T.NAME, SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T GROUP BY T.NAME HAVING SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) > (SELECT SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT T5.NAME, T5.KECHENG, T5.FENSHU FROM (SELECT T4.NAME, T4.KECHENG, NVL(T3.FENSHU, 0) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+)) T5, (SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG) T6 WHERE T5.KECHENG = T6.KECHENG AND T5.FENSHU = T6.FENSHU; SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG HAVING MIN(NVL(T3.FENSHU, 0))>60 [/quote] （2） 找出平均分比“王五”的平均分高的学生 答案应该没有结果，没有人的成绩比王五高，其他两人都缺考了一门。 （4） 列出各门课程的最低分学生 1 zhangsan shuxue 75 2 lisi yingyu 0 3 zhangsan yingyu 0 4 lisi yuwen 56 缺考的人当0分处理 （5） 列出所有学生都及格的课程 1 shuxue 75 只数学满足，学生都参加考试并达到60分 [/quote] 我这样写的第二道的题目 直接把英语课排出去了 select name,avg(fenshu) from student where kecheng<>'英语' group by name having avg(fenshu)<(select avg(fenshu) from student where kecheng<>'英语' and name='王五')

[CT_LXL]

引用 7 楼 tom95588 的回复:

[quote=引用 6 楼 zlloct 的回复:] [quote=引用 5 楼 zlloct 的回复:] [quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 tom95588 的回复:] [quote=引用 1 楼 zlloct 的回复:] SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

谢谢楼上的大哥，问题全部解决得了。非常感谢[/quote]但是我觉得下面三个题的答案有问题。主要是针对没有课程成绩的学生该如何处理 （2） 找出平均分比“王五”的平均分高的学生 （4） 列出各门课程的最低分学生； （5） 列出所有学生都及格的课程 [/quote] 如果没成绩的学生当0分处理，修改后的SQL 如下： SELECT T.NAME, SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T GROUP BY T.NAME HAVING SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) > (SELECT SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT T5.NAME, T5.KECHENG, T5.FENSHU FROM (SELECT T4.NAME, T4.KECHENG, NVL(T3.FENSHU, 0) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+)) T5, (SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG) T6 WHERE T5.KECHENG = T6.KECHENG AND T5.FENSHU = T6.FENSHU; SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG HAVING MIN(NVL(T3.FENSHU, 0))>60 [/quote] （2） 找出平均分比“王五”的平均分高的学生 答案应该没有结果，没有人的成绩比王五高，其他两人都缺考了一门。 （4） 列出各门课程的最低分学生 1 zhangsan shuxue 75 2 lisi yingyu 0 3 zhangsan yingyu 0 4 lisi yuwen 56 缺考的人当0分处理 （5） 列出所有学生都及格的课程 1 shuxue 75 只数学满足，学生都参加考试并达到60分 [/quote] 我这样写的第二道的题目 直接把英语课排出去了 select name,avg(fenshu) from student where kecheng<>'英语' group by name having avg(fenshu)<(select avg(fenshu) from student where kecheng<>'英语' and name='王五')[/quote] 我觉得不能把英语排出去吧，如果排出英语的话那就是求数学和语文的平均成绩了，个人觉得应该是全部课程的平均成绩，没参加考试的作0分处理

[CT_LXL]

SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

[tom95588]

引用 1 楼 zlloct 的回复:

SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

谢谢楼上的大哥，问题全部解决得了。非常感谢

[CT_LXL]

引用 2 楼 tom95588 的回复:

[quote=引用 1 楼 zlloct 的回复:] SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

谢谢楼上的大哥，问题全部解决得了。非常感谢[/quote]但是我觉得下面三个题的答案有问题。主要是针对没有课程成绩的学生该如何处理 （2） 找出平均分比“王五”的平均分高的学生 （4） 列出各门课程的最低分学生； （5） 列出所有学生都及格的课程

[空心兜兜]

好像是网上盛传的面试题

[CT_LXL]

引用 3 楼 zlloct 的回复:

[quote=引用 2 楼 tom95588 的回复:] [quote=引用 1 楼 zlloct 的回复:] SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

谢谢楼上的大哥，问题全部解决得了。非常感谢[/quote]但是我觉得下面三个题的答案有问题。主要是针对没有课程成绩的学生该如何处理 （2） 找出平均分比“王五”的平均分高的学生 （4） 列出各门课程的最低分学生； （5） 列出所有学生都及格的课程 [/quote] 如果没成绩的学生当0分处理，修改后的SQL 如下： SELECT T.NAME, SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T GROUP BY T.NAME HAVING SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) > (SELECT SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT T5.NAME, T5.KECHENG, T5.FENSHU FROM (SELECT T4.NAME, T4.KECHENG, NVL(T3.FENSHU, 0) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+)) T5, (SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG) T6 WHERE T5.KECHENG = T6.KECHENG AND T5.FENSHU = T6.FENSHU; SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG HAVING MIN(NVL(T3.FENSHU, 0))>60

[CT_LXL]

引用 5 楼 zlloct 的回复:

[quote=引用 3 楼 zlloct 的回复:] [quote=引用 2 楼 tom95588 的回复:] [quote=引用 1 楼 zlloct 的回复:] SELECT DISTINCT T.NAME FROM STUDENT T WHERE T.FENSHU > 0; SELECT T.NAME FROM STUDENT T GROUP BY T.NAME HAVING AVG(T.FENSHU) > (SELECT AVG(T.FENSHU) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT DISTINCT (T.NAME) FROM STUDENT T WHERE T.FENSHU > 80; SELECT T.NAME, T1.KECHENG, T1.FENGSHU FROM STUDENT T, (SELECT KECHENG, MIN(FENSHU) FENGSHU FROM STUDENT GROUP BY KECHENG) T1 WHERE T.KECHENG = T1.KECHENG AND T1.FENGSHU = T.FENSHU; SELECT DISTINCT KECHENG FROM STUDENT T WHERE T.KECHENG NOT IN (SELECT DISTINCT (KECHENG) KECHENG FROM STUDENT T WHERE T.FENSHU < 60)

谢谢楼上的大哥，问题全部解决得了。非常感谢[/quote]但是我觉得下面三个题的答案有问题。主要是针对没有课程成绩的学生该如何处理 （2） 找出平均分比“王五”的平均分高的学生 （4） 列出各门课程的最低分学生； （5） 列出所有学生都及格的课程 [/quote] 如果没成绩的学生当0分处理，修改后的SQL 如下： SELECT T.NAME, SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T GROUP BY T.NAME HAVING SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) > (SELECT SUM(T.FENSHU) / (SELECT COUNT(DISTINCT KECHENG) FROM STUDENT) FROM STUDENT T WHERE T.NAME = 'wangwu' GROUP BY T.NAME); SELECT T5.NAME, T5.KECHENG, T5.FENSHU FROM (SELECT T4.NAME, T4.KECHENG, NVL(T3.FENSHU, 0) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+)) T5, (SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG) T6 WHERE T5.KECHENG = T6.KECHENG AND T5.FENSHU = T6.FENSHU; SELECT T4.KECHENG, MIN(NVL(T3.FENSHU, 0)) FENSHU FROM STUDENT T3, ((SELECT DISTINCT T1.NAME, T2.KECHENG FROM STUDENT T1, (SELECT DISTINCT KECHENG FROM STUDENT) T2)) T4 WHERE T4.NAME = T3.NAME(+) AND T4.KECHENG = T3.KECHENG(+) GROUP BY T4.KECHENG HAVING MIN(NVL(T3.FENSHU, 0))>60 [/quote] （2） 找出平均分比“王五”的平均分高的学生 答案应该没有结果，没有人的成绩比王五高，其他两人都缺考了一门。 （4） 列出各门课程的最低分学生 1 zhangsan shuxue 75 2 lisi yingyu 0 3 zhangsan yingyu 0 4 lisi yuwen 56 缺考的人当0分处理 （5） 列出所有学生都及格的课程 1 shuxue 75 只数学满足，学生都参加考试并达到60分