Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题
1、查询“001”课程比“002”课程成绩高的所有学生的学号; w
2、查询平均成绩大于60分的同学的学号和平均成绩;
3、查询所有同学的学号、姓名、选课数、总成绩;
4、查询姓“李”的老师的个数;
5、查询没学过“叶平”老师课的同学的学号、姓名; w
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; w
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; w
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; w
9、查询所有课程成绩小于60分的同学的学号、姓名; w
10、查询没有学全所有课的同学的学号、姓名; w
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 同上
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; w
14、查询和“1002”号的同学学习的课程数完全相同的其他同学学号和姓名; w
15、删除学习“叶平”老师课的SC表记录
16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、课程编号为2,2号课的平均成绩; w
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,物理,历史,英语,有效课程数,有效平均分 w
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
// 无用
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 像这样的情况使用程序处理效率会更高
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004) 理由同上
21、查询不同老师所教不同课程平均分从高到低显示 同上
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004)
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 同上
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 同上
// 无用
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录:(不考虑成绩并列情况) // 无用
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询姓“张”的学生名单
30、查询同名同性别学生名单,并统计同名同性别人数 w
31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime) // 放
32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 w
34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数 w
35、查询所有学生的选课情况,要求学生号,课程号,学生姓名,课程名
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; w
37、查询不及格的课程,并按课程号从大到小排列 w
38、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名;
39、求选了课程的学生人数
40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩 w
41、查询各个课程及相应的选修人数
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 w
43、查询每门功成绩最好的前两名 w
44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程
号升序排列
45、检索至少选修两门课程的学生学号
46、查询全部学生都选修的课程的课程号和课程名
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 w
48、查询两门以上不及格课程的同学的学号及其平均成绩 w
49、检索“004”课程分数小于60,按分数降序排列的同学学号
50、删除“002”同学的“001”课程的成绩
答案
1、select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2、select S#,avg(score)
from sc
group by S# having avg(score) >60;
having 不能用于应被用于where字句的条目,select a from b where a>0不能这样,select a from b having a>0
3、
SELECT SC.S, Student.Sname, count( SC.C ) , SUM( SC.score )
FROM Student, SC
WHERE Student.S = SC.S
GROUP BY S
4、select count(distinct(Tname)) from Teacher where Tname like "李%"
distinct 检索出唯一的记录
5、select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
6、select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
exists求的是两者的交集,表示存在的意思
http://shenzhenchufa.blog.51cto.com/730213/269152
select Student.S,Student.Sname from Student,SC where Student.S=SC.S and SC.C=1 and exists(select * from SC t1 where t1.S = SC.S and t1.C = 2)
7、select S,Sname from Student where S in (select S from SC ,Course ,Teacher where SC.C=Course.C and Teacher.T=Course.T and Teacher.Tname='叶平' group by S having count(SC.C)=(select count(C) from Course,Teacher where Teacher.T=Course.T and Tname='叶平'));
下面的也是正确的
select Student.S,Student.Sname from Student,SC,Course,Teacher where Student.S = SC.S and SC.C=Course.C and Course.T = Teacher.T and Teacher.Tname = "叶平" group by S having count(-1) = (select count(-1) from Teacher,Course where Course.T = Teacher.T and Tname = "叶平")
8、select S,Sname from (select Student.S,Student.Sname,score,(select score from SC SC_2 where SC_2.S = Student.S and SC_2.C = 2)score2 from Student,SC where Student.S = SC.S and SC.C=1 )S_1 where score2<score
9、select S,Sname from Student where S not in (select distinct(Student.s) from Student,SC where Student.S=SC.S and score>60)
10、select Student.S,Student.Sname from Student,SC where Student.S = SC.S group by Student.S,Student.Sname having count(-1) < (select count(-1) from Course);
11 、select distinct(Student.S),Sname from Student,SC where Student.S = SC.S and SC.C in (select C from SC where S =1)
12 、select distinct(Student.S),Sname from Student,SC where Student.S = SC.S and SC.C in (select C from SC where S =1)
13、update SC set score = (select avg(SC2.score) from SC,(select SC.score,SC.C from SC,Teacher,Course where SC.C=Course.C and Course.T = Teacher.T and Teacher.Tname = '叶平')SC2 where SC.C = SC2.C) ??
14、select S from SC where s != 2 group by s having count(*)= (select count(*) from sc where s='2')
15、delete from sc where C in (select C from Course,Teacher where Course.T = Teacher.T and Teacher.Tname = "叶平")
16、insert into SC (S,C,score) select S,2,(select avg(score) from SC where C=2) from Student where S not in (select S from SC where C=3)
17、
SELECT S AS 学生ID ,
( SELECT score FROM SC , Course WHERE SC . S = t . S AND Course . C = SC . C AND Course . Cname = '物理' ) AS 物理 ,
( SELECT score FROM SC , Course WHERE SC . S = t . S AND Course . C = SC . C AND Course . Cname = '历史' ) AS 历史 ,
( SELECT score FROM SC , Course WHERE SC . S = t . S AND Course . C = SC . C AND Course . Cname = '英语' ) AS 英语 ,
count ( - 1 ) AS 有效课程 , round(AVG ( t . score ),2) AS 有效平均分
FROM SC AS t
GROUP BY S
ORDER BY AVG ( t . score ) desc
18 、
select C as 课程ID,
(select max(score) from SC where SC.C = t.C) as 最高分,
(select min(score) from SC where SC.C = t.C) as 最低分
from SC as t
group by C
24、select round(avg(score),2) as avgs from sc group by S order by avgs
26、select count(-1) from SC group by C
27、select Student.S,Sname from Student,SC where Student.S = SC.S group by SC.S,Sname having count(-1) <2
一般的都是会把select之后的列都放在group by 后面
28、select count(-1) as 人数,Ssex from Student group by Ssex
29、select Sname from student where Sname like "张%";
30、select Sname,count(-1) from Student group by Sname,Ssex having count(-1) >2
32、select C,avg(score) as avgs from SC group by C order by avgs,C desc
33、select Student.S,Sname,avg(score) from Student,SC where Student.S = SC.S group by Student.S,Sname having avg(score) > 85
34、select Sname,ifnull(score,0) from Student,SC,Course where Course.C = SC.C and Student.S = SC.S and Cname = '物理' and score<60 ifnull函数score,如果不是Null则返回score,如果为null返回0
35、select SC.S,SC.C,Sname,Cname from Student,SC,Course where Student.S = SC.S and SC.C= Course.C
36、select distinct(Sname),Cname,score from Student,SC,Course where Student.S = SC.S and SC.C = Couse.C and score >70
37、select distinct(C) from SC where score < 60 order by C desc
38、select Studnet.S,Sname from Student,SC where C = 3 and score>80 and SC.S = Student .S
39、select count(distinct(S)) from sc;
40、select ST.Sname,score from Student ST,SC ,Course,Teacher where ST.S = SC.S and SC.C = Course.C and Course.T = Teacher.T and Teacher.Tname = '叶平' and score = (select max(score) from SC where C = Course.C)
select ST.Sname,max(score) from Student ST,SC ,Course,Teacher where ST.S = SC.S and SC.C = Course.C and Course.T = Teacher.T and Teacher.Tname = '叶平'
41、select C,count(-1) from SC group by C
42、select distinct(A.S),B.score,A.C from SC A ,SC B where A.Score=B.Score and A.C <>B.C 没做出
43、select C,score from SC t1 where 2 > (select count(*) from SC where C = t1.C and score > t1.score ) order by C,score desc
44、select C as 课程号,count(-1) as 人数 from SC group by C having count(-1) >= 3 order by count(-1) desc , C
45、select S from SC group by S having count(-1) >2
46、
select C,Cname from Course where C in (select C from SC group by C having count(S) = (select count(-1) from Student))
select SC.C,Course.Cname from SC,Course where SC.C = Course.C group by SC.C having count(-1) = (select count(-1) from Student)
47、
select Sname from Student where S not in (select S from Course,Teacher,SC where Course.T=Teacher.T and SC.C= Course.C and Tname='叶平')
下面的是不正确的
select Sname from Student,SC where Student.S = SC.S and SC.C not in (select SC.C from SC,Course,Teacher where SC.C=Course.C and Course.T=Teacher.T and Teacher.Tname = "叶平")
48、这个可以从两个方面来考虑首先解决的是查找平均分的问题,然后再找约束条件就是两门以上不及格课程的同学
select S,avg(score) from SC group by S 这个是符合第一个条件,
select S from SC where score< 60 group by S having count(-1)>2 这是符合第二个条件
组合就是
select S ,avg(score) from SC where S in (select S from SC where score<60 group by S having count(-1) >2) group by S
这样分析起来比较简单,将复杂的问题简单化
49、select S from SC where C = 4 and scors < 60 order by scors desc
50、delete from SC where S = 002 and C = 001
其他
1 排重语句
http://blog.csdn.net/softwave/archive/2009/02/14/3890576.aspx
http://topic.csdn.net/u/20100604/14/af50e4cb-ddf7-443e-abbf-d6ccc1e99d41.html
总结
1 group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by后面。
比如 select name,sum(point) from table_name
这样sql语句会报错,必须写成:
select name,sum(point) from table_name GROUP BY name
就是select后面name没有使用聚合函数所以必须出现在group by 后面
2 聚合函数
http://database.51cto.com/art/201010/229363.htm
3 把 HAVING 加入 SQL 的原因是,WHERE 无法应用于合计函数,而如果没有 HAVING,就无法测试结果条件。
select name,sum(point)
from table_name GROUP BY name
HAVING sum(point)>1000
having通常和group by联合使用
出处 http://www.cnblogs.com/buzaixian/archive/2009/09/21/1571365.html