11、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select Cno,avg(Degree)
from Score
group by Cno having Cno like '3-%' and count(*)>5;
12、查询所有学生的Sname、Cno和Degree列。
select Student.Sname,Score.Cno,Score.Degree
from Student,Score
where Student.Sno=Score.Sno;
13、查询所有学生的Sname、Cname和Degree列。
select Student.Sname,Course.Cname,Score.Degree
from Student,Course,Score
where Student.Sno=Score.Sno and
Score.Cno=Course.Cno;
14、查询“95033”班学生的平均分。
select Student.Class,avg(Degree)
from Student,Score
where Student.Sno=Score.Sno and
Student.Class='95033';
15、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *
from Score
where Score.Cno='3-105' and
Degree>(select Degree
from Score
where Sno='109' and
Cno='3-105');
16、查询至少有2名男生的班号。
select Class
from Student
group by Class
having count('nan')>=2;
17、查询Student表中不姓“王”的同学记录。
select *
from Student
where Sname not like 'wang%';
18、查询Student表中每个学生的姓名和年龄。
select Sname,2021-year(Sbirthday)
from Student;
19、查询Student表中最大和最小的Sbirthday日期值。
select max(date(Sbirthday))
from Student
union
select min(date(Sbirthday))
from Student;
20、查询“男”教师及其所上的课程。
select Tname,Cname
from Teacher
join Course on Teacher.Tno=Course.Tno and
Teacher.Tsex='nan';
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select *
from Score
where Degree>(select Degree
from Score
where Sno='109' and
Cno='3-105');
22、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select Sno,Sname,Sbirthday
from Student
where YEAR(Sbirthday)=(select YEAR(Sbirthday)
from Student
where Sno='107');