--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:
--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.


--Join the o_sport_disciplines and o_sport_events tables.

-------------------------------------------------------------------------
--LEFT/RIGHT JOIN


--Join the o_sport_disciplines and o_sport_events tables.



-------------------------------------------------------
--NATURAL JOIN
--Join the o_teammembers and o_results tables.


---------------------------------------------------------
--Practicing exercises

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

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

--3. Return the name of each continent with the amount of gained gold, silver and bronze medals.

--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.

--5. Return the name of each discipline and the number of corresponding events. Order the rows by the discipline names.

--6. Return each continent and its worst and best places, if (and only if) they differ.

--7. Return each discipline and the number of male and female events.

--8. Return each discipline for which we know at least 1 result. Return each name only once.




--------------------------------------------
--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?


-- 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.


--3. How many book items was borrowed by each customer (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.

--5. Which category has more than 3 customers?


--6. Which books have more than 3 writers?


--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)?