20190411课堂作业<MYSQL语句四十五题>

/*建表==================================*/
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 class
select * from student ORDER BY class DESC 
/*8*/
select *from score ORDER BY Cno,degree DESC
/*9*/
select COUNT(1) from student
GROUP BY class
HAVING class in ('95031')
/*10*/
select sno,cno from score
where 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 
sno
ORDER BY
su DESC
LIMIT 0,5
/*13*/
select sno from score 
where degree>70 and degree<90
/*14*/
select sname,cno,degree from student as s
join score as c
on 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 co
on 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 co
on c.cno = co.cno
/*17*/
select avg(degree) from score as c
JOIN student as s
on  c.sno = s.sno
GROUP BY s.class
HAVING
s.class in ('95033')
/*18*/
select sno,cno,degree,rank from score,grade
where 
degree BETWEEN low and upp
ORDER BY rank

select sno,cno,degree,rank from score 
join grade 
on degree BETWEEN low and upp
ORDER BY rank
/*19*/
select * from student as s 
join score as c 
on s.sno = c.sno
where 
c.degree>(select c1.degree from score as c1 where 
c1.sno in ('109') and c1.cno in ('3-105'))
/*20*/
select * from score c
where c.degree<(select MAX(c1.degree) from score as c1 GROUP BY c.cno)
/*21*/
select * from score as c
where 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 s
where 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 cno
HAVING 
COUNT(1)>5))
/*25*/
select * from student 
where class in ('95033','95031')
/*26*/
select cno from score 
where degree >85
GROUP 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 s
join score as c
on 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 s
where (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 UNION
select 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 s
where ssex in ('女')
UNION
select tname as `name`,tsex as sex,tbirthday as birthday from teacher as t
where tsex in ('女')
/*33*/
select * from score as c
where degree > 
(select AVG(degree) from score c1 GROUP BY cno HAVING cno = c.cno)
/*34*/
SELECT tname,depart from teacher as t
where tno in (select tno from course 
where cno in (select cno from score)
)
/*35*/
SELECT tname,depart from teacher as t
where tno not in (select tno from course 
where cno in (select cno from score)
)
/*36*/
select ssex,class from student  
group by class,ssex
HAVING 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 c
join student as s
on s.sno = c.sno
where c.cno in(select cno from course where cname in ('计算机导论'))
and s.ssex in ('男')