使用SQL Server Management Studio创建学生选课数据库

/*使用SQL Server Management Studio创建学生选课数据库创建一个逻辑名为std_course的数据库，其存放路径为“d:data”，物理文件名为std_course.mdf，初始大小为10MB，最大值为100MB，数据文件增长增量为5MB；日志文件的逻辑名std_course_log的数据库，其存放路径为“d:data”，物理文件名为std_course_log.ldf，初始大小为2MB，最大值为20MB，日志文件增长增率为20%。*/gocreate database 学生选课on primary(name=std_course, filename=d:赵平tempstd_course.mdf, size=10MB, filegrowth=5MB, maxsize=100MB)log on(name=std_course_log, filename=d:赵平tempstd_course_log.ldf, size=2MB, filegrowth=20%, maxsize=20MB)gouse 学生选课gocreate table Student(sno char(8) primary key, sname nvarchar(20), sgender nchar(1), sdept nvarchar(10), sedat date)gocreate table course( cno char(3) primary key, cname nvarchar(15), ccredit smallint )gocreate table sc(sno char(8), cno char(3), grade numeric(4,1), primary key(sno,cno), foreign key(sno) references student(sno), foreign key(cno) references course(cno)goinsert into Student values(20131001,王力,男,计算机,2013-09-11),(20131002,张红,女,计算机,2005-08-24),(20132001,孙国庆,男,信息,2003-10-25),(20132002,历亦其,男,信息,2004-08-12),(20131003,王大伟,男,计算机,2003-06-18),(20132004,张历兰,女,数学,2005-06-4),(20131004,王历离,女,信息,2002-09-15),(20132005,张历山,男,数学,2001-06-13),(20131005,李力群,女,计算机,2002-01-13)select * from studentinsert into course values(101,数据结构,4),(102,数据库,3),(103,C语言程序设计,6),(104,DB_Design,2.5)select * from courseinsert into sc values(20131001,101,90),(20131001,102,76.1),(20131001,103,88.4),(20131001,104,69.5),(20131002,101,66.8),(20131002,103,57.4),(20132001,101,75.4),(20132001,103,78.4),(20132002,101,87.4),(20132002,102,80.2),(20132002,103,67.4),(20132002,104,77.4),(20132005,101,null),(20131004,103,null)select * from sc-1.查询全体学生的详细信息。select * from student-2.检索全体学生的学号、姓名。select sno,sname from student -3.检索已选课程的课程号，要求显示的课程号不重复。select distinct cno from sc-4.查询全体学生的姓名及其年龄(提示：使用系统函数getdate()获取当前日期)。select sname as 姓名,YEAR(GETDATE()-YEAR(sedat) as 年龄 from student-5.查询成绩大于80分的学生的学号、课程号及成绩。select sno as 学号,cno as 课程号,grade as 成绩 from sc where grade80-6.查询成绩介于7080分的学生的学号、课程号及成绩。select sno,cno,grade from sc where grade between 70 and 80select sno,cno,grade from sc where grade=70 and grade80-8.查询“数据结构”和“C语言程序设计”课程的详细信息。select * from course where cname=数据结构 or cname=C语言程序设计select * from course where cname=数据结构 union select * from course where cname=C语言程序设计-9.检索姓王的同学的详细信息。select * from student where sname like 王%-10.检索名字中第二个字是“力”或“历”的学生的详细信息。select * from Student where sname like _历力%-11.查询全体学生的姓名及其年龄,并按学生的年龄的降序排列。select sname,YEAR(GETDATE()-YEAR(sedat) as sage from Student order by sage desc-12.查询学生总人数。select COUNT(*) as 总人数 from student-13.计算选修“101”号课程的学生平均成绩、最高分和最低分。select AVG(grade) as 平均成绩,MAX(grade) 最高分,MIN(grade) 最低分 from sc where cno=101-14.汇总总分大于200分的学生的学号及总成绩。select * from scselect sno as 学号,SUM(grade) as 总成绩 from sc group by sno having SUM(grade)200-15.统计各个课程的选课人数。select cno as 课程号,COUNT(*) as 选课人数 from sc group by cno-16.检索多于2名学生选修的并以1结尾的课程号和平均成绩。select cno as 课程号,AVG(grade) as 平均成绩 from sc group by cno having COUNT(*)2 and cno like %1-18查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE -19查询DB_Design课程的课程号和学分SELECT Cno,CcreditFROM Course WHERE Cname LIKE DB_Design ESCAPE -实验三use 学生选课;go-1. 检索所有学生的选课信息，包括学号、姓名、课程号、课程名和成绩。select s.sno 学号,s.sname 姓名,o 课程号,ame 课程名,sc.grade 成绩from Student s join sc on s.sno=sc.sno join course c on o=o-2. 查询选修了“C语言程序设计”的学生的学号和姓名。select s.sno 学号,s.sname 姓名from sc join course c on o=o and ame=C语言程序设计 join Student s on s.sno=sc.sno - 3. 查询与“张红”在同一个系的学生学号、姓名和所属系。select s.sno 学号,s.sname 姓名,s.sdept 所属系from Student swhere s.sdept=(select sdept from student where sname=张红) -4. 查询其它系中比信息系的所有学生年龄都大的学生的学号、姓名和年龄。select s.sno 学号,s.sname 姓名,DATEDIFF(year,s.sedat,GETDATE() 年龄 from Student swhere s.sdept信息 and DATEDIFF(year,s.sedat,GETDATE()(select max(year(getdate()-year(sedat) from Student where sdept=信息)-5. 查询比“王力”年纪大的男学生信息。select * from Studentwhere sgender=男 and DATEDIFF(year,sedat,GETDATE() (select year(getdate()-year(sedat)from Student where sname=王力)-6. 查询每个学生选修课程的课程号和学号，要求该选修课程的成绩超过他选修课程的平均成绩。select sc.sno 学号,o 课程号 from sc join (select sno,avg(grade) 平均分 from sc group by sno) sc1 on sc.sno=sc1.snowhere sc.gradesc1.平均分-7. 检索最高分与最低分之差大于10分的学生的学号、最高分和最低分。select sc.sno 学号,max(grade) 最高分,min(grade) 最低分 from sc join (select sno,max(grade)-min(grade) 最大分差 from sc group by sno) sc1 on sc.sno=sc1.snowhere sc1.最大分差10group by sc.sno-8. 检索选修2门以上课程的学生的学号和总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。select sc.sno 学号,sum(grade) 总成绩 from scwhere grade=60 andsc.sno in (select sno from sc group by snohaving count(*)=2)group by sc.snoorder by sum(grade) desc-9. 查询选修了全部课程的学生学号和姓名。select sno 学号,sname 姓名 from Studentwhere sno in(select sno from sc group by sno having count(*)=(select count(*) from course)-10. 查询至少选修了学生“20132001”选修的全部课程的学生的学号和姓名。-方法一：select sno 学号,sname 姓名 from Studentwhere sno in(select distinct R1.sno from sc R1where not exists( select S.cno from (select * from sc where sno=20132001) Swhere not exists( select * from sc R2where R2.sno=R1.sno and R2.cno=S.cno) and sno20132001-方法二：SELECT s.sno 学号,s.sname 姓名FROM sc join Student s on sc.sno=s.snoWHERE cno in (SELECT cno FROM sc where sno=20132001) and s.sno!=20132001GROUP BY s.sno,s.snameHAVING COUNT(cno)=(SELECT COUNT(cno) FROM (SELECT cno FROM sc where sno