--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 b.title --, a.first_name, a.last_name 
from book_library.books b inner join book_library.writing w 
on b.book_id=w.book_id
inner join book_library.authors a on w.author_id=a.author_id
where a.first_name='Erich' and a.last_name='Gamma'
;
--2. Which books (title) were borrowed more than 2 times?
select title, count(borrowing_date), isbn
from book_library.books b inner join book_library.book_items i on b.book_id=i.book_id
inner join book_library.borrowing o on i.book_item_id=o.book_item_id
group by title, isbn
having count(borrowing_date)>2;

--3. Who is the oldest customer?
select *
from book_library.customers
order by birth_date 
fetch first 1 rows with ties
;

select *
from book_library.customers
order by birth_date desc nulls last
fetch first 1 rows with ties
;
--4. Select the book items which theoretical value is less than the
--   price of the related book.
select price from book_library.books;

select * --book_item_id
from book_library.book_items item
where theoretical_value < (select price from book_library.books book
                            where item.book_id=book.book_id)
;
                            
select item.*
from book_library.books book inner join book_library.book_items item 
on book.book_id=item.book_id
where book.price> item.theoretical_value
;
--5. Select the last borrowing.
select *
from book_library.borrowing
order by borrowing_date desc nulls last
fetch first 1 rows with ties
;

select max(borrowing_date) from book_library.borrowing;

select *
from book_library.borrowing
where borrowing_date= (select max(borrowing_date) from book_library.borrowing)
;

--select *
--from (select * from book_library.borrowing order by borrowing_date desc nulls last)
--where rownum=1;

--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 *
from book_library.books
where publishing_date between to_date('1990.01.01', 'yyyy.mm.dd') and to_date('2000.01.01', 'yyyy.mm.dd')
or 
price between 1000 and 3000
or
lower(topic)='science fiction'
and number_of_pages=20
;


select *
from book_library.books
where extract(year from publishing_date) between 1990 and 2000 
or 
price between 1000 and 3000
or
lower(topic)='science fiction'
and number_of_pages=20
;

select *
from book_library.books
where to_char(publishing_date, 'yyyy') between 1990 and 2000 
or 
price between 1000 and 3000
or
lower(topic)='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)--, count(*)
from book_library.customers
group by extract(year from birth_date)  --to_char(birth_Date, 'yyyy')
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

--9. List the books which got more than 2000 royalty for the writing,
--   or which has more than two book_items.

--10. In the thriller topic which book has the lowest price?
select *
from book_library.books
where price =(select min(price) from book_library.books where lower(topic)='thriller')
and lower(topic)='thriller'
;

select *
from book_library.books
where price =(select min(price) from book_library.books where lower(topic)='history')
and lower(topic)='history'
;

select *
from book_library.books
where price =(select min(price) from book_library.books where lower(topic)='history')
and lower(topic)='history'
;
--11. What is the sum of theoretical value of the book items for each
--    book id?

--12. List the topics which have less then 6 books.

--13. List the categories of the customers where the average
--membership fee is less than 3000.
select category--, avg(membership_fee)
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.

--15. How many book items was borrowed by each customer (name)?

--16. List the books which title contains exactly two 'c'
select *
from book_library.books
where lower(title) like '%c%c%' and lower(title) NOT like '%c%c%c%';

select *
from book_library.books
where title like '%c%c%' and title NOT like '%c%c%c%';



--17. List the books which price is between 2000 and 5000.
select *
from book_library.books
where price between 2000 and 5000    -- price>=2000 and price <=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?

--19. How many books belongs to each publisher?

--20. List the second 5 book items which has largest theoretical value