Week 5 exercises

Lab Questions:

1. Get a list of all books, including the author's names (duplicate books are okay, if the book has multiple authors).
2. Get a list of all books withdrawn by people with the initials 'B.W.'. Show a column for the first name, last name, initials, and the title of the book. Remember: keep things concise.
3. Get the number of books written by each American author. Include the first and last names of the author. Note that America might be represented in the 'country' column in more than one way - always check your results table to make sure you're getting the expected results.
4. For any book withdrawn in October, get the member's first name, last name, the withdrawal date and the book's title. Hint: Try getting the month from a date using the scalar function MONTH()
5. Get a list of people who have ever returned overdue books (i.e. any withdrawal where the return date is greater than the due date). Remember that, unless otherwise specified, lists shouldn't contain duplicate rows.
6. Get a list of all authors, and the books they wrote. Include authors multiple times if they wrote multiple books. Also include authors who don't have any books in the database. Hint: this is a tricky one - think back on why right joins exist in the first place.
7. Get a list of members who've never withdrawn a book. Hint: outer joins return rows with null values if there is no match between the tables.
8. Rewrite the above query as the opposite join (if you used a left join, rewrite it as a right join; if you used a right join, rewrite it as a left join). The results table should contain the same data.
9. Cross join books and authors. Isn't that ridiculous?
10. Get a list of all members who have the same first name as other members. Sort it by first name so you can verify the data.