Joins in SQL in DBMS Theory - Mini Project: Build & Apply

Joins in SQL

📖 Scenario: You are working with two tables in a company's database: employees and departments. You want to learn how to combine data from these tables to see which employee works in which department.

🎯 Goal: Build SQL queries step-by-step to join the employees and departments tables using different types of joins.

📋 What You'll Learn

Create the employees table with exact columns and data

Create the departments table with exact columns and data

Write a SQL query using INNER JOIN to combine employees with their departments

Write a SQL query using LEFT JOIN to include all employees even if they have no department

💡 Why This Matters

🌍 Real World

Joining tables is a common task in databases to combine information like employee details with their department names.

💼 Career

Understanding SQL joins is essential for data analysts, database administrators, and developers to query and report data effectively.

1

Create the employees table with data

Write SQL statements to create a table called employees with columns id (integer), name (text), and department_id (integer). Insert these exact rows: (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', NULL).

DBMS Theory

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the departments table with data

Write SQL statements to create a table called departments with columns id (integer) and name (text). Insert these exact rows: (10, 'HR'), (20, 'Engineering'), (30, 'Marketing').

DBMS Theory

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department_id INTEGER
);

INSERT INTO employees (id, name, department_id) VALUES
  (1, 'Alice', 10),
  (2, 'Bob', 20),
  (3, 'Charlie', NULL);

-- Create the departments table and insert data
-- Your code here

Need a hint?

Use the same pattern as the employees table to create departments.

3

Write an INNER JOIN query to combine employees with their departments

Write a SQL query that selects employees.name and departments.name as department using an INNER JOIN on employees.department_id = departments.id.

DBMS Theory

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department_id INTEGER
);

INSERT INTO employees (id, name, department_id) VALUES
  (1, 'Alice', 10),
  (2, 'Bob', 20),
  (3, 'Charlie', NULL);

CREATE TABLE departments (
  id INTEGER,
  name TEXT
);

INSERT INTO departments (id, name) VALUES
  (10, 'HR'),
  (20, 'Engineering'),
  (30, 'Marketing');

-- Write INNER JOIN query below
-- Your code here

Need a hint?

Use INNER JOIN to combine rows where the department matches.

4

Write a LEFT JOIN query to include all employees even without a department

Write a SQL query that selects employees.name and departments.name as department using a LEFT JOIN on employees.department_id = departments.id to include employees without a department.

DBMS Theory

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department_id INTEGER
);

INSERT INTO employees (id, name, department_id) VALUES
  (1, 'Alice', 10),
  (2, 'Bob', 20),
  (3, 'Charlie', NULL);

CREATE TABLE departments (
  id INTEGER,
  name TEXT
);

INSERT INTO departments (id, name) VALUES
  (10, 'HR'),
  (20, 'Engineering'),
  (30, 'Marketing');

SELECT employees.name, departments.name AS department
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

-- Write LEFT JOIN query below
-- Your code here

Need a hint?

Use LEFT JOIN to include all employees, even those without a matching department.