-- 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)];

---------------------------------------------------------------
--1. Return the number of events by genders.
select count(*), gender
from olympics.o_sport_events
group by gender
;
--2. Return the number of events by names.
select count(*), name
from olympics.o_sport_events
group by name
;
--3. Return the number of countries by continents.
select count(*), continent
from olympics.o_countries
group by continent;
--4. Return the number of countries by continents. Omit the NULL value from the output.
select count(*), continent
from olympics.o_countries
where continent is not null
group by continent;

select count(*), continent
from olympics.o_countries
group by continent
having continent is not null;

--5. Return the number of countries by continents. Return a continent only if it has more than 30 countries.
select count(*), continent
from olympics.o_countries
group by continent
having count(*)>30
;

--6. Return the name of each continent, and the minimum, maximum and average of the area.
select continent, min(area), max(area), avg(area)
from olympics.o_countries
group by continent
;
--7. Return a continent only if the average population is less than 2million.
select continent, avg(population)
from olympics.o_countries
group by continent
having avg(population)<2000000
;
--8. Return the name of each continent and the number of countries. 
--Return a continent only if the average population is less than 2million.
select continent, avg(population), count(*)
from olympics.o_countries
group by continent
having avg(population)<2000000
;

--9. Return the number of events of each name and gender combination.
select count(*), name, gender
from olympics.o_sport_events
group by gender, name
;

---------------------------
--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, nvl(continent, 'unknown')
from olympics.o_countries
where name='Cook Islands'
;
--3. Return the name, the population and the continent of each country.
--Replace the missing continent names with string 'unknown'.
select name, population, continent, nvl(continent, 'unknown')
from olympics.o_countries
;

--4. Return the name, the population and the continent of each country. 
--Replace the missing continent names with string 'unknown' and the missing population values with 0.
select name,  continent, nvl(continent, 'unknown'),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

--examples
select 'apple', lower('apple'), upper('apple'), initcap('apple')
from dual
;

--CONCATENATION
-- ||
--examples
select first_name||' '||last_name
from book_library.authors
;
--CONCAT()
--example
select concat(first_name,concat(' ',last_name))
from book_library.authors
;

select concat('apple','pie')
from dual
;

----------------------------------------
--SUBSTR()

--Extract a substring from a string (start at position x, extract y characters):

--syntax: substr(string, x, y )
--example
select first_name, substr(first_name, 3, 2)
from book_library.authors
;

select first_name, substr(first_name, 3)
from book_library.authors
;

select first_name, substr(first_name, -3, 2)
from book_library.authors
;

select first_name, substr(first_name, -5)
from book_library.authors
;

-------------------------------------
--DUAL Table:
--The DUAL is special one row, one column table present by default in all Oracle databases. The owner of DUAL is SYS (SYS owns 
--the data dictionary, therefore DUAL is part of the data dictionary.) but DUAL can be accessed by every user. The table has a 
--single VARCHAR2(1) column called DUMMY that has a value of 'X'.
--There may be a situation where we want to query something that is not from a table. For example, getting the current date or 
--querying a simple arithmetic expression like 2+2.

Examples: 
select 2*3 
from dual;

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

select title, replace(lower(title), 'the', 'ABCDEFG')
from  book_library.books
;
--example


--------------------------------
--INSTR()
--the instr() function returns the position of the first occurrence of a string in another string
--example
select title, instr(lower(title), 'the')
from book_library.books
;

-----------------------------------------
--Date functions
--SYSDATE

--example

SELECT SYSDATE
FROM dual;

--MONTHS_BETWEEN()
select months_between(sysdate, birth_date), months_between(birth_date, sysdate)
from book_library.authors
;
--example

--ADD_MONTHS()
--example
select birth_date, add_months(birth_date, 2)
from book_library.authors
;
select birth_date, add_months(birth_date, -20)
from book_library.authors
;

--NEXT_DAY()
--syntax: next_Day(date, number)
--example

select sysdate, next_day(sysdate, 1)
from dual;


--LAST_DAY()
--last day of the month
--example
select birth_Date, last_day(birth_date)
from book_library.authors
;

--EXTRACT()
--example
select birth_date, extract(year from birth_date)
from  book_library.authors
;
select birth_date, extract(month from birth_date)
from  book_library.authors
;
select birth_date, extract(day from birth_date)
from  book_library.authors
;
----------------------------
-- TO_CHAR function converts DATETIME or DATE values to character string values.
select birth_date, to_char(birth_Date, 'yyyy.mm.dd hh24:mi:ss')
from book_library.authors
;
select birth_date, to_char(birth_Date, 'mm')
from book_library.authors
;
select birth_date, to_char(birth_Date, 'dd/mm/yyyy')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'cc')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd cc')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd       d')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd       ddd')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd       w')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd       ww')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd       day DAY Day dy DY Dy')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'yyyy.mm.dd       month MONTH Month mon MON Mon')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'ds')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'ts')
from book_library.authors
;

select birth_date, to_char(birth_Date, 'dl')
from book_library.authors
;
-- TO_DATE function converts date strings in various formats to a date integer value, with data type DATE.

select  to_Date('1863.03.14 10:15:23', 'yyyy.mm.dd hh24:mi:ss')
from dual
;