sql server查询练习

/*创建studentCourseAndTeacher数据库*/
/*create database studentCourseAndTeacher*/

/*创建Student表*/

--use studentCourseAndTeacher
--go
--create table Student(
--Sno Char(3) not null primary key,
--Sname Char(8) not null,
--Ssex Char(2) not null,
--Sbirthday datetime,
--Class Char(5)
--)

/*创建Teacher表*/

--use studentCourseAndTeacher
--go
--create table Teacher(
--Tno Char(3) not null primary key,
--Tname Char(4) not null,
--Tsex Char(2) not null,
--Tbirthday datetime,
--Prof Char(6),
--Depart Varchar(10) not null
--)

/*创建Course表*/

--use studentCourseAndTeacher
--go
--create table Course(
--Cno Char(5) not null primary key,
--Cname Varchar(10) not null,
--Tno Char(3) not null,
--foreign key(Tno) references Teacher(Tno)
--)


/*创建成绩表*/

--use studentCourseAndTeacher
--go
--create table Score(
--Sno Char(3) not null,
--Cno Char(5) not null,
--Degree Decimal(4,1),
--foreign key(Sno) references Student(Sno),
--foreign key(Cno) references Course(Cno),
--)

/*查询题目*/
use studentCourseAndTeacher
go
/*（1）查询student表的所有记录Sname,Ssex,Class*/

--select Sname,Ssex,Class
--from Student

/*（2）查询教师所在的单位即不重复的Depart列*/

--select distinct Tno,Tname,Depart
--from Teacher

/*（3）查询Student表的所有记录*/

--select *
--from Student

/*（4）查询Score表中成绩在60-80之间的所有记录*/

--select *
--from Score
--where Degree between 60 and 80


/*（5）查询Score表中成绩为85,86，88的成绩*/

--select sno,cno,Degree
--from Score
--where Degree=85 or Degree=86 or Degree=88


/*（6）查询Student表中“95031”班或性别为“女”的同学记录*/

--select *
--from Student
--where Class = '95031' or Ssex ='女'


/*（7）以Class降序查询Student表的所有记录*/

--select *
--from Student
--order by Class desc 

/*（8）以Cno升序、Degree降序查询Score表中所有记录*/

--select *
--from Score
--order by Cno asc ,Degree desc


/*（9）查询“95031”班的学生人数*/

--select Class 班级,COUNT(Sno) 学生人数
--from Student
--where Class = '95031'
--group by Class

/*（10）查询Score表中的最高分的学生的学号和课程号*/

--select Sno,Cno
--from Score
--where Degree = (select max(Degree)from Score)



/*￥￥￥￥￥（11）查询每门课的平均成绩*/

--select Cno 课程,AVG(Degree)平均成绩
--from Score
--group by Cno

/*￥￥￥￥￥（12）查询Score表中至少有5名学生选修的并以3开头的课程的平均分数*/

--select Cno 以3开头课程,AVG(Degree)平均分数,COUNT(*) 五名学生
--from Score
--where Cno like '3%'
--group by Cno
--having count(Sno)>=5


/*（13）查询分数大于70，小于90的Sno列*/

--select Sno,Degree
--from Score
--where Degree>70 and Degree<90

--或
--use studentCourseAndTeacher
--go
--select Sno,Degree
--from Score
--where Degree between 70 and 90

/*（14）查询所有学生的Sname Cno Degree列*/

--select Student.Sname,Score.Cno,Score.Degree
--from Student inner join Score on Student.Sno = Score.Sno

--select Sname,Cno,Degree
--from Student,Score
--where Student.Sno = Score.Sno

/*（15）查询所有学生的Sno Cname Degree列*/

--select Score.Sno,Course.Cname,Score.Degree
--from Course inner join Score on Course.Cno = Score.Cno

--或
--use studentCourseAndTeacher
--go
--select Sno,Cname,Degree 
--from Course,Score
--where Course.Cno = Score.Cno;


/*（16）查询所有学生的Sname Cname Degree列*/

--select Sname,Cname,Degree from Student,Course,Score
--where student.Sno=Score.Sno and Course.Cno=Score.Cno; 


/*（17）查询“950332”班学生的平均分*/

--select Class,AVG(Degree)平均分
--from Score,Student
--where Score.Sno = Student.Sno and Student.Class='95032'
--group by Class

/*（18）查询所有同学的Sno Cno Degree和rank列（其中rank为层级的等级，成绩转换成为等级的规则是：大于等于90分为A 小于90且大于
等于80分为B 小于80且大于等于70分为C 小于70且大于60分为D 小于60分为E）*/

--看不懂

/*（19）查询选修’300’课程的成绩高于’004’号同学’成绩的所有同学的记录*/

--select *
--from Score
--where Cno = '300' and Degree>(Select degree from score where sno='004' and cno='300')


/*（20）查询选修score中选学多门课程的同学中分数为非最高分成绩的记录？？？？？？？？？？*/

--select Sno,Max(Degree)非最高分
--from Score
--group by Sno
--having COUNT(Cno)>1


--select * 
--from Score a where Degree <
--(select MAX(degree) from Score b where a.Cno=b.Cno) and Sno in(select Sno from Score group by Sno having count(*)>1)

/*（21）查询成绩高于学号为“004” 课程号为“300”的成绩的所有记录*/

--select *
--from Score
--where Cno = '300' and Degree>(select Degree from Score where Cno ='300' and sno='004')


/*（22）查询和学号为001的同学同年出生的室友学生的Sno Sname Sbirthday列*/

--select Sno,Sname,year(Sbirthday) 
--from Student
--where year(Sbirthday)  in (select year(Sbirthday) from Student where Sno = '001')


--select Sno,Sname,Sbirthday from student where year(student.Sbirthday)=(select year(Sbirthday) from student where Sno='001')


/*（23）查询“张旭”老师听上课程的学生成绩*/
--select  Tname,Cname,Degree
--from Teacher TC inner join Course C on  TC.Tno= C.Tno
--from Teacher,Course,Score
--where Tname ='张旭' and Teacher.Tno = Course.Tno and Course.Cno = Score.Cno


/*（24）查询选秀某课程的同学人数多余5人的教师姓名*/
--select Tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score group by Cno having COUNT(*)>2) )


/*（25）查询95033班和95031班全体学生的记录*/

--select * 
--from student 
--where Class='95033' or Class='95031'


/*（26）查询存在有85分以上成绩的课程Cno*/

--select Cno,Degree
--from Score
--where Degree>85

/*（27）查询出"计算机系"教师所教课程的成绩表*/


--select TC.Tno,Tname, Depart
--from Teacher TC,Course C
--where TC.Tno = C.Tno and Depart ='计算机系'

--select sno,Cno ,Degree 
--from Score 
--where Cno in (select Cno from Course where Tno in (select tno from Teacher where Depart='计算机系'))

/*（28）查询“计算机系”与“电子工程系”不同职称的教师人数*/

--select Tname,Prof 
--from Teacher a 
--where Prof not in(select Prof from Teacher b where a.Depart!=b.Depart)

/*（29）查询选修编号为“300”课程且成绩至少高于选修编号为“001”的同学的Cno Sno和Degree 并按Degree从高到低次序排序*/

--select Cno,Sno,Degree 
--from Score a where 
--(select Degree from Score b where Cno='300' and b.Sno=a.Sno)>=(select Degree from Score c where Cno='001' and c.Sno=a.Sno) order by Degree desc


/*（30）查询选修编号为“300”且成绩高于选修编号为“001”课程的同学的Cno Sno Degree*/

--select Cno,Sno,Degree 
--from Score a where 
--(select Degree from Score b where Cno='300' and b.Sno=a.Sno)>(select Degree from Score c where Cno='001' and c.Sno=a.Sno)


/*（31）查询所有教师和同学的name sex birthday*/

--select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student
--union
--select distinct Tname as name,Tsex as sex,Tbirthday as birthday from Teacher

/*（32）查询所有女教师和女同学的name sex birthday*/

--select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student where Ssex='女'
--union
--select distinct Tname as name,Tsex as sex,Tbirthday as birthday from Teacher where Tsex='女'

/*（33）查询成绩比该课程平均成绩地的同学的成绩表*/

--select Sno,Cno,Degree 
--from Score a where a.Degree<(select AVG(Degree) from Score b where a.Cno=b.Cno)

/*（34）查询所有认可教师的Tname Depart*/

--select Tname,Depart 
--from Teacher 
--where Tname in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)

--select Tname,Depart 
--from Teacher where tno in (select tno from course where Cno in (select distinct Cno from Score))

/*（35）查询所有未讲课的教师的Tname Depart*/

--select Tname,Depart 
--from Teacher 
--where Tname not in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)

/*（36）查询至少有2名男生的班号*/

--select Class 
--FROM student where Ssex='男' group by Class having COUNT(*)>1

/*（37）查询Student表中不姓王的同学记录*/

--select * from student where Sname not like ('王%')

/*（38）查询Student表中每个学生的姓名和年龄*/

--select Sname,YEAR(GETDATE())-year(Sbirthday) 
--from student

/*（39）查询Student表中最大和最小的Sbirthday日期值*/

--select MAX(Sbirthday) as 最大,MIN(Sbirthday) as 最小 from student

/*（40）以班号和年龄从大到小的顺序查询Student表中的全部记录*/

--select * from student order by Class desc,Sbirthday asc

/*（41）查询“男”教师一起所上的课程*/

--select Tname,Cname from Teacher,Course where Tsex='男' and Teacher.Tno=Course.Tno

/*（42）查询最高分同学的Sno Cno Degree列*/

--select Sno,Cno,Degree from Score where degree=(select MAX(Degree)from Score)

--select top 1* from Score order by Degree desc

/*（43）查询和“李军”同性别的所有同学的Sname*/

--select Sname
--from student where Ssex=(select Ssex from student where Sname='李军') and Sname not in ('李军')

/*（44）查询和“李军”同性别并同班的同学Sname*/

--select Sname 
--from student where Ssex=(select Ssex from student where Sname='李军') and Sname not in ('李军') and Class=(select Class from student where Sname='李军')

/*（45）查询所有选修“计算机导论”课程的“男”同学的成绩表*/

--select Sno,Degree 
--from Score where Sno in (select Sno from student where Ssex='男') and Cno in (select Cno from Course where Cname='计算机导论')