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

--------------------------------------------
---WE FINISHED HERE LAST TIME
--------------------------------------------
--------------------------------------------
--------------------------------------------

--PRACTICE PART:
--BOOK_LIBRARY DATABASE 

--1. LIST ALL CUSTOMERS 
--2. LIST THE NAME OF THE CUSTOMERS
--3. LIST THE BIRTH DATE AND ADDRESS OF THE CUSTOMERS
--4. LIST THE NAME AND BIRTHDATE OF THE AUTHORS
--5. LIST THE THEORETICAL VALUE OF THE BOOK ITEMS
--6. LIST THE ISBN OF THE BOOKS
--7. LIST ALL AUTHORS

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


--Concatenation:
--SYNTAX: CONCAT(STRING1, STRING2)
--SYNTAX: STRING1||STRING2   - (BETTER OPTION!!)
 
--List the names of the customers.  --> LIST THE FIRST_NAME AND LAST_NAME COLUMNS TOGETHER IN ONE COLUMN

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

--descending order

--ascending order

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

-- NULLS LAST 
-- List the books. Sort the list by prices ascending. 
-- Put the rows to the end of the list where the prices is not given.

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

-- price is less than 2000

-- price is less or equal than 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.

-- List the book(s) which number of pages is less than 200 or the 'Fontana Books' published it.
 
-- Use 'is null' to list the null records
-- is null

-- List the authors where the birth date record is unknown (so 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.


------------------------------------------------------
--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, ...);