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

1、/*使用SQL Server Management Studio 創(chuàng)建學(xué)生選課數(shù)據(jù)庫創(chuàng)建一個(gè)邏輯名為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學(xué)生選課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學(xué)生選課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;,'計(jì)算機(jī)','2013-09-11'),('20131002',' 紅； 女。計(jì)算機(jī),'2005-08-24'),('20132001',' 國慶',男',信息','2003-10-25'),('20132002',' 歷亦其,男',信息','2004-08-12'),('20131003',' 王大偉,男,計(jì)算機(jī),'2003-06-18'),('2

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

6、se values('101','數(shù)據(jù)結(jié)構(gòu)',4),('102', 數(shù)據(jù)庫 ,3),('103','C語言程序設(shè)計(jì) ,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.查詢?nèi)w學(xué)生的詳細(xì)信息。-2.檢索全體學(xué)生的學(xué)號(hào)、select * from studentselect sno,sname from student- -3.檢索已選課程的課程號(hào)，要求顯示的課程號(hào)不重復(fù)。select distinct。from sc- -4.查詢?nèi)w學(xué)生的及其年

8、齡(提示：使用系統(tǒng)函數(shù) getdate()獲取當(dāng)前日期)。select sname as '',YEAR(GETDATE()-YEAR(sedat) as ' 年齡'from student- -5.查詢成績大于80分的學(xué)生的學(xué)號(hào)、課程號(hào)及成績。select sno as ' 學(xué)號(hào)',cno as ' 課程號(hào)',grade as '成績'from sc where grade>80- -6.查詢成績介于7080分的學(xué)生的學(xué)號(hào)、課程號(hào)及成績。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.查詢選修了課程號(hào)為“ 101”，且成績大于80分的學(xué)生的學(xué)號(hào)。select sno from sc whereo='101' and grade>80- -8.查詢“數(shù)據(jù)結(jié)構(gòu)”和“ C語言程序設(shè)計(jì)”課程的詳細(xì)信息。select * from course whereame='數(shù)據(jù)結(jié)構(gòu)'orame='C 語言程序設(shè)計(jì)'select * from course whe

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

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

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

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

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

15、where s.sdept=(select sdeptfrom studentwhere sname='紅')- -4.查詢其它系中比信息系的所有學(xué)生年齡都大的學(xué)生的學(xué)號(hào)、和年齡select s.sno 學(xué)號(hào),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.查詢比“王力”年紀(jì)大的男學(xué)生信息。select * from Studentwhere sgender=' 男'andDATEDIFF(year,sedat,GETDATE() >(select year(getdate()-year(sedat)where sname='王力')- -6.查詢每個(gè)學(xué)生選修課程的課程號(hào)和學(xué)號(hào)，要求該選修課程的成績超過他選修課程的平均成 績。select sc.sno 學(xué)號(hào),o 課程號(hà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分的學(xué)生的學(xué)號(hào)、最高分和最低分。select sc.sno學(xué)號(hào),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門以上課程的學(xué)生的學(xué)號(hào)和總成績(不統(tǒng)計(jì)不及格的課程)

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