数据库实验报告 (3).doc

实验内容与要求
请有选择地实践以下各题。
(1）基于“教学管理”数据库ｊxgl,使用ＳQＬ得查询语句表达下列查询:
①检索年龄大于２３岁得男学生得学号与姓名;
ＳELECT Snｏ，Snamｅ
FROM Sｔudｅnｔ
WHEＲE Ｓｓex=’男'AND
　Ｓaｇe〉23;
②检索至少选修一门课程得女生姓名;
SELEＣT Snａmｅ
FROM Ｓtudｅnt
WHＥRE Ssex＝’女’AND Sｎo　IN
（ﻩSELECＴ Sno
ＦRＯM SC
GROUP BY　Sｎｏ
HAＶIＮG coｕｎt（*)>=1;
);
③检索王同学不学得课程得课程号;
SELECT Cno;
FROM Courｓe
WHEＲE　Cno NOT IN
( ＳELEＣT Cnｏ
FRＯＭ Ｓtuｄenｔ,ＳＣ
ＷＨERE Sｎame　liｋｅ ’王%'ＡND　Ｓtｕdeｎt、Sno=ＳC、Snｏ
);
④检索至少选修两门课程得学生学号;
ＳＥLＥCT DISＴIＮCＴ Sｎo
FＲOM SC
GＲOUP BY Sｎo
HAＶＩＮG coｕnt（*)>=2;
⑤检索全部学生都选修得课程得课程号与课程名；
SELEＣT Cno,Cｎame
FＲOM Course
WＨＥＲE NＯT EＸISTS
( SELEＣT ＊　
ＦROM Student
WHERE NOT ＥXISTＳ
（ﻩSＥＬECＴ　＊
FROM SC
WHＥRE　SC、Ｓno=Stuｄenｔ、Sno AND SCo=Courｓeo
)
)；
⑥检索选修了所有3学分课程得学生学号;
SEＬECＴ DＩSTIＮCT Sｎｏ
FRＯM　ＳC X
ＷHERＥ　NOT　EXIＳTＳ
( ＳEＬECT *
FRＯM Cｏｕrsｅ
WHERE　Ｃcｒeｄiｔ＝3 AND NOＴ　ＥXISＴＳ
( ＳELEＣＴ　*
FＲOM SC　Y
ＷHERE　X、Ｓｎo＝Y、Sｎo AND　Ｃoｕｒseo=Ｙo
)
）;
（2） 基于“教学管理”数据库ｊxgl，使用SQL得查询语句表达下列查询:
①统计有学生选修得课程门数;
ﻩSELECT count(DＩSTIＮＣT Cno)
FROM SC;
②求选修4号课程得学生得平均年龄;
ﻩSEＬECT AVＧ(Sage)
FROM　Student,SC
ＷHERE Ｃｎo=４ ＡND Studenｔ、Snｏ=SC、Ｓnｏ;
③求学分为3得每门课程得学生平均成绩;
ＳELEＣT AVG(Grａdｅ)
FRＯM Cｏurse,ＳＣ
WHEＲE Cｃreｄit=3　AＮD Courseｏ=SCo
GROUP ＢY　SＣo;
④统计每门课程得学生选修人数,要求超过3人得课程才统计,要求输出课程号与选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列；
SELＥＣＴ　Cnｏ，couｎt（Sｎo）
FROM SC
GRＯUP　BＹ Cno
HＡVING couｎt(Sno）〉3
ORDER　ＢＹ　count（Ｓno) DＥSＣ，Cｎo AＳＣ;
⑤检索学号比“王菲”同学大而年龄比她小得学生姓名；
SELEＣT Snａmｅ
ＦROM Studｅnt X
WHＥＲＥ Ｓno>
（ﻩSEＬECT Sｎo
FROM Ｓtudent Y
WHERE　Sname=’王菲’AND　Sａge〉
(ﻩＳＥLEＣT Sage
FROM Ｓｔudent Z
ＷHEＲE Snaｍe＝’王菲’ＡNＤ X、Sno=Z、Sno AＮD　Y、Sno=Z、Ｓno
)
）；
⑥检索姓名以“王”打头得所有学生得姓名与年龄；
SＥLECT Sｎame,Sａge
FROM Sｔuｄent
WＨERE　Ｓnaｍｅ LIＫＥ ‘王%＇;
⑦在SC中检索成绩为空置得学生学号与课程号;
SＥLEＣＴ Sno,Cno
FＲOM　SC
ＷＨERE Grade is　NULL;
⑧求年龄大于女同学平均年龄得男学生姓名与年龄;
SＥLECT Sｎame，Sagｅ
ＦＲOM　Stuｄent X
WＨＥRE　Ssex=’男’ＡND Sage〉
(ﻩSEＬECT AVG（Ｓａｇe)
FRＯM Student
ＷＨERE Ssex=＇女’ANＤ X、Sno=Y、Sｎｏ
）;
⑨求年龄大于所有女同学年龄得男同学姓名与年龄;
SＥLECT Sname，Sage
FROM Ｓtudｅnt　X
WHEＲE　Ｓｓeｘ＝’男’ＡND Saｇe〉
（ ＳELEＣT　MAＸ(Sage)
FROM Sｔuｄeｎt Y
WHERＥ Ssex=＇女'ANＤ Ｘ、Ｓno=Ｙ、Sｎo
);
⑩检索所有比“王华”年龄大得学生姓名,年龄与性别；
SＥLＥCT Ｓｎamｅ,Ｓagｅ,Ssex
FＲOM Studｅｎｔ　X
WＨERE　Ｓaｇe>
( ＳEＬECＴ　Sage
ＦＲＯＭ　Sｔｕdent Y
ＷHERE Sname=’王华'AND　Ｘ、Sno=Y、Ｓｎo
);
①检索选修“2”课程得学生中成绩最高得学生与学号；
SELECT　Snａme,ＳC、Sno
FROＭ Stｕｄenｔ，ＳＣ
ＷHEＲE Cno＝２　AＮＤ Stｕｄｅｎt、Sno=ＳC、Sno；
②检索学生姓名与其所选修课程得课程号与成绩;
ＳＥLECT Snａme,Cnｏ,Gｒade
ＦROM Ｓtudｅｎｔ，SC
WHＥRE　Sｔｕdent、Ｓno=ＳC、Sｎo
ＧROUＰ BY Sｎamｅ;
③检索选修４门以上课程得学生总成绩(不统计不及格得课程),并要求按总成绩得降序排列出来；
SＥＬECＴ Sｎo，SUＭ（Gradｅ)
FROM　SＣ X
WＨERE Gradｅ〉＝６0 ＡＮD　Sｎｏ IN
(ﻩSＥＬＥＣＴ Sｎｏ
FROＭ SC Ｙ
WHＥＲＥ Ｘ、Ｓｎo=Y、Sｎｏ
GＲOUＰ　ＢY Sｎｏ
HAＶINＧ ｃoｕｎt(Cno)〉４
)
`ﻩＯRＤER　ＢY SＵM(Graｄｅ) ＤESC；
（3） 设有表4－1～表４—4得4个基本表(表结构于表内容就是假设得),请先创建数据库及根据表内容创建表结构,并添加表记录,写出实现以下各题功能得SＱL语句:
创建以下4各表:
CREATＥ TABLE　STUＤEＮＴ
(
SNＯ CHAR(６)　PRIMARＹ　KEY,
ＳNAME CHAR（20)　UNIQUE,
SEＸ CHAR（2),
ＡＧE　SMＡLLINT，
CＬASS　ＣHAR（４)
)；
CREATＥ TABＬＥ TEＡCHER
（
TＮO CHAR(3) PRＩMARＹ　ＫET，
TＮAMW CHＡR(2０) UNＩQUE,
ＳＥX ＣHAR(2),
ＡＧE　SMＡLLIＮT,
PROF CHAR(10),
DEＰＴ　CHAR(1０)
);
ＣREATＥ TABLＥ ＣOURＳE
(
CNO　CHAR（4） PRＩMARY ＫEY,
CNAＭE CＨAＲ（20)　ＵNIQUE,
TNO CＨAT（3)，
FOＲEIGN ＫEY ＴNO　REFERENCES　TEACＨER（TNO)
);
CREATE ＴＡBLE　SC
（
SＮＯ CHＡR(6），
CＮO ＣＨAR(4),
ＧRAＤE　SMALLINT,
PRIMARY KEY(SNＯ,ＣNO),
FOREIＧN　KEY SＮO REFEＲEＮＣES STＵDENT（ＳＮO）,
ＦOREＩGN ＫEY CＮO　REFERENCＥS　COURＳＥ（CNO)
）；
插入数据：
IＮＳERT ＩＮＴO ＳTＵDENT
VALUES(‘９80１01',’李华’,‘男’，19,’9８01’）;
、、、、、、
同上方法依次插入数据
①查询选修课程“8105”且成绩在８0到90之间得所有记录；
SEＬECT ＊
FROＭ　ＳC
WHEＲＥ CNO＝’8１０5’ANＤ ＧRADＥ　BETWEＥN　80 ＡND　90；
②查询成绩为7９,89或９９得记录；
SＥＬＥＣＴ *
FROM SＣ
WHEＲE　ＧRAＤＥ IN(79,89，９９）;
③查询“98０３"班得学生人数；
SELECT　ｃｏｕnt(SNO)
ＦRＯＭ STUDENT
WHEＲＥ SNO LIKE ‘98０3％’；
④查询至少有20名学生选修得并且课程号以8开头得课程及平均成绩；
ＳＥLＥCT ＣNO,AVG(GRAＤＥ)
FROM　SC
WHERＥ CＮO ＬIKＥ ‘8％’
GROUP　BY　CNＯ；
ＨＡVＩNＧ ｃouｎt（SNO)〉＝20;
⑤查询最低分大于8０,最高分小于９5得SＮO与平均分;
ＳELＥCＴ SＮO,AVG(ＧRADＥ）
FROＭ ＳC
GROUP BY SNO
HＡＶING　MIN(GRADE）〉８０ AND MAＸ（ＧRAＤE)＜95；
⑥查询“9803"班得学生所选各课程得课程号及平均成绩;
SＥＬECＴ CＮO，ＡVＧ（GＲADE）
FROM ＳＣ
WHＥRE SNO LＩKE ‘9803’
ＧＲOUP BＹ CNＯ;
⑦查询选修“8105”课程得成绩高于“98０302"号同学成绩得所有同学得记录;
SELＥCT *
FROM　SＣ X
WHERE CNＯ=’８１05’AND GRATE〉
（
SELＥＣＴ GRATE
ＦRＯM SＣ Ｙ
WＨERＥ　CNO＝’8１05'AＮＤ ＳNO='9803０2’AＮD X、ＳNＯ=Ｙ、SNO
）;
⑧查询与学号为“98０103”得同学同岁得所有学生得ＳNO，SNAME与AGE;
ＳELEＣT SNO,SNＡME，AGE
FＲOM　SＴＵDENT　Ｘ
WHERE　AGＥ=
（
SELECT AGＥ
FROM SＴUＤEＮＴ　Y
WHERE SNO=’9801０３’AND　Ｘ、SＮO＝Ｙ、SNO
);
⑨查询“钱军”教师任课得课程号,以及选修其课程学生得学号与成绩;
ＳELECＴ　SCＯ,SＮO,GＲADE
FＲＯM ＳC,ＴEACＨRT，CＯＵRＳE
ＷHＥRE ＴNAME=’钱军’ANＤ　TEACHＥR、ＴNO=COURSE、TNO　AND　ＳC、CON=COURSE、CON；
⑩查询选修某课程得学生人数多于20人得教师姓名;
SEＬECT DISＴINＣT TNAＭE
FＲＯＭ TEAＣHEＲ,SC，CＯＵＲＳＥ
WＨERE TEAＣHＥＲ、TNO＝COＵRＳＥ、TＮO ＡNＤ ＳC、CＯN＝CＯURＳE、ＣＯＮ
GＲOUP　ＢY SCO
HAＶIＮG　count（ＳNO）＞20;
11查询选修编号为“81０5＂课程且成绩至少高于其选修编号为“8245”课程成绩得同学得
SＮＯ及“81０5”课程成绩，并按成绩从高到低依次排列；
ＳELEＣT SNO ＧRADE
FROＭ SＣ X
WＨERＥ ＣNO='８105’AND　GRADE〉
（
ＳEＬECT GRＡDＥ
FROM SＣ Y
WＨERE ＣNO='８245’AND X、ＳNO=Y、SNＯ
)
ORDER BY GRADE DESC;
12查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩得同学得ＣＮO、ＳNO、GＲＡＤE;
SＥLECT　CNＯ，ＳNO,ＧRAＤE
FRＯM SＣ
WHＥRＥ CNO='8105’AND GRADE〉
(
SEＬECT　MAＸ（GRAＤＥ)
FＲOM ＳC
ＷHEＲE ＣＮＯ＝’824５'AＮD　ＸO=Ｙ、SNO
);
1３列出所有教师与同学得姓名,SEX,AGE;
SEＬECT ＴNAME,TEAＣHEＲ、SEX,TＥＡＣHER、AGＥ，SNAME，STUDENＴ、SEX,STUＤEＮT、AGE
FROM TEＡＣHER，SC
14查询成绩比该课程平均成绩高得学生得成绩表；
SEＬECT　＊
FROＭ ＳC、X
ＧROUP BY SNＯ
HAＶING GRADE〉
(ﻩ
SＥＬＥＣT AVG（GRAＤE)
FRＯＭ SＣ、Y
WHERＥ　ＸO=YO
GROUP BＹ CＮＯ
）;
15列出所有任课教师得TNAME与ＤEPＴ;
SEＬＥＣT　ＴNＡME，DEPT
ＦROM ＴEACHER,COＵRＳE
ＷHERE　ＴEACHＥR、TNＯ=COURSE、TＮＯ
16列出所有未讲课教师得ＴNＡＭE与DEPT;
SELEＣT ＴNＡMＥ,DEPＹ
FROM　TEACHER
WＨERE ＮOＴ ＥXIＳTS
(
SＥLECＴ　＊
ＦROＭ ＣOＵRSＥ
WHERE TＥACHＥR、TNO＝CＯURSE、TNO
)；
17列出至少有4名男生得班号;
SEＬECＴ　CLASS
ＦROＭ STUDENT
GROUＰ BY　ＣＬASＳ
HAVING count(SNＯ)〉=４;
１8查询不姓“张"得学生记录;
SELECT　＊
FROM　STUDEＮT
WHＥＲE SNAＭＥ NＯT LIKＥ　‘张%’；
19查询每门课最高分得学生得ＳNＯ,CNＯ,GＲＡDＥ;
SEＬECT ＳＮO，CNO,GＲＡＤE
FROM SC
ＧROＵP　BY CNＯ
ＨＡVING ＧRADE=MAX(GＲADE）;
20查询与“李华”同性并同班得同学SNAMＥ；
SＥLEＣT　ＳNAＭE
FROM　SＴUDENT　X
WHERＥ CLASS＝
(
ＳELECT CLASS
ＦＲOM SＴUDENT Y
WHERE ＳNＡME=’李华’AND SＥＸ=
(
SELSCT　SEX
FROＭ SＴUＤENＴ Ｚ
ＷHERE SNＡME＝’李华’AND X、ＳＮO＝Y、SNＯ AＮD Y、SNO=Z、ＳNO
)
）；
21查询“女”教师及其所上得课程;
SELECT TＮAME,CNＯ，CNAME
ＦＲOM TEACHER，CＯURSE
ＷＨERＥ TEAＣHER、TNO=ＣＯＵRＳE、TNＯ，SＥX=’女'；
2２查询选修“数据库系统”课程得“男”同学得成绩表;
ＳＥＬEＣT ＊
ＦＲOＭ SC,ＣOURSE,SＴＵDENT
ＷHEＲE STUＤENT、SNＯ=SＣ、SＮO ANＤ ＣＯＵRSEO=ＳCＯ　AND CＮＡME＝＇数据库系统’
AND SEＸ=’男’；
23查询所有比刘涛年龄大得教师姓名,年龄与刘涛得年龄;
ＳEＬＥCＴ TNＡME，ＡＧＥ
FROＭ ＴEACHER X
ＷHERE SNＡME＝’刘涛'ＯＲ　ＡGE>
(
SELECT AGE
FＲOM　TEACHER　Y
WＨERＥ　Ｘ、TNO=Y、TNO　AND SＮAME=’刘涛’
）；
２4查询不讲授“８106”号课程得教师姓名.
ＳELECT TNAME
FROＭ　TＥAＣHER，CＯUＲSE
WHＥRE TＥＡCＨER、TNＯ=COURＳE、TNO AND CNO NOＴ IN（8106);