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

apple  -> le    substr('apple', 4, 2)

select substr('apple', 4, 1)
from dual;

select substr('appleappleapple', -3, 2)
from dual;
--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.

select substr(library_card_number, -2, 2)
from book_library.customers;

select substr(library_card_number, 1, 6)
from book_library.customers;

select address, first_name--, substr(address, -8, 8)
from book_library.customers
where substr(address, -8, 8) ='Debrecen' 
;
---------------------------------------------------------------
--REPLACE()
-- The REPLACE() function replaces all occurrences 
-- of a substring within a string, with a new substring.
-- Syntax:
-- REPLACE(string, old_string, new_string)

'appleappleapple'  -> 'a' - 'A'

select replace('appleappleapple', 'a', 'A')
from dual;

select replace('appleappleapple', 'app', 'APP')
from dual;
---------------------------------------------------------------
--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)

'applebananagrape' -> grape substringspos. 

select instr('applebananagrape', 'grape') 
from dual;


select instr(address, 'Market Str.')
from book_library.customers;

select address, first_name, substr(address,instr(address, 'Market Str.'),11 )
from book_library.customers
where substr(address,instr(address, 'Market Str.'),11 )='Market Str.';
---------------------------------------------------------------
--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. 
select min(area), max(area), avg(area), count(area), count(*), sum(area)
from olympics.o_countries;
--COUNT()
--1. Return the number of sport events.
select count(*)
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 genders and names in the o_sport_events table.
select count(distinct gender), count(distinct name)
from olympics.o_sport_events
;
--MIN(), MAX() and AVG()
--4. 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;
--5. Return the first and the last date on which an athlete was born.
select min(birthdate), max(birthdate)
from olympics.o_athletes;
--SUM()
--6. What is the total area of all countries?
select sum(area)
from olympics.o_countries;

---------------------------------------------------------------
select columnN  ....
from tableN ...
[where ...]
[group by ...]
[having ...]
[order by ...]
;

-- 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)];
select max(area), min(area), avg(area), continent
from olympics.o_countries
group by continent
;
---------------------------------------------------------------
-- 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)];
select min(area), continent
from olympics.o_countries
group by continent
having min(area)>200
;
---------------------------------------------------------------
--OLYMPICS DATABASE
--1. Return the number of events by genders.
select count(*) number_of_events, gender
from olympics.o_sport_events
group by gender;
--2. 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;
--3. 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
where continent is not null
group by continent
having count(*)>30;
--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 5000000.
select continent, avg(population)
from olympics.o_countries
group by continent
having avg(population)<5000000
;
---------------------------------------------------------------
-- PRACTICE PART:

--BOOK_LIBRARY
--1. return the number of distinct customer categories
select count(distinct category)
from book_library.customers;
--2. retunr the number of customers
select count(*) from book_library.customers;
--3. return the number of student customers
select count(*) from book_library.customers where category='student';

--4. return the smallest, largest price
select min(price), max(price)
from book_library.books;
--5. return tha average book price
select avg(price)
from book_library.books;
--6. return the number of customers for each category
select count(*) from book_library.customers group by category;

--7. return the number of customers for each category, 
--   but only if the category has more than 10 customers
select count(*) from book_library.customers group by category having count(*)>10;






--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.