sqlbeginner10 minutes

Build a Simple Employees and Departments Table with Basic Queries

Create two related tables, Employees and Departments, and write SQL queries to retrieve basic information such as employee names, their departments, and departments with employee counts.

Challenge prompt

You are tasked to create a small company database model with two tables: Departments and Employees. First, create the Departments table with columns department_id (integer, primary key) and department_name (text). Then create the Employees table with columns employee_id (integer, primary key), employee_name (text), and department_id (integer) as a foreign key referencing Departments. After creating the tables and inserting some sample data, write SQL queries to: 1. List all employees along with their department names. 2. Show the count of employees in each department. Write all the necessary SQL statements for this mini-project.

Guidance

• Start by creating the Departments table, then create the Employees table referencing Departments.
• Insert at least 3 department records and 5 employee records with assigned departments.
• Use JOIN operations to combine Employees and Departments data.
• Use GROUP BY to count employees per department.

Hints

• Remember to define department_id as the primary key in Departments and as a foreign key in Employees.
• Use INNER JOIN to combine employee and department information.
• Use COUNT() aggregation with GROUP BY department_id to get employee counts.

Starter code

CREATE TABLE Departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT
);

CREATE TABLE Employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  department_id INTEGER,
  FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

-- Insert sample data here

-- Write your queries here

Expected output

Query 1 Result: | employee_name | department_name | |---------------|-----------------| | Alice | HR | | Bob | IT | | Carol | HR | | Dave | Finance | | Eve | IT | Query 2 Result: | department_name | employee_count | |-----------------|----------------| | HR | 2 | | IT | 2 | | Finance | 1 |

Core concepts

SQL CREATE TABLEFOREIGN KEY constraintJOINGROUP BY

Challenge a Friend

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