php查题,php 查询45题

表格代码

create tablestudent

(

snovarchar(20) primary key,

snamevarchar(20) not null,

ssexvarchar(20) not null,

sbirthdaydatetime,

classvarchar(20)

)

;create tableteacher

(

tnovarchar(20) primary key,

tnamevarchar(20) not null,

tsexvarchar(20) not null,

tbirthdaydatetime,

profvarchar(20),

departvarchar(20) not null)

;create tablecourse

(

cnovarchar(20) primary key,

cnamevarchar(20) not null,

tnovarchar(20) not null,foreign key(tno) referencesteacher(tno)

)

;create tablescore

(

snovarchar(20) not null,

cnovarchar(20) not null,

degreedecimal(4,1),foreign key(sno) referencesstudent(sno),foreign key(cno) referencescourse(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‘);

View Code

题目答案

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

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 还是缺练

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

原文：http://www.cnblogs.com/bilibiliganbei/p/5536161.html