1、group by s#having count(c#)=2;3.2.5检索至少有学号为S2和S4所学的课程和课程名select c#,cname where c# in(select c# from sc where s#=S2)intersect(select c# from sc where s#=S4) );3.2.6检索WANG同学不学的课程号select c# from cexcept(select distinct c# from sc where s# =(select s# from s where sname=WANG);3.2.7检索全部学生都选修的课程号和课程名from
2、cwhere not exists(select s# from swhere c.c# not in (select c# from sc where sc.s#=s.s# );3.2.8检索选修课程包含LIU老师所授课程的全部课程的学生的学号和姓名select s#,sname where not exists(select c# from c where t#=(select t# from t where tname=) except(select c# from sc where sc.s#=s.s#) );3.4 设有两个基本表R(A,B,C)和S(A,B,C),试用SQL查询语句
3、表达下列关系代数表达式: RS RS RS RS A,B(R) B,C(S) 1,6(3=4(RS) 1,2,3(R S) RC(S)解: (SELECT * FROM R) UNION (SELECT * FROM S); (SELECT * FROM R) INTERSECT (SELECT * FROM S); (SELECT * FROM R) MINUS SELECT *FROM R, S; SELECT R.A, R.B, S.CFROM R, SWHERE R.B=S.B; SELECT R.A, S.CWHERE R.C=S.A; SELECT R.* (R.*表示R中全部属性
4、)WHERE R.C=S.C; RC(S)的元组表达式如下: t |(u)(v)(w)(R(u) S(v) R(w) w1=u1 w2=u2 w3=v3 t1=u1 t2=u2)据此,可写出SELECT语句: SELECT A, BFROM R RXWHERE NOT EXISTS( SELECT *FROM SFROM R RYWHERE RY.A=RX.A AND RY.B=RX.B AND RY.C=S.C); 3.6 试叙述SQL语言的关系代数特点和元组演算特点。答:SQL的关系代数特点如下:1有关系代数运算的并、交、差、自然联接等运算符;2FROM子句体现了笛卡尔积操作,WHERE子
5、句体现了选择操作,SELECT子句体现了投影操作。SQL的元组演算特点如下:1FROM子句中的基本表名应视为“元组变量”,属性名应视为“元组分量”;2有存在量词EXISTS符号。3.12 试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C的更新操作:往关系C中插一个课程元组(C8,VC+T6)。检索所授每门课程平均成绩均大于80分的教师姓名,并把检索到的值送往另一个已存在的表FACULTY(TNAME)。在SC中删除尚无成绩的选课元组。把选修LIU老师课程的女同学选课元组全部删去。把MATHS课不及格的成绩全改为60分。把低于所有课程总平均成绩的女同学成绩提高5。在表SC中修改C4课
6、程的成绩,若成绩小于等于70分时提高5,若成绩大于70 分时提高4(用两种方法实现,一种方法是用两个UPDATE语句实现,另一种方法是用带CASE操作的一个UPDATE语句实现)。 在表SC中,当某个成绩低于全部课程的平均成绩时,提高5%。 INSERT INTO CVALUES(); INSERT INTO FACULTY(TNAME)SELECT DISTINCT TEACHERFROM (SELECT TEACHER, C.C#, AVG(GRADE)FROM S, SCWHERE SC.C#=C.C#GROUP BY TEACHER, C.C#)AS RESULT(TEACHER, C
7、#, AVG_GRADE) AS XWHERE 80=ALL(SELECT AVG_GRADEFROM RESULT AS YWHERE Y.TEACHER=X.TEACHER); DELETE FROM SCWHERE GRADE IS NULL; DELETE FROM SCWHERE S# IN(SELECT S# FROM S WHERE SEX=FAND C# IN(SELECT C# FROM C WHERE TEACHER= UPDATE SCSET GRADE=60WHERE GRADE60AND C# IN(SELECT C# FROM C WHERE CNAME=MATHS
8、 UPDATE SCSET GRADE=GRADE*1.05AND GRADE70; AND GRADE70 THEN 1.04ELSE 1.05END; UPDATE SC SET GRADE=GRADE*1.05 WHERE GRADE50 AND SEX=M SELECT EMP.E#, ENAMEFROM EMP, WORKSWHERE EMP.E#=WORKS.E# AND SALARY1000; SELECT A.E#, A.ENAMEFROM EMP A, WORKS B, WORKS CWHERE A.E#=B.E# AND B.E#=C.E#AND B.C#= AND C.C
9、#= SELECT A.E#, A.ENAMEFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C#AND CNAME=联华公司 AND SALARY1000AND SEX= SELECT E#, COUNT(C#) AS NUM, SUM(SALARY) AS SUM_SALARYFROM WORKS GROUP BY E#; SELECT X.E#FROM WORKS X(SELECT *FROM WORKS YWHERE E#=E6AND NOT EXISTSFROM WORKS ZWHERE Z.E#=X.E#AND Z.C#=Y.C#); SELECT A.E#, A.ENAME AND CNAME=AND SALARY50); DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE60); DELETE FROM EMP WHERE AGE60;如有侵权请联系告知删除,感谢你们的配合!
copyright@ 2008-2023 冰点文库 网站版权所有
经营许可证编号:鄂ICP备19020893号-2