SQL Subqueries - Day17

@LearnWithRD0529

Employee Table script -

CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
job_id VARCHAR(20),
salary DECIMAL(10, 2),
manager_id INT,
department_id INT
);

-- Insert statements
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (1, 'John', 'Doe', 'HR1', 55000.00, NULL, 1);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (2, 'Jane', 'Smith', 'ENG2', 72000.00, 1, 2);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (3, 'Michael', 'Johnson', 'FIN3', 62000.00, 1, 3);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (4, 'Emily', 'Davis', 'HR1', 50000.00, NULL, 1);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (5, 'William', 'Brown', 'ENG2', 70000.00, 2, 2);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (6, 'Olivia', 'Wilson', 'FIN3', 63000.00, 2, 3);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (7, 'James', 'Miller', 'ENG1', 58000.00, 2, 2);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (8, 'Sophia', 'Lee', 'HR2', 52000.00, NULL, 1);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (9, 'Benjamin', 'Garcia', 'ENG1', 60000.00, 5, 2);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (10, 'Ava', 'Rodriguez', 'FIN2', 67000.00, 6, 3);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (11, 'Liam', 'Anderson', 'HR1', 54000.00, 4, 4);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (12, 'Mia', 'Martinez', 'ENG2', 71000.00, 5, 5);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (13, 'Noah', 'Gonzalez', 'FIN3', 63000.00, 6, 6);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (14, 'Ella', 'Harris', 'HR1', 52000.00, 4, 4);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (15, 'Logan', 'Clark', 'ENG2', 70000.00, 5, 5);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (16, 'Aiden', 'Moore', 'IT1', 78000.00, NULL, 8);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (17, 'Oliver', 'Walker', 'IT2', 72000.00, 16, 8);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (18, 'Sophia', 'Perez', 'LEGAL1', 65000.00, NULL, 9);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (19, 'Mia', 'Thomas', 'LEGAL2', 61000.00, 18, 9);
INSERT INTO employee (employee_id, first_name, last_name, job_id, salary, manager_id, department_id)
VALUES (20, 'Logan', 'James', 'QA1', 58000.00, 7, 10);

Department Table scripts -

CREATE TABLE department (
department_id INT PRIMARY KEY,
department_name VARCHAR(50),
location_id INT
);

-- Insert statements
INSERT INTO department (department_id, department_name, location_id)
VALUES (1, 'Human Resources', 101);
INSERT INTO department (department_id, department_name, location_id)
VALUES (2, 'Engineering', 102);
INSERT INTO department (department_id, department_name, location_id)
VALUES (3, 'Finance', 103);
INSERT INTO department (department_id, department_name, location_id)
VALUES (4, 'Marketing', 101);
INSERT INTO department (department_id, department_name, location_id)
VALUES (5, 'Research and Development', 102);
INSERT INTO department (department_id, department_name, location_id)
VALUES (6, 'Sales', 104);
INSERT INTO department (department_id, department_name, location_id)
VALUES (7, 'Customer Support', 105);
INSERT INTO department (department_id, department_name, location_id)
VALUES (8, 'IT', 102);
INSERT INTO department (department_id, department_name, location_id)
VALUES (9, 'Legal', 106);
INSERT INTO department (department_id, department_name, location_id)
VALUES (10, 'Quality Assurance', 102);