sql.txt

1.
select name from member,book,checkout_item where checkout_item.member_id=member.id and checkout_item.book_id=book.id and book.title='The Hobbit’;
Output :: Anand Beck

2.

select distinct count(*)
from member
where id not in(select distinct member_id
from checkout_item);
Output:37

3.
select distinct title
from book
where id not in(select distinct book_id
from checkout_item
where book_id is not null)
union
select distinct title
from movie
where id not in(select distinct movie_id
from checkout_item
where movie_id is not null);
Output:1984

Catcher in the Rye

Crouching Tiger, Hidden Dragon

Domain Driven Design

Fellowship of the Ring

Lawrence of Arabia

Office Space

Thin Red Line

To Kill a Mockingbird

Tom Sawyer



4.

insert into book(id,title) values(11,'The Pragmatic Programmer');
insert into member(id,name) values(43,'Sai Sree');
insert into checkout_item(member_id,book_id,movie_id) values(43,11,null);
select name from member,book,checkout_item where checkout_item.member_id=member.id and checkout_item.book_id=book.id and book.title='The Pragmatic Programmer'; 
Output: Sai Sree

5.
select name
from member,checkout_item
where checkout_item.member_id=member.id
GROUP BY (checkout_item.member_id) having count(checkout_item.member_id)>1;
output:Anand Beck

Frank Smith