数据库上练习第七章.docVIP

第七章 1、SELECT sc.Sno,Sname,Grade, CASE WHEN Grade = 90 THEN 好 WHEN Grade between 80 and 89 THEN 较好 WHEN Grade between 70 and 79 THEN 一般 WHEN Grade between 60 and 69 THEN 较差 WHEN Grade 60 THEN 差 END AS 成绩 FROM Student s join SC on S.sno=sc.sno join course c on c.cno=sc.cno where cname=java and dept=计算机系 2、SELECT S.Sno, COUNT(SC.Cno) 选课门数,CASE WHEN COUNT(SC.Cno) 4 THEN 多 WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN 一般 WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN 少 WHEN COUNT(SC.Cno) = 0 THEN 未选 END AS 选课情况 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno GROUP BY S.Sno ORDER BY COUNT(SC.Cno) DESC 3、SELECT dept, CASE WHEN avg(Grade) = 90 THEN 好 WHEN avg(Grade) between 80 and 89 THEN 较好 WHEN avg(Grade) between 70 and 79 THEN 一般 WHEN avg(Grade) between 60 and 69 THEN 较差 WHEN avg(Grade) 60 THEN 差 END as 考试情况 FROM Student s join SC ON s.Sno = SC.Sno JOIN Course c ON c.Cno = SC.Cno WHERE Cname = JAVA group by dept 4、UPDATE course SET credit = credit + CASE WHEN semester between 1 and 2 THEN 5 WHEN semester between 3 and 4 THEN 3 WHEN semester between 5 and 6 THEN 1 ELSE 0 END 5、select cname,credit, (select sum(Credit) from COURSE where Semester = 2)as 总学分 from COURSE where Semester = 2 create VIEW IS_New as select Student.sname,SC.cno,grade, AVG(grade) over(partition by sc.sno) as avgg from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Sno select * from IS_New where avgg=80 7、select sname 姓名,YEAR(GETDATE())-year(birthdate) 年龄 from student where YEAR(GETDATE())-year(birthdate) all (select YEAR(GETDATE())-year(birthdate) from student where dept=信息管理系) and dept=计算机系select sname 姓名,YEAR(GETDATE())-year(birthdate) 年龄 from student where YEAR(GETDATE())-year(birthdate) all (select YEAR(GETDATE())-year(birthdate) from student where dept=信息管理系) and dept=计算机系SELECT Cno 课程号,Cname 课程名 FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Cno = Course.Cno ) 10、SELECT Sname 姓名