1、6.1答： CREATE TABLE 職工(職工號(hào) CHAR(10) NOT NULL UNIQUE, 姓名 CHAR(8) NOT NULL, 年齡 SMALLINT, 性別 CHAR(2), CONSTRAINT C1 CHECK(性別 IN (男,女) Go CREATE TABLE 社會(huì)團(tuán)體(編號(hào) CHAR(8) NOT NULL UNIQUE, 名稱 CHAR(12) NOT NULL, 負(fù)責(zé)人 CHAR(8), 活動(dòng)地點(diǎn) VARCHAR(50), CONSTRAINT C2 FOREIGN KEY(負(fù)責(zé)人) REFERENCES職工(職工號(hào)) Go CREATE TABLE 參加(

2、職工號(hào) CHAR(8), 編號(hào) CHAR(8), 參加日期 DATE, CONSTRAINT C3 PRIMARY KEY(職工號(hào), 編號(hào)), CONSTRAIN C3 FOREIGN KEY(職工號(hào)) REFERENCES 職工(職工號(hào)) Go SELECT 職工號(hào), 姓名 FROM 職工, 社會(huì)團(tuán)體, 參加 WHERE 職工.職工號(hào)=參加.職工號(hào) AND 參加.編號(hào)=社會(huì)團(tuán)體.編號(hào) AND社會(huì)團(tuán)體.名稱 IN (唱歌隊(duì), 籃球隊(duì)) SELECT * FROM 職工 WHERE NOT EXISTS ( SELECT * FROM 參加 WHERE 參加.職工號(hào)=職工.職工號(hào)) SELECT

3、 * FROM 職工 WHERE NOT EXISTS (SELECT * FROM 參加 WHERE NOT EXISTS (SELECT * FROM 社會(huì)團(tuán)體 WHERE 參加.職工號(hào)=職工.職工號(hào) AND 參加.編號(hào)=社會(huì)團(tuán)體.編號(hào)), SELECT 職工號(hào) FROM 職工 WHERE NOT EXISTS (SELECT * FROM 參加 參加1 WHERE 參加1.職工號(hào)= 1001 AND NOT EXISTS (SELECT * FROM 參加 參加2 WHERE 參加2.編號(hào)=參加1.編號(hào) AND 參加2.職工號(hào)=職工.職工號(hào)) SELECT編號(hào),COUNT(職工號(hào)) FR

4、OM 參加 GROUP BY 編號(hào) SELECT 社會(huì)團(tuán)體.名稱,COUNT(*) FROM 社會(huì)團(tuán)體，參加 A WHERE社會(huì)團(tuán)體.編號(hào)=A.編號(hào) GROUP BY A.編號(hào) HAVING COUNT(*) = (SELECT MAX(COUNT(*) FROM 參加 B GROUP BY B.編號(hào)) 或： SELECT 社會(huì)團(tuán)體.名稱,COUNT(*) FROM 社會(huì)團(tuán)體，參加 A WHERE社會(huì)團(tuán)體.編號(hào)=A.編號(hào) GROUP BY A.編號(hào) HAVING COUNT(*) = ALL(SELECT COUNT(*) FROM 參加 B GROUP BY B.編號(hào)) SELECT 社會(huì)團(tuán)

5、體.名稱,職工.姓名 FROM 職工, 社會(huì)團(tuán)體, 參加 WHERE 社會(huì)團(tuán)體.編號(hào)=參加.編號(hào) AND 社會(huì)團(tuán)體.負(fù)責(zé)人=職工.職工號(hào) GROUP BY 參加.編號(hào) HAVING COUNT(參加.編號(hào))100; CREATE VIEW社團(tuán)負(fù)責(zé)人(編號(hào),名稱,負(fù)責(zé)人職工號(hào),負(fù)責(zé)人姓名,負(fù)責(zé)人性別) AS SELECT 編號(hào), 名稱, 負(fù)責(zé)人, 姓名, 性別 FROM 社會(huì)團(tuán)體, 職工 WHERE 社會(huì)團(tuán)體.負(fù)責(zé)人=職工.職工號(hào) GO CREATE VIEW 參加人情況(職工號(hào), 姓名, 社團(tuán)編號(hào), 社團(tuán)名稱, 參加日期) AS SELECT參加.職工號(hào), 姓名, 社會(huì)團(tuán)體.編號(hào), 名稱, 參

6、加日期 FROM 職工, 社會(huì)團(tuán)體, 參加 WHERE 職工.職工號(hào)=參加.職工號(hào) AND 參加.編號(hào)=社會(huì)團(tuán)體.編號(hào),6.2答： SELECT 姓名,聯(lián)系電話 FROM 供應(yīng)商 WHERE 所在城市=天津 SELECT * FROM 工程 WHERE 預(yù)算 BETWEEN 50000 AND 100000 ORDER BY 預(yù)算 DESC SELECT 工程代號(hào) FROM 供應(yīng)零件 WHERE 供應(yīng)商代碼=S1 SELECT 零件.零件名, 供應(yīng)零件.數(shù)量 FROM 零件，供應(yīng)零件 WHERE 零件.零件代碼=供應(yīng)零件.零件代碼 AND 供應(yīng)零件.工程代碼= J2 SELECT 零件代號(hào) F

7、ROM 供應(yīng)商, 供應(yīng)零件 WHERE供應(yīng)商.供應(yīng)商代碼=供應(yīng)零件.供應(yīng)商代碼 AND 供應(yīng)商.所在城市=上海, SELECT 工程名 FROM 供應(yīng)商, 供應(yīng)零件, 工程 WHERE 供應(yīng)商.供應(yīng)商代碼=供應(yīng)零件.供應(yīng)商代碼 AND 供應(yīng)零件.工程代碼=工程.工程代碼 AND 供應(yīng)商.所在城市=上海 SELECT 工程號(hào)碼 FROM 工程 WHERE NOT EXISTS ( SELECT * FORM 供應(yīng)零件 WHERE工程.工程代碼=供應(yīng)零件.工程代碼 AND 供應(yīng)商代碼 IN ( SELECT供應(yīng)商代碼 FROM 供應(yīng)商 WHERE所在城市=天津) UPDATE 零件 SET 顏色=

8、藍(lán)色 WHERE顏色=紅色 UPDATE 供應(yīng)零件 SET 供應(yīng)商代碼=S3 WHERE供應(yīng)商代碼= S5 AND 工程代碼= J4 AND 零件代碼= P6 DELETE FROM 供應(yīng)零件 WHERE供應(yīng)商代碼=S2; DELETE FROM 供應(yīng)商 WHERE供應(yīng)商代碼=S2,6.3答： SELECT S.S# FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE C.TEACHER=程軍 AND NOT EXISTS (SELECT * FROM SC WHERE SC.C# =C.C# AND SC.S# =S.S# ) SELECT SN F

9、ROM S, SC, C WHERE S.S# =SC.S# AND SC.C# =C.C# AND GRADE=90 AND CN=程序設(shè)計(jì) SELECT SN, AGE, SEX FROM S S1 WHERE AGE (SELECT AGE FROM S S2 WHERE SN=王華) SELECT S# FROM SC WHERE C# = C2 AND GRADE = (SELECT MAX(GRADE) FROM SC WHERE C#=C2) SELECT SN,C#,GRADE FROM S,SC WHERE S. S# =SC. S#, SELECT S#, SUM (GRA

10、DE) FROM SC GROUP BY S# HAVING COUNT(C#)= 4 SELECT C# FROM SC WHERE C# NOT IN (SELECT C# FROM S,SC SC1 WHERE S.S# =SC1.S# AND SEX=男 ) SELECT S#,SN FROM S WHERE SEX=男 AND S# NOT IN (SELECT S.S# FROM S S1,SC WHERE S1.S# =SC.S# AND C# =C6); INSERT INTO SC_C(C#,CNAME,AVG_GRADE) SELECT C. C#,CN,AVG(GRADE) FROM SC,C WHERE SC. C# =C. C# GROUP BY C. C# DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX=女) AND C# IN (SELECT C# FROM C WHERE TEACHER IS LIKE 吳%),6.4答：SELECT A, ANAME FROM A WHERE WQTY100 OR CITY=長沙 SELECT A.ANAME, A.CITY FROM A, B