Mastering SQL doesn't come from reading alone; it requires practical application. Hands-on exercises and solving problems are key to truly understanding and enhancing your SQL skills. In this comprehensive guide, we've compiled a list of SQL exercises along with their solutions to help you grasp various aspects of this powerful language.
Whether you're a beginner starting your journey into data management or an experienced developer looking to refine your skills, these exercises will provide valuable insights and help you build a solid foundation in SQL. Let's dive into the world of SQL exercises and solutions.
SQL Basics: Fundamental Operators and Syntax
The foundation of SQL lies in understanding its basic operators and syntax. Let's start with two fundamental exercises that will help you grasp these concepts.
Exercise 1: Retrieving Data
Write an SQL query to fetch all the columns and their respective data from an 'employees' table.
Solution:
SELECT * FROM employees;
Exercise 2: Selecting Specific Columns
Write a query to retrieve only the 'first_name' and 'last_name' columns from the 'employees' table.
Solution:
SELECT first_name, last_name FROM employees;
Advanced SQL: Aggregation and Grouping
Once you're comfortable with basic SQL, it's time to explore advanced topics like aggregation and grouping.
Exercise 3: Aggregate Data
Find the total salary paid to all employees.
Solution:
SELECT SUM(salary) FROM employees;
Exercise 4: Grouping Data
Group the employees by their departments and find the total salary paid in each department.
Solution:
SELECT department, SUM(salary) FROM employees GROUP BY department;
SQL Joins: Connecting Different Tables
SQL joins are a crucial part of the language. Let's practice with two exercises involving different types of joins.
Exercise 5: INNER JOIN
From 'employees' and 'departments' tables, fetch the employee details along with their respective department names.
Solution:
SELECT e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;
Exercise 6: LEFT JOIN
Write a query to fetch all employees along with their respective departments. If an employee doesn't have a department, retrieve null values.
Solution:
SELECT e.first_name, e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;
Practicing these SQL exercises and understanding their solutions will help you gain confidence and proficiency in using SQL. Happy learning, and keep exploring the vast world of data management!