/* 
This is a
multiline comment.
*/  
-- One line comment.


--SQL: Structured Query Language
-- - DDL: data definition language: create, alter, drop
-- - DML: data manupulation language: insert, update, delete
-- - Query language: select 
-- - DCL: data control language 
--  *transaction handling: commit, rollback, savepoint
--  * privilege handling: grant, revoke;

-- the semicolon (;) is not part of the statement
-- Run statement: ctrl+enter OR right click -> run statement OR F9

--Use a SELECT statement or subquery to retrieve data from one or more tables.

--List all books from book_library
select * from book_library.books;

--List the titles and prices from book_library 
select title, price
from book_library.books;

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

--PRACTICE PART:
--BOOK_LIBRARY DATABASE 

--1. LIST ALL CUSTOMERS 
select * 
from book_library.customers;
--2. LIST THE NAME OF THE CUSTOMERS
select first_name, last_name
from book_library.customers;
--3. LIST THE BIRTH DATE AND ADDRESS OF THE CUSTOMERS
select address, birth_date
from book_library.customers;
--4. LIST THE NAME AND BIRTHDATE OF THE AUTHORS
select first_name||' '||last_name, birth_date
from book_library.authors;
--5. LIST THE THEORETICAL VALUE OF THE BOOK ITEMS
select theoretical_value 
from book_library.book_items;
--6. LIST THE ISBN OF THE BOOKS
select isbn 
from book_library.books;
--7. LIST ALL AUTHORS
select * from book_library.authors;
--------------------------------------------
--Concatenation:
--SYNTAX: CONCAT(STRING1, STRING2)
--SYNTAX: STRING1||STRING2   - (BETTER OPTION!!)

-- | - hungarian keyboard: alt+W

--List the names of the customers.  --> LIST THE FIRST_NAME AND LAST_NAME COLUMNS TOGETHER IN ONE COLUMN
select concat(first_name, concat(' ', last_name))
from book_library.customers;

select first_name||' '||last_name
from book_library.customers;
-------------------------------------------------
select columN ...
from tableN...
[where ...]
[group by ...]
[having ...]
[order by ...]
;
--Use the ORDER BY clause to specify the order in which cells on the left-hand
--side of the rule are to be evaluated.
--You can specify an ascending (asc or nothing) or a descending (desc) order.
--You can specify whether returned rows containing null values should appear
--first or last in the ordering sequence with NULLS FIRST or NULLS LAST
--clause.

--SYNTAX: 
--ORDER BY COLUMN_NAME ASC/DESC;

select theoretical_value 
from book_library.book_items;


--ascending order
select theoretical_value 
from book_library.book_items
order by theoretical_value asc;

select theoretical_value 
from book_library.book_items
order by theoretical_value;

--descending order
select theoretical_value 
from book_library.book_items
order by theoretical_value desc;

-- NULLS FIRST 
-- List the books. Sort the list by number of pages ascending. 
-- Put the rows to the beggining of the list where the number of pages is not given. --> NULL
select *
from book_library.books
order by number_of_pages asc nulls first;
-- NULLS LAST 
-- List the books. Sort the list by prices descending. 
-- Put the rows to the end of the list where the prices is not given. --> null
select title, price
from book_library.books
order by price desc nulls last;
------------------------------------------------
-- The WHERE condition lets you restrict the rows selected
--to those that satisfy one or more conditions.

-- =		(equal)
-- != or <>	(not equal)
-- <		(less)
-- <=  		(less or equal)
-- >		(more)
-- >=		(more or equal)

-- topic is History ---> topic='History'
select title, topic
from book_library.books
where topic='History'
;

-- price is less than 2000 -- price<2000
select title, price
from book_library.books
where price<2000
order by price;

-- price is less or equal than 2000
select title, price 
from book_library.books 
where price<=2000;
-- Use AND/OR between conditions, if you have more than one
-- AND
-- OR

-- List the book(s) which topic is History, and cheaper than 2000.
select title, topic, price
from book_library.books
where topic='History' AND price<2000;

-- List the book(s) which number of pages is less than 200
-- or the 'Fontana Books' published it.
 select title, number_of_pages, publisher
 from book_library.books
 where number_of_pages < 200 OR publisher='Fontana Books'
 ;
 
-- Use 'is null' to list the null records
-- is null
-- List the authors where the birth date record is unknown (so NULL).
select *
from book_library.authors
where birth_date is null
;
--Use 'is not null' to list each record except null records.
-- is not null

-- List the authors, but only if we know their birth date.
select *
from book_library.authors
where birth_date is not null
;

--------------------------------------------
--------------------------------------------
--------------------------------------------
---WE FINISHED HERE LAST TIME
--------------------------------------------
--------------------------------------------
--------------------------------------------
-PRACTICE PART:
--BOOK_LIBRARY DATABASE 

--1.List the student customers.
--2.List the borrowing records where the late_fee is greater than 25k.
--3.Retrieve the book titles and page numbers combined into one column, separated by a comma.
--4.Retrieve the authors and order the results from youngest to oldest.
--5.List the customers first names and addresses in a single column, with the membership_fee and
--  sort the list by membership_fee in decending order. 

--------------------------------------------
--------------------------------------------
--------------------------------------------
--The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

--There are two wildcards often used in conjunction with the LIKE operator:
--     The percent sign (%) represents zero, one, or multiple characters
--     The underscore sign (_) represents one, single character

SYNTAX:
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

EXAMPLES:
 
-- List the books which title begin with 'The'.

-- List the books which title contains at least two 'a'.

-- List the books which title contains exactly two 'a'.

-- List the books which title ends with 'd'.

-- List the books which title begins with any character, BUT the second character is 'a'.


---------------------------------------------------------
--round() 
--The ROUND() function rounds a number to a specified number of decimal places.
--SYNTAX: 	 ROUND(number, decimals, operation)

----------------------------------------------------------
-- trunc()
--The TRUNC() function truncates a number to a specified number of decimal places.

----------------------------------------------------------
-- length()
--The LENGTH() function returns the length of a string (so the number of characters)

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

select sysdate 
from dual;

-- sysdate  - current system date and time

------------------------------------------------------------
--between
--A BETWEEN condition determines whether the value of one expression is in an interval
--defined by two other expressions.
--All three expressions must be numeric, character, or datetime expressions
--syntax:  expression BETWEEN expression1 AND expression2

-- PRICE MORE 880 BUT NOT MORE THAN 1444

--not between
--syntax:  expression NOT BETWEEN expression1 AND expression2

------------------------------------------------------------
--in
--The IN operator is used in the WHERE clause to check if a specified column's value matches any value within a provided list.
--The IN operator functions as a shorthand for multiple OR conditions, making queries shorter and more readable.
--SYNTAX: .... column_name IN (value1, value2, ...);

--not in
--SYNTAX: .... column_name NOT IN (value1, value2, ...);

--------------------------------------------
--------------------------------------------
--------------------------------------------
--OLYMPICS
--1. Return the data of each event that is organized for women.

--2. Return the ID and the number of medals of each country which has at least 1 gold medal.

--3. Return the ID and the number of medals of each country which gained exactly the same number of gold and silver medals.

--4. Return the ID and the number of medals of each country which gained exactly the same number of gold, silver and bronze medals.

--5. Return the ID and the number of medals of each country which has not gained any gold nor any silver medals.

--6. Return the ID and the number of medals of each country which has not gained any gold nor any silver medals but gained bronze medals.

--7. Return the ID and the number of medals of each country which has zero gold or zero silver medal while gained at least 1 bronze medal.

--8. Return the ID and the number of medals of each country which has gained more than 10 gold and silver medals (total).

--9. Return the ID and the number of medals of each country which has gained more than 10 gold and silver medals (total). Name the number of medals as total.


--BETWEEN

--10. Return the ID and the number of medals of each country which number of gold medals is between 5 and 9.

--or

--11. Return the ID and the number of medals of each country which number of gold medals is between the number of bronze and silver medals.

--12. Return all the data of each event which name is missing.

--13. Return each gender for which an event is scheduled with a missing name.

--14. Return each gender for which an event is scheduled with a missing name. Return each gender only once.

‐-----------
--Pattern matching

--1. Return each country.

--2. Return the country which name matches Albania.

--3. Return the country which name matches albania.

--4. Return each country which name starts with 'A'.

--5. Return each country which name starts with 'A', continues with a custom character, then ends with 'gola'.

--6. Return each country which name ends with 'istan'.

--7. Return each country which name starts with 'A' and ends with 'istan'.

--8. Return each country which name starts with 'A', ends with 'a' and exactly 5 letters long.

--9. Return each country which name starts with 'A' and at least 8 letters long.

--10. Return each country which name starts with 'Aust' and ends with 'ia'.

--11. Return each country which name starts with 'Aust' or ends with 'ia'.

--12. Return each country which area starts with '3'.

-----------
--Sorting
--Basic exercises
--1. Return the ID and the number of medals of each country. Sort the countries by the number of gold medals.

--2. Return the ID and the number of medals of each country. Sort the countries by the number of gold medals in descending order.

--3. Return the ID and the number of medals of each country. Sort the countries by the number of gold medals in descending order. If two countries gained the same number of gold medals, sort them by the number of their silver medals.

--4. Return the ID and the number of medals of each country. Sort the countries by the number of gold medals in descending order. If two countries gained the same number of gold medals, sort them by the number of their silver medals in descending order.

--5. Return the ID and the number of medals of each country. Sort the countries by the total number of their medals in descending order. If two countries gained the same number of medals, sort them by the number of their silver medals in ascending order.

--6. Return all the data of each discipline. Sort them by their names.

--7. Return all the data of each discipline. Sort them by their names in descending order.

--Combined exercises

--8. Return the ID and the number of medals of each country which has gained more then 10 medals (total). Name the number of medals as total. Sort the countries by the number of their medals.