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, 'Engineering', 'New York'),
(102, 'Marketing', 'San Francisco'),
(103, 'Finance', 'Chicago'),
(104, 'Human Resources', 'Boston');

/*
SELECT employee_id, first_name 
FROM employees e
WHERE EXISTS (
    SELECT 1 
    FROM departments d 
    WHERE d.department_id = e.department_id AND d.location = 'New York'
);
*/
SELECT DISTINCT e.employee_id, e.first_name 
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'New York';

employee_id	first_name
1	Ava
2	Bob
3	Amy

(10 rows affected)

(4 rows affected)

(3 rows affected)