數(shù)據(jù)庫實驗[驕陽教育]

1、實驗三 建表、修改表、刪除表1.建立數(shù)據(jù)庫jxgl在jxgl數(shù)據(jù)庫中建立學生表student,課程表course,選修表sc，建表過程有如下方式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=男;檢索至少選修一門課程的女學生的姓名；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);檢索王同學不學的課程的課程號；select Cnofrom Coursewhere Course.Cno not in (select Cno from SC,Studentwhere SC.Sno=Student.Sno AND Sname LIKE 王%);檢索至少選修兩門課程的學生學

3、號；select DISTINCT Student.Snofrom Student,SCWHERE Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)=2;檢索全部學生都選修的課程的課程號與課程名； SELECT Cno,Cnamefrom Course where not exists(select *from studentwhere not exists(select *from SCwhere SC.sno=Student.Sno AND SC.Cno=Course.Cno)或者假設所有學生只有兩人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學分課程的學生學號。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) 基于“教學管理”數(shù)據(jù)庫jxgl,試用SQL的查詢語句表達下列查詢：統(tǒng)計有學生選修的課程門數(shù)；select count(distinct SC.Cno) FROM SC;求選修4號課程的學生的平均年齡；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;求學分為3的每門課程的學生平均成績；SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit=3group by SC.Cno用group by 語句以課程號分組注意，如果程序是這樣的：SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit=3AND Course.Cno=SC.Cno;只顯示一門課程的成

7、績！統(tǒng)計每門課程的學生選修人數(shù)，要求超過3人的課程才統(tǒng)計，要求輸出課程號和選修人數(shù)，查詢結果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列；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;檢索學號比“王林”同學大而年齡比他小的學生姓名；SELECT SnameFROM Studentw

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

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

10、并要求按總成績的降序排列出來。教育專業(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再注意：