數(shù)據(jù)庫(kù)實(shí)驗(yàn)[驕陽(yáng)教育]

1、實(shí)驗(yàn)三 建表、修改表、刪除表1.建立數(shù)據(jù)庫(kù)jxgl在jxgl數(shù)據(jù)庫(kù)中建立學(xué)生表student,課程表course,選修表sc，建表過(guò)程有如下方式1）以圖形界面操作2）以SQL操作（以下腳本可直接在查詢分析器中執(zhí)行）Create Table Student(Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),Sname VARCHAR(20),Sage SMALLINT CHECK(Sage=15 AND Sage23 AND Ssex=男;檢索至少選修一門課程的女學(xué)生的姓名；select Sname from Student,SCwhere Ssex=女 AND Stu

2、dent.Sno=SC.Sno group by Student.Sname having count(*)=1;或者Select SnameFrom StudentWhere Ssex=女AND Sno in(select sno from SCgroup by snohaving count(*)=1);檢索王同學(xué)不學(xué)的課程的課程號(hào)；select Cnofrom Coursewhere Course.Cno not in (select Cno from SC,Studentwhere SC.Sno=Student.Sno AND Sname LIKE 王%);檢索至少選修兩門課程的學(xué)生學(xué)

3、號(hào)；select DISTINCT Student.Snofrom Student,SCWHERE Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)=2;檢索全部學(xué)生都選修的課程的課程號(hào)與課程名； SELECT Cno,Cnamefrom Course where not exists(select *from studentwhere not exists(select *from SCwhere SC.sno=Student.Sno AND SC.Cno=Course.Cno)或者假設(shè)所有學(xué)生只有兩人SELECT Cno,Cnam

4、efrom Course WHERE Course.Cno in(select Cno from SCgroup by SC.Cnohaving count(Sno)=(select count(*)from Student);SELECT Cno,Cnamefrom Course WHERE Course.Cno in(select Cno from SCgroup by SC.Cnohaving count(Sno)=2);檢索選修了所有3學(xué)分課程的學(xué)生學(xué)號(hào)。select distinct Student.Snofrom Student,SCwhere exists(select *fro

5、m Coursewhere Ccredit =3 AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno);(2) 基于“教學(xué)管理”數(shù)據(jù)庫(kù)jxgl,試用SQL的查詢語(yǔ)句表達(dá)下列查詢：統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)；select count(distinct SC.Cno) FROM SC;求選修4號(hào)課程的學(xué)生的平均年齡；SELECT avg(Student.Sage) from Student,SCwhere Student.Sno=SC.Sno AND Cno=4;SELECT avg(Student.Sage) as 平均年齡from Student,SCwhe

6、re Student.Sno=SC.Sno AND Cno=3;求學(xué)分為3的每門課程的學(xué)生平均成績(jī)；SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit=3group by SC.Cno用group by 語(yǔ)句以課程號(hào)分組注意，如果程序是這樣的：SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit=3AND Course.Cno=SC.Cno;只顯示一門課程的成

7、績(jī)！統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)，要求超過(guò)3人的課程才統(tǒng)計(jì)，要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列；SELECT Cno,count(Sno)from SCGROUP BY Cno HAVING Count(Sno)3order by count(sno)DESC,Cno;SELECT Cno,count(Sno) as 選修人數(shù)from SCGROUP BY Cno HAVING Count(Sno)1order by count(sno)DESC,Cno;檢索學(xué)號(hào)比“王林”同學(xué)大而年齡比他小的學(xué)生姓名；SELECT SnameFROM Studentw

8、here Sno(select Snofrom Studentwhere Sname=王林)AND SageY.Sno AND X.Sage(select avg(Sage)from studentwhere Ssex=女)AND Ssex=男;求年齡大于所有女學(xué)生年齡的男學(xué)生姓名和年齡；SELECT Sname,Sagefrom Studentwhere Sage(SELECT MAX(Sage)from Studentwhere Ssex=女)AND Ssex=男;檢索所有比“王林”年齡大的學(xué)生姓名、年齡和性別；SELECT Sname,Sage,Ssexfrom Studentwhere

9、 Sage(select Sagefrom Studentwhere Sname=王林)檢索選修“2”課程的學(xué)生中成績(jī)最高的學(xué)生的學(xué)號(hào)；SELECT Sno,Gradefrom SCwhere Grade=(select MAX(Grade)from SCwhere Cno=2);注意：不能寫成Grade=MAX(Grade)的形式，因?yàn)椴淮嬖?！Count,min,avg也是檢索學(xué)生姓名以及所選修課程的課程號(hào)和成績(jī)；select Sname,Cno,Gradefrom SC,Studentwhere Student.Sno=SC.Sno;檢索選修4門以上課程的學(xué)生總成績(jī)（不統(tǒng)計(jì)不及格的課程），

10、并要求按總成績(jī)的降序排列出來(lái)。教育專業(yè)b選擇4門以上select sno,sum(Grade) sumfrom SCwhere grade=60 AND Sno in(select Sno from SCgroup by Snohaving count(Sno)4)group by snoorder by sum(grade) desc注意：選擇2門課（包括兩門）select sno,sum(Grade)from SCwhere grade=60 AND Sno in(select Sno from SCgroup by Snohaving count(Sno)=2)group by snoorder by sum(grade) desc再注意：