SELECTS#,C#
FROMSC
WHERESCOREISNULL;
SELECTSNAME,AGE
FROMS
WHERESNAMELIKE’L%’;
SELECTSNAME,AGE
FROMS
WHERESEX=’M’
ANDAGE>(SELECTAVG(AGE)
FROMS
WHERESEX=’F’);
SELECTSNAME,AGE
FROMS
WHERESEX=’M’
ANDAGE>ALL(SELECTAGE
FROMS
WHERESEX=’F’);
3.8对于下面的关系R和S,试求出下列各种联接操作的执行结果:
①RNATURALINNERJOINS
②RNATURALRIGHTOUTERJOINS
③RRIGHTOUTERJOINSUSING(C)
④RINNERJOINS
⑤RFULLOUTERJOINSONfalse
R
A
B
C
S
B
C
D
a1
b1
c1
b1
c1
d1
a2
b2
c2
b2
c2
d2
a3
b3
c3
b4
c4
d4
解:
①
A
B
C
D
②
A
B
C
D
③
A
R.B
C
S.B
D
a1
b1
c1
d1
a1
b1
c1
d1
a1
b1
c1
b1
d1
a2
b2
c2
d2
a2
b2
c2
d2
a2
b2
c2
b2
d2
null
b4
c4
d4
null
null
c4
b4
d4
④
A
R.B
R.C
S.B
S.C
D
⑤
A
R.B
R.C
S.B
S.C
D
a1
b1
c1
b1
c1
d1
a1
b1
c1
null
null
null
a1
b1
c1
b2
c2
d2
a2
b2
c2
null
null
null
a1
b1
c1
b4
c4
d4
a3
b3
c3
null
null
null
a2
b2
c2
b1
c1
d1
null
null
null
b1
c1
d1
a2
b2
c2
b2
c2
d2
null
null
null
b2
c2
d2
a2
b2
c2
b4
c4
d4
null
null
null
b4
c4
d4
a3
b3
c3
b1
c1
d1
a3
b3
c3
b2
c2
d2
a3
b3
c3
b4
c4
d4
3.12试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C、T的更新操作:
往关系C中插一个课程元组('C8','VC++','T6')。
检索所授每门课程平均成绩均大于80分的教师姓名,并把检索到的值送往另一个已存在的表FACULTY(TNAME)。
在SC中删除尚无成绩的选课元组。
把选修LIU老师课程的女同学选课元组全部删去。
把MATHS课不及格的成绩全改为60分。
把低于所有课程总平均成绩的女同学成绩提高5%。
在表SC中修改C4课程的成绩,若成绩小于等于70分时提高5%,若成绩大于70分时提高4%(用两种方法实现,一种方法是用两个UPDATE语句实现,另一种方法是用带CASE操作的一个UPDATE语句实现)。
⑧在表SC中,当某个成绩低于全部课程的平均成绩时,提高5%。
解:
①INSERTINTOC
VALUES('C8','VC++','T6');
②INSERTINTOFACULTY(TNAME)
SELECTDISTINCTTNAME
FROMTX
WHERENOTEXIST
(SELECT*
FROMSC,C,TY
WHERESC.C#=C.C#ANDC.T#=T.#ANDX.T#=Y.T#
GROUPBYT#,C.C#HAVINGAVG(SECOR)<=80));
;
③DELETEFROMSC
WHERESCOREISNULL;
④DELETEFROMSC
WHERES#IN(SELECTS#FROMSWHERESEX='F')
ANDC#IN(SELECTC#FROMC,TWHEREC.T#=T.#ANDTNAME='LIU');
⑤UPDATESC
SETSCORE=60
WHERESCORE<60
ANDC#IN(SELECTC#FROMCWHERECNAME='MATHS');
⑥UPDATESC
SETSCORE=SCORE*1.05
WHERES#IN(SELECTS#FROMSWHERESEX='F')
ANDSCORE<(SELECTAVG(SCORE)FROMSC);
⑦用两个UPDATE语句实现:
UPDATESC
SETSCORE=SCORE*1.04
WHEREC#='C4'ANDSCORE>70;
UPDATESC
SETSCORE=SCORE*1.05
WHEREC#='C4'ANDSCORE<=70;
(这两个UPDATE语句的顺序不能颠倒。
)
用一个UPDATE语句实现:
UPDATESC
SETSCORE=SCORE*CASE
WHENSCORE>70THEN1.04
ELSE1.05
END
WHEREC#='C4';
⑧UPDATESC
SETSCORE=SCORE*1.05
WHERESCORE<(SELECTAVG(SCORE)
FROMSC);
3.13设数据库中有三个关系:
职工表EMP(E#,ENAME,AGE,SEX,ECITY),
其属性分别表示职工工号、姓名、年龄、性别和籍贯。
工作表WORKS(E#,C#,SALARY),
其属性分别表示职工工号、工作的公司编号和工资。
公司表COMP(C#,CNAME,CITY),
其属性分别表示公司编号、公司名称和公司所在城市。
试用SQL语句写出下列操作:
①用CREATETABLE语句创建上述三个表,需指出主键和外键。
②检索超过50岁的男职工的工号和姓名。
③假设每个职工只能在一个公司工作,检索工资超过1000元的男性职工工号和姓名。
④假设每个职工可在多个公司工作,检索在编号为C4和C8公司兼职的职工工号和姓名。
⑤检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。
⑥假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数.显示(E#,NUM,SUM_SALARY),分别表示工号、公司数目和工资总数。
⑦工号为E6的职工在多个公司工作,试检索至少在E6职工兼职的所有公司工作的职工工号。
⑧检索联华公司中低于本公司平均工资的职工工号和姓名。
⑨在每一公司中为50岁以上职工加薪100元(若职工为多个公司工作,可重复加)。
⑩在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。
解:
①CREATETABLEEMP
(E#CHAR(4)NOTNULL,
ENAMECHAR(8)NOTNULL,
AGESMALLINT,
SEXCHAR
(1),
ECITYCHAR(20),
PRIMARYKEY(E#));
CREATETABLECOMP
(C#CHAR(4)NOTNULL,
CNAMECHAR(20)NOTNULL,
CITYCHAR(20),
PRIMARYKEY(C#));
CREATETABLEWORKS
(E#CHAR(4)NOTNULL,
C#CHAR(4)NOTNULL,
SALARYSMALLINT,
PRIMARYKEY(E#,C#),
FOREIGNKEY(E#)REFERENCESEMP(E#),
FOREIGNKEY(C#)REFERENCESCOMP(C#));
②SELECTE#,ENAME
FROMEMP
WHEREAGE>50ANDSEX='M';
③SELECTEMP.E#,ENAME
FROMEMP,WORKS
WHEREEMP.E#=WORKS.E#ANDSALARY>1000;
④SELECTA.E#,A.ENAME
FROMEMPA,WORKSB,WORKSC
WHEREA.E#=B.E#ANDB.E#=C.E#
ANDB.C#='C4'ANDC.C#='C8';
⑤SELECTA.E#,A.ENAME
FROMEMPA,WORKSB,COMPC
WHEREA.E#=B.E#ANDB.C#=C.C#
ANDCNAME='联华公司'ANDSALARY>1000
ANDSEX='M';
⑥SELECTE#,COUNT(C#)ASNUM,SUM(SALARY)ASSUM_SALARY
FROMWORKS
GROUPBYE#;
⑦SELECTX.E#
FROMWORKSX
WHERENOTEXISTS
(SELECT*
FROMWORKSY
WHEREE#='E6'
ANDNOTEXISTS
(SELECT*
FROMWORKSZ
WHEREZ.E#=X.E#
ANDZ.C#=Y.C#));
⑧SELECTA.E#,A.ENAME
FROMEMPA,WORKSB,COMPC
WHEREA.E#=B.E#ANDB.C#=C.C#
ANDCNAME='联华公司'
ANDSALARY<(SELECTAVG(SALARY)
FROMWORKS,COMP
WHEREWORKS.C#=COMP.C#
ANDCNAME='联华公司');
⑨UPDATEWORKS
SETSALARY=SALARY+100
WHEREE#IN(SELECTE#FROMEMPWHEREAGE>50);
⑩DELETEFROMWORKS
WHEREE#IN(SELECTE#FROMEMPWHEREAGE>60);
DELETEFROMEMP
WHEREAGE>60;
3.14对第3.13题中的关系建立一个有关女职工信息的视图EMP_WOMAN,属性包括(E#,ENAME,C#,CNAME,SALARY)。
然后对视图EMP_WOMAN操作,检索每一位女职工的工资总数。
(假设每个职工可在多个公司兼职)
解:
CREATEVIEWEMP_WOMAN
ASSELECTA.E#,A.ENAME,C.C#,CNAME,SALARY
FROMEMPA,WORKSB,COMPC
WHEREA.E#=B.E#ANDB.C#=C.C#
ANDSEX='F';
SELECTE#,SUM(SALARY)
FROMEMP_WOMAN
GROUPBYE#;
3.15对于3.2题的教学数据库中基本表SC,建立一个视图:
CREATEVIEWS_SCORE(S#,C_NUM,AVG_SCORE)
ASSELECTS#,COUNT(C#),AVG(SCORE)
FROMSC
GROUPBYS#;
试判断下列查询和更新操作是否允许执行。
如允许,写出转换到基本表SC上的相应操作。
①SELECT*
FROMS_SCORE;
②SELECTS#,C_NUM
FROMS_SCORE
WHEREAVG_SCORE>80;
③SELECTS#,AVG_SCORE
FROMS_SCORE
WHEREC_NUM>(SELECTC_NUM
FROMS_SCORE
WHERES#='S4');
④UPDATES_SCORE
SETS#='S3'
WHERES#='S4';
⑤DELETEFROMS_SCORE
WHEREC_NUM>4;
答:
①允许查询。
相应的操作如下:
SELECTS#,COUNT(C#)ASC_NUM,AVG(SCORE)ASAVG_SCORE
FROMSC
GROUPBYS#;
②允许查询。
相应的操作如下