backend/cases/case05.sql - master - romanchel1s/sql-translator

backend/cases/case05.sql
42 строки2 KB
Roman Purtov
innit
01 сен 2024, 11:27
01 сен 2024, 11:2758a1772
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(50) NOT NULL
);

CREATE TABLE employees (
    employee_id   NUMBER(6) PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50) NOT NULL,
    email         VARCHAR2(100) UNIQUE,
    phone_number  VARCHAR2(20),
    hire_date     DATE DEFAULT SYSDATE NOT NULL,
    job_id        VARCHAR2(10) NOT NULL,
    salary        NUMBER(8,2),
    commission_pct NUMBER(2,2),
    department_id NUMBER(4),
    manager_id    NUMBER(6),
    CONSTRAINT fk_department
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES (10, 'Accounting');
INSERT INTO departments (department_id, department_name) VALUES (20, 'Research');
INSERT INTO departments (department_id, department_name) VALUES (30, 'Sales');
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, department_id, manager_id) 
VALUES (1001, 'John', 'Doe', 'jdoe@example.com', '1234567890', TO_DATE('2023-01-01', 'YYYY-MM-DD'), 'IT_PROG', 6000, NULL, 10, 101);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, department_id, manager_id) 
VALUES (1002, 'Jane', 'Smith', 'jsmith@example.com', '0987654321', TO_DATE('2023-02-15', 'YYYY-MM-DD'), 'SA_REP', 4500, 0.1, 30, 102);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, department_id, manager_id) 
VALUES (1003, 'Alice', 'Johnson', 'ajohnson@example.com', '1122334455', TO_DATE('2023-03-10', 'YYYY-MM-DD'), 'HR_REP', 5000, NULL, 20, 103);

SELECT 
    d.department_name,
    NVL(COUNT(e.employee_id), 0) AS employee_count
FROM 
    departments d, 
    employees e
WHERE 
    d.department_id = e.department_id(+)
GROUP BY 
    d.department_name;