SQL实训50题

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

select s_id,c_id,score as score_01 from stu_sco where c_id='01';
​
select s_id,c_id,score as score_02 from stu_sco where c_id='02';
​
-- 然后我们就可以通过多表查询得到那些"01"课程比"02"课程成绩高的学生s_id
select a.s_id from (select s_id,c_id,score as score_01 from stu_sco where c_id='01') a
inner join 
(select s_id,c_id,score as score_02 from stu_sco where c_id='02')b
on a.s_id=b.s_id and a.score_01>b.score_02;
​
-- 得到"01"课程比"02"课程成绩高的学生s_id后，利用这些s_id
select a.*,c.c_id,c.score from student a left join 
(select a.s_id from (select s_id,c_id,score as score_01 from stu_sco where c_id='01') a
inner join 
(select s_id,c_id,score as score_02 from stu_sco where c_id='02')b
on a.s_id=b.s_id and a.score_01>b.score_02) b
on a.s_id=b.s_id
inner join stu_sco c on b.s_id=c.s_id;
​
-- 上面的方法的逻辑理解简单，便于根据类似的题型作出灵活修改，但是写起来繁琐。
-- 如果题目不要求查询出目标对象的所有课程，只要求查询出‘01’和‘02’课程，我们可以做两次内连接，学生表内连接到分数表a，再内连接到分数表b，再作筛选，是可以实现相同的查询目的的。
select a.*,b.score as '语文',c.score as '数学' from student a 
inner join stu_sco b on a.s_id=b.s_id and c_id ='01' 
inner join stu_sco c on b.s_id=c.s_id and c.c_id ='02' 
where b.score>c.score;

 

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

select a.*,c.c_id,c.score from student a left join 
(select a.s_id from (select s_id,c_id,score as score_01 from stu_sco where c_id='01') a
inner join 
(select s_id,c_id,score as score_02 from stu_sco where c_id='02')b
on a.s_id=b.s_id and a.score_01<b.score_02) b
on a.s_id=b.s_id
inner join stu_sco c on b.s_id=c.s_id;

 

--3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT student.s_id,student.s_name,stu_sco.score FROM student RIGHT JOIN 
(SELECT stu_sco.AVG(score) FROM stu_sco GROUP BY s_id)stu_sco on student.s_id=stu_sco.s_id WHERE stu_sco.score >= 60 ORDER BY student.s_id;

 

--4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT student.s_id,student.s_name,stu_sco.score FROM student RIGHT JOIN 
(SELECT stu_sco.AVG(score) FROM stu_sco GROUP BY s_id)stu_sco on student.s_id=stu_sco.s_id WHERE stu_sco.score =< 60 ORDER BY student.s_id;

 

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

SELECT student.s_id,student.s_name,course.c_name,stu_sco.score FROM 
student RIGHT JOIN stu_sco ON student.s_id=stu_sco.s_id RIGHT JOIN course 
ON stu_sco.c_id=course.c_id

 

--6、查询"李"姓老师的数量

SELECT * FROM teacher WHERE t_name LIKE '李%';

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

SELECT teacher.t_name,course.c_name,student.s_id,student.s_name,student.s_age FROM teacher RIGHT JOIN course 
on teacher.t_id=course.t_id RIGHT JOIN stu_sco ON course.c_id=stu_sco.c_id RIGHT JOIN student 
ON stu_sco.s_id=student.s_id WHERE t_name = '张三'

 

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

select a.*,b.c_id from student a inner join stu_sco b 
on a.s_id=b.s_id and b.c_id in (select b.c_id from teacher a , course b where a.t_id=b.t_id and a.t_name!='张三');

 

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT student.s_id,student.s_name,student.s_age,student.s