SQL 查询45题

表格代码

create table student
(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(20) not null,
    sbirthday datetime,
    class varchar(20)
)
;
create table teacher
(
    tno varchar(20) 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) 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,
    cno varchar(20) not null,
    degree decimal(4,1),
    foreign key(sno) references student(sno),
    foreign key(cno) references course(cno),
    primary key(sno,cno)
)
;


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');

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');

题目答案

1.select sname,ssex,class from student
2.select distinct depart from teacher
3.select * from student
4.select * from score where degree>60 and degree<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 asc,degree desc
9.select * from student where class='95031'
10.select * from score order by degree desc
11.我的方法：select avg(degree) from score where cno='3-245'
select avg(degree) from score where cno='3-105'
select avg(degree) from score where cno='6-166'
答案：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%' group by Cno
13.select sno from score where degree>70 and degree<90
14.select sname,cno,degree from score join student on student.sno=score.sno
15.select sno,cname,degree from score join course on course.cno=score.cno
16.这个题做错了
我的答案：select sname,cname,degree from score join student,course on(student.sno=score.sno and course.cno=score.cno)
正确答案：select Sname,Cname,Degree from Score join Student on Student.Sno = Score.Sno join Course on 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 join grade on Score.Degree between low and upp
19.select * from score where cno ='3-105' and degree>(select degree from score where sno = '109' and cno = '3-105')
若果没写后面的cno = '3-105' 软件就会崩溃 不知道为什么 试了好几次 0v0
20.这题只会写前半部分 OTZ
答案：理解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 b.Cno = a.Cno)
理解2
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 a where Sno in (select Sno from Score group by Sno having count(*)>1 ))
21.弄不懂和19题有啥区别 理解不能
答案：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.关联了3个表 想的出来 但写不出来
答案：select * from Score where Cno in(select Cno from Course where Tno =(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('95031','95033')
突然又变简单了
26.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 = '计算机系'))
28.依旧写不出...
答案：select Tname,prof from Teacher where prof not in(select prof from Teacher where depart = '计算机系' and prof in(select prof from Teacher where depart='电子工程系' ))
select prof from Teacher where depart = '计算机系' and prof not in(select prof from Teacher where depart='电子工程系' ) union
select prof from Teacher where depart = '电子工程系' and prof not in(select prof from Teacher where depart='计算机系' )
29.一脸懵b中
答案：select * from Score where Cno='3-105' and Degree>any(select Degree from Score where Cno ='3-245') order by Degree desc
30.select * from score where cno='3-105' and degree>all(select degree from score where cno ='3-245')
31.select sname,ssex,sbirthday from student
union
select tname,tsex,tbirthday from teacher
32.select sname,ssex,sbirthday from student where ssex='女'
union
select tname,tsex,tbirthday from teacher where tsex='女'
33.select * from score a where degree<(select avg(degree) from score b where b.cno = a.cno)
34.我的答案：select tname,depart from teacher
答案：select Tname,depart from Teacher where Tno in (select Tno from Course )
结果是一样的
35.这个题脑子直接想不明白...
答案：select Tname,depart from Teacher where Tno in(select Tno from Course where Cno not in(select Cno from Score))
36.select class from student where ssex='男' group by class having count(*)>1
37.select * from student where sno not in(select sno from student where sname like '王%')
38.select Sname,YEAR(now())-YEAR(Sbirthday) from Student
不知道为什么我写的运行不出来 我没写year
39.select max(sbirthday) from student
select min(sbirthday) from student
40.select * from student order by class desc,sbirthday
41.脑子要炸了
答案：select * from Teacher join Course on Teacher.Tno = Course.Tno where Teacher.Tsex='男'
42.select * from score where degree = (select max(degree) from score )
43.select sname from student where ssex = (select ssex from student where sname='李军')
44.加了个条件就弄不出来了...
答案：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 Sno in(select Sno from Student where Ssex='男') and Cno in(select Cno from Course where Cname='计算机导论')

做完这45道题感觉脑子变得和浆糊一样   有三分之一写不出来的   甚至有的看答案都弄不明白不过感觉思路学到不少   就是高级查询一旦到了3层、4层就开始糊迷了   很多时候想

得到却写不出     看来还是too young too simple 还是缺练

另外感觉程序不能马虎啊   一点点问题  甚至一个分号  就会导致崩盘    要细心啊