经典SQL练习题

题目地址：http://blog.csdn.net/qaz13177_58_/article/details/5575711

sqlserver版本  29,30再议。。

select SName,Ssex,class from STUDENT

select distinct depart from teacher

select * from STUDENT

select * from score where degree between 60 and 80

select * from score where degree in (85,86,88)

select * from STUDENT where CLASS=‘95031‘ or SSEX=‘女‘

select * from STUDENT order by CLASS desc

select * from score order by cno asc,degree desc

select count(*) from STUDENT where CLASS=‘95031‘

select sno,cno from score where degree=(select MAX(degree) from score)

select AVG(degree) from score where cno=‘3-105‘

select AVG(degree) from score where cno=(select cno from score where cno like ‘3%‘ group by cno having count(*)>5)

select sno from score group by sno having MAX(degree)<90 and MIN(degree)>70

--查询所有学生的Sname、Cno和Degree列

select t.SNAME,c.cno,s.degree from STUDENT t left join score s on t.SNO=s.sno left join course c on s.cno=c.cno

--查询所有学生的Sno、Cname和Degree列

select t.SNO,c.cname,s.degree from STUDENT t left join score s on t.SNO=s.sno left join course c on s.cno=c.cno

--查询所有学生的Sname、Cname和Degree列

select t.SNAME,c.cname,s.degree from STUDENT t left join score s on t.SNO=s.sno left join course c on s.cno=c.cno

--查询“95033”班所选课程的平均分

select s.cno,AVG(s.degree) from STUDENT t left join score s on t.SNO=s.sno  where t.class=‘95033‘ group by s.cno

--查询所有同学的Sno、Cno和rank列

select sno,cno,(select rank from grade where degree between low and upp) from score

declare @score int=90 select rank from grade where @score between low and upp

--查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

select * from STUDENT t left join score s on t.SNO=s.sno where s.cno=‘3-105‘ and s.degree>(select degree from score where sno=‘109‘ and cno=‘3-105‘)

--查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

select sno from score where degree<(select MAX(degree) from score) group by sno having COUNT(*)>1

--查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列

select sno,sname,sbirthday from STUDENT where year(SBIRTHDAY)=(select year(SBIRTHDAY) from STUDENT where SNO=‘108‘)

--查询“张旭“教师任课的学生成绩

select sno,degree from score where cno=(select cno from course where tno=(select tno from teacher where tname=‘张旭‘))

--查询选修某课程的同学人数多于5人的教师姓名

select t.tname from teacher t inner join course c on t.tno=c.tno left join score s on c.cno=s.cno group by t.tname having COUNT(*)>5

--查询95033班和95031班全体学生的记录

select * from STUDENT where CLASS in(‘95033‘,‘95031‘)

--查询存在有85分以上成绩的课程Cno

select distinct cno from score where degree>85

--查询出“计算机系“教师所教课程的成绩表

select * from teacher t left join course c on t.tno=c.tno left join score s on c.cno=s.cno where depart=‘计算机系‘

--查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

select TName,Prof from TEACHER where DEPART=‘计算机系‘ and PROF not in (select PROF from TEACHER where DEPART=‘电子工程系‘) union select TName,Prof from TEACHER where DEPART=‘电子工程系‘ and PROF not in (select PROF from TEACHER where DEPART=‘计算机系‘)

--29，30不是很理解。。。

--查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按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

--查询选修编号为“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‘)

--查询所有教师和同学的name、sex和birthday

select TNAME as Name,TSEX as Sex,TBIRTHDAY as Birthday from TEACHER union select SName,SSex,Sbirthday from STUDENT

--查询所有“女”教师和“女”同学的name、sex和birthday

select TName as Name,TSex as Sex,TBirthday as Birthday from TEACHER union select SName,SSex,Sbirthday from STUDENT where SSex=‘女‘

--查询成绩比该课程平均成绩低的同学的成绩表

select * from SCORE a where DEGREE<(select AVG(DEGREE) from Score b where a.CNO=b.CNO) select * from SCORE s left join (select cno,AVG(DEGREE) as average from SCORE group by CNO) t on s.CNO=t.CNO where s.DEGREE<t.average

--查询所有任课教师的Tname和Depart

select t.TNAME,t.DEPART from COURSE c inner join TEACHER t on c.TNO=t.TNO

--查询所有未讲课的教师的Tname和Depart

select t.TNAME,t.DEPART from TEACHER t left join COURSE c on t.TNO=c.TNO where c.CNO is null

--查询至少有2名男生的班号

select class,COUNT(*) from (select * from STUDENT where SSEX=‘男‘) temptable group by CLASS having COUNT(*)>1

--查询Student表中不姓“王”的同学记录

select * from STUDENT where SNO not in (select SNO from STUDENT where SNAME like ‘王%‘) select * from STUDENT where SNAME not like ‘王%‘

--查询Student表中每个学生的姓名和年龄

select SNAME,(select DATEDIFF(YEAR,SBIRTHDAY,GETDATE())) as Age from STUDENT

--查询Student表中最大和最小的Sbirthday日期值

select * from (select top 1 SNAME,SBIRTHDAY from STUDENT order by SBIRTHDAY DESC) a union select * from(select top 1 SNAME,SBIRTHDAY from STUDENT order by SBIRTHDAY ASC) b

--以班号和年龄从大到小的顺序查询Student表中的全部记录

select *,DATEDIFF(YEAR,SBIRTHDAY,GETDATE()) as Age from STUDENT order by CLASS DESC,SBIRTHDAY

--查询“男”教师及其所上的课程

select * from TEACHER inner join COURSE on TEACHER.TNO=COURSE.TNO where TSEX=‘男‘

--查询最高分同学的Sno、Cno和Degree列

select top 1 SNO,Cno,Degree from SCORE order by DEGREE DESC

--查询和“李军”同性别的所有同学的Sname

select SNAME from STUDENT where SSEX=(select SSEX from STUDENT where SNAME=‘李军‘)

--查询和“李军”同性别并同班的同学Sname

select SNAME from STUDENT where SSEX=(select SSEX from STUDENT where SNAME=‘李军‘) and CLASS=(select CLASS from STUDENT where SNAME=‘李军‘)

--查询所有选修“计算机导论”课程的“男”同学的成绩表

select * from STUDENT s inner join SCORE c on s.SNO=c.SNO where c.CNO=(select CNO from COURSE where CNAME=‘计算机导论‘) and s.SSEX=‘男‘