---------------------------------------------------------------
--SUBSTR()
--syntax:
--substr(char, position, substring_length)
apple -> le substr('apple',4,2 )

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

select substr('apple',0,2)
from dual;

select substr('apple',-4,3)
from dual;

select substr('apple',3)
from dual;

select * --substr(address, -8, 8)
from book_library.customers
where substr(address, -8, 8)='Debrecen';

--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)
'grapegrapegrape' -> 'g' -> 'G'

select replace('grapegrapegrape', 'g', 'G')
from dual;

select title, replace(lower(title), 'the', 'ABCDEFG')
from book_library.books;
---------------------------------------------------------------
--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)

'grapebananaapple' -> 'banana'-s start pos.

select instr('grapebananaapple','banana')
from dual;

select instr(address, ' '), instr(address, ', '),
substr(address, instr(address, ' ')+1,instr(address, ', ')-instr(address, ' ')-1)
from book_library.customers;
---------------------------------------------------------------
--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(name), 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) total_area 
from olympics.o_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)];

select topic, count(*), min(price), avg(price)
from book_library.books
group by topic;

---------------------------------------------------------------
-- 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 topic, count(*)
from book_library.books
group by topic
having count(*)>7
;
---------------------------------------------------------------
--OLYMPICS DATABASE
--1. Return the number of events by genders.
select count(*), 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.
select min(area), max(area), avg(area), continent
from olympics.o_countries
group by continent;
--5. Return a continent only if the average 
--population is less than 5.000.000.
select avg(population), continent
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.