Production Relational SchemaBeginner → Advanced

Company HR

Employees, departments, salaries, managers and locations.

5 Tables5K+ RecordsJoinsSubqueriesRanking

Interactive SQL Playground & Query Runner

In-Browser Sandbox

Run realistic queries against the Company HR database. Write custom SQL or solve high-yield interview problems.

Interview Challenge:

easy

Department Salary Expenditure & Headcount

Calculate the total payroll, employee count, and average salary for each department.

JOINGROUP BYAVG

query.sql

Press Ctrl + Enter to execute

Entity Relationship (ER) & Foreign Key Architecture

Referential integrity mappings and table cardinalities

5 Key Relations

1:NEmploys Staff

departments

unit_id

employees

unit_id

1:NManages (Self Join)

employees

staff_id

employees

lead_id

1:NAssigned To

employees

staff_id

employee_office

staff_id

1:NHouses Staff

offices

office_id

employee_office

office_id

1:NTakes Leaves

employees

staff_id

leave_log

staff_id

Relational Tables & Column Data Types

Field definitions, types, and primary keys for query construction

5 tables total

departments

3 cols

PKunit_id

INT

unit_name

VARCHAR(80)

floor_no

INT

employees

6 cols

PKstaff_id

INT

full_name

VARCHAR(90)

PKunit_id

INT

annual_pay

INT

PKlead_id

INT

joined_on

DATE

offices

3 cols

PKoffice_id

INT

office_name

VARCHAR(80)

city

VARCHAR(60)

employee_office

2 cols

PKstaff_id

INT

PKoffice_id

INT

leave_log

4 cols

PKleave_id

INT

PKstaff_id

INT

leave_date

DATE

leave_type

VARCHAR(30)

Runnable DDL Setup & Seed Script

Run directly in MySQL Workbench, pgAdmin, DBeaver, or terminal

CREATE DATABASE IF NOT EXISTS studyflow_hr;
USE studyflow_hr;

CREATE TABLE departments (
  unit_id INT PRIMARY KEY,
  unit_name VARCHAR(80) NOT NULL,
  floor_no INT
);

CREATE TABLE employees (
  staff_id INT PRIMARY KEY,
  full_name VARCHAR(90) NOT NULL,
  unit_id INT,
  annual_pay INT NOT NULL,
  lead_id INT NULL,
  joined_on DATE,
  FOREIGN KEY (unit_id) REFERENCES departments(unit_id),
  FOREIGN KEY (lead_id) REFERENCES employees(staff_id)
);

CREATE TABLE offices (
  office_id INT PRIMARY KEY,
  office_name VARCHAR(80),
  city VARCHAR(60)
);

CREATE TABLE employee_office (
  staff_id INT,
  office_id INT,
  PRIMARY KEY (staff_id, office_id),
  FOREIGN KEY (staff_id) REFERENCES employees(staff_id),
  FOREIGN KEY (office_id) REFERENCES offices(office_id)
);

CREATE TABLE leave_log (
  leave_id INT PRIMARY KEY,
  staff_id INT,
  leave_date DATE,
  leave_type VARCHAR(30),
  FOREIGN KEY (staff_id) REFERENCES employees(staff_id)
);

INSERT INTO departments VALUES
(1, 'Product Lab', 3), (2, 'People Ops', 2), (3, 'Growth Studio', 4), (4, 'Platform', 5);

INSERT INTO employees VALUES
(1, 'Ira', 1, 80000, NULL, '2023-04-12'),
(2, 'Dev', 2, 55000, NULL, '2024-01-15'),
(3, 'Mira', 1, 95000, 1, '2022-09-20'),
(4, 'Kabir', 3, 70000, NULL, '2023-11-02'),
(5, 'Tara', 1, 88000, 1, '2024-06-18'),
(6, 'Rohan', 4, 91000, NULL, '2022-03-08'),
(7, 'Nia', 4, 72000, 6, '2025-01-10'),
(8, 'Aman', 3, 68000, 4, '2025-02-22');

INSERT INTO offices VALUES
(1, 'Jaipur Hub', 'Jaipur'), (2, 'Pune Hub', 'Pune'), (3, 'Bengaluru Hub', 'Bengaluru');

INSERT INTO employee_office VALUES
(1,1),(2,1),(3,1),(4,2),(5,1),(6,3),(7,3),(8,2);

INSERT INTO leave_log VALUES
(1,3,'2026-02-12','Casual'),(2,5,'2026-03-03','Sick'),(3,7,'2026-04-15','Casual');