数据库系统第三章部分习题解答.doc

3.2-1SELECT S#,SNAME FROM S WHERE AGE17 AND SEX=F-2SELECT C.C#,CNAME FROM S,SC,CWHERE S.S#=SC.S# AND C.C#=SC.C# AND SEX=M-3SELECT T.T#,TNAME FROM S,SC,C,TWHERE S.S#=SC.S# AND C.C#=SC.C# AND C.T#=T.T# AND SEX=M-4SELECT DISTINCT X.S#FROM SC AS X,SC AS YWHERE X.S#=Y.S# AND X.C#Y.C#select s#from scgroup by s#having count(s#)1-5SELECT C#FROM SCWHERE S#=S2 AND C# IN (SELECT C# FROM SC WHERE S#=S4)SELECT X.C#FROM SC AS X, SC AS YWHERE X.S#=S2 AND Y.S#=S4 AND X.C#=Y.C#-6SELECT C# FROM C WHERE C# NOT IN(SELECT C#FROM SCWHERE S# IN (SELECT S# FROM S WHERE SNAME=WANG)SELECT C# FROM C WHERE C# NOT IN(SELECT C# FROM SC,S WHERE SC.S#=S.S# AND SNAME=WANG)3.7SELECT COUNT(DISTINCT TEACHER)FROM C；SELECT AVG(AGE)FROM S, SCWHERE S.S#=SC.S# AND C#=C4 AND SEX=F；SELECT C.C#，AVG(GRADE)FROM SC，CWHERE SC.C#=C.C# AND TEACHER=LIUGROUP BY C.C#；SELECT S#, COUNT(C#)FROM SCGROUP BY S#HAVING COUNT(*)5ORDER BY 2 DESC, 1；SELECT SNAMEFROM SWHERE S#ALL(SELECT S# FROM S WHERE SNAME=WANG) AND AGE(SELECT AVG(AGE)FROM SWHERE SEX=F)；SELECT SNAME, AGEFROM SWHERE SEX=M AND AGEALL(SELECT AGEFROM SWHERE SEX=F)；3.12 INSERT INTO CVALUES(C8，VC+，BAO)； 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#, 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=F)AND C# IN(SELECT C# FROM C WHERE TEACHER=LIU)； UPDATE SCSET GRADE=60WHERE GRADE60AND C# IN(SELECT C# FROM C WHERE CNAME=MATHS)； UPDATE SCSET GRADE=GRADE*1.05WHERE S# IN(SELECT S# FROM S WHERE SEX=F)AND GRADE70；UPDATE SCSET GRADE=GRADE*1.05WHERE C#=C4 AND GRADE70 THEN 1.04ELSE 1.05ENDWHERE C#=C4； UPDATE SCSET GRADE=GRADE*1.05WHERE 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#=C4 AND C.C#=C8；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=M；SELECT E#, COUNT(C#) AS NUM, SUM(SALARY) AS SUM_SALARYFROM WORKS GROUP BY E#；SELECT X.E#FROM WORKS XWHERE NOT EXISTS(SELECT *FROM WORKS YWHERE E#=E6AND NOT EXISTS(SELECT *FROM WORKS ZWHERE Z.E#=X.E#AND Z.C#=Y.C#)；SELECT A.E#, A.ENAMEFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C# AND CNAME=联华公司AND SALARY50)；DELETE FROM WORKSWHERE E# IN (SELECT E# FROM EMP WHERE AGE60)；DELETE FROM EMPWHERE AGE60；3.14 解：CREATE VIEW EMP_WOMANAS SELECT A.E#, A.ENAME, C.C#, CNAME, SALARYFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C# AND SEX=F；SELECT E#，SUM(SALARY)FROM EMP_WOMANGROUP BY E#；3.15CREATE TABLE PART（P#CHAR(6),PNAMECHAR(10) NOT NULL,COLORCHAR(6),WEIGHTFLOAT(6),PRIMARY KEY(P#);CREATE TABLE PROJECT（J#CHAR(6),JNAMECHAR(12) NOT NULL,DATEDATE, PRIMARY KEY(J#);CREATE TABLE SUPPLIER（S#CHAR(8),SNAMECHAR(12) NOT NULL,SADDRVARCHAR(30),PRIMARY KEY(S#);CREATE TABLE P_P（J#CHAR(6),P#CHAR(6),TOTAL INTEGER,PRIMARY KEY (J#, P#)FOREIGN KEY(J#) REFERENCES PROJECT(J#),FOREIGN KEY(P#) REFERENCES PART(P#);CREATE TABLE P_S（P#CHAR(6),S#CHAR(8),QUANTITY INTEGER,PRIMARY KEY (P#, S#)FOREIGN KEY(P#) REFERENCES PART(P#),FOREIGN KEY(S#) REFERENCES SUPPLIER(S#);CREATE VIEW VIEW1AS SELECT A.J#, JNAME, DATE, C.P#, PNAME, COLOR,WEIGHT, TOTALFROM PROJECT A, P_P B, PART CWHERE A.J#=B.J# AND B.P#=C.P#;CREATE VIEW VIEW2AS SELECT A.P#, PNAME, COLOR, WEIGHT, C.S#, SNAME,SADDR, QUAN