当前位置：首页 > article >正文

SQL基础练习题三

article 2024/10/19 16:50:14

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select student.*,sc1.s_score as score01,sc2.s_score as score02 from student 
join score sc1 on student.s_id = sc1.s_id and sc1.c_id ="01"
join score  sc2 on student.s_id = sc1.s_id and sc2.c_id ="02"
where sc1.s_score>sc2.s_score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select student.*,sc1.s_score as score01,sc2.s_score as score02 from student 
join score sc1 on student.s_id = sc1.s_id and sc1.c_id ="01"
join score  sc2 on student.s_id = sc1.s_id and sc2.c_id ="02"
where sc1.s_score<sc2.s_score;

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

这个是把新生成的表放入join中和之前的表连接，我当时错误原因就是子查询放入位置错误

select student.s_id,s_name,result.course_numb,result.sum_score  from student 
join  (
SELECT score.s_id, count(*) AS course_numb,SUM(score.s_score) AS sum_score
FROM score 
GROUP BY s_id 
) as result on student.s_id = result.s_id;

4、查询学过"张三"老师授课的同学的信息

select DISTINCT student.s_id ,student.s_name,student.s_sex from student 
join score on student.s_id = score.s_id
join course on score.c_id = course.c_id
join teacher on teacher.t_id = course.t_id
WHERE teacher.t_name = '张三';