create database school;

#学生表

create table `Student`

(

  `Sno` varchar(20) not null COMMENT '人名',

  `Sname` varchar(20) not null COMMENT '姓名',

  `Ssex` varchar(20) not null COMMENT '性别',

  `Sbirthday` datetime COMMENT '出生日期',

  `Class` varchar(20) COMMENT '班级'

);

#课程表

create table `Course`(

  `Cno` varchar(20) not null COMMENT '课程号',

  `Cname` varchar(20) not null COMMENT '课程名称',

  `Tno` varchar(20) not null COMMENT '教工编号'

);

#成绩表

Create table `Score` (

  `Sno` varchar(20) not null COMMENT '学号',

  `Cno` varchar(20) not NULL comment '课程号',

  `Degree` DECIMAL(4,1) null COMMENT '成绩'

);

#教师表

create table `Teacher` (

  `Tno` varchar(20) not null COMMENT '教工编号',

  `Tname` varchar(20) not null COMMENT '教工姓名',

  `Tsex` varchar(20) not null COMMENT '教工性别',

  `Tbirthday` datetime null COMMENT '教工出生日期',

  `Prof` varchar(20) null COMMENT '职称',

  `Depart` varchar(20) null COMMENT '教工所在部门'

);

insert into `Student` value

(108,'曾华','男','1977-09-01',95033),

(105,'匡明','男','1975-10-02',95031),

(107,'王丽','女','1976-01-23',95033),

(101,'李军','男','1976-02-20',95033),

(109,'王芳','女','1975-02-10',95031),

(103,'陆军','男','1974-06-03',95031);

insert into `Course` value

("3-105","计算机导论",825),

("3-245","操作系统",804),

("6-166","数字电路",856),

("9-888","高等数学",831);

insert into `Score` value

(103,"3-245",86),

(105,"3-245",75),

(109,"3-245",68),

(103,"3-105",92),

(105,"3-105",88),

(109,"3-105",76),

(101,"2-105",64),

(107,"3-105",91),

(108,"3-105",78),

(101,"6-166",85),

(107,"6-166",79),

(108,"6-166",81);

insert into `Teacher` value

(804,'李城','男','1958-12-02','副教授','计算机系'),

(856,'张旭','男','1969-03-12','讲师','电子工程系'),

(825,'王萍','女','1972-05-05','助教','计算机系'),

(831,'刘冰','女','1977-08-14','助教','电子工程系');

1查询Student表中的所有记录的Sname、Ssex和Class列。

select sname,ssex,class from student;

2查询教师所有的单位即不重复的Depart列

select distinct depart from teacher;

3查询Student表的所有记录

select * from student;

4查询Score表中成绩在60到80之间的所有记录。

select * from score where degree>=60 and degree<=80;

或者：select * from score where degree between 60 and 80;

5查询Score表中成绩为85，86或88的记录。

select * from score where degree=85 or degree=86 or degree=88;

或者：select * from score where degree in (85,86,88);

6查询Student表中“95031”班或性别为“女”的同学记录。

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

7以Class降序查询Student表的所有记录

select * from student order by class desc;

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

select * from score order by degree desc,cno asc;

9查询"95031"班的学生人数.

select count(1) from student where class='95031';

10查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

select sno,cno from score where degree=(select max(degree) from score);

或者：select sno,cno from score order by degree desc limit 1;

11查询每门课的平均成绩。(group by:根据Cno进行分组)

select cno,avg(degree) as 平均分 from score group by cno;

12查询Score表中至少有5名学生选修的并以3开头的课程的平均分数.(单表查询)

select cno,avg(degree) from score where cno like “3-%” group by cno having count(cno)>=5;

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

13查询分数大于70，小于90的Sno列。(单表查询)

select sno from score where degree >70 and degree < 90;

或者：select sno from score where degree between 70 and 90;

14查询所有学生的Sname、Cno和Degree列.（多表查询）

select b.sname,a.cno,a.degree from score a inner join student b on a.sno=b.sno;

或者：select sname,cno,degree from student,score where student.sno=score.sno;

15查询所有学生的Sno、Cname和Degree列。(3表连接查询)

select a.sno,b.degree,c.cname from student a inner join score b on a.sno=b.sno

left join course c on b.cno=c.cno;

或者：select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

16查询所有学生的Sname、Cname和Degree列.（3表连接查询）

select a.sname,c.cname,b.degree from student a,score b,course c where a.sno=b.sno and b.cno=c.cno;

或者：select a.sname,c.cname,b.degree from student a inner join score b on a.sno=b.sno left join course c on b.cno=c.cno;

17查询“95033”班学生的平均分.(2表连接查询)

select avg(degree) from score,student where student.sno=score.sno and slass='95033';

或者：select avg(degree) from student a inner join score b on a.sno=b.sno and a.class='95033';

或者：select avg(degree) from student a inner join score b on a.sno=b.sno where class='95033';

或者：select avg(degree) from score where sno in (select sno from student where class='95033');

18假设使用如下命令建立了一个grade表：

create table grade(low  int(3),upp  int(3),rank  char(1));

insert into grade values(90,100,"A");

insert into grade values(80,89,"B");

insert into grade values(70,79,"C");

insert into grade values(60,69,"D");

insert into grade values(0,59,"E");

查询所有同学的Sno、Cno和rank列（成绩排名）.

select Sno,Cno,rank from Score,grade where Degree between low and upp;

这道题等同于[0,60)为E，[60,70)为D,[70,80)为C,(80,90]为B,(90,100]为A；用case when

select

   sno,cno,

   case when degree<60 and degree>=0 then "E"

        when degree>=60 and degree<70 then "D"

        when degree>=70 and degree<80 then "C"

        when degree>=80 and degree<90 then "B"

        when degree>=90 and degree<100 then "A" end as rank

from score;

19查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

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

20查询score表中选学多门课程的同学中分数为非最高分成绩的记录

select * from score a where sno in (select sno from score group by sno having count(*)>1) and degree<( select max(degree ) from score b where b.cno = a.cno);

或者：select * from score a where Sno in (select Sno from score group by Sno having count(*)>1) and degree not in (select max(degree ) from Score b where b.cno = a.cno);

21查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

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

22查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列

select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno="108" or sno="101");

23查询“张旭“教师任课的学生成绩。

第一种方法：select sno,degree from Score where cno in (select sno,degree from score where tno in (select tno from teacher where tname = '张旭'));

第二种方法：select sno,degree from score a,course b,teacher c where a.cno=b.cno and b.tno=c.tno and tname = '张旭';

第三种方法：select a.sno,b.degree from student a left join score b on a.sno=b.sno

left join course c on b.cno=c.cno left join teacher d on c.tno=d.tno where d.tname="张旭";

24查询选修某课程的同学人数多于5人的教师姓名。

第一种方法：select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(1)>=5));

第二种方法：select tname from teacher a,course b,score c where a.tno=b.tno and b.cno=c.cno group by c.cno having count(c.sno)>=5;

25查询95033班和95031班全体学生的记录。

第一种方法（复杂）：select * from student a,score b,course c,teacher d where a.sno=b.sno and b.cno=c.cno and c.tno=d.tno and class = '95033' or class= '95031' group by a.sno;

第二种方法（简单）：select * from student where class="95033" or class="95031";

26查询存在有85分以上成绩的课程Cno.

第一种方法：select cno from score where degree>=85 group by cno;

第二种方法：select distinct 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="计算机系" ));

第二种方法：select a.* from score a,course b,teacher c where a.cno=b.cno and b.tno=c.tno and depart="计算机系";

28查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select Tname,Prof from Teacher where Prof not in (

select Prof from Teacher where Prof in (select Prof from Teacher where Depart = '计算机系') and Depart = '电子工程系');

select Tname,Prof from Teacher where Prof not in (select Prof from Teacher where Depart = '计算机系' or Depart ='电子工程系' group by Prof having count(*)>1);

select Tname,Prof from Teacher where Depart ='计算机系' and Prof  not in( select Prof from Teacher where Depart ='电子工程系')

union

select Tname,Prof from Teacher where Depart ='电子工程系' and Prof  not in( select Prof from Teacher where Depart ='计算机系');

29查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

any:代表括号中任意一个成绩就可以

第一种方法：select cno,sno,degree from score where cno="3-105" and degree >any(select degree from score where cno="3-245") order by degree desc; 

第二种方法：select cno,sno,degree from score where cno = '3-105' and degree >

(select min(degree) from score where cno = '3-245') order by degree desc;

30查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree。

all:代表括号中的所有成绩

第一种方法：select cno,sno,degree from score where cno="3-105" and degree >(select max(degree) from score where cno="3-245") order by degree desc;

第二种方法：select cno,sno,degree from score where cno="3-105" and degree >all(select degree from score where cno="3-245") order by degree desc;

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

第一种union：select Sname as ”姓名“,Ssex as "性别",Sbirthday as "出生日期" from Student

union

select Tname,Tsex,Tbirthday from Teacher;

第二种要以teacher表为主表连接

select d.tname,a.sname,d.tsex,a.ssex,d.tbirthday,a.sbirthday

from teacher d left join course c on c.tno=d.tno left join score b on b.cno=c.cno

left join student a on a.sno=b.sno;

32查询所有“女”教师和“女”同学的name、sex和birthday。

select sname as name,ssex as sex,sbirthday as birthday from student where ssex="女"

union

select tname,tsex,tbirthday from teacher where tsex="女";

33查询成绩比该课程平均成绩低的同学的成绩表。

第一种方法：select * from score a where degree <(select avg(degree) as avg_score from score b where a.cno=b.cno);

第二种方法：select * from score a left join

(select cno,sno,avg(degree) as avg_score from score b group by cno) b  on a.cno=b.cno

where a.degree

34查询所有任课教师的Tname和Depart.

第一种方法：select tname,depart from teacher; 

第二种方法：select tname,depart from teacher where tno in (select tno from course);

35查询所有未讲课的教师的Tname和Depart.

select tname,depart from teacher where tno not in (select tno from course where cno in (select cno from score));

36查询至少有2名男生的班号.

select class from student where ssex="男" group by class having count(ssex)>=2;

select class from student where ssex="男" group by class having count(1)>1;

37查询Student表中不姓“王”的同学记录.

select * from student where sname not like "王%%";

38查询Student表中每个学生的姓名和年龄。

第一种方法：select sname,timestampdiff(year,sbirthday,now()) as age from student;

第二种方法：select sname,year(now())-year(sbirthday) as age from student;

39查询Student表中最大和最小的Sbirthday日期值。

select Max(sbirthday),Min(sbirthday) from student;

40以班号和年龄从大到小的顺序查询Student表中的全部记录。

第一种方法：select *,timestampdiff(year,sbirthday,now()) as age from student order by age,class desc;

第二种方法：select * from student order by class desc,sbirthday;

41查询“男”教师及其所上的课程

第一种方法：select * from course where tno in (select tno from teacher where tsex="男");

第二种方法：select tname,cname from course a,teacher b where a.tno=b.tno and b.tsex="男";

第三种：select * from course a left join teacher b on a.tno=b.tno where b.tsex="男";

42查询最高分同学的Sno、Cno和Degree列。

第一种方法：select sno,cno,degree from score where degree=(select max(degree) from score);

第二种方法：select  Sno,Cno,Degree from score order by degree desc limit 0,1;

扩展：查询各科最高分的同学的sno，cno和degree.

select sno,cno,degree from score a where degree=(select max(degree) from score b where a.cno=b.cno);

43查询和“李军”同性别的所有同学的Sname.

select sname from student where ssex=(select ssex from student where sname="李军");

44查询和“李军”同性别并同班的同学Sname.

select sname from student where ssex=(select ssex from student where sname="李军") and class=(select class from student where sname="李军");

45查询所有选修“计算机导论”课程的“男”同学的成绩表。

第一种方法：select * from score where cno in (select cno from course where cname="计算机导论" and sno in (select sno from student where ssex="男"));

第二种方法：select * from course a left join score b on a.cno=b.cno left join student c

on b.sno=c.sno where a.cname="计算机导论" and c.ssex="男";