使用SQL_Server_Management_Studio創(chuàng)建學生選課數(shù)據(jù)庫

1、/*使用SQL Server Management Studio 創(chuàng)建學生選課數(shù)據(jù)庫創(chuàng)建一個邏輯名為std_course的數(shù)據(jù)庫，其存放路徑為"d:data ”，物理文件名為 std_course.mdf ,初始大小為10MB最大值為100MB數(shù)據(jù)文件增長增量為5MB日志文件的邏輯名std_course_log的數(shù)據(jù)庫， 其存放路徑為"d:data ”，物理文件名為 std_course_log.ldf ,初始大小 為2MB最大值為20MB日志文件增長增率為20%*/gocreate database學生選課on primary(name='std_course&#

2、39;,平 tempstd_course.mdf', size=10MB,filegrowth=5MB, maxsize=100MB) log on (name='std_course_log',平 tempstd_course_log.ldf, size=2MB, filegrowth=20%, maxsize=20MB) go use學生選課gocreate table Student(sno char(8) primary key,sname nvarchar(20),sgender nchar(1),sdept nvarchar(10),sedat date)g

3、ocreate table course(o char(3) primary key,ame nvarchar(15),ccredit smallint)gocreate table sc(sno char(8),o char(3),grade numeric(4,1),primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno)goinsert into Studentvalues('2013100T,'王力','男&#

4、39;,'計算機','2013-09-11'),('20131002',' 紅； 女。計算機,'2005-08-24'),('20132001',' 國慶',男',信息','2003-10-25'),('20132002',' 歷亦其,男',信息','2004-08-12'),('20131003',' 王大偉,男,計算機,'2003-06-18'),('2

5、0132004', 歷蘭',女',數(shù)學','2005-06-4'),('20131004',' 王歷離,女',信息','2002-09-15'),('20132005',' 歷山； 男',數(shù)學','2001-06-13'),('20131005',' 力群',女',計算機','2002-01-13')select * from studentinsert into cour

6、se values('101','數(shù)據(jù)結構',4),('102', 數(shù)據(jù)庫 ,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)

7、,(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.查詢全體學生的詳細信息。-2.檢索全體學生的學號、select * from studentselect sno,sname from student- -3.檢索已選課程的課程號，要求顯示的課程號不重復。select distinct。from sc- -4.查詢全體學生的及其年

8、齡(提示：使用系統(tǒng)函數(shù) getdate()獲取當前日期)。select sname as '',YEAR(GETDATE()-YEAR(sedat) as ' 年齡'from student- -5.查詢成績大于80分的學生的學號、課程號及成績。select sno as ' 學號',cno as ' 課程號',grade as '成績'from sc where grade>80- -6.查詢成績介于7080分的學生的學號、課程號及成績。select sno,cno,grade from sc where

9、grade between 70 and 80select sno,cno,grade from sc where grade>=70 and grade<=80-7.查詢選修了課程號為“ 101”，且成績大于80分的學生的學號。select sno from sc whereo='101' and grade>80- -8.查詢“數(shù)據(jù)結構”和“ C語言程序設計”課程的詳細信息。select * from course whereame='數(shù)據(jù)結構'orame='C 語言程序設計'select * from course whe

10、reame='數(shù)據(jù)結構'union select * from course whereame='C語言程序設計- -9.檢索姓王的同學的詳細信息。select * from student where sname like '王 '- -10.檢索名字中第二個字是“力”或“歷”的學生的詳細信息。select * from Student where sname like '_歷力%'- -11.查詢全體學生的及其年齡，并按學生的年齡的降序排列。select sname,YEAR(GETDATE()-YEAR(sedat) as '

11、;sage' from Student order by sage desc- -12.查詢學生總人數(shù)。select COUNT(*) as '總人數(shù)'from student-13.計算選修“101”號課程的學生平均成績、最高分和最低分。select AVG(grade) as '平均成績,MAX(grade)' 最高分',MIN(grade)' 最低分'from scwhereo='101'- -14.匯總總分大于200分的學生的學號及總成績。select * from scselect sno as '

12、; 學號',SUM(grade) as ' 總成績'from sc group by sno having SUM(grade)>200- -15.統(tǒng)計各個課程的選課人數(shù)。selecto as ' 課程號',COUNT(*) as '選課人數(shù)'from sc group byo- -16.檢索多于2名學生選修的并以1結尾的課程號和平均成績。selecto as ' 課程號',AVG(grade) as '平均成績'from sc group byo having COUNT(*)>2 andol

13、ike '%1'-18查詢以"DB_"開頭，且倒數(shù)第3個字符為i的課程的詳細情況。SELECT *FROM CourseWHERE Cname LIKE 'DB_%i_' ESCAPE ''- -19查詢DB_Design課程的課程號和學分SELECT Cno,CcreditFROM CourseWHERE Cname LIKE 'DB_Design' ESCAPE ''- -實驗三use學生選課；go-1.檢索所有學生的選課信息，包括學號、課程號、課程名和成績。select s.sno 學號,

14、s.sname ,o 課程號,ame 課程名,sc.grade 成績from Student s join sc on s.sno=sc.snojoin course c on o=o語言程序設計年齡-2.查詢選修了 “ C語言程序設計”的學生的學號和 select s.sno 學號,s.snamefrom sc join course c on o=o and ame='Cjoin Student s on s.sno=sc.sno- -3.查詢與“紅”在同一個系的學生學號、和所屬系。select s.sno 學號,s.sname ,s.sdept 所屬系from Student s

15、where s.sdept=(select sdeptfrom studentwhere sname='紅')- -4.查詢其它系中比信息系的所有學生年齡都大的學生的學號、和年齡select s.sno 學號,s.sname ,DATEDIFF(year,s.sedat,GETDATE() from Student swhere s.sdept<>' 信息'andDATEDIFF(year,s.sedat,GETDATE()>(select max(year(getdate()-year(sedat)from Studentwhere sdep

16、t='信息')- -5.查詢比“王力”年紀大的男學生信息。select * from Studentwhere sgender=' 男'andDATEDIFF(year,sedat,GETDATE() >(select year(getdate()-year(sedat)where sname='王力')- -6.查詢每個學生選修課程的課程號和學號，要求該選修課程的成績超過他選修課程的平均成 績。select sc.sno 學號,o 課程號from sc join (select sno,avg(grade)平均分 from sc grou

17、p by sno) sc1 onsc.sno=sc1.snowhere sc.grade>sc1. 平均分- -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門以上課程的學生的學號和總成績(不統(tǒng)計不及格的課程)

18、，并要求按總成績的 降序排列出來。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 scgroup by snohaving count(*)=(select count(*) from course)-10.查詢至少選修了學生“ 20132001”選修的全部課程的學生的學號和。- -方法select sno 學號,snamefrom Studentwhere sno in(select distinct R1.snofrom sc R1where not exists(select S.cno from (selec