---------------------------------------------------------------
--SUBSTR()
--syntax:
--substr(char, position, substring_length.)

--The SUBSTR functions return a portion of char, beginning at character position, 
--substring_length characters long. SUBSTR calculates lengths using characters as defined by 
--the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode 
--complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
-- • If position is 0, then it is treated as 1.
-- • If position is positive, then Oracle Database counts from the beginning of char to find 
--   the first character.
-- • If position is negative, then Oracle counts backward from the end of char.
-- • If substring_length is omitted, then Oracle returns all characters to the end of char. If 
--   substring_length is less than 1, then Oracle returns null.

---------------------------------------------------------------
--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()
--The INSTR functions search string for substring ) . The search operation is defined as 
--comparing the substring argument with substrings of string of the same length for equality 
--until a match is found or there are no more substrings left.
--syntax: instr(string, substring)
---------------------------------------------------------------
-----------
--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.

--2. Return the number of distinct genders in the o_sport_events table.

--3. Return the number of distinct genders and names in the o_sport_events table.


--MIN(), MAX() and AVG()

--4. Return the minimum, maximum and average number of gold medals scored by a country.

--5. Return the first and the last date on which an athlete was born.

--SUM()

--6. What is the total area of all countries?

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

---------------------------------------------------------------
--OLYMPICS DATABASE
--1. Return the number of events by genders.

--2. Return the number of countries by continents. Omit the NULL value from the output.

--3. Return the number of countries by continents. Return a continent only if it 
--   has more than 30 countries.

--4. Return the name of each continent, and the minimum, maximum and average of the area.

--5. Return a continent only if the average population is less than 500000.

---------------------------------------------------------------
-- PRACTICE PART:
--1. Return the number of distinct names in the o_sport_events table.

--2. Return the number of distinct genders and names in the o_sport_events table. 
--   Use the names genders and names for the columns.

--3. Return the athletes' name which is the first or last in alphabetic order.

--4. Return the minimum, maximum and average number of medals scored by a country (total).

--5. Return the number of events by names.

--6. Return the number of countries by continents.

--7. Return the name of each continent and the number of countries. 
--   Return a continent only if the average population is less than 500000.

--8. Return the number of events of each name and gender combination.