Created
April 22, 2020 14:54
-
-
Save jessicacarneiro/c0b386fc45d04813f487908eb0578f90 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT "Who checked out the book 'The Hobbit’?"; | |
| SELECT DISTINCT member.name FROM member WHERE member.id | |
| IN (SELECT checkout_item.member_id | |
| FROM checkout_item WHERE checkout_item.book_id | |
| IN (SELECT id FROM book WHERE title = "The Hobbit") | |
| ); | |
| SELECT "How many people have not checked out anything?"; | |
| SELECT COUNT(DISTINCT member.id) | |
| FROM member, checkout_item | |
| WHERE checkout_item.member_id != member.id; | |
| SELECT "What books and movies aren't checked out?"; | |
| SELECT DISTINCT book.title FROM book, checkout_item WHERE book.id | |
| IN (SELECT book_id FROM checkout_item WHERE book_id != 0); | |
| SELECT DISTINCT movie.title FROM movie, checkout_item WHERE movie.id | |
| IN (SELECT movie_id FROM checkout_item WHERE movie_id != 0); | |
| SELECT "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."; | |
| SELECT @LastBookId = COUNT(book.id) FROM book; | |
| SELECT @LastMemberId = COUNT(member.id) FROM member; | |
| INSERT INTO book VALUES (@LastBookId + 1, "The Pragmatic Programmer"); | |
| INSERT INTO member VALUES (@LastMemberId + 1, "Jessica Carneiro"); | |
| INSERT INTO checkout_item VALUES (@LastBookId + 1, @LastMemberId + 1, NULL); | |
| SELECT DISTINCT member.name FROM member WHERE member.id | |
| IN (SELECT checkout_item.member_id | |
| FROM checkout_item WHERE checkout_item.book_id | |
| IN (SELECT id FROM book WHERE title = "The Pragmatic Programmer") | |
| ); | |
| SELECT "Who has checked out more than 1 item?"; | |
| SELECT member.name, COUNT(checkout_item.member_id) | |
| FROM member, checkout_item | |
| WHERE member.id = checkout_item.member_id | |
| GROUP BY (checkout_item.member_id) | |
| ORDER BY COUNT(checkout_item.member_id) DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment