SQL Practice : HR Database Schema

🔥 10 Practice Questions to Become a SQL Pro

1. 🧑‍💼 List all employees with their job titles and department names.
2. 💰 Find the average salary for each job title.
3. 🏢 Show all departments along with their location city and country.
4. 📈 Find the top 5 highest-paid employees.
5. 🔄 Display employees who have changed jobs (using JOB_HISTORY).
6. 🌍 List all countries and their regions.
7. 👨‍👩‍👧‍👦 Find employees who are managers and count how many employees report to them.
8. 📅 Show employees hired after January 1, 2020.
9. 💵 Find jobs where the max salary is more than 15,000.
10. 🗂️ Display the number of employees in each department.

📚 Understanding the HR Database Schema

This schema represents a typical Human Resources (HR) database often used in SQL practice environments. It consists of 7 tables that store employee, job, department, and location-related data. Let’s break down each table and its relationships:

EMPLOYEES Table

• Purpose: Stores details of each employee.
• Key Columns:
  • employee_id (Primary Key)
  • first_name, last_name, email, phone_number
  • hire_date
  • job_id (Foreign Key → JOBS table)
  • salary, commission_pct
  • manager_id (Self-referencing FK → EMPLOYEES table)
  • department_id (FK → DEPARTMENTS table)

Relationship:

• Each employee belongs to a department and has a job role.
• Employees can have managers (hierarchical structure).

JOBS Table

• Purpose: Defines job roles and salary ranges.
• Key Columns:
  • job_id (Primary Key)
  • job_title
  • min_salary, max_salary

Relationship:

• Linked to EMPLOYEES and JOB_HISTORY via job_id.

3️⃣ DEPARTMENTS Table

• Purpose: Stores department details.
• Key Columns:
  • department_id (Primary Key)
  • department_name
  • manager_id (FK → EMPLOYEES)
  • location_id (FK → LOCATIONS)

Relationship:

• Each department is located in a specific location and has a manager.

4️⃣ LOCATIONS Table

• Purpose: Stores physical location details.
• Key Columns:
  • location_id (Primary Key)
  • street_address, postal_code, city, state_province
  • country_id (FK → COUNTRIES)

Relationship:

• Each location belongs to a country.

5️⃣ COUNTRIES Table

• Purpose: Stores country details.
• Key Columns:
  • country_id (Primary Key)
  • country_name
  • region_id (FK → REGIONS)

Relationship:

• Each country belongs to a region.

6️⃣ REGIONS Table

• Purpose: Stores region details.
• Key Columns:
  • region_id (Primary Key)
  • region_name

Relationship:

• Regions group multiple countries.

7️⃣ JOB_HISTORY Table

• Purpose: Tracks historical job assignments for employees.
• Key Columns:
  • employee_id (FK → EMPLOYEES)
  • start_date, end_date
  • job_id (FK → JOBS)
  • department_id (FK → DEPARTMENTS)

Relationship:

• Shows which jobs employees held previously and in which departments.

🔗 How These Tables Work Together

• EMPLOYEES is the central table connecting to JOBS, DEPARTMENTS, and indirectly to LOCATIONS, COUNTRIES, and REGIONS.
• JOB_HISTORY provides historical context for EMPLOYEES.
• DEPARTMENTS link employees to physical locations.
• LOCATIONS → COUNTRIES → REGIONS form a geographical hierarchy.

💡 Why Use This in a SQL Playground?

This schema is perfect for practicing:

• Joins (INNER, LEFT, RIGHT)
• Aggregations (SUM, AVG, COUNT)
• Subqueries
• GROUP BY & HAVING
• Self Joins (for manager relationships)
• Data Analysis Queries