SQL Practice for Beginners: AdventureWorks Exercises

Hone your SQL skills with hands-on exercises using the AdventureWorks sample database. Practice essential SQL features using real-world scenarios.

Practicing SQL is important if you want to get better at using it. This article has 20 beginner-friendly exercises using the AdventureWorks sample database from Microsoft. This database is designed to show how SQL Server works. It represents a fictitious bicycle manufacturing company called AdventureWorks Cycles and includes five schemas: HumanResources, Person, Production, Purchasing, and Sales. This makes it great for learning and practicing SQL.

The AdventureWorks database covers various business scenarios such as manufacturing, sales, purchasing, product management, contact management, and human resources. This makes it an excellent resource for learning and practicing SQL because it provides a wide range of data and business processes to work with. By practicing with this database, you can gain hands-on experience with real-world data and scenarios; this will help you understand how to write efficient queries and effectively solve data problems.

If you want to try other sample databases, check out our list of the most interesting data sources for SQL practice. However, setting up your own SQL practice environment can take time. For quick practice, try our SQL Practice track or our SQL practice databases. We also release one free SQL course each month to help you keep learning.

Now, let’s start practicing SQL with AdventureWorks exercises!

Reviewing the AdventureWorks Database

The AdventureWorks database comprises five database schemas (that is, database structures used to group data tables), including HumanResources, Person, Production, Purchasing, and Sales. In the following exercises, we will use the HumanResources schema.

Let’s start by analyzing the schema:

First, let’s review the concepts of primary and foreign keys:

• The primary key (PK) is a column (or a set of columns) that uniquely identifies each row in a table. For example, the BusinessEntityID column is the primary key of the Employee table because each employee is assigned a unique ID number.
• The foreign key (FK) is a column (or a set of columns) that links two tables. Note that the foreign key of one table is the primary key of another table – based on that, the two tables are linked. For example, the BusinessEntityID column is the primary key of the Employee table and also a foreign key in the JobCandidate table. This links both tables together and lets the JobCandidate table refer to rows in the Employee

Now, let’s analyze the tables in the schema.

The Employee table stores information about the employees and is the main table of this schema. It is linked with the following tables:

• The JobCandidate table stores resumes of job applicants. The BusinessEntityID column is a PK in the Employee table and an FK in the JobCandidate The PK of the JobCandidate table is the JobCandidateID column.
• The EmployeePayHistory table stores the history of employees’ pay rates. The BusinessEntityID column is a PK in the Employee table and an FK in the EmployeePayHistory Note that the BusinessEntityID column in the EmployeePayHistory table is both an FK and part of the PK at the same time. The PK of the EmployeePayHistory table comprises both the BusinessEntityID and RateChangeDate columns. This is called a composite primary key.
• The EmployeeDepartmentHistory table stores the history of employees’ departments. The BusinessEntityID column is a PK in the Employee table and an FK in the EmployeeDepartmentHistory Note that the BusinessEntityID column in the EmployeeDepartmentHistory table is both an FK and part of the PK at the same time. The PK of the EmployeeDepartmentHistory table comprises the BusinessEntityID, DepartmentID, ShiftID, and StartDate columns. This is another composite primary key.

The EmployeeDepartmentHistory table stores information about employees and their departments over time and can be considered as another main table of this schema. It is linked with the following tables:

• The Shift table stores information about available shifts. The ShiftID column is a PK in the Shift table and an FK in the EmployeeDepartmentHistory Note that the ShiftID column is both an FK and part of the PK in the EmployeeDepartmentHistory table.
• The Department table stores information about departments. The DepartmentID column is a PK in the Department table and an FK in the EmployeeDepartmentHistory Note that the DepartmentID column is both an FK and part of the PK in the EmployeeDepartmentHistory table.

Now we are ready to begin our AdventureWorks exercises.

AdventureWorks Exercises for Beginners

We’ll cover all the basics of SQL (and dabble in some advanced features) as we move through this article:

• Single table queries for retrieving and filtering data.
• Multiple table queries that use JOINs to combine data from two or more tables.
• Grouping and aggregating data to perform mathematical operations on selected data.
• Other relevant SQL features like subqueries, UNION and INTERSECT, and common table expressions (CTEs).

Part 1: Single Table Queries

In this part, we will retrieve and filter data.

Note: To write correct queries, you should first familiarize yourself with the data stored in the tables.

Exercise 1: Select the job titles of all single male employees

Exercise: Select the job title of all male employees who are not married.

Solution:

SELECT JobTitle
FROM HumanResources.Employee
WHERE Gender = 'M'
AND MaritalStatus != 'M';

Explanation: We select the JobTitle column from the Employee table. Here the SQL syntax can be read exactly like plain English.

The filtering conditions are placed in the WHERE clause:

• We want to select only male employees, so we impose a condition on the Gender column: Gender = 'M'.
• We want to select only employees that are not married, so we impose another condition on the MaritalStatus column: MaritalStatus != 'M'.

Since both the conditions should apply at the same time, we use the AND operator to combine them.

Exercise 2: Select employees whose pay rate is 50 or more

Exercise: Select the BusinessEntityID, Rate, and RateChangeDate for all employees whose pay rate has ever been 50 or more.

Solution:

SELECT BusinessEntityID, Rate, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE Rate >= 50;

Explanation: We select three columns – BusinessEntityID, Rate, and RateChangeDate – from the EmployeePayHistory table.

Then we provide a filtering condition in the WHERE clause to retrieve only the ones whose pay rate has ever been greater than or equal to 50: Rate >= 50.

Exercise 3: Select all employees who joined new departments in 2008

Exercise: Select the BusinessEntityID, DepartmentID, and StartDate for each employee who started working in any department in 2008.

Solution:

SELECT BusinessEntityID, DepartmentID, StartDate
FROM HumanResources.EmployeeDepartmentHistory
WHERE StartDate BETWEEN '2008-01-01' AND '2008-12-31';

Explanation: We select IDs of employees (BusinessEntityID), the IDs of departments (DepartmentID), and the date when the employee joined that department (StartDate) from the EmployeeDepartmentHistory table.

As we want to list only employees who joined new departments in 2008, we impose a condition on the StartDate column: StartDate BETWEEN '2008-01-01' AND '2008-12-31'.

This condition is self-explanatory, as it is understandable in plain English. We want the StartDate value to be between Jan 1, 2008 and Dec 31, 2008, ensuring we cover all days in 2008.

Exercise 4: Select departments whose names match the pattern

Exercise: Select the department IDs, names, and group names for all departments whose name starts with ‘Prod’ OR for all departments whose group name ends with ‘ring’.

Solution:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
WHERE Name LIKE 'Prod%'
OR GroupName LIKE '%ring';

Explanation: We select DepartmentID, Name, and GroupName columns from the Department table.

Then, we provide the WHERE clause conditions:

• We want to select departments whose name starts with ‘Prod’, so we impose a condition on the Name column: Name LIKE 'Prod%'.
• We also want to select departments whose group name ends with ‘ring’, so we impose a condition on the GroupName column: GroupName LIKE '%ring'.

The LIKE keyword lets us define the pattern which the column value should match. For example, we want the Name column to start with ‘Prod’, so the pattern is 'Prod%'; % stands for any sequence of characters.

Because we want to find all records where at least one of the conditions is true, we use the OR keyword.

Exercise 5: Select departments that belong to certain groups

Exercise: Select department names that belong to either the ‘Research and Development’ group or the ‘Manufacturing’ group.

Solution:

SELECT Name
FROM HumanResources.Department
WHERE GroupName IN ('Research and Development', 'Manufacturing');

Explanation: We select the Name column from the Department table.

As we want to list departments that belong to defined groups, we use the IN keyword in the WHERE clause condition: GroupName IN ('Research and Development', 'Manufacturing').

This ensures that we output all departments that belong to groups listed in the IN keyword.

Part 2: Multiple Table Queries

In this part, we will use JOINs to combine data from multiple tables.

Note: To write correct queries, you should first familiarize yourself with data stored in the relevant tables.

Exercise 6: Select employees and their departments

Exercise: Select employees’ IDs with all the names of the departments where they have ever worked.

Solution:

SELECT edh.BusinessEntityID, d.Name
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID;

Explanation: We select the BusinessEntityID column from the EmployeeDepartmentHistory table and the Name column from the Department table.

We join these two tables using the JOIN clause on their common column, DepartmentID.

Exercise 7: Select female employees’ job titles and dates of department change

Exercise: Select the employee ID and job title along with the dates when the employee changed departments (StartDate) for all female employees.

Solution:

SELECT e.BusinessEntityID, e.JobTitle, edh.StartDate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE e.Gender = 'F';

Explanation: We select the BusinessEntityID and JobTitle columns from the Employee table and the StartDate column from the EmployeeDepartmentHistory table.

We use the JOIN clause to join the tables on their common column, BusinessEntityID.

As we want to list this information only for female employees, we impose a condition on the Gender column: e.Gender = 'F'.

Exercise 8: Select job titles per department

Exercise: Select job titles and their corresponding department names to find all job titles that have ever been used in each department. Do not include multiples of the same job title.

Solution:

SELECT DISTINCT e.JobTitle, d.Name
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID;

Explanation: We select the JobTitle column from the Employee table and the Name column from the Department table.

To select distinct job titles (i.e. no duplicates), we use the DISTINCT keyword before the column names.

We must join the Employee table with the EmployeeDepartmentHistory table on their common column, BusinessEntityID. Then we join the EmployeeDepartmentHistory  table with the Department table on their common column, DepartmentID.

Exercise 9: Select employees with their departments and shifts

Exercise: Select distinct department names  and shift names (no duplicate pairs) that employees in each department work. Rename the department name to DepartmentName and shift name to ShiftName.

Solution:

SELECT DISTINCT d.Name AS DepartmentName, s.Name AS ShiftName
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
JOIN HumanResources.Shift AS s
ON edh.ShiftID = s.ShiftID;

Explanation: We select the Name column from the Department table and the Name column from the Shift table, providing alias names for them using the AS keyword. We use DISTINCT to select distinct pairs.

To select department names and shift names, we must join these three tables:

• We join the EmployeeDepartmentHistory table with the Department table on the DepartmentID
• We join the EmployeeDepartmentHistory table with the Shift table on the ShiftID

Each of the JOIN clauses has an ON clause that defines the common columns on which join is performed.

Exercise 10: Select employees hired after 2010 with their departments and shifts

Exercise: Select employees’ IDs, department names, and shift names. Include only employees hired after 2010-01-01 and who work for departments within the Manufacturing and Quality Assurance groups.

Solution:

SELECT e.BusinessEntityID, d.Name, s.Name
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
JOIN HumanResources.Shift AS s
ON edh.ShiftID = s.ShiftID
WHERE e.HireDate > '2010-01-01'
AND d.GroupName IN ('Manufacturing', 'Quality Assurance');

Explanation: We select the BusinessEntityID column from the Employees table, the Name column from the Department table, and the Name column from the Shift table.

To select employee IDs along with department names and shift names, we must use three joins:

• We join the Employee table with the EmployeeDepartmentHistory table on the BusinessEntityID column.
• We join the EmployeeDepartmentHistory table with the Department table on the DepartmentID
• We join the EmployeeDepartmentHistory table with the Shift table on the ShiftID column.

Then we provide the WHERE clause conditions as follows:

• We want to list all employees hired after Jan 1, 2010, so we impose a condition on the HireDate column: HireDate > '2010-01-01'.
• We want to list only the employees that belong to certain department groups, so we use the IN keyword to create this condition: GroupName IN ('Manufacturing', 'Quality Assurance').

Part 3: Grouping and Aggregating Data

In this part, we will group and aggregate data so we can perform mathematical operations on selected data.

Note: To write correct queries, you should first familiarize yourself with data stored in the relevant tables.

Exercise 11: Select the highest and lowest sick leave hours

Exercise: Select the minimum and maximum number of sick leave hours taken by the employees.

Solution:

SELECT MIN(SickLeaveHours) AS MinSickLeaveHours,
       MAX(SickLeaveHours) AS MaxSickLeaveHours
FROM HumanResources.Employee;

Explanation: We use the MIN() and MAX() aggregate functions – providing the SickLeaveHours column from the Employee table as an argument – to select the minimum and maximum numbers of sick leave hours taken by the employees.

Exercise 12: Select the average number of vacation hours per job title

Exercise: Select the job titles and the average number of vacation hours per job title.

Solution:

SELECT JobTitle, AVG(VacationHours) AS AvgVacationHours
FROM HumanResources.Employee
GROUP BY JobTitle;

Explanation: We select the JobTitle column from the Employee table.

We use the AVG() aggregate function to get the average number of vacation hours for each job title. We want to have groups of data based on the distinct values in the JobTitle column; this requires us to use the GROUP BY clause with the JobTitle column as its argument.

Exercise 13: Select the count of employees based on their gender

Exercise: Select the employees’ gender and the count of employees of each gender.

Solution:

SELECT Gender, COUNT(*) AS Count
FROM HumanResources.Employee
GROUP BY Gender;

Explanation: We select the Gender column from the Employee table.

We use the COUNT() aggregate function to get the count of employees for each gender. This requires us to use the GROUP BY clause with the Gender column as its argument; we want to have groups of data based on the distinct values in the Gender column.

Exercise 14: Select the count of departments in each group

Exercise: Find the count of departments in each department group. List only those department group names that have more than two departments.

Solution:

SELECT GroupName, COUNT(*) AS DepartmentsCount
FROM HumanResources.Department
GROUP BY GroupName
HAVING COUNT(*) > 2;

Explanation: We select the GroupName column from the Department table.

We use the COUNT() aggregate function to get the count of departments in each department group. This requires us to use the GROUP BY clause with the GroupName column as its argument.

To impose a condition on the aggregate function, we use the HAVING clause after the GROUP BY clause: HAVING COUNT(*) > 2.

Exercise 15: Select the sum of sick leave hours for each department

Exercise: Select the department names and the sum of sick leave hours taken by employees working currently in each department. Rename this column to SumSickLeaveHours.

Solution:

SELECT d.Name, SUM(e.SickLeaveHours) AS SumSickLeaveHours
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE edh.EndDate IS NULL
GROUP BY d.Name;

Explanation: We select the Name column from the Department.

We use the SUM() aggregate function to get the sum of sick leave hours used by employees for each department. This requires us to use the GROUP BY clause with the Name column from the Department table as its argument.

We must join the Employee table with the EmployeeDepartmentHistory table on the BusinessEntityID column. Then we join the EmployeeDepartmentHistory  table with the Department table on the DepartmentID column.

As we consider only the employees that currently work in any department, we impose a condition that the EndDate column from the EmployeeDepartmentHistory table must be NULL.

Part 4: Other SQL features

In this part, we will cover subqueries (i.e. queries nested within queries), operators like UNION and INTERSECT, and common table expressions (CTEs).

Note: To write correct queries, you should first familiarize yourself with data stored in the relevant tables.

Exercise 16: Select employees with their current pay rate

Exercise: Select employees’ IDs and their current pay rates.

Solution:

SELECT e.BusinessEntityID, e.Rate AS CurrentPayRate
FROM HumanResources.EmployeePayHistory AS e
WHERE e.RateChangeDate = (
			SELECT MAX(e2.RateChangeDate)
			FROM HumanResources.EmployeePayHistory AS e2
			WHERE e2.BusinessEntityID = e.BusinessEntityID
		);

Explanation: We select the BusinessEntityID and Rate columns from the EmployeePayHistory table.

Since the EmployeePayHistory table stores the history of pay rates for each employee, we need to impose a condition on the RateChangeDate column value to be equal to an employee’s most recent date of pay rate change. We do this by defining a subquery in the WHERE clause that selects the most recent date when the pay rate was modified. This subquery uses the MAX() aggregate function to select the most recent date.

We’re selecting the most recent RateChangeDate for each employee separately, so we add a WHERE clause in the subquery to match the employee IDs between outer and inner queries.

Exercise 17: Select the minimum, average, and maximum pay rates

Exercise: Select the minimum, average, and maximum pay rates from employees’ current pay rates.

Solution:

SELECT MIN(CurrentPayRate) AS MinPayRate,
       AVG(CurrentPayRate) AS AvgPayRate,
       MAX(CurrentPayRate) AS MaxPayRate
FROM (
		SELECT e.BusinessEntityID, e.Rate AS CurrentPayRate
		FROM HumanResources.EmployeePayHistory AS e
		WHERE e.RateChangeDate = (
				SELECT MAX(e2.RateChangeDate)
				FROM HumanResources.EmployeePayHistory AS e2
				WHERE e2.BusinessEntityID = e.BusinessEntityID
			)
) AS cpr;

Explanation: We use the MIN(), AVG(), and MAX() aggregate functions to select the minimum, average, and maximum pay rates, providing the CurrentPayRate column from the subquery as an argument.

We use the subquery in the FROM clause. This subquery selects the current pay rates for each employee, as explained in Exercise 16.

Note this solution nests two subqueries. First, we use a subquery in the FROM clause. And then that subquery uses a subquery in its own WHERE clause.

Exercise 18: Select employee IDs with their out-of-office hours

Exercise: Select all employee IDs who took more than 60 hours of vacation or who took more than 60 hours of sick leave.

Solution:

SELECT BusinessEntityID
FROM HumanResources.Employee
GROUP BY BusinessEntityID
HAVING SUM(VacationHours) > 60

UNION

SELECT BusinessEntityID
FROM HumanResources.Employee
GROUP BY BusinessEntityID
HAVING SUM(SickLeaveHours) > 60;

Explanation: The first query selects the BusinessEntityID column from the Employee table and uses the SUM() aggregate function to sum the number of vacation hours taken by each employee. We group by the employee ID and impose a condition that the sum must be greater than 60.

Analogically, the second query selects the BusinessEntityID column from the Employee table and uses the SUM() aggregate function to sum the number of sick leave hours taken by each employee. We group by the employee ID and impose a condition that the sum must be greater than 60.

The UNION operator combines the output of both queries.

Exercise 19: Select employee IDs with certain job titles and departments

Exercise: Select the IDs of employees who have the job titles ‘Sales Representative’ or ‘Tool Designer’ and who have worked (or are working) in the Sales or Marketing departments.

Solution:

SELECT e.BusinessEntityID
FROM HumanResources.Employee AS e
WHERE e.JobTitle IN ('Sales Representative', 'Tool Designer')

INTERSECT

SELECT edh.BusinessEntityID
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE d.Name IN ('Sales', 'Marketing');

Explanation: The first query selects the BusinessEntityID column from the Employee table. It filters the JobTitle column to include only Sales Representatives and Tool Designers.

The second query selects the BusinessEntityID column from the EmployeeDepartmentHistory table. It filters the department name to include only Sales and Marketing departments.

The INTERSECT operator finds the common output of the two queries – that is, it will output only the  IDs of employees that fulfill the WHERE clause conditions of both queries.

Exercise 20: List sales reps and marketing managers with their departments

Exercise: Select the IDs, job titles, and department names for employees associated with Sales Representative or Marketing Manager roles.

Solution:

SELECT e.BusinessEntityID, e.JobTitle, d.Name AS DepartmentName
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh 
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE e.JobTitle IN ('Sales Representative', 'Marketing Manager');

Explanation: The query uses a JOIN to link the Employee table with the EmployeeDepartmentHistory table based on the BusinessEntityID column. This join ensures that each employee's record is connected to their department history.

Another JOIN links the EmployeeDepartmentHistory table to the Department table using the DepartmentID. This allows the query to retrieve department information related to the employee's job history.

The WHERE clause filters the results to include only those employees whose job titles are either ‘Sales Representative’ or ‘Marketing Manager’. This filtering is done using the IN operator, which specifies the desired job titles. The query's structure efficiently combines and filters data from multiple tables to return the relevant employee job titles and their associated departments.

Practice SQL with AdventureWorks Exercises!

In this article, you explored the fundamentals of SQL through using exercises with the AdventureWorks database. You learned how to perform single table queries with SELECT, combine table data using JOIN, group data with GROUP BY, and use aggregate functions like COUNT(), AVG(), SUM(), MIN(), and MAX(). Additionally, you delved into subqueries, UNION, and INTERSECT.

For more exercises with solutions and detailed explanations, check out our articles 10 Beginner SQL Practice Exercises With Solutions and SQL Joins: 12 Practice Questions with Detailed Answers. Dive deeper into SQL practice opportunities with our comprehensive Guide to SQL Practice at LearnSQL.com. And as I mentioned earlier, you can also continue your practice with our SQL Practice track, SQL practice databases, or monthly free SQL course.

Good luck on your SQL journey!