Employee Dataset for SQL Practice

• employees.department_id →references departments.department_idmany to one: each Employees row points to one Departments row
• salaries.employee_id →references employees.employee_idmany to one: each Salaries row points to one Employees row

1. Write a SQL query to find the average salary for each department, joining employees and departments tables.

   You will need to join the employees table with the departments table on department_id and then join with the salaries table on employee_id to calculate the average salary per department.

   tables: employees, departments, salaries

2. Write a SQL query to list all employees hired in the last year, ordered by hire date.

   Filter the employees table for hire_date within the last year from the current date.

   tables: employees

3. Write a SQL query to identify employees who have not had a salary update in the last two years.

   Join employees and salaries tables, then filter for employees whose latest salary effective_date is older than two years ago.

   tables: employees, salaries

4. Find the employee with the highest salary using a window function.

   Use a window function like ROW_NUMBER() or RANK() partitioned by employee and ordered by salary amount descending.

   tables: employees, salaries

Table names match the SQLite file and the SQL script.

SELECT d.department_name, AVG(s.amount) AS average_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN salaries s ON e.employee_id = s.employee_id
GROUP BY d.department_name
LIMIT 20;

SELECT e.employee_id, e.first_name, e.last_name, MAX(s.effective_date) AS last_salary_update
FROM employees e
LEFT JOIN salaries s ON e.employee_id = s.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name
HAVING last_salary_update < date('now', '-2 years') OR last_salary_update IS NULL
LIMIT 20;

SELECT employee_id, first_name, last_name, amount
FROM (
  SELECT 
    e.employee_id, 
    e.first_name, 
    e.last_name, 
    s.amount,
    ROW_NUMBER() OVER (ORDER BY s.amount DESC) as rn
  FROM employees e
  JOIN salaries s ON e.employee_id = s.employee_id
) AS ranked_salaries
WHERE rn = 1
LIMIT 20;

Excel

The dataset can be downloaded in XLSX format. Each table will be on a separate sheet.

Power BI

The dataset is available in multiple formats, including CSV and Excel, which can be easily imported into Power BI for visualization.

SQL

The dataset is provided as a SQLite database script. You can load this script into a SQLite client to practice SQL queries. The tables are 'departments', 'employees', and 'salaries'. Relationships are established via 'department_id' and 'employee_id'.

Python

The dataset is available in CSV and JSON formats, suitable for loading into pandas DataFrames for analysis with Python.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file