tuliooassis/twu-biblioteca-tulioassis

Biblioteca Assignment

ThoughtWorks University

SQL Database Queries

Who checked out the book 'The Hobbit'?

select member.name
from checkout_item
join book on book.id = checkout_item.book_id 
join member on member.id = checkout_item.member_id
where book.title = 'The Hobbit';

How many people have not checked out anything?

select count(member.id)
from member 
where  member.id not in (
	select checkout_item.member_id
	from checkout_item
);

or

select count(member.id)
from member
left join checkout_item on member.id = checkout_item.member_id
where checkout_item.book_id is null and checkout_item.movie_id is null;

What books and movies are not checked out?

select book.title
from book
left join checkout_item on checkout_item.book_id = book.id
where checkout_item.member_id is null

union 

select movie.title
from movie
left join checkout_item on checkout_item.movie_id = movie.id
where checkout_item.member_id is null;

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.

insert into book (title) values ('The Pragmatic Programmer');

insert into member (name) values ('Tulio Assis');

insert into checkout_item (member_id, book_id) values (43, 11);

select member.name
from checkout_item
join book on checkout_item.book_id = book.id 
join member on member.id = checkout_item.member_id
where book.title = 'The Pragmatic Programmer';