sqlbeginner10 minutes

Create a Simple Employee and Department Tables with Basic Queries

Build and query basic employee and department tables to practice simple SQL data modeling and retrieval.

Challenge prompt

You are tasked with creating two tables, Employees and Departments, for a small company database. The Employees table should store employee_id, name, and department_id. The Departments table should store department_id and department_name. After creating the tables and inserting sample data, write a query to list all employees along with the name of the department they belong to.

Guidance

• Start by creating the Departments table with department_id as the primary key.
• Create the Employees table with a foreign key reference to Departments.
• Insert at least 3 departments and 5 employees distributed across these departments.
• Write a SELECT query using a JOIN to combine employee names with their department names.

Hints

• Use CREATE TABLE statements with appropriate data types (e.g., INT, VARCHAR).
• Remember to use INNER JOIN to combine Employees and Departments based on department_id.
• Use aliasing for readability, e.g., SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.department_id = d.department_id.

Starter code

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

CREATE TABLE Employees (employee_id INT PRIMARY KEY, name VARCHAR(50), department_id INT);

-- Insert sample data here

-- Write your SELECT query here

Expected output

name | department_name ---------|---------------- Alice | Sales Bob | Marketing Charlie | IT Diana | IT Eve | Sales

Core concepts

CREATE TABLEINSERT INTOINNER JOINSELECT

Challenge a Friend

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