第三章课后作业.docx

第三章课后作业.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 AGE

FROM 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 EMP.E#, ENAME 

FROM EMP, WORKS 

WHERE EMP.E#=WORKS.E# AND SALARY>1000；

4 假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。

SELECT A.E#, A.ENAME 

FROM EMP A, WORKS B, WORKS C WHERE A.E#=B.E# AND B.E#=C.E# 

AND B.C#='C4' AND C.C#='C8'；

 ⑤ 检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。

SELECT A.E#, A.ENAME 

FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C# 

AND CNAME='联华公司' AND SALARY>1000 AND SEX='M'；

⑥ 假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数.显示（E#，NUM，SUM_SALARY），分别表示工号、公司数目和工资总数。

SELECT E#, COUNT（C#） AS NUM, SUM（SALARY） AS SUM_SALARY 

FROM WORKS  GROUP BY E#；

⑦ 工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。

SELECT X.E# 

FROM WORKS X WHERE NOT EXISTS 

（SELECT * 

FROM WORKS Y 

WHERE E#='E6' AND NOT EXISTS 

（SELECT * 

FROM WORKS Z 

WHERE Z.E#=X.E# AND Z.C#=Y.C#））；  

⑧ 检索联华公司中低于本公司平均工资的职工工号和姓名。

SELECT A.E#, A.ENAME 

FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C# 

  AND CNAME='联华公司' 

AND SALARY<（SELECT AVG（SALARY） 

FROM WORKS, COMP 

WHERE WORKS.C#=COMP.C# 

AND CNAME='联华公司'）；

⑨在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）。

UPDATE WORKS 

SET SALARY=SALARY+100 

WHERE E# IN （SELECT E# FROM EMP WHERE AGE>50）；

10 在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。

DELETE FROM WORKS   WHERE E# IN （SELECT E# FROM EMP WHERE AGE>60）；   

DELETE FROM EMP  

 WHERE AGE>60；