Introduction
A library database administrator needs help creating a report that combines information from two tables. The library has separate tables for authors and their books, and they need a query that will show book titles alongside author names in a single view.
Create a Combined Author-Book Report
Tasks
- Connect to MySQL as the root user
- Use the
librarydatabase - Write a query that combines data from both tables to display:
- Book title
- Author name
- Publication year
- Results should be ordered by publication year (newest first)
- Save the results to a file named
author_books.txtin the~/projectdirectory
Requirements
- All operations must be performed in the
~/projectdirectory - Query must join the
authorsandbookstables - All books and their corresponding authors must be included
- Results must show exactly three columns in this order: title, author name, publication year
- Publication year must be displayed as a number
- The output must be saved to a file named
author_books.txtin the~/projectdirectory
Example
After writing the correct query and saving the results, you can verify the output:
cat ~/project/author_books.txt
+------------------------+--------------+------------------+
| title | name | publication_year |
+------------------------+--------------+------------------+
| Tech Trends | Maria Garcia | 2023 |
| Digital Innovation | Robert Chen | 2022 |
| SQL Mastery | Jane Smith | 2021 |
| Database Design Basics | Jane Smith | 2020 |
+------------------------+--------------+------------------+
Summary
In this challenge, you practiced creating a query that combines data from multiple tables using INNER JOIN in MySQL. The skills demonstrated include connecting tables using their relationship fields, selecting specific columns from multiple tables, and ordering the results. These fundamental JOIN operation skills are essential for working with relational databases and will be used frequently when querying data from multiple related tables.
