Practice Writing Basic SQL Queries

Now that you've learned the fundamental SQL statements for interacting with data (SELECT, INSERT, UPDATE, DELETE, along with WHERE and ORDER BY), it's time to put that knowledge into practice. Working through examples is the best way to solidify your understanding and build confidence in writing your own queries.

Setting Up a Practice Environment

For these exercises, imagine we have a simple database for a small library or bookstore. It consists of two tables: Authors and Books.

Authors Table:

This table stores information about authors.

author_id	first_name	last_name
1	Jane	Austen
2	George	Orwell
3	Mary	Shelley
4	Charles	Dickens

• author_id: A unique number identifying each author (Primary Key).
• first_name: The author's first name.
• last_name: The author's last name.

Books Table:

This table stores information about books, linking each book to its author using author_id.

book_id	title	publication_year	author_id
101	Pride and Prejudice	1813	1
102	Emma	1815	1
103	1984	1949	2
104	Animal Farm	1945	2
105	Frankenstein	1818	3
106	Oliver Twist	1838	4
107	Great Expectations	1861	4

• book_id: A unique number identifying each book (Primary Key).
• title: The title of the book.
• publication_year: The year the book was published.
• author_id: A number linking to the author_id in the Authors table (Foreign Key).

You don't need to create these tables yourself right now. Just use this structure and data as the basis for writing the queries below. If you have access to a database tool (like DB Browser for SQLite, DBeaver, or an online SQL environment like SQL Fiddle), you could create these tables and insert the data to run the queries yourself.

Practice Exercises

Let's work through some common tasks using SQL. Try to write the query yourself before looking at the provided solution.

Exercise 1: Retrieving All Authors

Task: Write a query to retrieve all information about all authors from the Authors table.

Solution:

SELECT *
FROM Authors;

This is the most basic SELECT query, using * to indicate "all columns".

Exercise 2: Retrieving Specific Book Information

Task: Write a query to retrieve only the title and publication_year for all books in the Books table.

Solution:

SELECT title, publication_year
FROM Books;

Here, we specify the exact columns we want instead of using *.

Exercise 3: Filtering Books by Author

Task: Write a query to find all books written by the author with author_id = 2 (George Orwell). Retrieve all columns for these books.

Solution:

SELECT *
FROM Books
WHERE author_id = 2;

The WHERE clause filters the rows, returning only those where the author_id column matches the value 2.

Exercise 4: Filtering Books by Publication Year

Task: Write a query to find all books published after 1900. Retrieve their title and publication_year.

Solution:

SELECT title, publication_year
FROM Books
WHERE publication_year > 1900;

The WHERE clause uses the greater than operator (>) for numerical comparison.

Exercise 5: Sorting Books by Title

Task: Write a query to retrieve the title and author_id of all books, sorted alphabetically by title.

Solution:

SELECT title, author_id
FROM Books
ORDER BY title ASC;

ORDER BY title sorts the results based on the title column. ASC specifies ascending order (A-Z), which is often the default, but it's good practice to be explicit. To sort in descending order (Z-A), you would use DESC.

Exercise 6: Adding a New Author

Task: Write a query to add a new author, 'Leo Tolstoy', to the Authors table. Assign him author_id = 5.

Solution:

INSERT INTO Authors (author_id, first_name, last_name)
VALUES (5, 'Leo', 'Tolstoy');

The INSERT INTO statement adds a new row. We specify the table, the columns to populate, and the corresponding values using the VALUES clause.

Exercise 7: Adding a New Book

Task: Write a query to add Leo Tolstoy's book 'War and Peace', published in 1869, to the Books table. Assume its book_id is 108 and Leo Tolstoy's author_id is 5.

Solution:

INSERT INTO Books (book_id, title, publication_year, author_id)
VALUES (108, 'War and Peace', 1869, 5);

Similar to adding an author, we insert a new row into the Books table.

Exercise 8: Updating a Book Title

Task: It turns out the title for book_id 105 should be 'Frankenstein; or, The Modern Prometheus'. Write a query to update the title for this book.

Solution:

UPDATE Books
SET title = 'Frankenstein; or, The Modern Prometheus'
WHERE book_id = 105;

The UPDATE statement modifies existing data. SET specifies the column and its new value. The WHERE clause is significant here; without it, all book titles would be changed. Always use WHERE with UPDATE unless you intend to modify every row.

Exercise 9: Deleting a Book

Task: Let's say the book 'Emma' (book_id = 102) is removed from the collection. Write a query to delete this book from the Books table.

Solution:

DELETE FROM Books
WHERE book_id = 102;

The DELETE FROM statement removes rows. Again, the WHERE clause is essential to specify which row(s) to delete. Omitting WHERE would delete all rows from the table. Be very careful when using DELETE.

Keep Practicing

These exercises cover the basic Create, Read, Update, Delete (CRUD) operations using SQL. The best way to become comfortable with SQL is to continue practicing.

• Try modifying the WHERE clauses with different conditions (e.g., find books published before a certain year, find authors with a specific last name).
• Experiment with ORDER BY on different columns and using DESC for descending order.
• If you set up a practice database, try adding more authors and books, updating different fields, and selectively deleting records.

Understanding these fundamental commands provides a solid foundation for working with relational databases. As you progress, you'll learn more advanced SQL features for more complex data retrieval and manipulation.