10 Essential SQL Practice Exercises for Beginners (With Detailed Solutions)

Introduction

If you‘re just starting to learn SQL, you‘ve made a great choice. SQL (Structured Query Language) is an incredibly valuable skill to have in today‘s data-driven world. Whether you want to become a data analyst, data scientist, database administrator, web developer, or really any role that involves working with data, mastering SQL is crucial.

SQL allows you to extract, manipulate, and analyze data stored in relational databases. With SQL, you can gain valuable insights from your data to drive business decisions. Almost every company today uses databases in some form, so SQL skills are highly sought-after by employers.

One of the best ways to learn SQL is through hands-on practice. Solving practice problems and writing real queries will help reinforce your understanding of key concepts. To help you get started, I‘ve compiled a list of 10 SQL practice exercises that are perfect for beginners. These exercises cover the most important SQL fundamentals you need to know.

For each exercise, I‘ll provide a detailed solution and explanation. I‘ll also share some tips and best practices I‘ve learned over the years for writing efficient and effective SQL queries. Let‘s dive in!

SQL Practice Exercises

1. SELECT Statements

Problem: Write a SQL query to retrieve all columns and rows from a table named "employees".

Solution:

SELECT *
FROM employees;

Explanation: This is one of the simplest SQL queries you can write. The SELECT statement is used to retrieve data from a database. The "*" means we want to select all columns. The FROM keyword specifies which table we want to retrieve data from, in this case the "employees" table.

Best practice tip: In practice, you should avoid using SELECT * and instead only select the specific columns you need. This is more efficient and also makes your code more maintainable if the table structure changes in the future.

2. Filtering with WHERE

Problem: Write a query to retrieve only employees whose salary is greater than $50,000.

Solution:

SELECT *
FROM employees
WHERE salary > 50000;

Explanation: The WHERE clause allows you to filter results based on a specified condition. Here, we only want employees who have a salary value greater than 50,000. SQL will check each row in the employees table, and only include those rows that meet the condition in the result set.

3. Sorting with ORDER BY

Problem: Retrieve all employees sorted by hire date in descending order.

Solution:

SELECT *
FROM employees
ORDER BY hire_date DESC;

Explanation: The ORDER BY keyword lets you sort the result set by one or more columns. Here, we‘re sorting by the hire_date column. The DESC keyword means we want to sort in descending order (newest to oldest). If we wanted ascending order instead, we would use ASC (or leave it off, since ascending is the default).

4. Limit Results with TOP/LIMIT

Problem: Retrieve the first 10 employees in the table.

Solution:

SELECT TOP 10 *
FROM employees;

— or

SELECT *
FROM employees
LIMIT 10;

Explanation: If you only want to retrieve a certain number of rows, you can use the TOP or LIMIT keyword. This is useful for situations where you have a very large table and don‘t need all the results. The syntax is slightly different between database systems – SQL Server uses TOP while MySQL uses LIMIT.

5. Aggregations with GROUP BY

Problem: Calculate the average salary for each department.

Solution:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Explanation: The GROUP BY clause is used to group rows that have the same values in one or more columns. Here, we want to group employees by their department and calculate the average salary for each. The AVG() function calculates the average value of a numeric column. We give the average salary result an alias of "avg_salary" using the AS keyword.

6. Joining Tables

Problem: Retrieve the name of each employee along with their respective department name.

Solution:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Explanation: Joining tables allows you to combine rows from two or more tables based on a related column. Here, we want data from both the employees and departments tables. The JOIN keyword specifies how to join the tables, and the ON keyword defines the join condition. We‘re doing an inner join, which will only include rows where the join condition is true (i.e. the department_id foreign key in the employees table matches the department_id primary key in the departments table).

7. Subqueries

Problem: Retrieve employees whose salary is above the average salary for their department.

Solution:

SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);

Explanation: A subquery is a query within another query. It allows you to use the results of one query as input to another. Here, for each employee, we want to compare their salary to the average salary for their department. The subquery calculates the average salary per department, and the outer query compares each employee‘s salary to the result.

8. Updating Data

Problem: Give a 10% raise to all employees in the ‘Marketing‘ department.

Solution:

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = ‘Marketing‘
);

Explanation: The UPDATE statement is used to modify existing data in a table. Here, we‘re increasing the salary column by 10% for certain rows. The WHERE clause specifies which rows to update – in this case, employees in the ‘Marketing‘ department. We use a subquery to find the department_id for the ‘Marketing‘ department name.

9. Deleting Data

Problem: Remove all employees who have been with the company for less than 1 year.

Solution:

DELETE FROM employees
WHERE hire_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Explanation: The DELETE statement removes rows from a table. Here, we want to delete all employees whose hire_date is within the last year. The DATE_SUB() function subtracts a time interval from a date. CURDATE() gives today‘s date, and we subtract 1 year from that. So any hire dates after that cutoff date will be deleted.

10. Finding Duplicates

Problem: Find all employees who share the same first and last name.

Solution:

SELECT first_name, last_name, COUNT()
FROM employees
GROUP BY first_name, last_name
HAVING COUNT() > 1;

Explanation: To find duplicate values, we can use the GROUP BY clause to group rows by the columns we want to check for duplicates. The HAVING clause then lets us filter the groups to only those that have a COUNT greater than 1 (meaning there is more than one row with those values). The COUNT(*) function counts the number of rows in each group.

Conclusion

SQL is an essential skill for working with relational databases and data in general. While it can seem intimidating at first, the best way to learn is through hands-on practice. The 10 SQL exercises we covered here are a great starting point for beginners looking to master the basics.

We went over fundamental SQL concepts like:

• Selecting data with SELECT statements
• Filtering results with WHERE clauses
• Sorting data with ORDER BY
• Limiting result sets with TOP/LIMIT
• Aggregating data with GROUP BY
• Joining tables to combine related data
• Using subqueries for complex operations
• Modifying data with UPDATE and DELETE
• Identifying duplicate values

If you‘re serious about learning SQL, I recommend working through practice problems like these on a regular basis. Start with simple queries and gradually work your way up to more complex challenges. Don‘t be afraid to experiment and break things – that‘s the best way to learn!

In addition to practice problems, there are many great resources available for going deeper with SQL. Online courses, textbooks, documentation, and SQL communities are all helpful for expanding your knowledge.

Some recommended resources:

• W3Schools SQL Tutorial (https://www.w3schools.com/sql/)
• SQL Basics for Beginners Course (https://www.analyticssteps.com/courses/sql-for-beginners)
• SQL Queries for Mere Mortals (https://www.amazon.com/SQL-Queries-Mere-Mortals-Hands/dp/0321992474)
• StackOverflow SQL Questions (https://stackoverflow.com/questions/tagged/sql)

Remember, learning SQL is a journey. Stay curious, keep practicing, and don‘t get discouraged. With dedication and the right resources, you‘ll be writing complex queries in no time. The skills you gain will be incredibly valuable for your career in our increasingly data-driven world.