sqlbeginner10 minutes

Create a Simple Employee-Department Relationship Table

Build SQL tables to model employees and departments, then write queries to retrieve employee details along with their department names.

Challenge prompt

You need to create two tables: Employees and Departments. The Employees table should store employee_id, employee_name, and department_id. The Departments table should store department_id and department_name. Populate both tables with sample data. Then, write a query to list all employees with their corresponding department names.

Guidance

• Define primary keys for both tables: employee_id for Employees and department_id for Departments.
• Use a foreign key (department_id) in Employees to link to Departments.
• Write a SELECT query joining Employees and Departments on department_id.

Hints

• Use CREATE TABLE statements to define the tables with appropriate columns and constraints.
• Insert a few rows in each table using INSERT INTO statements.
• Use INNER JOIN to combine data from Employees and Departments in your SELECT query.

Starter code

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

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

-- Insert sample data here
-- Write your SELECT query here

Expected output

employee_id | employee_name | department_name ------------|---------------|---------------- 1 | Alice Smith | HR 2 | Bob Johnson | IT 3 | Charlie Lee | Marketing

Core concepts

CREATE TABLEPRIMARY KEYFOREIGN KEYJOIN

Challenge a Friend

Send this duel to someone else and see if they can solve it.