/*创建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='计算机导论')
