# DATA 210 Week 3 Class 2 Activity 1 Resources

Creating `employees` table

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

Insert data for `employees` table

``````INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
(1, 'John', 'Doe', 1, 50000.00),
(2, 'Jane', 'Smith', 2, 60000.00),
(3, 'Bob', 'Johnson', 1, 55000.00),
(4, 'Sarah', 'Lee', 3, 65000.00),
(5, 'Mike', 'Brown', 2, 70000.00),
(6, 'Karen', 'Davis', 3, 75000.00),
(7, 'Happy', 'Says', 4, 80000.00);
``````

Creating `departments` table

``````CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
``````

Insert data for `departments` table

``````INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering'),
(5, 'Whatever');
``````

#### Question implementing INNER JOIN

What is the name of each employee and their corresponding department?

``````SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
``````

#### Question implementing LEFT OUTER JOIN

What is the name of each employee and their corresponding department, even if the department has no employees?

``````SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;
``````

#### Question implementing RIGHT OUTER JOIN

What is the name of each employee and their corresponding department, even if the employee has no department assigned?

``````SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.department_id;
``````