Here are five graded tasks based on the lab notes, starting from easy and gradually increasing in difficulty.

### Task 1: The Basics (Easy) 🚀

**Goal:** Get comfortable with creating a database and a table, and performing basic data insertion and retrieval.

**Scenario:** You need to create a simple inventory for a local library.

1. **Create a Database:** Create a new database named `library`.
2. **Create a Table:** Inside the `library` database, create a table named `books`. It should have the following columns:
* `book_id` (`INT`, `PRIMARY KEY`)
* `title` (`VARCHAR(100)`)
* `author` (`VARCHAR(100)`)
* `published_year` (`INT`)
3. **Insert Data:** Use the `INSERT INTO` command to add at least three of your favorite books to the `books` table.
4. **View Data:** Write a `SELECT` query to display all the information for every book in your table.

---

### Task 2: Filtering and Sorting (Medium-Easy) 🧐

**Goal:** Practice filtering data using the `WHERE` clause and sorting the results.

**Scenario:** The library wants to find specific books and organize its catalog display.

1. **Update Table:** Add a new column to the `books` table called `genre` (`VARCHAR(50)`). Use the `UPDATE` command to assign a genre (e.g., 'Fantasy', 'Science Fiction', 'Mystery') to each of your existing books.
2. **Filter by Condition:** Write a query to find all books that were published after the year 2000.
3. **Filter with `AND`:** Write a query to find all books where the genre is 'Fantasy' **AND** the author is 'J.K. Rowling'. (If you don't have this, use an author/genre combination that exists in your data).
4. **Sort Data:** Write a query to list all books, but this time, order them by `published_year` in descending order (newest first).
5. **Delete a Record:** A book is lost. Use the `DELETE` command to remove one book from the table using its unique `book_id`.

---

### Task 3: Aggregation and Grouping (Medium) 📊

**Goal:** Use aggregate functions and `GROUP BY` to summarize data and find patterns.

**Scenario:** The library needs a report on its collection statistics.

1. **Count Books:** Write a query to find the total number of books in the `books` table.
2. **Find Oldest/Newest:** Use the `MIN()` and `MAX()` functions in a single query to find the publication year of the oldest and newest book in the collection.
3. **Group by Author:** Write a query that shows how many books each author has in the library. The result should show the author's name and their total count of books.
4. **Use `LIKE`:** Find all books where the title contains the word 'The'.
5. **Find Average:** Calculate the average `published_year` of all books in the 'Science Fiction' genre.

---

### Task 4: Working with Multiple Tables (Hard) 🔗

**Goal:** Learn to connect data from two separate tables using an `INNER JOIN`.

**Scenario:** The library wants to track which members have borrowed which books.

1. **Create a `members` Table:** Create a new table named `members` with the following columns:
* `member_id` (`INT`, `PRIMARY KEY`)
* `first_name` (`VARCHAR(50)`)
* `last_name` (`VARCHAR(50)`)
* `join_date` (`DATE`)
2. **Create a `borrowals` Table:** Create a third table named `borrowals` to link members and books. It should have:
* `borrow_id` (`INT`, `PRIMARY KEY`)
* `book_id` (`INT`, a `FOREIGN KEY` that references `books(book_id)`)
* `member_id` (`INT`, a `FOREIGN KEY` that references `members(member_id)`)
* `borrow_date` (`DATE`)
3. **Populate Data:** Insert a few members into the `members` table. Then, insert at least three records into the `borrowals` table, linking different members to different books.
4. **Join Tables:** Write a query using `INNER JOIN` that lists the first name of the member and the title of the book they have borrowed.

---

### Task 5: Complex Reporting (Very Hard) 🏆

**Goal:** Combine joins, aggregation, and filtering to create a detailed summary report.

**Scenario:** The library manager needs a report on member activity, but only for the most active members.

1. **The Challenge:** Write a single query that shows the full name of each member (use `CONCAT`) and the total number of books they have borrowed.
2. **Filter the Groups:** Use the `HAVING` clause to ensure your report only includes members who have borrowed **more than one book**.
3. **Sort the Final Report:** Order the results to show the member with the most borrowed books at the top.

**Your final report should have two columns:** `full_name` and `books_borrowed`.