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


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

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