--Subqueries.
---------------------------------------------------
--A subquery is a SQL query nested inside a larger query.

--A subquery may occur in :
--	- A SELECT clause
--	- A FROM clause
--	- A WHERE clause

--The subquery can be nested inside a SELECT, INSERT, UPDATE, 
--or DELETE statement or inside another subquery.
--A subquery is usually added within the WHERE Clause 
--of another SQL SELECT statement.
--You can use the comparison operators, such as >, <, or =. 
--The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
--A subquery is also called an inner query or inner select,
--while the statement containing a subquery is also called an outer query or outer select.
--The inner query executes first before its parent query so
--that the results of an inner query can be passed to the outer query.

--You can use a subquery in a SELECT, INSERT, DELETE, 
--or UPDATE statement to perform the following tasks:
--	-Compare an expression to the result of the query.
--	-Determine if an expression is included in the results of the query.
--	-Check whether the query selects any rows.

--The subquery (inner query) executes once before the main query (outer query) executes.
--The main query (outer query) use the subquery result.

--SYNTAX:
--	SELECT select_list
--	FROM table
--	WHERE expr operator (SELECT select_list
--			     FROM table)
--	;


--TASKS:
--BOOK_LIBRARY
--1. Who is oldest customer?
select min(birth_date) from book_library.customers
;

select first_name||' '||last_name, to_char(birth_date, 'yyyy.mm.dd')
from book_library.customers
where birth_date = (select min(birth_date)
                    from book_library.customers)
;
--2. Who are customers who did not borrowed anything?
select distinct customer_id from book_library.borrowing;

select *
from book_library.customers
where library_card_number not in (select customer_id from book_library.borrowing);
--3. What are title of the books which price is more than 
--the average price of the books which topic is Thriller, Literature or History?
select avg(price)
from book_library.books
where lower(topic) in ('thriller', 'literature', 'history');

select title, price
from book_library.books
where price > (select avg(price)
                from book_library.books
                where lower(topic) in ('thriller', 'literature', 'history'))
;
-- =
select title, price
from book_library.books
where price > 2477.590909090909090909090909090909090909;
--OLYMPICS
--4. How many athletes were born before Cseh László?
select birthdate from olympics.o_athletes where name='Cseh László';

select count(*)
from olympics.o_athletes
where birthdate < (select birthdate from olympics.o_athletes where name='Cseh László')
;
--5. Find those African countries that have more population than every European
--country.
select max(population) from olympics.o_countries where continent='Europe';

select *
from olympics.o_countries
where population > (select max(population) 
                    from olympics.o_countries 
                    where continent='Europe')
and continent='Africa';

--6. Which country has the most medals?
select max(gold+bronze+silver) from olympics.o_medal_table;

select country_id
from olympics.o_medal_table
where gold+silver+bronze = (select max(gold+bronze+silver) from olympics.o_medal_table);

select *
from olympics.o_countries
where c_id = (select country_id
                from olympics.o_medal_table
                where gold+silver+bronze = (select max(gold+bronze+silver) 
                                                from olympics.o_medal_table))
;
select *
from olympics.o_countries c join olympics.o_medal_table m on c.c_id=m.country_id
where gold+silver+bronze = (select max(gold+bronze+silver) 
                           from olympics.o_medal_table)
;
-- Top N analysis.
---------------------------------------------------------------
-- ROWNUM
--For each row returned by a query, the ROWNUM pseudocolumn 
--returns a number indicating the order in which Oracle selects 
--the row from a table or set of joined rows. 
--The first row selected has a ROWNUM of 1, the second has 2, and so on.

select title, rownum
from book_library.books
;

select title, rownum
from book_library.books
order by title;

select title, rownum
from book_library.books
where rownum<11
;

select title, rownum
from book_library.books
where rownum<11
order by title
;
--You can use ROWNUM to limit the number of rows returned by a query, as in this example:

--If an ORDER BY clause follows ROWNUM in the same query, 
--then the rows will be reordered by the ORDER BY clause. 

--If you embed the ORDER BY clause in a subquery and place 
--the ROWNUM condition in the top-level query, 
--then you can force the ROWNUM condition to be applied after the ordering of the rows.

-- BOOK_LIBRARY
-- List the first 5 customer’s name in alphabetical order.
select first_name, last_name
from book_library.customers
order by first_name, last_name
;

select *
from (select first_name, last_name
        from book_library.customers
        order by first_name, last_name)
where rownum<11;
-------------        
--FETCH FIRST n ROWS ONLY: 

--SYNTAX:
--SELECT select_list 
--FROM table_name 
--FETCH FIRST n ROWS ONLY;       (where n is a number)

select first_name, last_name
from book_library.customers
order by first_name, last_name
fetch first 10 rows only;
------------------------------------------------------------------------------------
--FETCH FIRST n ROWS WITH TIES: 
--handling duplicates. 
--WITH TIES is therefore important if you want to include all 
--rows of a certain kind – without stopping at a fixed number of rows.
select title, price
from book_library.books
order by price
fetch first 5 rows only;

select title, price
from book_library.books
order by price
fetch first 5 rows with ties;
--SYNTAX
--SELECT select_list 
--FROM table_name 
--FETCH FIRST n ROWS WITH TIES;       (where n is a number)

--EXAMPLES:
--List the first 10 author (only author_id and royalty) with the largest royalty.
select *
from book_library.writing
order by royalty desc nulls last
fetch first 5 rows only;
--List the first 5 book (only book_id and price) with the largest price.

--List the book which has the highest price

-----------------------------------------
--OFFSET n ROWS FETCH NEXT m ROWS WITH TIES
-- n - number
-- m - number

--List the authors by royalty ascending,  and list from it the second 5 authors.
select *
from book_library.writing
order by royalty asc
offset 5 rows fetch next 5 rows only;
----------------------------------------------------------------------------------
--PRACTICE:

--OLYMPICS
--1. List the names and birthdate of each athlete who was born before 1980.01.01
--   to_date('1980.01.01', 'yyyy.mm.dd')
select birthdate, name
from olympics.o_athletes
where birthdate < to_date('1980.01.01', 'yyyy.mm.dd');
--2. Select the names and the population densities (population/area) of each country.
select name, population/area "Population density"
from olympics.o_countries;
--3. List all European countries.
select *
from olympics.o_countries
where continent='Europe';
--4. List the countries whose population is higher than 1000000.
select *
from olympics.o_countries
where population>1000000;
--5. List the country names between Germany and Hungary.
--Remark: 'between x and y' can be used for character sequences too!
select name
from olympics.o_countries
where name between 'Germany' and 'Hungary';
--6. List all countries except for Hungary, Germany and China
select *
from olympics.o_countries
where lower(name) not in ('hungary', 'germany', 'china');
--7. List the countries that have no capital.
select * from olympics.o_countries where capital is null;
--8. Select the countries whose name has a double 'r' in it.
-- rr
select * from olympics.o_countries 
where lower(name) like '%rr%';
--9. List those cities that have more than 5 athletes in alphabetical order.
-- Remark: In the where you cannot place group functions!
select birthplace
from olympics.o_athletes 
group by birthplace
having count(*)>5
;
--10. How many countries have higher population density than 10?
select *
from olympics.o_countries
where population/area>10;
--------------------------------------------------------------------------------
--BOOK_LIBRARY
--1. Select the authors’ full names in one column 
select first_name||' '||last_name 
from book_library.authors;
--2. List the books which (topic is Science Fiction or Literature) or
--which (price is between 1000 and 4000) 
--and (which number of pages is given).
--Sort the list by topic descending and number of pages ascending.
select *
from book_library.books
where lower(topic) in ('science fiction', 'literature') or price between 1000 and 4000
and number_of_pages is not null
order by topic desc, number_of_pages asc;
--3. What are the minimum price, the maximum price, the sum of
--the prices and the average of the prices of books for each publisher?
select min(price), max(price), avg(price), sum(price)
from book_library.books
group by publisher
;
--4. List the titles of the books and the name of their 
-- authors of which were born earlier than 1940.
select title, first_name||' '||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 to_char(birth_date, 'yyyy')< 1940;
--5. How many authors are there in the author table whose birth
--date is not given?
select count(*)
from book_library.authors
where birth_date is null;
--6. List the title of the books which title contain at least two ’e’
--characters (case-insensitive) or which second letter is ’o’. Sort the list by
--title.

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

--8. List the authors(name, birth_date, age) who are older than 100 years;

--9. List the authors (name, birth_date)
--who was born in May or July or November.
--Order the list by month desc and the name of the author;

--10. List the customers (name, birth_date, category)
--who were born between 1980 and 1999
--and whose category is adult or supporter
--or whose name contains puntually two 'e' (lowercase or uppercase).
--List should be sorted by category,and birth_date desc;

--11. List the books which are under 30 years 
--and which topic is not given. 
                 
-------------------------------------------

--OLYMPICS DATABASE:

--1. How many countries have higher population density than 2? 
--   (pop.density=population divided by area)

--2. List the athletes' names, birthplaces and the countries they come from.

--3. List the names of the countries and the numbers of medals (gold,silver,bronze)
--   they earned.

--4. List the Asian and the European countries.			

--5. Select the countries whose name has a 'g' in it.

--6. Select those individual athletes who were born after 1990.02.01.

--7. List the average population for each continent.

--8. How many countries are there in each continent? List only those continents
--where this value is more than 20. Do not display the continents that have NULL
--value.

--9. List the athletes' names, birthplaces and the countries they come from.

--10. Display only those where this value is more than 1!

--11.  List those European athletes who earned a medal.

--12. Which countries have higher population than that of Hungary?

--13. List those countries that are not in the medal table.