错误练习

【SQL练习】经典SQL练习题 - CSDN博客

8、 以Cno升序、Degree降序查询Score表的所有记录。------------------------------------------order by

select *
from mysql_practice.scores
order by cno ,degree desc

9、 查询“95031”班的学生人数。------------------聚合函数(aggregate function)不能用在where子句中，having中可以

SELECT COUNT(1) AS StuNum
FROM Students
WHERE Class='95031'

5、 查询Score表中成绩为85，86或88的记录。--------------------------------------------in 指定条件范围

select *
from mysql_practice.scores
where degree in(85,86,88)

10、查询Score表中的最高分的学生学号和课程号。-----------------------------------------查询一行数据

SELECT Sno,Cno
FROM Scores
ORDER BY Degree DESC
LIMIT 1

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。----------------------like 匹配通配符

select cno,avg(degree) as avg
from scores
where cno like'3%'
group by cno
having count(cno)>=5

14、查询所有学生的Sname、Cno和Degree列。----------------------------------inner join table on()两表连接

select sname,cno,degree
from students inner join scores
on(students.sno=scores.sno)
order by sname

16、查询所有学生的Sname、Cname和Degree列。

select sname,cname,degree
from courses ,scores,students
where courses.cno=scores.cno and scores.sno=students.sno

SELECT Sname,Cname,Degree
FROM Students INNER JOIN Scores
ON(Students.Sno=Scores.Sno) INNER JOIN Courses
ON(Scores.Cno=Courses.Cno)
ORDER BY Sname;

17、查询“95033”班所选课程的平均分。

select cname,avg(degree)-------------------------------要把95033班的课程分出来之后在计算每个课的平均分

from students,scores,courses
where students.sno=scores.sno
and scores.cno=courses.cno
and class=95033
group by courses.cno 

18.现查询所有同学的Sno、Cno和rank列。------------------------范围

select sno,cno,rank
from scores inner join grade
on(scores.degree>=grade.low and scores.degree<=grade.upp)

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。-------------------同一张表当两张用

select s1.sno,s1.degree
from scores as s1 inner join scores as s2
on(s1.cno=s2.cno and s1.degree>s2.degree)
where s1.cno='3-105' and s2.sno='109'
order by s1.sno

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。---------------------

select sno,cno,degree
from scores
group by sno
having count(cno)>1 and degree!=max(degree)

31、查询所有教师和同学的name、sex和birthday.------------------------------union

select tname,tsex,tbirthday
from teachers
union
select sname,ssex,sbirthday
from students