Activity 1: Learn about JOINS
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;
Top comments (0)