tw_sql_problems.md

1. Who checked out the book 'The Hobbit’?

Query:

SELECT name FROM member
JOIN checkout_item on checkout_item.member_id = member.id
JOIN book on checkout_item.book_id = book.id
WHERE book.title = "The Hobbit";

Result:

name
----------
Anand Beck

2. How many people have not checked out anything?

Query:

SELECT COUNT(*) FROM member
LEFT JOIN checkout_item on checkout_item.member_id = member.id
WHERE member_id is null;

Result:

COUNT(*)
----------
37

3. What books and movies aren't checked out?

Movie query:

SELECT title FROM movie
LEFT JOIN checkout_item on movie.id = checkout_item.movie_id
WHERE movie_id is null;

Result:

title
-------------
Thin Red Line
Crouching Tig
Lawrence of A
Office Space

Book query:

SELECT title FROM book
LEFT JOIN checkout_item on book.id = checkout_item.book_id
WHERE book_id is null;

Result:

title
----------------------
Fellowship of the Ring
1984
Tom Sawyer
Catcher in the Rye
To Kill a Mockingbird
Domain Driven Design

4. Add the book 'The Pragmatic Programmer', and add yourself as a member. Check out 'The Pragmatic Programmer'. Use your query from question 1 to verify that you have checked it out. Also, provide the SQL used to update the database.

Add 'The Pragmatic Programmer' to the database:

INSERT_INTO book (title)
VALUES (The Pragmatic Programmer);

Add myself to the database:

INSERT INTO member (name)
VALUES ('Brian Sayler');

Checkout the book:

INSERT INTO checkout_item (member_id, book_id)
VALUES (43, 11);

5. Who has checked out more that 1 item?

Anand Beck and Frank Smith

SELECT *, COUNT(*) AS total
FROM member
JOIN checkout_item on checkout_item.member_id = member.id
GROUP BY member.name;

id          name                            member_id   book_id     movie_id    total
----------  ------------------------------  ----------  ----------  ----------  ----------
1           Anand Beck                      1           5                       3
43          Brian Sayler                    43          11                      1
6           Frank Smith                     6           1                       3
8           Julian Listov                   8                       4           1
7           Sneha Carmack                   7                       2           1
2           Yaxuan Evans                    2           4                       1