--1. Return the data of each event that is organized for women.
select *
from olympics.o_sport_events
where gender='women'
;

select *
from olympics.o_sport_events
where gender in ('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.

-- gold=silver=bronze --> error

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 *
from OLYMPICS.o_medal_table
where gold+silver>10
;

select gold+silver as total 
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 gold+silver as total 
from OLYMPICS.o_medal_table
where gold+silver>10
;

select 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
--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'
;

select name
from  olympics.o_countries
where name like 'Albania'
;

--3. Return the country which name matches albania.
select name
from  olympics.o_countries
where name like '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'
;

select name
from olympics.o_countries
where lower(name) like 'a%a' and length(name)=5
;
--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
from olympics.o_countries
where lower(name) like 'a%' and length(name)>=8
;
--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 *
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.
select *
from olympics.o_medal_table
order by gold asc
;
--2. Return the ID and the number of medals of each country. 
--Sort the countries by the number of gold medals in descending order.
select *
from olympics.o_medal_table
order by gold desc
;
--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 *
from olympics.o_medal_table
order by gold+silver+bronze desc, silver 
;

select gold+silver+bronze as total, silver
from olympics.o_medal_table
order by total desc, silver 
;
--6. Return all the data of each discipline. Sort them by their names.
select *
from olympics.o_sport_disciplines
order by name;
--7. Return all the data of each discipline. Sort them by their names in descending order.
select *
from olympics.o_sport_disciplines
order by name desc;
--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, 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), round(avg(gold), 3)  
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+silver+bronze), max(gold+silver+bronze), avg(gold+silver+bronze)
from olympics.o_medal_table
;