select count(t.title_id),count(tc.copy_id),t.category
from title t join title_copy tc on(t.title_id=tc.title_id) 
    join rental r on (r.title_id=tc.title_id) and (tc.copy_id=r.copy_id)
group by t.category
having count(*)=
(Select max(count(*))
from title t join title_copy tc on (t.title_id=tc.title_id)
join rental r on(r.copy_id=tc.copy_id) and(r.title_id=tc.title_id)
group by t.category);


select t.title, count(tc.copy_id)
from title t join title_copy tc on (t.title_id=tc.title_id)
where (t.title_id,tc.copy_id)  not in(
select title_id,copy_id
from rental 
where act_ret_date is null
)
group by t.title;


select t.title_id, tc.copy_id, status "status setat",
case 
    when (t.title_id, tc.copy_id) in (select title_id, copy_id
                                        from rental
                                        where act_ret_date is null)
    then 'rented'
     when(t.title_id, tc.copy_id) in (select title_id,copy_id
                                        from title_copy
                                        minus
                                        select title_id, copy_id
                                        from rental
                                        where act_ret_date is null)
     then 'Available'
     end "status corect"
from title t join title_copy tc on (t.title_id=tc.title_id);