2024年1月9日发(作者：)

设计编程题

1、设职工_社团数据库中有三个基本表：

职工（职工号，姓名，性别，出生日期）；

社会团体（社团编号，社团名称，负责人，活动地点）；

参加社团（职工号，社团编号，参加日期）。

其中：

（1） 职工表的主关键字为职工号。

（2） 社会团体表的主关键字为社团编号；外关键字为负责人，被参照表是职工表，参照对象为职工号。

（3） 参加社团表的职工号和社团编号为主关键字；职工号为外关键字，其参照对象是职工表中的职工号；社团编号也是外关键字，其参照对象是社会团体表中的社团编号。

试用SQL语句表达下列操作：

①定义职工表、社会团体表、参加社团表，并说明主关键字和参照关系。

②查找参加舞蹈表演队或篮球队的职工号和姓名。

③查找没有参加任何社会团体的职工情况。

④查找参加了全部团体的职工情况。

⑤查找参加了职工号为“zg001”的职工所参加的全部社会团体的职工号。

⑥统计每个社团参加人数。

⑦查找参加人数超过2人的社会团体的名称和负责人。

⑧统计参加人数最多的社会团体的名称和参加人数。

设计编程题答案

① CREATE TABLE 职工(

职工号 CHAR(5),

姓名 CHAR(6),

性别 CHAR(2),

出生日期 DATETIME,

PRIMARY KEY (职工号));

CREATE TABLE 社会团体(

社团编号 CHAR(5),

社团名称 CHAR(15),

负责人 CHAR(6),

活动地点 CHAR(16),

PRIMARY KEY(社团编号),

FOREIGN KEY(负责人) REFERENCES 职工(职工号));

CREATE TABLE 参加社团(

职工号 CHAR(5),

社团编号 CHAR(5),

参加日期 DATETIME,

PRIMARY KEY(职工号,社团编号),

FOREIGN KEY(职工号) REFERENCES 职工(职工号),

FOREIGN KEY(社团编号) REFERENCES 社会团体(社团编号));

② SELECT 职工号, 姓名

FROM 职工

WHERE 职工号 IN(

SELECT 职工号

FROM 参加社团

WHERE 社团编号 IN(

SELECT 社团编号

③

④

FROM 社会团体

WHERE 社团名称='舞蹈'OR

社团名称='篮球';));

SELECT *

FROM 职工

WHERE 职工号 NOT IN(

SELECT 职工号

FROM 参加社团);

SELECT 职工号, 姓名

FROM 职工

WHERE NOT EXISTS(

SELECT *

FROM 社会团体

WHERE NOT EXISTS(

SELECT *

FROM 参加社团

WHERE 社会团体.社团编号=参加社团.社团编号 AND

职工.职工号=参加社团.职工号));

⑤ SELECT 职工号

FROM 职工

WHERE NOT EXISTS(

SELECT *

FROM 参加社团 参加1

WHERE 参加1.职工号='zg001' AND NOT EXISTS(

SELECT *

FROM 参加社团 参加2

WHERE 参加2.社团编号=参加1.社团编号

AND参加2.职工号=职工.职工号;))

AND职工号<>'zg001';

⑥ SELECT 社团编号, COUNT(*) AS 参加人数

FROM 参加社团

GROUP BY 社团编号;

⑦ SELECT 社团名称, 负责人

FROM 社会团体

WHERE 社团编号 IN(

SELECT 社团编号

FROM 参加社团

GROUP BY 社团编号 HAVING COUNT(*)>2);

⑧

SELECT 社会团体.社团名称, COUNT(参加社团.职工号) AS 参加人数

FROM 社会团体, 参加社团

WHERE 社会团体.社团编号)=参加社团.社团编号

GROUP BY 社团名称, 社团编号

HAVING COUNT(职工号)>=All(

SELECT COUNT(职工号)

FROM 参加社团

GROUP BY 社团编号);