--JOIN:
-------
--(We use the join operator when we need data from multiple tables.)

--A JOIN clause is used to combine rows from two or more tables, 
--based on a related column between them.

--A JOIN is a query that combines rows from two or more tables, views, or materialized
--views. Oracle Database performs a join whenever multiple tables appear in the FROM
--clause of the query. The select list of the query can select any columns from any of
--these tables. If any two of these tables have a column name in common, then you
--must qualify all references to these columns throughout the query with table names to
--avoid ambiguity.

--join condition:   column1=column2
--Most join queries contain at least one JOIN CONDITION, either in the FROM clause or in the WHERE
--clause. The join condition compares two columns, each from a different table. To execute a
--join, Oracle Database combines pairs of rows, each containing one row from each table, for
--which the join condition evaluates to TRUE. The columns in the join conditions need not also
--appear in the select list.
--To execute a join of three or more tables, Oracle first joins two of the tables based on the join
--conditions comparing their columns and then joins the result to another table based on join
--conditions containing columns of the joined tables and the new table. Oracle continues this
--process until all tables are joined into the result. The optimizer determines the order in which
--Oracle joins tables based on the join conditions, indexes on the tables, and, any available
--statistics for the tables.

--Here are the different types of the JOINs in SQL:

--An inner join (sometimes called a simple join) is a join of two or more tables that
--returns only those rows that satisfy the join condition.


--An OUTER JOIN extends the result of a simple join. An outer join returns all rows that
--satisfy the join condition and also returns some or all of those rows from one table for
--which no rows from the other satisfy the join condition.
--   - To write a query that performs an outer join of tables A and B and returns all rows
--     from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or
--     apply the outer join operator (+) to all columns of B in the join condition in the
--     WHERE clause. For all rows in A that have no matching rows in B, Oracle Database
--     returns null for any select list expressions containing columns of B.
--   - To write a query that performs an outer join of tables A and B and returns all rows
--     from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause,
--     or apply the outer join operator (+) to all columns of A in the join condition in the WHERE
--     clause. For all rows in B that have no matching rows in A, Oracle returns null for any
--     select list expressions containing columns of A.
--   - To write a query that performs an outer join and returns all rows from A and B, extended
--     with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER]
--     JOIN syntax in the FROM clause.

--In short:
-- - INNER JOIN: Returns records that have matching values in both tables
-- - LEFT OUTER JOIN: Returns all records from the left table, 
--    and the matched records from the right table
-- - RIGHT OUTER JOIN: Returns all records from the right table, 
--    and the matched records from the left table
-- - FULL OUTER JOIN: Returns all records when there is a match
--   in either left or right table


--OLYMPICS DATABASE:

--INNER JOIN
--Join the o_countries and o_medal_table_ tables.
select *
from olympics.o_countries country inner join olympics.o_medal_table medal
on c_id=country_id;

select *
from olympics.o_countries country inner join olympics.o_medal_table medal
on country.c_id=medal.country_id;

select *
from olympics.o_countries country join olympics.o_medal_table medal
on country.c_id=medal.country_id;

select *
from olympics.o_countries country left join olympics.o_medal_table medal
on country.c_id=medal.country_id;

select *
from olympics.o_countries country right join olympics.o_medal_table medal
on country.c_id=medal.country_id;

select *
from olympics.o_countries country full join olympics.o_medal_table medal
on country.c_id=medal.country_id;




select *
from olympics.o_countries country inner join olympics.o_medal_table medal
on country.c_id=medal.country_id
inner join olympics.o_athletes athlete on  athlete.country_id=country.c_id;

select *
from olympics.o_countries c inner join olympics.o_medal_table m
on c.c_id=m.country_id
inner join olympics.o_athletes a on  a.country_id=c.c_id;

select *
from olympics.o_countries inner join olympics.o_medal_table 
on c_id=olympics.o_medal_table.country_id
inner join olympics.o_athletes  on  olympics.o_athletes.country_id=c_id;

--Join the o_sport_disciplines and o_sport_events tables.
select *
from olympics.o_sport_events event inner join olympics.o_sport_disciplines disc
on event.discipline_id=disc.d_id;
-------------------------------------------------------------------------
--LEFT/RIGHT JOIN

--Join the o_sport_disciplines and o_sport_events tables.
-------------------------------------------------------
--NATURAL JOIN
--Join the o_teammembers and o_results tables.
select *
from olympics.o_countries c, olympics.o_medal_table m
where c.c_id=m.country_id; 
---------------------------------------------------------
--Practicing exercises

--1. List the athletes' names, birthplaces and the countries they come from.
select athlete.name, athlete.birthplace, country.name country_name
from olympics.o_athletes athlete inner join olympics.o_countries country
on athlete.country_id=country.c_id;

--2. List the names of the countries and the numbers of medals (gold,silver,bronze)
--they earned.
select country.name, medal.gold, medal.silver, medal.bronze
from olympics.o_countries country 
inner join olympics.o_medal_table medal on c_id=country_id;

select c.name, nvl(m.gold,0), nvl(m.silver,0), nvl(m.bronze,0)
from olympics.o_countries c left join olympics.o_medal_table m on c.c_id=m.country_id
;
--3. Return the name of each continent with the amount of gained gold,
--silver and bronze medals.
select c.continent, sum(m.gold), sum(m.silver), sum(m.bronze)
from olympics.o_countries c inner join olympics.o_medal_table m on c.c_id=m.country_id
group by c.continent
;

--4. Return the name of each European country and the number of disctinct medals. 
--Order the rows  by the number of gold medals, then by the number of silver medals, 
--and the number of bronze models. 
--Use descending order in all stages.
select distinct c.name, m.gold, m.silver, m.bronze
from olympics.o_countries c left join olympics.o_medal_table m on c.c_id=m.country_id
where continent='Europe'
order by 2 desc, 3 desc, 4 desc
;
--5. Return the name of each discipline and the number of corresponding events.
--Order the rows by the discipline names.
select dis.name, count(*)
from olympics.o_sport_disciplines dis left join olympics.o_sport_events event 
on dis.d_id=event.discipline_id
group by dis.d_id, dis.name
order by dis.name
;

--6. Return each continent and its worst and best places, if (and only if) they differ.
select country.continent, min(result.place), max(result.place)
from olympics.o_Results result inner join olympics.o_athletes athl 
on athl.a_id=result.athlete_id
right join olympics.o_countries country on country.c_id=athl.country_id
group by country.continent
having min(result.place)<>max(result.place)
;
--7. Return each discipline and the number of male and female events.
select disc.name, event.gender, count(*)
from olympics.o_sport_disciplines disc left join olympics.o_sport_events event
on disc.d_id=event.discipline_id
group by disc.name, event.gender;
--8. Return each discipline for which we know at least 1 result. Return each name only once.
select distinct disc.*
from olympics.o_sport_disciplines disc left join olympics.o_sport_events event
on disc.d_id=event.discipline_id inner join olympics.o_results res
on event.e_id=res.event_id;




--------------------------------------------
--Example: 
select  *
from book_library.books books inner join book_library.writing w
    on books.book_id=w.book_id
    ;

--1. What books (only book id) did Christie Agatha write?
select w.book_id
from book_library.writing w inner join book_library.authors a on w.author_id=a.author_id
where a.last_name='Christie' and a.first_name='Agatha';

-- The SELECT DISTINCT statement is used to return only distinct (different) values.

--2. How many authors did write the books which topic is history,
--   science, or literature. One author should be counted only one time.
select count(distinct w.author_id)
from book_library.writing w inner join book_library.books b on w.book_id=b.book_id
where topic in ('History', 'Science', 'Literature');

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

select borr.customer_id, count(distinct borr.book_item_id), customer.first_name, customer.last_name
from book_library.borrowing borr right join book_library.customers customer
    on borr.customer_id=customer.library_card_number
group by borr.customer_id, customer.first_name, customer.last_name
;

--4. List all books (with id and title) and if they have book items
--   put their book item id-s next to the title.
select book.book_id, book.title, item.book_item_id
from book_library.books book left join book_library.book_items item on book.book_id=item.book_id
;
--5. Which category has more than 3 customers?
select category, count(library_card_number)
from book_library.customers
group by category;

--6. Which books have more than 3 writers?
select b.title, count(author_id)
from book_library.writing w inner join book_library.books b on w.book_id=b.book_id
group by w.book_id, b.title; 

--7. List the authors who got at least 2 times more than 150000 royalty.
--   The list should be order by author name.


--8. How many book items do belong to each book (title, book_id)?