第三章课后作业教材.docx
《第三章课后作业教材.docx》由会员分享,可在线阅读,更多相关《第三章课后作业教材.docx(10页珍藏版)》请在冰豆网上搜索。
第三章课后作业教材
第三章习题
2013312055王洁
习题3.2
(1)检索年龄小于17岁的男学生的学号和姓名。
SELECTSNOSNAME
FROMS
WHEREAGE<'17'ANDSEX='M'
(2)检索男学生所学课程的课程号和课程名。
SELECTCNO,CNAME
FROMC
WHERECNOIN(SELECTCNO
FROMSC
WHERESNOIN(SELECTSNO
FROMS
WHERESEX='M'));
(3)检索男学生所学课程的任课老师的工号和姓名
SELECTTNO,TNAME
FROMT
WHERETNOIN(SELECTTNO
FROMC
WHERECNOIN(SELECTCNO
FROMSC
WHERESNOIN(SELECTSNO
FROMS
WHERESEX='M')));
(4)检索至少选修两门课程的学生学号。
SELECTDISTINCTX.SNO
FROMSCASX,SCASY
WHEREX.SNO=Y.SNOANDX.CNO!
=Y.CNO
(5)检索至少有学号01和02学生选修的课程的课程号
SELECTDISTINCTX.CNO
FROMSCASX,SCASY
WHEREX.CNO=Y.CNOANDX.SNO='01'ANDY.SNO='02';
(6)检索小红同学不学的课程的课程号
SELECTCNO
FROMC
WHERENOTEXISTS(SELECT*
FROMS,SC
WHERES.SNO=SC.SNOANDSC.CNO=C.CNOANDSNAME='小红');
(7)检索全部学生都选修的课程的课程号与课程名
SELECTCNO,CNAME
FROMC
WHERENOTEXISTS(SELECT*
FROMS
WHERENOTEXISTS(SELECT*
FROMSC
WHERESC.SNO=S.SNOANDSC.CNO=C.CNO));
(8)检索选修课程中包含马艳老师所授全部课程的学生学号
SELECTDISTINCTSNO
FROMSCASX
WHERENOTEXISTS
(SELECT*
FROMC,T
WHEREC.TNO=T.TNOANDTname='杨红'
ANDNOTEXISTS
(SELECT*
FROMSCASY
WHEREY.SNO=X.SNOANDY.CNO=X.CNO));
习题3.4
设有两个基本表R(A,B,C)和S(A,B,C),试用SQL查询语句表达下列关系代数表达式:
① (SELECT * FROM R) UNION
(SELECT * FROM S);
② (SELECT * FROM R) INTERSECT
(SELECT * FROM S);
③ (SELECT * FROM R) MINUS
(SELECT * FROM S);
④ SELECT *
FROM R, S;
⑤ SELECT R.A, R.B, S.C
FROM R, S
WHERE R.B=S.B;
⑥ SELECT R.A, S.C
FROM R, S
WHERE R.C=S.A;
⑦ SELECT R.* (R.*表示R中全部属性)
FROM R, S
WHERE R.C=S.C;
⑧SELECT A, B
FROM R RX
WHERE NOT EXISTS
( SELECT * FROM S
WHERE NOT EXISTS
( SELECT * FROM R RY
WHERE RY.A=RX.A AND RY.B=RX.B AND RY.C=S.C));
习题3.7
试用SQL查询语句表达下列对3.2题的教学数据中4个基本表T、C、S、SC的查询:
1统计有学生选修的课程门数
SELECT COUNT(DISTINCT TEACHER)
FROM C;
2 求选修C4课程的女学生的平均年龄
SELECT AVG(AGE)
FROM S, SC
WHERE S.S#=SC.S# AND C#=’C4’ AND SEX=’F’;
3 求LIU老师所授课程的每门课程的平均成绩
SELECT C.C#,AVG(GRADE)
FROM SC,C
WHERE SC.C#=C.C# AND TEACHER=‘LIU’GROUPBYC.C#;
4 统计每门课程的学生选修人数(超过10人的课程才统计)。
要求显示课程号和人数,查询结果按人数降序排序,若人数相同,按课程号升序排列。
SELECT S#, COUNT(C#)
FROM SC
GROUP BY S#
HAVING COUNT(*)>5 ORDER BY 2 DESC, 1;
5 检索学号比WANG同学大,而年龄比他小的学生姓名。
SELECT SNAME FROM S
WHERE S#>ALL(SELECT S#
FROM S
WHERE SNAME=’WANG’)
AND AGEFROM S
WHERE SNAME=’WANG’);
6 在表SC中检索成绩为空值的学生学号和课程号。
SELECT S#, C#
FROM SC
WHERE GRADE IS NULL;
7 检索姓名以L打头的所有学生的姓名和年龄。
SELECT SNAME, AGE
FROM S
WHERE SNAME LIKE ’L%’;
8 求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT SNAME, AGE
FROM S
WHERE SEX=’M’
AND AGE>(SELECT AVG(AGE)
FROM S
WHERE SEX=’F’);
9 求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT SNAME, AGE
FROM S
WHERE SEX=’M’ AND AGE>ALL(SELECT AGE
FROM S
WHERE SEX=’F’);
习题3.12
试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C的更新操作:
1 往关系C中插一个课程元组('C8','VC++','BAO')。
INSERT INTO C
VALUES('C8','VC++','BAO');
② 检索所授每门课程平均成绩均大于80分的教师姓名,并把检索到的值送往另一个已存在的表FACULTY(TNAME)。
INSERT INTO FACULTY(TNAME)
SELECT DISTINCT TEACHER
FROM (SELECT TEACHER, C.C#, AVG(GRADE)
FROM S, SC
WHERE SC.C#=C.C#
GROUP BY TEACHER, C.C#) AS RESULT(TEACHER, C#, AVG_GRADE) AS X WHERE 80<=ALL(SELECT AVG_GRADE
FROM RESULT AS Y
WHERE Y.TEACHER=X.TEACHER);
③在SC中删除尚无成绩的选课元组。
DELETE FROM SC
WHERE GRADE IS NULL;
④ 把选修LIU老师课程的女同学选课元组全部删去。
DELETE FROM SC
WHERE S# IN(SELECT S# FROM S WHERE SEX='F')
AND C# IN(SELECT C# FROM C WHERE TEACHER='LIU');
⑤ 把MATHS课不及格的成绩全改为60分。
UPDATE SC
SET GRADE=60
WHERE GRADE<60 AND C# IN(SELECT C#
FROM C
WHERE CNAME='MATHS');
⑥ 把低于所有课程总平均成绩的女同学成绩提高5%。
UPDATE SC
SET GRADE=GRADE*1.05
WHERE S# IN(SELECT S#
FROM S
WHERE SEX='F')
AND GRADE<(SELECT AVG(GRADE)
FROM SC);
⑦ 在表SC中修改C4课程的成绩,若成绩小于等于70分时提高5%,若成绩大于70 分时提高4%(用两种方法实现,一种方法是用两个UPDATE语句实现,另一种方法是用带CASE操作的一个UPDATE语句实现)。
UPDATE SC
SET GRADE=GRADE*CASE
WHEN GRADE>70 THEN 1.04 ELSE 1.05 END
WHERE C#='C4';
⑧ 在表SC中,当某个成绩低于全部课程的平均成绩时,提高5%。
UPDATE SC
SET GRADE=GRADE*1.05
WHERE GRADE<(SELECT AVG(GRADE) FROM SC);
习题3.13
设数据库中有三个关系:
职工表 EMP(E#,ENAME,AGE,SEX,ECITY),
其属性分别表示职工工号、姓名、年龄、性别和籍贯。
工作表 WORKS(E#,C#,SALARY),
其属性分别表示职工工号、工作的公司编号和工资。
公司表 COMP(C#,CNAME,CITY),
其属性分别表示公司编号、公司名称和公司所在城市。
试用SQL语句写出下列操作:
① 用CREATE TABLE语句创建上述三个表,需指出主键和外键。
CREATE TABLE EMP
( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL,
(2003/9/21) (GJ-DA) (共2页) 目录--36
AGE SMALLINT, SEX CHAR
(1), ECITY CHAR(20), PRIMARY KEY(E#)); CREATE TABLE COMP
( C# CHAR(4) NOT NULL,
CNAME CHAR(20) NOT NULL, CITY CHAR(20), PRIMARY KEY(C#));
CREATE TABLE WORKS
( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#),
FOREIGN KEY(E#) REFERENCES EMP(E#), FOREIGN KEY(C#) REFERENCES COMP(C#));
2 检索超过50岁的男职工的工号和姓名。
SELECT E#, ENAME
FROM EMP
WHERE AGE>50 AND SEX='M';
3 假设每个职工只能在一个公司工作,检索工资超过1000元的男性职工工号和姓名。
SELECT