Write a query to get employee names along with their department names using INNER JOIN.

Sure! Let’s walk through a detailed SQL solution for the query:

"Write a query to get employee names along with their department names using INNER JOIN."

Step-by-Step Guide

To achieve this, we need two tables:

1. employees – Contains employee info (name, department ID).
2. departments – Contains department ID and department name.

Step 1: Create departments Table

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

Step 2: Insert Sample Data into departments

INSERT INTO departments (dept_id, dept_name) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Sales'),
(4, 'Marketing');

Step 3: Modify employees Table

We’ll recreate the employees table so it refers to the department via dept_id.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Step 4: Insert Sample Data into employees

INSERT INTO employees (emp_id, emp_name, dept_id, salary, hire_date) VALUES
(1, 'John Smith', 1, 50000.00, '2020-01-15'),
(2, 'Alice Johnson', 2, 75000.00, '2019-03-22'),
(3, 'Bob Lee', 3, 62000.00, '2021-07-10'),
(4, 'Mary Jane', 2, 80000.00, '2018-11-05'),
(5, 'Tom Hardy', 4, 55000.00, '2022-06-01');

Step 5: Write the INNER JOIN Query

Now we want to fetch employee names along with their department names. Here's the SQL:

SELECT 
    e.emp_name,
    d.dept_name
FROM 
    employees e
INNER JOIN 
    departments d ON e.dept_id = d.dept_id;

Output

Explanation

• INNER JOIN combines records from employees and departments where the dept_id matches in both tables.
• e and d are aliases for employees and departments.

Summary

• Use INNER JOIN when you want to fetch matching records from two or more tables.
• Ensure foreign key relationships (like dept_id) are properly set up.
• Use aliases (e, d) to simplify your query syntax.