The Project | About Us | Contribute | Donations | License

HOME

In this section, we will provide practical exercises to help you reinforce your understanding of SQL. These exercises will cover various aspects of SQL, including basic operations, complex queries, and data manipulation. Each exercise will be followed by a detailed solution to help you understand the correct approach.

Exercise 1: Basic SELECT Queries

Task:

Write a SQL query to retrieve all columns from the employees table.

Solution:

SELECT * FROM employees;

Explanation:

The SELECT * statement is used to select all columns from the employees table.

Exercise 2: Filtering Data

Task:

Write a SQL query to retrieve the names and salaries of employees who earn more than $50,000.

Solution:

SELECT name, salary
FROM employees
WHERE salary > 50000;

Explanation:

SELECT name, salary: Selects the name and salary columns.
FROM employees: Specifies the employees table.
WHERE salary > 50000: Filters the results to include only those employees whose salary is greater than $50,000.

Exercise 3: Sorting Data

Task:

Write a SQL query to retrieve the names of employees, sorted by their hire date in ascending order.

Solution:

SELECT name
FROM employees
ORDER BY hire_date ASC;

Explanation:

SELECT name: Selects the name column.
FROM employees: Specifies the employees table.
ORDER BY hire_date ASC: Sorts the results by the hire_date column in ascending order.

Exercise 4: Aggregating Data

Task:

Write a SQL query to find the average salary of all employees.

Solution:

SELECT AVG(salary) AS average_salary
FROM employees;

Explanation:

SELECT AVG(salary) AS average_salary: Calculates the average salary and labels the result as average_salary.
FROM employees: Specifies the employees table.

Exercise 5: Grouping Data

Task:

Write a SQL query to count the number of employees in each department.

Solution:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Explanation:

SELECT department, COUNT(*) AS employee_count: Selects the department column and counts the number of employees in each department, labeling the result as employee_count.
FROM employees: Specifies the employees table.
GROUP BY department: Groups the results by the department column.

Exercise 6: Joining Tables

Task:

Write a SQL query to retrieve the names of employees along with the names of their departments. Assume there are two tables: employees and departments. The employees table has a department_id column that references the id column in the departments table.

Solution:

SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

Explanation:

SELECT employees.name AS employee_name, departments.name AS department_name: Selects the name column from both employees and departments tables and labels them as employee_name and department_name respectively.
FROM employees: Specifies the employees table.
JOIN departments ON employees.department_id = departments.id: Joins the employees table with the departments table on the department_id column.

Exercise 7: Subqueries

Task:

Write a SQL query to find the names of employees who earn more than the average salary.

Solution:

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation:

SELECT name: Selects the name column.
FROM employees: Specifies the employees table.
WHERE salary > (SELECT AVG(salary) FROM employees): Filters the results to include only those employees whose salary is greater than the average salary calculated by the subquery.

Exercise 8: Updating Data

Task:

Write a SQL query to increase the salary of all employees in the 'Sales' department by 10%.

Solution:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

Explanation:

UPDATE employees: Specifies the employees table to update.
SET salary = salary * 1.10: Increases the salary column by 10%.
WHERE department = 'Sales': Applies the update only to employees in the 'Sales' department.

Exercise 9: Deleting Data

Task:

Write a SQL query to delete all employees who have not been active since 2019. Assume there is a column last_active_date in the employees table.

Solution:

DELETE FROM employees
WHERE last_active_date < '2020-01-01';

Explanation:

DELETE FROM employees: Specifies the employees table to delete from.
WHERE last_active_date < '2020-01-01': Deletes employees whose last_active_date is before January 1, 2020.

Conclusion

These exercises cover a range of SQL operations, from basic SELECT queries to more complex tasks like joins and subqueries. By practicing these exercises, you should gain a solid understanding of how to manipulate and query data using SQL. Remember to test your queries in a database environment to see the results and further solidify your learning.

SQL Exercises

Exercise 1: Basic SELECT Queries

Task:

Solution:

Explanation:

Exercise 2: Filtering Data

Task:

Solution:

Explanation:

Exercise 3: Sorting Data

Task:

Solution:

Explanation:

Exercise 4: Aggregating Data

Task:

Solution:

Explanation:

Exercise 5: Grouping Data

Task:

Solution:

Explanation:

Exercise 6: Joining Tables

Task:

Solution:

Explanation:

Exercise 7: Subqueries

Task:

Solution:

Explanation:

Exercise 8: Updating Data

Task:

Solution:

Explanation:

Exercise 9: Deleting Data

Task:

Solution:

Explanation:

Conclusion

Fundamentals of Databases

Module 1: Introduction to Databases

Module 2: Relational Databases

Module 3: Non-Relational Databases

Module 4: Schema Design

Module 5: Normalization

Module 6: Practical Exercises

Module 7: Case Studies

Module 8: Additional Resources