[size=medium]在一个组织内，每个成员都属于一个部门，每个部门都隶属于自己的上级部门，每个程序员都被分配了若干角色。
问题1：请设计数据表。
问题2：写出SQL语句，统计每个部门的成员数量。
问题3：写出SQL语句，列出查询符合条件的部门名称：部门内所有成员的角色数量都大于1。[/size]
[b]问题补充：[/b]
这道题目用的是Oracle
[b]问题补充：[/b]
最主要是第3问不知如何写SQL语句
我刚刚在我机子上测试了几组数据，都通过了。这次应该没什么问题了，呵呵！
面试官让你用什么数据库来完成，mssql,还是oracle?
我来试试，当是练习吧，数据库是MySql，不过好像在其他数据库上也差不多，没有涉及到数据库特有的属性
[code="sql"]
//表设计
//Department :
CREATE TABLE Department (
id INT PRIMARY KEY,
name VARCHAR(50) UNIQUE,
superior_id INT,
FOREIGN KEY(superior_id) REFERENCES Department(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
//Employee:
CREATE TABLE Employee(
id INT PRIMARY KEY,
name VARCHAR(60),
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES department(id) ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
//Role:
CREATE TABLE Role(
id INT PRIMARY KEY,
name VARCHAR(30)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
//Employee_Role:
CREATE TABLE Employee_Role(
employee_id INT NOT NULL,
role_id INT NOT NULL,
PRIMARY KEY(employee_id, role_id),
FOREIGN KEY(employee_id) REFERENCES Employee(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(role_id) REFERENCES Role(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
//统计每个部门的成员数量
SELECT em.dept_id AS 'DEPARTMENT_ID', COUNT(em.id) AS 'EMPLOYEE NUM' FROM Employee em GROUP BY em.dept_id;
//列出查询符合条件的部门名称：部门内所有成员的角色数量都大于1
SELECT dept.name AS 'DEPT_NAME' FROM Department dept WHERE dept.id IN (
SELECT em.dept_id FROM Employee em WHERE em.id IN(
(SELECT employee_id FROM Employee_role GROUP BY employee_id HAVING COUNT(employee_id) > 1)
)
);
[/code]
经测试好像过的了。行的话要记得给分喏！
我昨天晚上看了一下你的信息，发现第三题是错了，我给的答案没有符合”所有成员”这个限定条件。可能是昨天测试数据只有几行，所以没有及时发现错误。抱歉。我想应该有两个思路：
第一，从正面来考虑，先统计每个部门成员的数量
[code="sql"]
SELECT em.dept_id, COUNT(em.id) FROM Employee em GROUP BY em.dept_id;
[/code]
这样有一个中间表，然后再求出在Employee_Role表里成员的角色数大于1的employee，接而根据结果再求出这些成员所在的部门，并统计数量
[code="sql"]
SELECT em.dept_id, COUNT(em.dept_id)FROM Employee em WHERE em.id IN(
(SELECT employee_id FROM Employee_role GROUP BY employee_id HAVING COUNT(employee_id) > 1)
) GROUP BY em.dept_id;
[/code]
两个中间表内连接，若要部门名称的话，再和Department表连接一次选出需要的字段。可是这个Sql我也不知道怎么写，我在MySql下试试将两个中间表命名来连接但是不成功。Oracle更不熟，就不清楚了；
第二个思路，因为既然要求是部门的每一个成员，逆过来思考的话，也即”所在部门的员工里面只要有一个没有角色或角色数小于等于一，就可以把这个部门剔除了“。
[code="sql"]
找出角色数小于等于1的员工所在部门，作为剔除对象
SELECT dept_id FROM Employee, Employee_role WHERE id = employee_id GROUP BY employee_id HAVING COUNT(employee_id) <= 1;
[/code]
再找出没有分配角色的员工部门
SELECT dept_id FROM Employee WHERE ID NOT IN( SELECT employee_id FROM Employee_Role) GROUP BY dept_id;
[code="sql"]
[/code]
综合起来则是：
[code="sql"]
SELECT dept.name FROM Department dept WHERE dept.id NOT IN
(SELECT dept_id FROM Employee, Employee_role WHERE id = employee_id GROUP BY employee_id HAVING COUNT(employee_id) <= 1)
AND dept.id NOT IN
(SELECT dept_id FROM Employee WHERE ID NOT IN( SELECT employee_id FROM Employee_Role) GROUP BY dept_id)
[/code]