Structured Query Language, or SQL, is an essential skill for anyone working with databases. Whether you're a seasoned data professional or just starting out, practicing SQL exercises is crucial for improving your skills and understanding. This article will provide you with a variety of SQL exercises, along with their solutions, covering common tasks and operations.
SQL is a standard language for managing and manipulating relational databases. It's used to create, modify, and extract data from databases. Familiarizing yourself with SQL will not only help you manage data more effectively, but it will also open up opportunities for data analysis and business intelligence.
Basic SQL Operations
Let's start with some fundamental SQL operations. We'll use a simple database named 'library' with two tables: 'books' and 'authors'.
The 'books' table has the following schema:
| id | title | author_id |
|---|---|---|
| 1 | The Catcher in the Rye | 1 |
| 2 | To Kill a Mockingbird | 2 |
The 'authors' table has the following schema:
| id | name |
|---|---|
| 1 | J.D. Salinger |
| 2 | Harper Lee |
SELECT Statement
The SELECT statement is used to select data from a database. Here's an example:
SELECT title, author_id FROM books;
This query will return the title and author ID of each book in the 'books' table.
WHERE Clause
The WHERE clause filters records. Here's an example:
SELECT title, author_id FROM books WHERE author_id = 1;
This query will return books written by the author with ID 1 (J.D. Salinger).
JOIN Operations
Joins are used to combine rows from two or more tables based on a related column between them.
Let's join the 'books' and 'authors' tables to retrieve the book title and author name:
INNER JOIN
SELECT books.title, authors.name FROM books
INNER JOIN authors ON books.author_id = authors.id;
This query will return a table containing the book title and author name.
LEFT JOIN
SELECT books.title, authors.name FROM books
LEFT JOIN authors ON books.author_id = authors.id;
This query will return all books, even if there's no matching author ID in the 'authors' table. The result will have null values for the author name in such cases.
Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. Here's an example using the COUNT function:
COUNT Function
SELECT COUNT(*) FROM books;
This query will return the total number of books in the 'books' table.
GROUP BY Clause
The GROUP BY clause groups rows that have the same values in specified columns into aggregated data.
SELECT author_id, COUNT(*) FROM books GROUP BY author_id;
This query will return the number of books written by each author.
Practicing these SQL exercises will help you build a strong foundation in SQL and prepare you for more complex queries and operations. Keep learning and practicing to advance your SQL skills!