You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
91 lines
3.0 KiB
91 lines
3.0 KiB
-- CREATE
|
|
CREATE TABLE Company
|
|
(
|
|
company_code VARCHAR(255) PRIMARY KEY,
|
|
founder VARCHAR(255)
|
|
);
|
|
|
|
|
|
CREATE TABLE Lead_Manager
|
|
(
|
|
lead_manager_code VARCHAR(255) PRIMARY KEY,
|
|
company_code VARCHAR(255)
|
|
);
|
|
|
|
|
|
CREATE TABLE Senior_Manager
|
|
(
|
|
senior_manager_code VARCHAR(255) PRIMARY KEY,
|
|
lead_manager_code VARCHAR(255),
|
|
company_code VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE Manager
|
|
(
|
|
manager_code VARCHAR(255) PRIMARY KEY,
|
|
senior_manager_code VARCHAR(255),
|
|
lead_manager_code VARCHAR(255),
|
|
company_code VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE Employee
|
|
(
|
|
employee_code VARCHAR(255) PRIMARY KEY,
|
|
manager_code VARCHAR(255),
|
|
senior_manager_code VARCHAR(255),
|
|
lead_manager_code VARCHAR(255),
|
|
company_code VARCHAR(255)
|
|
);
|
|
|
|
-- INSERT
|
|
|
|
INSERT INTO Company (company_code, founder)
|
|
VALUES ('C001', 'Alice Johnson'),
|
|
('C002', 'Bob Smith');
|
|
|
|
INSERT INTO Lead_Manager (lead_manager_code, company_code)
|
|
VALUES ('LM001', 'C001'),
|
|
('LM002', 'C002');
|
|
|
|
INSERT INTO Senior_Manager (senior_manager_code, lead_manager_code, company_code)
|
|
VALUES ('SM001', 'LM001', 'C001'),
|
|
('SM002', 'LM002', 'C002');
|
|
|
|
INSERT INTO Manager (manager_code, senior_manager_code, lead_manager_code, company_code)
|
|
VALUES ('M001', 'SM001', 'LM001', 'C001'),
|
|
('M002', 'SM002', 'LM002', 'C002');
|
|
|
|
INSERT INTO Employee (employee_code, manager_code, senior_manager_code, lead_manager_code, company_code)
|
|
VALUES ('E001', 'M001', 'SM001', 'LM001', 'C001'),
|
|
('E002', 'M001', 'SM001', 'LM001', 'C001'),
|
|
('E003', 'M002', 'SM002', 'LM002', 'C002'),
|
|
('E004', 'M002', 'SM002', 'LM002', 'C002');
|
|
|
|
|
|
-- QUERY
|
|
|
|
SELECT DISTINCT C.company_code,
|
|
C.founder,
|
|
COALESCE(LM.total_lead_managers, 0) AS total_lead_managers,
|
|
COALESCE(SM.total_senior_managers, 0) AS total_senior_managers,
|
|
COALESCE(M.total_managers, 0) AS total_managers,
|
|
COALESCE(E.total_employees, 0) AS total_employees
|
|
FROM Company AS C
|
|
LEFT JOIN (SELECT DISTINCT company_code,
|
|
COUNT(lead_manager_code) AS total_lead_managers
|
|
FROM Lead_Manager
|
|
GROUP BY company_code) AS LM ON C.company_code = LM.company_code
|
|
LEFT JOIN (SELECT DISTINCT company_code,
|
|
COUNT(senior_manager_code) AS total_senior_managers
|
|
FROM Senior_Manager
|
|
GROUP BY company_code) AS SM ON C.company_code = SM.company_code
|
|
LEFT JOIN (SELECT DISTINCT company_code,
|
|
COUNT(manager_code) AS total_managers
|
|
FROM Manager
|
|
GROUP BY company_code) AS M ON C.company_code = M.company_code
|
|
LEFT JOIN (SELECT DISTINCT company_code,
|
|
COUNT(employee_code) AS total_employees
|
|
FROM Employee
|
|
GROUP BY company_code) AS E ON C.company_code = E.company_code
|
|
ORDER BY C.company_code;
|