SQL語句創(chuàng)建學(xué)生信息數(shù)據(jù)庫表的示例

1、用SQL語句創(chuàng)建如下三個基本表：學(xué)生表 (Student)、課程表(Course)、學(xué)生選課表 (SC),結(jié)構(gòu)如下所示Student表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號字符串，長度為7主碼Sname姓名字符串，長度為10非空Ssex性別字符串，長度為2取'男'或'女Sage年齡整數(shù)取值1545Sdep t所在院系字符串，長度為20默認(rèn)為，計算機(jī)系，Create table StudentSno varchar (7) primary key,Sname varchar(10) not null,Ssex char (2) check(Ssex='男or Ssex

2、=，女'),1 / 13Sage int check(Sage between 15 and 45),Sdept varchar (20) default ('計算機(jī)系')Course表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Cno課程號字符串，長度為10主碼Cname課程名字符串，長度為20非空Ccredit學(xué)分整數(shù)取值大于0Semester學(xué)期整數(shù)取值大于0Period學(xué)時整數(shù)取值大于0Create table course(Cno varchar (10) primary key,Cname varchar (20) not null,Ccredit int check(Scte

3、dit>0),Semester int check(Semester>0)，Period int check(Period>0)SC表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號字符串，長度為7主碼,引用Student的外碼Cno課程號字符串，長度為10主碼，引用Course的外碼Grade成績整數(shù)取值0100Create table SC(Sno varchar(7) foreign key references student(Sno),Cno varchar(10) foreign key references course(Cno),Grade int check(Grade

4、between 0 and 100),Primary key (Sno,Cno)1. 查詢學(xué)生選課表中的全部數(shù)據(jù)。SELECT *FROM SCgo2. 查詢計算機(jī)系學(xué)生的姓名、年齡。Select Sname, SageFrom StudentWhere Sdept二'計算機(jī)系3. 查詢成績在7080分之間的學(xué)生的學(xué)號、課程號和成績。Select Sno, Cno, GradeFrom Course, ScWhere course, eno二sc Cno and sc.Grade between 70 and 804. 查詢計算機(jī)系年齡在1820之間且性別為“男”的學(xué)生的姓名和年齡。S

5、elect Sname, SageFrom StudentWhere Sage between 18 and 20 and Ssex=， 男'and Sdept二' 計 算機(jī)系'go5. 查詢課程號為“C01”的課程的最高分?jǐn)?shù)。Select top 1 Gradeselect max(Grade) as 最iH分from ScWhere Cno二'C01where Cno二'C01Order by Grade descorder by Grade desc6. 查詢計算機(jī)系學(xué)生的最大年齡和最小年齡。Select max (Sage) as 年齡最大，mi

6、n (Sage) as 年齡最小From StudentWhere Sdept二'計算機(jī)系'7. 統(tǒng)計每個系的學(xué)生人數(shù)。Select count (Sdept) as 學(xué)生人數(shù)，SdeptFrom StudentGroup by Sdept8. 統(tǒng)計每門課程的選課人數(shù)和考試最高分。Select count (Sno) as 選 課人數(shù)，c. Sno, max (Grade) as 最高分From Course c left join Sc s on c. eno二s CnoGroup by c. Cno9. 統(tǒng)計每個學(xué)生的選課門數(shù)和考試平均成績，并按學(xué)號的升序顯示 結(jié)果。Sel

7、ect sno, avg (grade) as '平均成績',count (cno) as '選課門數(shù)Group by snoOrder by sno10. 查詢總成績超過200分的學(xué)生，要求列出學(xué)號、總成績。Select sno, sum(grade)From scGroup by snoHaving sum(grade)>20011. 查詢選修了課程“C02”的學(xué)生的姓名和所在系。Select sname, sdeptFrom student si,sc s2Where slsno=s2 sno and s2eno二'c0212. 查詢成績在80分以上

8、的學(xué)生的姓名、課程號和成績，并按成績 的降序排列結(jié)果。Select sl sname, s2 eno, s2 gradeFrom student si,sc s2Where sl sno=s2 sno and grade >80Order by grade desc13. 查詢哪些課程沒有人選修、要求列出課程號和課程名。Select c. eno, c. cnameFrom course c left join sc s on c. eno二s enoGroup by c.eno,c. cnameHaving count(s.sno)=014. 用子查詢實現(xiàn)如下查詢：(1) 查詢選修了課

9、程“C01”的學(xué)生的姓名和所在系。Select sname, sdept , snoFrom studentWhere sno in (Select snoFrom scWhere eno二'c01)(2) 查詢信息系成績在80分以上的學(xué)生的學(xué)號、姓名。Select sno, snameFrom studentWhere sdept='夕卜語系'and sno in (Select snoWhere grade>80)(3) 查詢計算機(jī)系考試成績最高的學(xué)生的姓名。Select sl sname from studentsWhere sdept=,計算機(jī)系'

10、and sno in(select sno from scWhere grade in(select max(Grade)from sc)15. 刪除選課成績小于50分的學(xué)生的選課記錄。Delete from scWhere grade<70Select* from sc一驗證16. 將所有選修了課程“C01”的學(xué)生的成績加10分：Update scSet grade=grade+10Where eno二'c0117. 將計算機(jī)系所有選修了課程“計算機(jī)文化基礎(chǔ)”課程的學(xué)生的 成績加10分。Update scSet grade=grade+10Where eno in（select

11、 eno from courseWhere cname='計算機(jī)文化基礎(chǔ)'）18. 創(chuàng)建查詢學(xué)生的學(xué)號、姓名、所在系、課程號、課程名、課程學(xué)分的視圖。Select* from courseSelect* from studentsSelect* from scCreate view學(xué)生基本信息AsSelect students sno, sname, sdept, sc. eno, cname, ccreditFrom course, sc, studentsWhere course, eno二sc enoAnd sc. cno=students sno19. 創(chuàng)建查詢每個學(xué)生

12、的平均成績的視圖，要求列岀學(xué)生學(xué)號及平 均成績。Create view s_avgAsSelect sno, avg (Grade) as 平均成績 from scGroup by sno20. 創(chuàng)建查詢每個學(xué)生的選課學(xué)分的視圖，要求列岀學(xué)生學(xué)號及總學(xué)分。Create view s_scAsSelect students sno, sum(cctedit)as 總學(xué)分 fromStudents, sc, courseWhere students. sno=sc snoAnd sc. cno=course enoGroup by students sno21. 用SQL語句創(chuàng)建一個名為f_l的函

13、數(shù)，該函數(shù)能夠求出3到100之間的所有素數(shù)之和。Create function f_l ()Returns intAsBeginDeclare a int,b int,i int, ©sum intSet i=3Set sum二0Wh訂e i<101BeginSet b=0While a<=i/2BeginIf i%a=OBeginSet b=lBreakEndSet a=a+lEndIf b=0 b為0說明之前沒有比虹小的數(shù)字可以把匚整除BeginSet sum=sum+iEndEndReturn sumEndGoSelect dbo. f_l ()22. 用SQL語句

14、創(chuàng)建一個名為f_2的函數(shù)，該函數(shù)能夠求出任意兩 個數(shù)的最大值。Create function f_2 (xl int, x2 int比七吐皿 intAsBeginDeclare max intIf xl>x2Return maxEndSelect dbo. f_2 (2, 6)23. 用SQL語句創(chuàng)建一個名為pro_get_stu_information的存儲過 程，該存儲過程能夠根據(jù)用戶指定的Sno(學(xué)號)求岀與該學(xué)號對 應(yīng)的學(xué)生姓名、課程名、成績。Create procedure pro_get_stu_information m char (6) outputAsSelect sname, cname, grade from students,sc,courseWhere students. sno=sc sno andsc. eno二course, enoandsc. sno二mExec pro_get_stu_information''