--1. Return the data of each event that is organized for women.
select *
from olympics.o_sport_events
where lower(gender)='women'
;
--2. Return the ID and the number of medals of each country
--which has at least 1 gold medal.

-- gold <>0 
-- gold != 0
-- gold > 0
-- gold >= 1

select *
from olympics.o_medal_table
where gold>=1
;

--3. Return the ID and the number of medals of each country 
--which gained exactly the same number of gold and silver medals.
select *
from olympics.o_medal_Table
where gold=silver
;
--4. Return the ID and the number of medals of each country which 
--gained exactly the same number of gold, silver and bronze medals.
select *
from olympics.o_medal_Table
where gold=silver and gold=bronze
;
--5. Return the ID and the number of medals of each country which 
--has not gained any gold nor any silver medals.
select * 
from olympics.o_medal_table
where gold=0 and silver=0
;
--6. Return the ID and the number of medals of each country which 
--has not gained any gold nor any silver medals but gained bronze medals.
select *
from olympics.o_medal_table
where gold=0 and silver=0 and bronze!=0
;
--7. Return the ID and the number of medals of each country which
--has zero gold or zero silver medal while gained at least 1 bronze medal.
select *
from olympics.o_medal_table
where (gold=0 or silver=0) and bronze!=0
;
--8. Return the ID and the number of medals of each country which 
--has gained more than 10 gold and silver medals (total).
select country_id, gold+silver as "Total medal"
from olympics.o_medal_table
where gold+silver > 10
;
--9. Return the ID and the number of medals of each country which
--has gained more than 10 gold and silver medals (total). Name the number of medals as total.
select country_id, gold+silver as "Total medal"
from olympics.o_medal_table
where gold+silver > 10
;


select first_name, last_name, first_name||' '||last_name as "Name"
from book_library.customers;

--BETWEEN

--10. Return the ID and the number of medals of each country which number of gold medals is between 5 and 9.
select *
from olympics.o_medal_table
where gold>=5 and  gold<=9
;
--or
select *
from olympics.o_medal_table
where gold between 5 and 9 
;
--11. Return the ID and the number of medals of each country which number 
--of gold medals is between the number of bronze and silver medals.
select *
from olympics.o_medal_table
where gold between bronze and silver
;

-- <>
select *
from olympics.o_medal_table
where gold between silver and bronze
;
--12. Return all the data of each event which name is missing.
select *
from olympics.o_sport_events
where name is null
;

--13. Return each gender for which an event is scheduled with a missing name.
select gender
from olympics.o_sport_events
where name is null
;
--14. Return each gender for which an event is scheduled with a missing name. Return each gender only once.
select distinct gender
from olympics.o_sport_events
where name is null
;
?-----------
--Pattern matching
--The LIKE operator is used in a WHERE clause to search for
--a specified pattern in a column.

--There are two wildcards often used in conjunction with the LIKE operator:
--     The percent sign (%) represents zero, one, or multiple characters
--     The underscore sign (_) represents one, single character

--1. Return each country.
select name
from olympics.o_countries
;
--2. Return the country which name matches Albania.
select name
from olympics.o_countries
where name='Albania'
;
--3. Return the country which name matches albania.
select name
from olympics.o_countries
where name='albania'
;
--4. Return each country which name starts with 'A'.
select name
from olympics.o_countries
where lower(name) like 'a%'
;
--5. Return each country which name starts with 'A', continues with a custom character, then ends with 'gola'.
select name
from olympics.o_countries
where lower(name) like 'a_gola'
;
--6. Return each country which name ends with 'istan'.
select name
from olympics.o_countries
where lower(name) like '%istan'
;
--7. Return each country which name starts with 'A' and ends with 'istan'.
select name
from olympics.o_countries
where lower(name) like 'a%istan'
;
--8. Return each country which name starts with 'A', ends with 'a' and exactly 5 letters long.
select name
from olympics.o_countries
where lower(name) like 'a___a'
;
--9. Return each country which name starts with 'A' and at least 8 letters long.
select name
from olympics.o_countries
where lower(name) like 'a_______%'
;
--  =
select name, length(name)
from olympics.o_countries
where lower(name) like 'a%' and length(name)>7
;
--10. Return each country which name starts with 'Aust' and ends with 'ia'.
select name
from olympics.o_countries
where lower(name) like  'aust%ia'
;
--11. Return each country which name starts with 'Aust' or ends with 'ia'.
select name
from olympics.o_countries
where lower(name) like 'aust%' OR lower(name) like '%ia'
;
--12. Return each country which area starts with '3'.
select name, area
from olympics.o_countries
where area like '3%' 
;
-----------
--Sorting
--Basic exercises
--1. Return the ID and the number of medals of each country. 
--Sort the countries by the number of gold medals.

--2. Return the ID and the number of medals of each country. 
--Sort the countries by the number of gold medals in descending order.

--3. Return the ID and the number of medals of each country. 
--Sort the countries by the number of gold medals in descending order. 
--If two countries gained the same number of gold medals, sort them by the number of their silver medals.
select *
from olympics.o_medal_table
order by gold desc, silver
;
--4. Return the ID and the number of medals of each country. 
--Sort the countries by the number of gold medals in descending order. 
--If two countries gained the same number of gold medals, 
--sort them by the number of their silver medals in descending order.
select *
from olympics.o_medal_table
order by gold desc, silver desc
;
--5. Return the ID and the number of medals of each country. 
--Sort the countries by the total number of their medals in descending order. 
--If two countries gained the same number of medals,
--sort them by the number of their silver medals in ascending order.
select country_id, gold+silver+bronze as total
from olympics.o_medal_table
order by total desc , silver
;
--6. Return all the data of each discipline. Sort them by their names.

--7. Return all the data of each discipline. Sort them by their names in descending order.

--Combined exercises

--8. Return the ID and the number of medals of each country which has gained more then 10 medals (total).
--Name the number of medals as total. Sort the countries by the number of their medals.
select country_id, gold+silver+bronze as total
from olympics.o_medal_table
where gold+silver+bronze > 10
order by total
;
-----------
--Aggregation

-- The MIN() function returns the smallest value of the selected column.
-- The MAX() function returns the largest value of the selected column.
-- The COUNT() function returns the number of rows that matches a specified criterion.
-- The AVG() function returns the average value of a numeric column. 
-- The SUM() function returns the total sum of a numeric column. 

--COUNT()
--1. Return the number of sport events.
select count(*)
from olympics.o_sport_events
;

select count(name)
from olympics.o_sport_events
;
--2. Return the number of distinct genders in the o_sport_events table.
select count(distinct gender) 
from olympics.o_sport_events
;
--3. Return the number of distinct names in the o_sport_events table.
select count(distinct name) 
from olympics.o_sport_events
;
--4. Return the number of distinct genders and names in the o_sport_events table.
select count(distinct gender), count(distinct name)
from olympics.o_sport_events
;
--5. Return the number of distinct genders and names in the o_sport_events table. 
--Use the names genders and names for the columns.
select count(distinct gender) as "Gender", count(distinct name) as "Name"
from olympics.o_sport_events
;
--MIN(), MAX() and AVG()

--6. Return the minimum, maximum and average number of gold medals scored by a country.
select min(gold), max(gold), avg(gold) 
from olympics.o_medal_table
;
--7. Return the first and the last date on which an athlete was born.
select min(birthdate), max(birthdate) 
from olympics.o_athletes
;
--8. Return the athletes' name which is the first or last in alphabetic order.
select min(name), max(name)
from olympics.o_athletes
;
--9. Return the minimum, maximum and average number of medals scored by a country (total).
select min(gold+bronze+silver), max(gold+silver+bronze), round(avg(gold+silver+bronze), 3)
from olympics.o_medal_Table
;