--SELECT column_name(s)
--FROM table_name
--[WHERE condition]
--[GROUP BY column_name(s)]
--[HAVING condition]
--[ORDER BY column_name(s)];

--concatenation

--desc, asc, nulls last, nulls first

--conditions in WHERE clause:
--	<, >, =, <>, !=
--	between X and y
--	in, not in
--	is null, is not null

-- LIKE  _ - one character,  % - zero or more character

-- AND
-- OR

--Functions:
--	round()
--	trunc()
--	substr()
--	instr()
--	length()
--	to_char()
--	to_date()
--	month_between()
--	extract(... from ...)
--	lower()
--	upper()
--	replace()
--	nvl()

--	max()
--	min()
--	count()
--	avg()
--	sum()

-- sysdate  - current system date and time

-- subquery ! 

-- The different set operators are:
--	UNION
--	UNION ALL
--	MINUS
--	INTERSECT

-- ROWNUM	

-- INNER JOIN
-- LEFT OUTER JOIN
-- RIGHT OUTER JOIN 
-- FULL OUTER JOIN

-- FETCH FIRST n ROWS ONLY
-- FETCH FIRST n ROWS WITH TIES
-- OFFSET n ROWS FETCH NEXT m ROWS WITH TIES

-- DISTINCT

----------------------------------------------------------------------------
--1. List the title of the books which author is Erich Gamma
select title 
from book_library.books books inner join book_library.writing writing on books.book_id=writing.book_id
inner join book_library.authors auth on writing.author_id=auth.author_id
where first_name='Erich' and last_name='Gamma';

--2. Which books (title) were borrowed more than 2 times?
select title 
from book_library.books books inner join book_library.book_items bitems on books.book_id=bitems.book_id
inner join BOOK_LIBRARY.borrowing borr on bitems.book_item_id=borr.book_item_id
group by title
having count(borrowing_date)>2;

--3. Who is the oldest customer?
select FIRST_name||' '||last_name, to_char(birth_date, 'yyyy.mm.dd')
from BOOK_LIBRARY.customers
order by birth_date nulls LAST
fetch first rows with ties;

--4. Select the book items which theoretical value is less than the
--price of the related book.
select bitems.book_item_id --, price, bitems.theoretical_value
from BOOK_LIBRARY.books books inner join BOOK_LIBRARY.book_items bitems on books.book_id=bitems.book_id
where bitems.theoretical_value<books.price
;

--5. Select the last borrowing.
select *
from BOOK_LIBRARY.borrowing
where borrowing_date = (select max(borrowing_date) from BOOK_LIBRARY.borrowing)
;

--6. Number of books that were published between 1990 and 2000, or
--whose price is between 1000 and 3000, or
--whose topic is 'Science Fiction' and are shorter than 20 pages
select count(*)
from BOOK_LIBRARY.books
where extract( year from publishing_date) between 1990 and 2000
OR
price between 1000 and 3000
OR
topic in ('Science Fiction') AND number_of_pages<20
;

--7. List only those years when more than one customer was born
select extract(year from birth_date)
from BOOK_LIBRARY.customers
group by extract(year from birth_date)
having count(*) > 1
;

--8. Select full name of student customers having currently some books 
--borrowed (i.e., bring-back date is unknown), together with the titles
--of the borrowed books and the book 
--item IDs
select customer.first_name||' '||customer.last_name, books.title, bitems.book_item_id
from BOOK_LIBRARY.books books 
inner join BOOK_LIBRARY.book_items bitems on books.book_id=bitems.book_id
inner join book_library.borrowing borr on borr.book_item_id=bitems.book_item_id
inner join book_library.customers customer on customer.library_card_number=borr.customer_id
where customer.category='student' AND borr.bring_back_date is null
;
--9. List the books which got more than 2000 royalty for the writing,
--or which has more than two book_items.

select title, book_id
from BOOK_LIBRARY.books
where book_id in (select book_id
                    from BOOK_LIBRARY.writing
                    where royalty>2000)
OR book_id in (select book_id
            from BOOK_LIBRARY.book_items
            group by book_id
            having count(book_item_id)>2)
;

--10. In the thriller topic which book has the lowest price?

 select *
 from BOOK_LIBRARY.books
 where topic ='Thriller'
 order by price
 fetch first 1 rows with ties;
 
 select *
 from BOOK_LIBRARY.books
 where price = (select min(price) from BOOK_LIBRARY.books where topic='Thriller')
and topic='Thriller';

-- 11. What is the sum of theoretical value of the book items for each
-- book id?
select sum(theoretical_value), book_id
from book_library.book_items
group by book_id
;

-- 12. List the topics which have less then 6 books.
select topic, count(*)
from book_library.books
group by topic
having count(*)<6
;


-- 13. List the categories of the customers where the average membership fee is less than 
-- 3000.
select avg(membership_fee), category
from book_library.customers
group by category
having avg(membership_fee)<3000;

--14. Create a view which lists for each books (id, title)
--   how many times was it borrowed.
create view v6 as
select bo.book_id, title, count(borrowing_date) nu
from book_library.books bo left outer join
book_library.book_items bi on bo.book_id=bi.book_id
left outer join book_library.borrowing bw
on bw.book_item_id=bi.book_item_id
group by bo.book_id, title;

--15. How many book items was borrowed by each customer (name)?
select count(borr.book_item_id), customers.first_name, customers.last_name
from book_library.customers customers inner join book_library.borrowing borr 
    on customers.library_card_number=borr.customer_id
group by customers.library_card_number, customers.first_name, customers.last_name

--16. List the books which title contains exactly two 'c'

--17. List the books which price is between 2000 and 5000.

--18. What are title of the books which price is less than 
--the average price of the books which topic is Thriller,
--or History?

select title
from BOOK_LIBRARY.books
where price < (select avg(price)
                from BOOK_LIBRARY.books
                where topic in ('History', 'Thriller'))
;


--19. How many books belongs to each publisher?
select count(*), publisher
from book_library.books
group by publisher;

--20. List the second 5 book items which has largest theoretical value
select * 
from book_library.book_items
order by theoretical_value desc nulls last
OFFSET 5 ROWS FETCH NEXT 5 ROWS WITH TIES;