MySQL - - 题

http://www.cnblogs.com/wangfengming/articles/7891939.html##label7

查询学习课程"python"比课程 "java" 成绩高的学生的学号;

select python.s_id,student.s_name from
(select score.num,score.s_id from course,score where course.c_id = score.c_id and course.c_name = 'python') as python ,
(select score.num,score.s_id from course,score where course.c_id = score.c_id and course.c_name = 'java') as java,
student
where python.s_id = java.s_id and python.num > java.num and python.s_id = student.s_id

查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);

select student.s_name,ROUND(AVG(score.num)) from score,student where student.s_id = score.s_id GROUP BY score.s_id HAVING avg(num) > 65

查询所有同学的姓名、选课数、总成绩；

select student.s_name,COUNT(score.s_id),sum(num) from student,score where student.s_id = score.s_id GROUP BY score.s_id

查询所有的课程的名称以及对应的任课老师姓名;

select course.c_name,teacher.t_name from course,teacher where course.t_id = teacher.t_id

查询没学过“alex”老师课的同学的姓名;

select course.c_id from teacher,course where teacher.t_id = course.t_id and teacher.t_name = 'alex'
select * from student where student.s_id not in (select score.s_id from score where score.c_id in (2,4))

查询学过'python'并且也学过编号'java'课程的同学的姓名;

select student.s_name from course,score,student where course.c_id = score.c_id and course.c_name in('python','java') and score.s_id = student.s_id
GROUP BY score.s_id HAVING COUNT(1) > 1

查询学过“alex”老师所教的全部课程的同学的姓名；

select * from score,student where student.s_id = score.s_id and score.c_id in (select c_id from teacher,course where teacher.t_id = course.t_id and teacher.t_name = 'alex') GROUP BY score.s_id HAVING COUNT(1) = (select COUNT(1) from teacher,course where teacher.t_id = course.t_id and teacher.t_name = 'alex')

查询挂科超过两门(包括两门)的学生姓名;

select student.s_name,score.num from score,student where num < 70 and student.s_id = score.s_id GROUP BY score.s_id HAVING COUNT(1) >= 2

查询有课程成绩小于60分的同学的姓名;

select DISTINCT student.s_name from score,student where score.s_id = student.s_id and score.num < 60

查询选修了全部课程的学生姓名;

select s_name from score,student where score.s_id = student.s_id GROUP BY score.s_id HAVING COUNT(1) = (select COUNT(1)-1 from course)

查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；

select DISTINCT student.s_name from student,score where student.s_id = score.s_id and score.c_id in (select score.c_id from student,score where student.s_id = score.s_id and student.s_name = '貂蝉') and student.s_name != '貂蝉'

查询学过'貂蝉'同学全部课程 的其他同学姓名；

select student.s_name from student,score where student.s_id = score.s_id and score.c_id in (select score.c_id from student,score where student.s_id = score.s_id and student.s_name = '貂蝉') and student.s_name != '貂蝉' GROUP BY score.s_id HAVING COUNT(1) = (select COUNT(1) from student,score where student.s_id = score.s_id and student.s_name = '貂蝉')

查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；

select * from score,student where student.s_id = score.s_id and score.s_id in
(select score.s_id from score GROUP BY score.s_id HAVING COUNT() =
(select COUNT() from score,student where score.s_id = student.s_id and student.s_name = '貂蝉'))
and score.c_id in(1,2) GROUP BY score.s_id HAVING COUNT(*) = 2 and student.s_name != '貂蝉'

按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分

select s.s_id ,
(select num from score,course where score.c_id = course.c_id and course.c_name = 'python' and score.s_id = s.s_id) as python,
(select num from score,course where score.c_id = course.c_id and course.c_name = 'java' and score.s_id = s.s_id) as java,
(select num from score,course where score.c_id = course.c_id and course.c_name = 'linux' and score.s_id = s.s_id) as linux,
COUNT(s.s_id),
ROUND(avg(s.num),2)
from score s GROUP BY s.s_id

统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select course.c_id as '课程ID',course.c_name,
SUM(case when num BETWEEN 85 and 100 THEN 1 else 0 end) as '[100-85]',
SUM(case when num BETWEEN 85 and 100 THEN 1 else 0 end) as '[85-70]',
SUM(case when num BETWEEN 85 and 100 THEN 1 else 0 end) as '[70-60]',
SUM(case when num < 60 THEN 1 else 0 end) as '[<60]'
from score,course
where score.c_id = course.c_id GROUP BY score.c_id

查询每门课程被选修的次数

select course.c_name,COUNT(1) from score,course where course.c_id = score.c_id GROUP BY score.c_id

查询出只选修了一门课程的学生的学号和姓名

select student.s_id,student.s_name from score,student where score.s_id = student.s_id GROUP BY score.s_id HAVING COUNT(*) = 1

查询学生表中男生、女生各有多少人

select s_sex,COUNT(*) from student GROUP BY s_sex

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select ROUND(avg(num),2),c_id from score GROUP BY c_id ORDER BY AVG(num) asc,c_id DESC

查询课程名称为“python”，且分数低于60的学生姓名和分数

select student.s_name,score.num from course,score,student where course.c_id = score.c_id and score.s_id = student.s_id and course.c_name = 'python' and score.num < 70