數(shù)據(jù)庫實驗報告 (3)

1、.實驗內(nèi)容與要求請有選擇地實踐以下各題。（1）基于“教學(xué)管理”數(shù)據(jù)庫jxgl，使用SQL的查詢語句表達(dá)下列查詢：檢索年齡大于23歲的男學(xué)生的學(xué)號和姓名；SELECT Sno,SnameFROM StudentWHERE Ssex=男AND Sage23;檢索至少選修一門課程的女生姓名；SELECT SnameFROM StudentWHERE Ssex=女AND Sno IN(SELECT SnoFROM SCGROUP BY SnoHAVING count(*)=1;);檢索王同學(xué)不學(xué)的課程的課程號；SELECT Cno;FROM CourseWHERE Cno NOT IN(SELECT

2、CnoFROM Student,SCWHERE Sname like 王%AND Student.Sno=SC.Sno);檢索至少選修兩門課程的學(xué)生學(xué)號；SELECT DISTINCT SnoFROM SCGROUP BY SnoHAVING count(*)=2; 檢索全部學(xué)生都選修的課程的課程號與課程名；SELECT Cno,CnameFROM CourseWHERE NOT EXISTS(SELECT * FROM StudentWHERE NOT EXISTS(SELECT *FROM SCWHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno);

3、檢索選修了所有3學(xué)分課程的學(xué)生學(xué)號；SELECT DISTINCT SnoFROM SC XWHERE NOT EXISTS(SELECT *FROM CourseWHERE Ccredit=3 AND NOT EXISTS(SELECT *FROM SC YWHERE X.Sno=Y.Sno AND Course.Cno=Y.Cno);（2） 基于“教學(xué)管理”數(shù)據(jù)庫jxgl，使用SQL的查詢語句表達(dá)下列查詢：統(tǒng)計有學(xué)生選修的課程門數(shù)；SELECT count(DISTINCT Cno)FROM SC;求選修4號課程的學(xué)生的平均年齡；SELECT AVG(Sage)FROM Student,S

4、CWHERE Cno=4 AND Student.Sno=SC.Sno;求學(xué)分為3的每門課程的學(xué)生平均成績；SELECT AVG(Grade)FROM Course,SCWHERE Ccredit=3 AND Course.Cno=SC.CnoGROUP BY SC.Cno;統(tǒng)計每門課程的學(xué)生選修人數(shù)，要求超過3人的課程才統(tǒng)計，要求輸出課程號和選修人數(shù),查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列；SELECT Cno,count(Sno)FROM SC GROUP BY CnoHAVING count(Sno)3ORDER BY count(Sno) DESC,Cno ASC;檢索學(xué)

5、號比“王菲”同學(xué)大而年齡比他小的學(xué)生姓名；SELECT SnameFROM Student XWHERE Sno(SELECT SnoFROM Student YWHERE Sname=王菲AND Sage(SELECT SageFROM Student ZWHERE Sname=王菲AND X.Sno=Z.Sno AND Y.Sno=Z.Sno);檢索姓名以“王”打頭的所有學(xué)生的姓名和年齡；SELECT Sname,SageFROM StudentWHERE Sname LIKE 王%;在SC中檢索成績?yōu)榭罩玫膶W(xué)生學(xué)號和課程號；SELECT Sno,CnoFROM SCWHERE Grade

6、 is NULL;求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡；SELECT Sname,SageFROM Student XWHERE Ssex=男AND Sage(SELECT AVG(Sage)FROM StudentWHERE Ssex=女AND X.Sno=Y.Sno);求年齡大于所有女同學(xué)年齡的男同學(xué)姓名和年齡；SELECT Sname,SageFROM Student XWHERE Ssex=男AND Sage(SELECT MAX(Sage)FROM Student YWHERE Ssex=女AND X.Sno=Y.Sno);檢索所有比“王華”年齡大的學(xué)生姓名，年齡和性別；SEL

7、ECT Sname,Sage,SsexFROM Student XWHERE Sage(SELECT SageFROM Student YWHERE Sname=王華AND X.Sno=Y.Sno);檢索選修“2”課程的學(xué)生中成績最高的學(xué)生和學(xué)號；SELECT Sname,SC.SnoFROM Student,SCWHERE Cno=2 AND Student.Sno=SC.Sno;檢索學(xué)生姓名和其所選修課程的課程號和成績；SELECT Sname,Cno,GradeFROM Student,SCWHERE Student.Sno=SC.SnoGROUP BY Sname;檢索選修4門以上課程

8、的學(xué)生總成績（不統(tǒng)計不及格的課程），并要求按總成績的降序排列出來；SELECT Sno,SUM(Grade)FROM SC XWHERE Grade=60 AND Sno IN(SELECT SnoFROM SC YWHERE X.Sno=Y.SnoGROUP BY SnoHAVING count(Cno)4)ORDER BY SUM(Grade) DESC;（3） 設(shè)有表4-1表4-4的4個基本表（表結(jié)構(gòu)于表內(nèi)容是假設(shè)的），請先創(chuàng)建數(shù)據(jù)庫及根據(jù)表內(nèi)容創(chuàng)建表結(jié)構(gòu)，并添加表記錄，寫出實現(xiàn)以下各題功能的SQL語句：創(chuàng)建以下4各表：CREATE TABLE STUDENT(SNO CHAR(6) P

9、RIMARY KEY,SNAME CHAR(20) UNIQUE,SEX CHAR(2),AGE SMALLINT,CLASS CHAR(4);CREATE TABLE TEACHER(TNO CHAR(3) PRIMARY KET,TNAMW CHAR(20) UNIQUE,SEX CHAR(2),AGE SMALLINT,PROF CHAR(10),DEPT CHAR(10);CREATE TABLE COURSE(CNO CHAR(4) PRIMARY KEY,CNAME CHAR(20) UNIQUE,TNO CHAT(3),FOREIGN KEY TNO REFERENCES TEA

10、CHER(TNO);CREATE TABLE SC(SNO CHAR(6),CNO CHAR(4),GRADE SMALLINT,PRIMARY KEY(SNO,CNO),FOREIGN KEY SNO REFERENCES STUDENT(SNO),FOREIGN KEY CNO REFERENCES COURSE(CNO);插入數(shù)據(jù)：INSERT INTO STUDENTVALUES(980101,李華，男，19，9801);.同上方法依次插入數(shù)據(jù)查詢選修課程“8105”且成績在80到90之間的所有記錄；SELECT *FROM SCWHERE CNO=8105AND GRADE BETW

11、EEN 80 AND 90;查詢成績?yōu)?9，89或99的記錄；SELECT *FROM SCWHERE GRADE IN(79,89,99);查詢“9803”班的學(xué)生人數(shù)；SELECT count(SNO)FROM STUDENTWHERE SNO LIKE 9803%;查詢至少有20名學(xué)生選修的并且課程號以8開頭的課程及平均成績；SELECT CNO,AVG(GRADE)FROM SCWHERE CNO LIKE 8%GROUP BY CNO;HAVING count(SNO)=20;查詢最低分大于80，最高分小于95的SNO與平均分；SELECT SNO,AVG(GRADE)FROM SC

12、GROUP BY SNOHAVING MIN(GRADE)80 AND MAX(GRADE)(SELECT GRATEFROM SC YWHERE CNO=8105AND SNO=980302AND X.SNO=Y.SNO);查詢與學(xué)號為“980103”的同學(xué)同歲的所有學(xué)生的SNO,SNAME和AGE；SELECT SNO,SNAME,AGEFROM STUDENT XWHERE AGE=(SELECT AGEFROM STUDENT YWHERE SNO=980103AND X.SNO=Y.SNO);查詢“錢軍”教師任課的課程號，以及選修其課程學(xué)生的學(xué)號和成績；SELECT SC.CNO,S

13、NO,GRADEFROM SC,TEACHRT,COURSEWHERE TNAME=錢軍AND TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CON;查詢選修某課程的學(xué)生人數(shù)多于20人的教師姓名；SELECT DISTINCT TNAMEFROM TEACHER,SC,COURSEWHERE TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CONGROUP BY SC.CNOHAVING count(SNO)20;11查詢選修編號為“8105”課程且成績至少高于其選修編號為“8245”課程成績的同學(xué)的SNO及“8105”課程

14、成績，并按成績從高到低依次排列；SELECT SNO GRADEFROM SC XWHERE CNO=8105AND GRADE(SELECT GRADEFROM SC YWHERE CNO=8245AND X.SNO=Y.SNO)ORDER BY GRADE DESC;12查詢選修編號為“8105”課程且成績高于所有選修編號為“8245”課程成績的同學(xué)的CNO、SNO、GRADE；SELECT CNO,SNO,GRADEFROM SCWHERE CNO=8105AND GRADE(SELECT MAX(GRADE)FROM SCWHERE CNO=8245AND X.CNO=Y.SNO);1

15、3列出所有教師和同學(xué)的姓名，SEX,AGE；SELECT TNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENT.SEX,STUDENT.AGEFROM TEACHER,SC14查詢成績比該課程平均成績高的學(xué)生的成績表；SELECT *FROM SC.XGROUP BY SNOHAVING GRADE(SELECT AVG(GRADE)FROM SC.YWHERE X.CNO=Y.CNOGROUP BY CNO);15列出所有任課教師的TNAME和DEPT；SELECT TNAME,DEPTFROM TEACHER,COURSEWHERE TEACHER.TNO

16、=COURSE.TNO16列出所有未講課教師的TNAME和DEPT;SELECT TNAME,DEPYFROM TEACHERWHERE NOT EXISTS(SELECT *FROM COURSEWHERE TEACHER.TNO=COURSE.TNO);17列出至少有4名男生的班號；SELECT CLASSFROM STUDENTGROUP BY CLASSHAVING count(SNO)=4;18查詢不姓“張”的學(xué)生記錄；SELECT *FROM STUDENTWHERE SNAME NOT LIKE 張%;19查詢每門課最高分的學(xué)生的SNO,CNO,GRADE;SELECT SNO,

17、CNO,GRADEFROM SCGROUP BY CNOHAVING GRADE=MAX(GRADE);20查詢與“李華”同性并同班的同學(xué)SNAME；SELECT SNAMEFROM STUDENT XWHERE CLASS=(SELECT CLASSFROM STUDENT YWHERE SNAME=李華AND SEX=(SELSCT SEXFROM STUDENT ZWHERE SNAME=李華AND X.SNO=Y.SNO AND Y.SNO=Z.SNO);21查詢“女”教師及其所上的課程；SELECT TNAME,CNO,CNAMEFROM TEACHER,COURSEWHERE TEACHER.TNO=COURSE.TNO,SEX=女;22查詢選修“數(shù)據(jù)庫系統(tǒng)”課程的“男”同學(xué)的成績表；SELECT *FROM SC,COURSE,STUDENTWHERE STUDENT.SNO=SC.SNO AND COURSE.C