学生表、课程表、 成绩表 、教师表sql练习

[sql]代码库

<!--http://www.cnblogs.com/qixuejia/p/3637735.html-->
1、查询“1”课程比“2”课程成绩高的所有学生的学号；
SELECT t1.student_id 
FROM
	(SELECT score, student_id FROM student_score WHERE course_id = 1 ) t1,
	(SELECT score, student_id FROM student_score WHERE course_id = 2 ) t2
WHERE t1.score > t2.score AND t1.student_id = t2.student_id

2、查询平均成绩大于60分的同学的学号和平均成绩：
select student_id, avg(score) from student_score group by student_id HAVING avg(score)>=60 ;

3、查询所有同学的学号、姓名、选课数、总成绩； 
select ss.student_id, st.student_name, count(course_id), sum(score) from student_score ss 
left join student st on ss.student_id=st.student_id
group by ss.student_id, st.student_name
order by ss.student_id

4、查询姓“李”的老师的个数；
select count(teacher_name) from teacher where teacher_name like '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名； 
select student_id,student_name from student where student_id not in (
	select ss.student_id from 
	student_score ss 
	left join course co on ss.course_id=co.course_id
	left join teacher te on te.teacher_id= co.teacher_id
	where te.teacher_name='叶平'
	group by ss.student_id
)

6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名； 
select st.student_id,st.student_name from student st 
inner join (select student_id from student_score where course_id=1) t1 on st.student_id=t1.student_id
inner join (select student_id from student_score where course_id=2) t2 on t1.student_id=t2.student_id

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT
	st.student_id,st.student_name
FROM student_score ss
inner join student st on ss.student_id=st.student_id
inner join course co on ss.course_id=co.course_id
inner join teacher te on te.teacher_id=co.teacher_id
where te.teacher_name='叶平'

8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名； 
select st.student_id,st.student_name from student st 
inner join (select student_id, score from student_score where course_id=1) t1 on st.student_id=t1.student_id
inner join (select student_id, score from student_score where course_id=2) t2 on t1.student_id=t2.student_id
where t2.score<t1.score

9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT st.student_id,st.student_name FROM student st WHERE st.student_id not in (
	SELECT DISTINCT ss.student_id FROM student_score ss
	WHERE ss.score>=60
)

10、查询没有学全所有课的同学的学号、姓名；
select st.student_id, st.student_name from student_score ss
inner join student st on st.student_id=ss.student_id
group by st.student_id,st.student_name
having count(ss.course_id)<(select count(course_id) from course)

11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select st.student_id,st.student_name from 
student st inner join student_score ss on st.student_id=ss.student_id
where course_id in(select course_id from student_score where student_id=1)
group by st.student_id,st.student_name

14、查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名； 
select ss.student_id from student_score ss 
inner join student st on ss.student_id=st.student_id
where ss.course_id in (select course_id from student_score where student_id=1)
group by ss.student_id
having count(ss.course_id)=(select count(course_id) from course)

17、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门课程成绩
SELECT ss.student_id,
	avg(ss.score) as 平均成绩,
	(select score from student_score
	where course_id = (SELECT course_id FROM course  WHERE course_name ='语文')
	and student_id = ss.student_id) as 语文,
	(select score from student_score
	where course_id = (SELECT course_id FROM course  WHERE course_name ='数学')
	and student_id = ss.student_id) as 数学,
	(select score from student_score
	where course_id = (SELECT course_id FROM course  WHERE course_name ='英语')
	and student_id = ss.student_id) as 英语
FROM student_score ss 
GROUP BY ss.student_id,语文,数学,英语
ORDER BY avg(ss.score)  DESC

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT course_id AS 课程id, MAX (score) AS 最高分, MIN (score) AS 最低分
FROM student_score
GROUP BY course_id

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 
SELECT
	co.course_name AS 科目名称,
	avg (ss.score) as 平均成绩,
	CAST (CAST (SUM(case WHEN COALESCE(ss.score,0)>=60 THEN 1 ELSE 0 END) AS FLOAT)/
	(SELECT COUNT(*) FROM course) AS DECIMAL (10, 2))
FROM
	student_score ss
INNER JOIN course co ON ss.course_id = co.course_id
GROUP BY
	co.course_name

或者

SELECT
	student_id,
	CAST (AVG(score) AS DECIMAL(10, 2)) AS 平均成绩,
	CAST (CAST (COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) AS 及格率
FROM student_score
WHERE 	score >= 60
GROUP BY student_id
ORDER BY AVG (score),CAST (CAST (COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) desc

24、查询学生平均成绩及其名次 
SELECT 1 + (
		SELECT COUNT (DISTINCT 平均成绩)
		FROM
			( SELECT student_id, AVG (score) AS 平均成绩 FROM student_score GROUP BY student_id ) AS T1
		WHERE 平均成绩 > T2.平均成绩
	) AS 名次,
	student_id AS 学生学号,
	平均成绩
FROM
	( SELECT student_id, AVG (score) 平均成绩 FROM student_score GROUP BY 	student_id ) AS T2
ORDER BY
	平均成绩 DESC;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT ss.student_id, ss.course_id, ss.score
FROM student_score ss
GROUP BY ss.student_id, 	ss.course_id, ss.score
HAVING student_id in (select student_id from student_score where ss.course_id=course_id LIMIT  3)
ORDER BY ss.course_id, ss.score

26、查询每门课程被选修的学生数 
SELECT course_id, COUNT (course_id)
FROM student_score
GROUP BY 	course_id
ORDER BY 	course_id

27、查询出只选修了一门课程的全部学生的学号和姓名
SELECT ss.student_id,st.student_name, COUNT (ss.course_id)
FROM student_score ss
inner join student st on st.student_id=ss.student_id
GROUP BY 	ss.student_id,st.student_name
having COUNT (ss.course_id)=1
ORDER BY 	ss.student_id

28、查询男生、女生人数 
SELECT student_sex, COUNT (student_id)
FROM student
GROUP BY student_sex

29、查询姓“张”的学生名单 
SELECT * FROM	student WHERE student_name LIKE '张%'

30、查询同名同性学生名单，并统计同名人数 
SELECT student_name,count(*) FROM	student group by student_name having count(*)>1

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 
select course_id,avg(score) from student_score group by course_id order by avg(score) asc,course_id

33、查询平均成绩大于70的所有学生的学号、姓名和平均成绩 
SELECT ss.student_id,st.student_name, AVG (ss.score)
FROM student_score ss
inner join student st on ss.student_id=st.student_id
GROUP BY ss.student_id,st.student_name
ORDER BY AVG (ss.score) ASC, ss.student_id

34、查询课程名称为“语文”，且分数低于60的学生姓名和分数 
select st.student_name,ss.score 
from student_score ss 
inner join student st on ss.student_id=st.student_id
inner join course co on ss.course_id=co.course_id
where co.course_name='语文' and ss.score<60

35、查询所有学生的选课情况；
select st.student_name,co.course_name,ss.score 
from student_score ss 
inner join student st on ss.student_id=st.student_id
inner join course co on ss.course_id=co.course_id

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select st.student_name,co.course_name,ss.score 
from student_score ss 
inner join student st on ss.student_id=st.student_id
inner join course co on ss.course_id=co.course_id
where ss.score>70

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
select st.student_name,co.course_name,te.teacher_name,ss.score 
from student_score ss 
inner join student st on ss.student_id=st.student_id
inner join course co on ss.course_id=co.course_id
inner join teacher te on co.teacher_id=te.teacher_id
where te.teacher_name='叶平'
order by ss.score desc
limit 1

41、查询各个课程及相应的选修人数
select course_id,count(course_id) from student_score group by course_id order by course_id

43、查询每门功成绩最好的前两名 
SELECT
	t0.student_id, t0.course_id, t0.score
FROM student_score t0
WHERE
	t0.student_score_id IN (
		SELECT student_score_id
		FROM student_score
		WHERE t0.course_id = course_id
		ORDER BY 	score DESC
		limit 2
	)

44、统计每门课程的学生选修人数，要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人	数相同，按课程号升序排列 
select course_id,count(student_id) from student_score group by course_id order by count(student_id) desc, course_id

45、检索至少选修两门课程的学生学号
select student_id,count(*) 
from student_score 
group by student_id 
having count(*)>=2
order by count(*) desc, student_id

46、查询全部学生都选修的课程的课程号和课程名 
SELECT co.course_id, co.course_name
FROM student_score ss
INNER JOIN course co ON co.course_id = ss.course_id
GROUP BY co.course_id, co.course_name
HAVING COUNT(co.course_id) = (SELECT COUNT (student_id) FROM student)

48、查询两门以上不及格课程的同学的学号及其平均成绩 
select student_id,avg(score) from student_score where score<60 group by student_id having count(1)>=2