use Student;
create table Student(Sno varchar(20)not null,primary key(Sno), Sname varchar(20)not null,
Ssex varchar(20)not null,Sbirthday datetime,Class varchar(20));
create table Course(Cno varchar(20)not null ,primary key(Cno),Cname varchar(20)
not null,Tno varchar(20)not null );
create table Score(Sno varchar(20)not null,Cno varchar(20)not null,Degree decimal(4,1));
create table Teacher(Tno varchar(20)not null,primary key(Tno),Tname varchar(20)not null,
Tsex varchar(20)not null,Tbirthday datetime,Prof varchar(20),Depart varchar(20));
alter table Course add constraint fk1 foreign key(Tno) references Teacher(Tno);
alter table Score add constraint fk2 foreign key(Cno) references Course(Cno);
alter table Score add constraint fk3 foreign key(Sno) references Student(Sno);
alter table Score drop foreign key kf3;
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 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');
select * from Course;
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 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','助教','电子工程系');
alter table Score add primary key(Sno,Cno);
-- 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<=80 and Degree>=60;
-- 5、 查询Score表中成绩为85，86或88的记录。
select * from Score where Degree=85 or Degree=86 or Degree=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(*)from Student where class=95031;
-- 10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序） **
select Sno,Cno from Score where Degree = (select max(Degree) from Score) ;
-- 11、 查询每门课的平均成绩。
select Cno, avg(Degree) as 平均分 from Score group by Cno;
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select Sno,avg(Degree) from Score where Cno like '3%' group by Cno having count(Cno)>=5;
-- 13、查询分数大于70，小于90的Sno列。
select Sno from Score where Degree between 70 and 90;
-- 14、查询所有学生的Sname、Cno和Degree列。
select Sname,Cno,Degree from Student,Score where Score.Sno=Student.Sno;
-- 15、查询所有学生的Sno、Cname和Degree列。
select Sno,Cname,Degree from Score,Course where Course.Cno=Score.Cno;
-- 16、查询所有学生的Sname、Cname和Degree列。
Select Sname,Cname,Degree from Student,Course,Score where Student.Sno=Score.Sno and Course.Cno=Score.Cno;
-- 17、 查询“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 grade,Score where Score.Degree between low and upp;
-- 19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。**
select * from Student where Sno in(select Sno from Score where Degree>=(select Degree from Score where Sno='109'and Cno='3-105'));
-- 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。***********不会
select * from Score where Degree <(select max(Degree) from Score where Score in(select * from Score where count(Cno)>1));
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 a.Cno=b.Cno group by Cno));
-- 21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from Score where Degree>(select Degree from Score where Sno='109'and Cno='3-105');
-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select Sno,Sname,Sbirthday from student where Sbirthday in(select Sbirthday from Student where Sno='108');
-- 23、查询“张旭“教师任课的学生成绩。
select Degree from Score where Cno in(select Cno from Course where Course.Tno=(select Tno from Teacher where 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(*)>5) );
-- 25、查询95033班和95031班全体学生的记录。
select * from Student where Class='95033' or Class='95031';
-- 26、 查询存在有85分以上成绩的课程Cno.
select Cno from Score where degree in(select degree from score where degree>85);
-- 27、查询出“计算机系“教师所教课程的成绩表。
select degree from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'));
-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
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从高到低次序排序。
select Cno,Sno,degree from score where degree in (select degree from score where cno='3-105') > degree in (select degree from score where cno='3-245') order by degree desc;
-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
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;
-- 31、 查询所有教师和同学的name、sex和birthday.
select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student union select distinct Tname as name,Tsex as sex,Tbirthday as birthday from Teacher;
-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student where ssex='女' union select distinct Tname as name,Tsex as sex,Tbirthday as birthday from Teacher where tsex='女';
-- 33、 查询成绩比该课程平均成绩低的同学的成绩表。
select Sno,Cno,Degree from Score a where a.Degree<(select AVG(Degree) from Score b where a.Cno=b.Cno);
-- 34、 查询所有任课教师的Tname和Depart.
select Tname,depart from teacher;
-- 35 、 查询所有未讲课的教师的Tname和Depart.
select Tname,Depart from Teacher where Tname not in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno);
-- 36、查询至少有2名男生的班号。
select Class from student where ssex='男' group by class having count(class)>=2;
-- 37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%' ;
-- 38、查询Student表中每个学生的姓名和年龄。
select Sname,YEAR(now())-year(Sbirthday)from student;
-- 39、查询Student表中最大和最小的Sbirthday日期值。
select max(Sbirthday) as 最大,min(Sbirthday) as 最小 from student;
-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select Class,YEAR(now())-year(Sbirthday) from student order by YEAR(now())-year(Sbirthday) desc;
-- 41、查询“男”教师及其所上的课程。
select Tname,Cname from teacher,Course where Tsex='男' and teacher.Tno=Course.tno;
-- 42、查询最高分同学的Sno、Cno和Degree列。
select * from Score where Score.Degree=(select max(Degree) from Score );
-- 43、查询和“李军”同性别的所有同学的Sname.
select Sname from student where Ssex=(select Ssex from student where Sname='李军') and Sname not in ('李军');
-- 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='男');