数据库上机练习第七章.docx

1、数据库上机练习第七章第七章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 4 THEN 多 WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN 一般 WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN 少 WHEN COUNT(SC.

2、Cno) = 0 THEN 未选END AS 选课情况FROM Student S LEFT JOIN SC ON S.Sno = SC.SnoGROUP BY S.SnoORDER BY COUNT(SC.Cno) DESC3、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(Grad

3、e) =807、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=计算机系9、SELECT Cno 课程号,Cname 课程名 FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Cno = Course.Cno )10、SELEC

4、T Sname 姓名 FROM StudentWHERE Dept = 计算机系 AND NOT EXISTS ( SELECT * FROM SC WHERE Sno = Student.Sno ) 11、SELECT Sno 学号,C.Cno 所选课程的课程号,Semester 开课学期FROM Course C JOIN SC ON SC.Cno = C.Cno WHERE NOT EXISTS(SELECT * FROM Course WHERE Semester = 2 AND NOT EXISTS (SELECT * FROM SC X WHERE X.Cno = C.Cno )1

5、2、select sno,dept from student s where not exists (select * from Course c where semester =4 and not exists(select * from SC where SC.Cno=o and SC.Sno=s.sno)13、select sno from student s where not exists (select * from SC c where c.Sno =0831102 and not exists(select * from SC where SC.Cno=o and SC.Sno

6、=s.sno)14、SELECT S.Sno 学号,Dept 所在系,C.Cno 课程号 FROM Student S JOIN SC S1 ON S.Sno = S1.Sno JOIN Course C ON C.Cno = S1.Cno WHERE NOT EXISTS ( SELECT * FROM Course C JOIN SC ON C.Cno = SC.Cno JOIN Student S ON S.Sno = SC.Sno WHERE Sname = 张海 AND NOT EXISTS ( SELECT * FROM SC X WHERE X.Cno = C.Cno AND X

7、.Sno = S1.Sno ) AND Sname != 张海15、select sc.Sno,Dept,sc.Cno,cname,credit from student s join SC on s.Sno=SC.Sno join Course c on c.Cno=SC.Sno where not exists (select * from Course c where c.Credit 3 and not exists(select * from SC where SC.Cno=o and SC.Sno=s.sno)16、SELECT Cname 课程名,Credit 学分 FROM C

8、ourse CWHERE Semester = 4 AND NOT EXISTS ( SELECT * FROM Course C1 WHERE C1.Credit = C.Credit AND C.Semester = 1 )17、select cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=李勇intersectselect cname,semester,creditfrom Course join SC on Cours

9、e.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=王大力18、select Cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=李勇exceptselect Cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere s

10、name=王大力19、select SC.Sno,SC.Cno from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere SC.Sno in(select t1.sno from (select * from SC where Cno=C001) as t1join (select * from SC where Cno=C002) as t2on t1.Sno=t2.Sno)20、select sno,sname,dept,COUNT(*) over(partition by dept)

11、该系的学生人数 from Student21、create VIEW IS_studentasselect sname,dept,year(GETDATE()-year(Birthdate) age from studentselect sname,dept,AVG(age)over() as 平均年龄,MAX(age)over() as 最大年龄,MIN(age)over() as 最小年龄from IS_student22、select sno,sname,sex,dept,COUNT(*) over(partition by dept) as 系总人数,COUNT(Sex) over(p

12、artition by sex,dept) as 男女生人数,cast(1.0*count(sex)over(partition by sex,dept)/COUNT(*) over(partition by dept) as decimal(5,2) 男女生百分比from Studentorder by dept23、select sc.Sno,sname,Cname,grade,dense_RANK()over(order by grade desc) as 成绩排名from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Sno24、select sc.Sno,sc.Cno,grade,dense_RANK()over(partition by o order by grade desc) as RANKfrom Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Sno25、select Sname,Dept,birthdate,dense_RANK()over(partition by dept order by birthdate asc) as RANKfrom Student