/*建表==================================*/create table `course` (    `Cno` VARCHAR(50),    `Cname` VARCHAR(50),    `Tno` int)insert into course (`Cno`,`Cname`,`Tno`) values ('3-105','计算机导论','825');insert into course values ('3-245','操作系统','804');insert into course values ('6-166','数字电路','856'),('9-888','高等数学','831');delete from course where course.con in ('6-166') /*delete 不支持别名*/delete from course as c where c.con in ('6-166')create table `grade`(    `ids` int ,    `low` int ,    `upp` int ,    `rank` CHAR(5)) insert into grade values ('1','90','100','A'),('2','80','89','B'),('3','70','79','C'),('4','60','69','D'),('5','0','59','E')create table `score` (    `Sno` int,    `Cno` VARCHAR(50),    `Degree` int )insert into `score` values ('101','3-105','64'),('101','6-166','85'),('103','3-105','92'),('103','3-245','86'),('105','3-105','88'),('105','3-245','75'),('107','3-105','91'),('107','6-166','79'),('108','3-105','78'),('108','6-166','81'),('109','3-105','76'),('109','3-245','68')create table `student` (    `Sno` INT,    `Sname` VARCHAR(50),    `Ssex` CHAR(5),    `Sbirthday` VARCHAR (50),    `Class` VARCHAR(20))INSERT into student VALUES ('101','李军','男','1976-02-20 00:00:00','95033'),('103','陆君','男','1974-06-03 00:00:00','95031'),('105','匡明','男','1975-10-02 00:00:00','95031'),('107','王丽','女','1976-01-23 00:00:00','95033'),('108','曾华','男','1977-09-01 00:00:00','95033'),('109','王芳','女','1975-02-10 00:00:00','95031')create table `teacher` (    `Tno` INT,    `Tname` VARCHAR(20),    `Tsex` CHAR(5),    `Tbirthday` VARCHAR(50),    `Prof` VARCHAR (20),    `Depart` VARCHAR (50))INSERT into teacher values ('804','李诚','男','1958-12-02 00:00:00','副教授','计算机系'),('825','王萍','女','1972-05-05 00:00:00','助教','计算机系'),('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系'),('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系')/*解题========================================*//*1*/SELECT sname,ssex,class from student/*2*/select depart from teacher GROUP BY depart/*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 in ('95031') and ssex in ('女')/*7*/select * from student ORDER BY classselect * from student ORDER BY class DESC /*8*/select *from score ORDER BY Cno,degree DESC/*9*/select COUNT(1) from studentGROUP BY classHAVING class in ('95031')/*10*/select sno,cno from scorewhere degree in (select MAX(degree) from score)/*11*/select cno,SUM(degree) from score GROUP BY cno/*12*/select cno,SUM(degree) as su from score where cno like '3%'GROUP BY snoORDER BYsu DESCLIMIT 0,5/*13*/select sno from score where degree>70 and degree<90/*14*/select sname,cno,degree from student as sjoin score as con s.sno = c.sno/*15*/select s.sno,co.cname,c.degree from student as s join score as c on s.sno = c.sno join course as coon c.cno = co.cno/*16*/select s.sname,co.cname,c.degree from student as s join score as c on s.sno = c.sno join course as coon c.cno = co.cno/*17*/select avg(degree) from score as cJOIN student as son  c.sno = s.snoGROUP BY s.classHAVINGs.class in ('95033')/*18*/select sno,cno,degree,rank from score,gradewhere degree BETWEEN low and uppORDER BY rankselect sno,cno,degree,rank from score join grade on degree BETWEEN low and uppORDER BY rank/*19*/select * from student as s join score as c on s.sno = c.snowhere c.degree>(select c1.degree from score as c1 where c1.sno in ('109') and c1.cno in ('3-105'))/*20*/select * from score cwhere c.degree<(select MAX(c1.degree) from score as c1 GROUP BY c.cno)/*21*/select * from score as cwhere c.degree > (select c1.degree from score as c1 where c1.sno in ('109') and c1.cno in ('3-105') )and c.cno in ('3-105')/*22*/select s.sno,s.sname,s.sbirthday from student as swhere YEAR(s.sbirthday) in (select YEAR(s1.sbirthday) from student as s1 where sno in ('108'))select Sno,Sname,Sbirthday from student where year(student.Sbirthday)=(select year(Sbirthday) from student where Sno='107')/*23*/select sno,degree from score where sno in (select sno from score where cno in (select cno from course where tno in (select t.tno from teacher as t where t.tname in ('张旭'))))/*24*/select tname from teacher where tno in (select tno from course where cno in (select cno from score GROUP BY cnoHAVING COUNT(1)>5))/*25*/select * from student where class in ('95033','95031')/*26*/select cno from score where degree >85GROUP BY cno/*27*/select * from score where cno in (select cno from teacher where depart in ('计算机系'))/*28*/select tname,prof from teacher GROUP BY depart,tno/*29*/select cno,s.sno,degree from student as sjoin score as con s.sno = c.sno where cno in ('3-105') and degree>(select c1.degree from score c1 where (c1.cno in ('3-245') and s.sno = c1.sno))/*30*/select * from student as swhere (select c.degree from score as c where cno in ('3-105') and s.sno = c.sno)>(select c1.degree from score as c1 where cno in ('3-245') and s.sno = c1.sno)/*31*/select sname as `name`,ssex as sex,sbirthday as birthday from student UNIONselect tname as `name`,tsex as sex,tbirthday as birthday from teacher/*32*/select sname as `name`,ssex as sex,sbirthday as birthday from student as swhere ssex in ('女')UNIONselect tname as `name`,tsex as sex,tbirthday as birthday from teacher as twhere tsex in ('女')/*33*/select * from score as cwhere degree > (select AVG(degree) from score c1 GROUP BY cno HAVING cno = c.cno)/*34*/SELECT tname,depart from teacher as twhere tno in (select tno from course where cno in (select cno from score))/*35*/SELECT tname,depart from teacher as twhere tno not in (select tno from course where cno in (select cno from score))/*36*/select ssex,class from student  group by class,ssexHAVING COUNT(1)>=2/*37*/select *from student where sname not like '王%'/*38*/select sname,(2019-year(sbirthday)) as 年龄 from student/*39*/select MAX(sbirthday),MIN(sbirthday) from student /*40*/select * from student ORDER BY class,(2019-year(sbirthday)) desc /*41*/select cno from course where tno in (select tno from teacher where tsex in ('男')) /*42*/select sno,cno,degree from score where sno in (select sno from score HAVING MAX(degree) ) and cno in (select cno from score HAVING MAX(degree) )/*43*/select sname from student where ssex in (select ssex from student where sname in ('李军')) /*44*/select sname from student where ssex in (select ssex from student where sname in ('李军')) and class in (select class from student where sname in ('李军'))/*45*/select * from score cjoin student as son s.sno = c.snowhere c.cno in(select cno from course where cname in ('计算机导论'))and s.ssex in ('男')