FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(100)  );
    
    
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES 
(1, 'Ava', 'J', 101, 60000),
(2, 'Bob', 'S', 101, 75000),
(3, 'Amy', 'B', 101, 50000),
(4, 'Deivan', 'L', 102, 80000),
(5, 'Neilson', 'W', 102, 90000),
(6, 'Frankie', 'T', 102, 70000),
(7, 'Jai', 'H', 103, 55000),
(8, 'Kai', 'W', 103, 58000),
(9, 'Ivy', 'C', 103, 62000),
(10, 'Jackie', 'M', 103, 75000);

INSERT INTO departments (department_id, department_name, location) VALUES 
(101, 'Sales', 'New York'),
(102, 'Marketing', 'San Francisco'),
(103, 'Finance', 'Chicago'),
(104, 'Human Resources', 'Boston');

/*
SELECT employee_id, first_name 
FROM employees 
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = (
        SELECT department_id 
        FROM departments 
        WHERE department_name = 'Sales'
    )
);
*/

WITH SalesDept AS (
    SELECT department_id 
    FROM departments 
    WHERE department_name = 'Sales'
),
AvgSalary AS (
    SELECT AVG(salary) AS avg_salary 
    FROM employees 
    WHERE department_id IN (SELECT department_id FROM SalesDept)
)
SELECT employee_id, first_name 
FROM employees 
WHERE salary > (SELECT avg_salary FROM AvgSalary);

employee_id	first_name
2	Bob
4	Deivan
5	Neilson
6	Frankie
9	Ivy
10	Jackie

(10 rows affected)

(4 rows affected)

(6 rows affected)