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

1、用SQL語(yǔ)句創(chuàng)建如下三個(gè)基本表：學(xué)生表 (Student)、課程表(Course)、學(xué)生選課表 (SC),結(jié)構(gòu)如下所示Student表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束Sno學(xué)號(hào)字符串，長(zhǎng)度為7主碼Sname姓名字符串，長(zhǎng)度為10非空Ssex性別字符串，長(zhǎng)度為2取'男'或'女Sage年齡整數(shù)取值1545Sdep t所在院系字符串，長(zhǎng)度為20默認(rèn)為，計(jì)算機(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ì)算機(jī)系')Course表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束Cno課程號(hào)字符串，長(zhǎng)度為10主碼Cname課程名字符串，長(zhǎng)度為20非空Ccredit學(xué)分整數(shù)取值大于0Semester學(xué)期整數(shù)取值大于0Period學(xué)時(shí)整數(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)列名說(shuō)明數(shù)據(jù)類型約束Sno學(xué)號(hào)字符串，長(zhǎng)度為7主碼,引用Student的外碼Cno課程號(hào)字符串，長(zhǎng)度為10主碼，引用Course的外碼Grade成績(jī)整數(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ì)算機(jī)系學(xué)生的姓名、年齡。Select Sname, SageFrom StudentWhere Sdept二'計(jì)算機(jī)系3. 查詢成績(jī)?cè)?080分之間的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。Select Sno, Cno, GradeFrom Course, ScWhere course, eno二sc Cno and sc.Grade between 70 and 804. 查詢計(jì)算機(jī)系年齡在1820之間且性別為“男”的學(xué)生的姓名和年齡。S

5、elect Sname, SageFrom StudentWhere Sage between 18 and 20 and Ssex=， 男'and Sdept二' 計(jì) 算機(jī)系'go5. 查詢課程號(hào)為“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ì)算機(jī)系學(xué)生的最大年齡和最小年齡。Select max (Sage) as 年齡最大，mi

6、n (Sage) as 年齡最小From StudentWhere Sdept二'計(jì)算機(jī)系'7. 統(tǒng)計(jì)每個(gè)系的學(xué)生人數(shù)。Select count (Sdept) as 學(xué)生人數(shù)，SdeptFrom StudentGroup by Sdept8. 統(tǒng)計(jì)每門課程的選課人數(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)計(jì)每個(gè)學(xué)生的選課門數(shù)和考試平均成績(jī)，并按學(xué)號(hào)的升序顯示 結(jié)果。Sel

7、ect sno, avg (grade) as '平均成績(jī)',count (cno) as '選課門數(shù)Group by snoOrder by sno10. 查詢總成績(jī)超過(guò)200分的學(xué)生，要求列出學(xué)號(hào)、總成績(jī)。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. 查詢成績(jī)?cè)?0分以上

8、的學(xué)生的姓名、課程號(hào)和成績(jī)，并按成績(jī) 的降序排列結(jié)果。Select sl sname, s2 eno, s2 gradeFrom student si,sc s2Where sl sno=s2 sno and grade >80Order by grade desc13. 查詢哪些課程沒有人選修、要求列出課程號(hào)和課程名。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. 用子查詢實(shí)現(xiàn)如下查詢：(1) 查詢選修了課

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

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

11、 eno from courseWhere cname='計(jì)算機(jī)文化基礎(chǔ)'）18. 創(chuàng)建查詢學(xué)生的學(xué)號(hào)、姓名、所在系、課程號(hào)、課程名、課程學(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)建查詢每個(gè)學(xué)生

12、的平均成績(jī)的視圖，要求列岀學(xué)生學(xué)號(hào)及平 均成績(jī)。Create view s_avgAsSelect sno, avg (Grade) as 平均成績(jī) from scGroup by sno20. 創(chuàng)建查詢每個(gè)學(xué)生的選課學(xué)分的視圖，要求列岀學(xué)生學(xué)號(hào)及總學(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語(yǔ)句創(chuàng)建一個(gè)名為f_l的函

13、數(shù)，該函數(shù)能夠求出3到100之間的所有素?cái)?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說(shuō)明之前沒有比虹小的數(shù)字可以把匚整除BeginSet sum=sum+iEndEndReturn sumEndGoSelect dbo. f_l ()22. 用SQL語(yǔ)句

14、創(chuàng)建一個(gè)名為f_2的函數(shù)，該函數(shù)能夠求出任意兩 個(gè)數(shù)的最大值。Create function f_2 (xl int, x2 int比七吐皿 intAsBeginDeclare max intIf xl>x2Return maxEndSelect dbo. f_2 (2, 6)23. 用SQL語(yǔ)句創(chuàng)建一個(gè)名為pro_get_stu_information的存儲(chǔ)過(guò) 程，該存儲(chǔ)過(guò)程能夠根據(jù)用戶指定的Sno(學(xué)號(hào))求岀與該學(xué)號(hào)對(duì) 應(yīng)的學(xué)生姓名、課程名、成績(jī)。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''