数据库作业17~26

--17、 查询“95033”班学生的平均分。
select avg(degree) from Score where Sno in (Select Sno from Student where Class=95033)
--18、 假设使用如下命令建立了一个grade表：现查询所有同学的Sno、Cno和rank列。?
create table grade(low  int,upp  int,rank  char(1))
insert into grade values(90,100,‘A‘)
insert into grade values(80,89,‘B‘)
insert into grade values(70,79,‘C‘)
insert into grade values(60,69,‘D‘)
insert into grade values(0,59,‘E‘)
select *from grade
select*from score
select Sno,cno,RANK from Score join grade on Degree between low and upp order by rank
--第二种方法 select sno,cno,(select RANK from grade where Score.Degree between low and upp) as degreee from Score 
--19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *from Score where Degree >(select Degree from Score where Sno=109 and Cno=‘3-105‘) and Cno=‘3-105‘
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
--这个是剔除选多门课的所有人的分数最高分
select *from Score where Sno in(select Sno from Score group by Sno having COUNT(*)>1) and Degree not in(select max(degree)from Score where Sno in(select Sno from Score group by Sno having COUNT(*)>1))

--剔除选多门课的每门课最高分
select *from Score a where Degree not in (select MAX(degree) from Score b where b.Cno=a.cno)and Sno in(select Sno from Score group by Sno having COUNT(*)>1)
--剔除除了每门课最高分学生之外的其他学生分数信息
select *from Score a where Degree not in (select MAX(degree) from Score b where b.Cno=a.cno)
--21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select *from Score  where Degree >(select Degree from Score where Sno=109 and Cno=‘3-105‘)
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 
--时间日期函数：
--year(时间日期列)：取年份
--month(时间日期列)：取月份
--day(时间日期列)：取天
select sno ,sname, sbirthday from student where year(Sbirthday)
=(select year(Sbirthday) from Student where Sno=108)

select DATEADD(DAY,5,‘1999-08-31‘)--在原来的基础上加5天
select DATEADD(week,5,‘1999-08-31‘)--在原来的基础上加5周
select DATEDIFF(WEEKDAY,‘2015-04-23‘,‘2015-05-01‘) --两天之间相差几天(weekday)，相差几周(week)
print datename(weekday,‘2015-4-23‘) --返回今天星期几--
print datename(week,‘2015-4-23‘) --返回这是今年的第多少个周
print datepart(weekday,‘2015-4-23‘) --返回这是今年的第多少个周 返回int类型的 datepart
print getdate()--获取当前系统时间
print isdate(‘1999-111-213‘)--判断一个值是否为正确的日期时间 1:正确 0:错误
print sysdatetime() --获取更为详细的时间

--23、查询“张旭“教师任课的学生成绩。
select sno,degree from Score where Cno=(select Cno from Course where Tno = (Select Tno from Teacher where Tname=‘张旭‘))
--select sno,degree from Course join Score on Course.Cno=Score.Cno join teacher on Course.tno=teacher.tno where Tname=‘张旭‘
--
select *from Teacher 
select *from Course
select *from Score
select *from Student

--24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher where tno 
=(select Tno from course where cno=
(select cno from Score
 group by cno having COUNT(*)>5))

--
--select Tno from course where Cno in
--(select score.Cno from Course
--join Score on Score.Cno=Course.Cno 
--group by score.cno having COUNT(*)>5)
--25、查询95033班和95031班全体学生的记录。
select *from Student where Class in(95033,95031)
--26、  查询存在有85分以上成绩的课程Cno.
select distinct Cno from Score  where Degree>85