答案（1）

答案1-27

表格：

create table student(
    sno varchar(20) not null primary key, #学号
    sname varchar(20) not null ,#学生姓名
    ssex varchar(20) not null,#学生性别
    sbirthday datetime,#学生出生年月日
    class varchar(20)  #学生所在班级
);

create table teacher(
    tno varchar(20) not null primary key,#教工编号
    tname varchar(20) not null,#教工姓名
    tsex varchar(20) not null, #教工性别
    tbirthday datetime,#教工出生年月日
    prof varchar(20),#职称
    depart varchar(20) not null #教工所在部门
);


create table course(
    cno varchar(20) not null primary key,#课程号
    cname varchar(20) not null,#课程名称
    tno varchar(20) not null 
    #foreign key(tno) references teacher (tno)
    
);

create table score(
    sno varchar(20) not null,#学号
    #foreign key(sno) references student(sno),
    cno varchar(20) not null,#课程号
    #foreign key(cno) references course(cno),
    degree decimal(4,1),#成绩
    primary key(sno,cno)
)

insert into score values ('103','3-245','86' );
insert into score values ('105','3-245','75' );
insert into score values ('109','3-245','68' );
insert into score values ('103','3-105','92' );
insert into score values ('105','3-105','88' );
insert into score values ('109','3-105','76' );
insert into score values ('101','3-105','64' );
insert into score values ('107','3-105','91' );
insert into score values ('108','3-105','78' );
insert into score values ('101','6-166','85' );
insert into score values ('107','6-166','79' );
insert into score values ('108','6-166','81' )

insert into student values('108','曾华','男','1977-09-01','95033' );
insert into student values('105','匡明','男','1975-10-02','95031' );
insert into student values('107','王丽','女','1976-01-23','95033' );
insert into student values('101','李军','男','1976-02-20','95033' );
insert into student values('109','王芳','女','1975-02-10','95031' );
insert into student values('103','陆君','男','1974-06-03','95031' )

insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

insert into course values('3-105','计算机导论','825' );
insert into course values('3-245','操作系统','804' );
insert into course values('6-166','数字电路','856' );
insert into course values('9-888','高等数学','831' )

答案：

1.

select sname,ssex,class from student

 2.

select distinct depart from teacher

3.

select * from student

4.

select * from score where degree between 60 and 80

5.

select * from score where degree in(85,86,88)

6.

select * from student where class='95031' or ssex='女'

7.

select * from student order by class desc

8.

select * from score order by cno,degree desc

9.

select count(*) from student where class='95031'

10.

select sno,cno from score where degree = (select max(degree) from score)
select sno,cno from score order by degree desc limit 0,1

11.

select cno,avg(degree) from score group by cno

12.

select avg(degree) from score where cno in(select cno from score group by cno having count(*)>=5) and cno like '3%'

13.

select sno from score where degree between 70 and 90

14.

select sname,cno,degree from student,score where student.sno = score.sno 

15.

select sno,cname,degree from score,course where score.cno = course.cno

16.

select sname,cname,degree from score,student,course where score.sno=student.sno and score.cno=course.cno

17.

select avg(degree) from score where sno in(select sno from student where class='95033')

18.

select sno,cno,rank from score,grade where score.degree between grade.low and grade.upp

19.

select * from score where cno='3-105' and degree>(select degree from score where cno='3-105' and sno='109')

20.

#最高分为所有学生的最高分
select * from score where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score)
#最高分为当前这门课程的最高分
select * from score a where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(b.degree) from score b where b.cno = a.cno)

21.

select * from score where degree>(select degree from score where sno='109' and cno='3-105')

22.

select sno,sname,sbirthday from student where YEAR(sbirthday) = (select YEAR(sbirthday) from student where sno='108')

23.

select * from score where cno in(select cno from course where tno in(select tno from teacher where tname='张旭'))

24.

select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5))

25.

select * from student where class in('95033','95031')

26.

select cno from score where degree>85

27.

select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'))