数据库课堂实验3

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');