--PRACTICE PART:
--OLYMPICS DATABASE 
--1.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 silver=bronze 
;
--2.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 between 5 and 9;

select *
from olympics.o_medal_table
where gold>4 and gold<10;
--3.Return each country which name ends with 'istan'.
select *
from olympics.o_countries
where lower(name) like '%istan';
--4.Return each country which name starts with 'A' and at least 8 letters long.
select *
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;
--5.Return each country which area starts with '3'.
select *
from olympics.o_countries
where area like '3%';
--6.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;
--7.Return all the data of each event which name is missing.  ---name is null
select *
from olympics.o_sport_events
where name is null;
---------------------------------------------------------------
--DATE FUNCTIONS-----------------------------------------------
---------------------------------------------------------------
--TO_DATE()
select '2010.02.15 10:30:05', to_date('2010.02.15 10:30:05', 'yyyy.mm.dd hh24:mi:ss')
from dual
;

select '2010.02.15 10:30:05', to_date('2010.02.15', 'yyyy.mm.dd')
from dual
;

select '2010.02.15 10:30:05', to_date('2010', 'yyyy')
from dual
;
--TO_CHAR()
select birthdate, to_char(birthdate, 'yyyy.mm.dd')
from olympics.o_athletes
;

select birthdate, to_char(birthdate, 'yyyy')
from olympics.o_athletes
;

select birthdate, to_char(birthdate, 'Year        year          YEAR')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'Month        month        MONTH    mon  Mon  MON')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'Day    day     DAY   dy     Dy    DY')
from olympics.o_athletes;


select birthdate, to_char(birthdate, 'yyyy.mm.dd hh24:mi:ss')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'yyyy.mm.dd hh24:mi:ss    cc')
from olympics.o_athletes; 

select birthdate, to_char(birthdate, 'yyyy.mm.dd     w')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'yyyy.mm.dd     ww')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'yyyy.mm.dd     d')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'yyyy.mm.dd     ddd')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'dl')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'ds')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'ts')
from olympics.o_athletes;

select birthdate, to_char(birthdate, 'yyyy')
from olympics.o_athletes;

--EXTRACT()
select birthdate, extract(year from birthdate), extract(month from birthdate),
extract(day from birthdate)
from olympics.o_athletes;

--MONTHS_BETWEEN()
select months_between(sysdate, birthdate)
from olympics.o_athletes
;

select months_between(sysdate, birthdate)/12
from olympics.o_athletes
;
--ADD_MONTHS()
select add_months(sysdate, 1) 
from dual;

select add_months(sysdate, 12) 
from dual;

--NEXT_DAY()
--NEXT_DAY returns the date of the first weekday named by char that is later than the
---date
select next_day(sysdate, 'Sunday') 
from dual;

select next_day(sysdate, 2) 
from dual;

--LAST_DAY()
select last_day(sysdate) 
from dual;
---------------------------------------------------------------
--DISTINCT - returns unique records
select gender 
from olympics.o_sport_events;

select distinct gender 
from olympics.o_sport_events;

select distinct topic from book_library.books;
---------------------------------------------------------------
--ALIASES
select first_name||' '||last_name Name
from book_library.customers;

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

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

select first_name||' '||last_name as "Name name"
from book_library.customers;
---------------------------------------------------------------
--NVL()
-- The NVL() function allows you to replace null
-- with a more meaningful alternative in the results of a query.

--1. Return the name, the population and the continent of country Cook Islands.
select name,population, continent
from olympics.o_countries
where name='Cook Islands';
--2. Return the name, the population and the continent of country Cook Islands. 
--Replace the missing continent name with string 'unknown'.
select name, population, continent, nvl(continent, 'unknown')
from olympics.o_countries
where name='Cook Islands';

select name, population, continent, nvl(continent, 0)
from olympics.o_countries
where name='Cook Islands';

select name, population, nvl(population, 0)
from olympics.o_countries
;
---------------------------------------------------------------
--LOWER(), UPPER(), INITCAP()
-- lower()  - Convert the text to lower-case
-- upper()  - Convert the text to upper-case

select 'apple', initcap('apple')
from dual;
---------------------------------------------------------------
--SUBSTR()

--syntax:
--substr(column_name, start_position, number_of_char.)

---------------------------------------------------------------
--REPLACE()
-- The REPLACE() function replaces all occurrences 
-- of a substring within a string, with a new substring.
-- Syntax:
-- REPLACE(string, old_string, new_string)

---------------------------------------------------------------
--INSTR()
--syntax: instr(string, substring)

---------------------------------------------------------------
-- GROUP BY
-- The SQL GROUP BY Statement
-- The GROUP BY statement groups rows that have the same values into 
-- summary rows, like "find the number of customers in each country".

-- The GROUP BY statement is often used with 
-- aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group 
-- the result-set by one or more columns.

-- GROUP BY Syntax
--	SELECT column_name(s)
--	FROM table_name
--	[WHERE condition]
--	[GROUP BY column_name(s)]
--	[ORDER BY column_name(s)];

---------------------------------------------------------------
-- HAVING

-- The SQL HAVING Clause
-- The HAVING clause was added to SQL because the WHERE keyword 
-- cannot be used with aggregate --functions.


-- HAVING Syntax
--	SELECT column_name(s)
--	FROM table_name
--	[WHERE condition]
--	[GROUP BY column_name(s)]
--	[HAVING condition]
--	[ORDER BY column_name(s)];

---------------------------------------------------------------