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

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

select first_name, last_name
from BOOK_LIBRARY.authors;

select first_name, last_name
from book_library.customers;


--Concatenation

--List the names of the customers. 

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

select price
from book_library.books
order by price asc;

--descending
select price
from book_library.books
order by price desc;

select price
from book_library.books
order by price desc nulls last;

select price
from book_library.books
order by price asc nulls first;
--ascending

--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 first 
select *
from book_library.books
order by number_of_pages nulls first
;

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

-- The WHERE condition lets you restrict the rows selected to those that satisfy
--one or more conditions.


-- topic is History
select title, topic
from book_library.books
where lower(topic)='history'
;


select title, topic
from book_library.books
where UPPER(topic)='HISTORY'
;


-- price is less than 2000


--price is less or equal than 2000


--AND
--OR

--is null

--is not null

-- List all customers.
select *
from book_library.customers;
-- List all books - just title.
select title
from book_library.books;
-- List all authors.
select *
from book_library.authors;
-- List all royalties.
 select royalty
 from book_library.writing;
-- List theoretical values in descending order.
select theoretical_value
from book_library.book_items
order by theoretical_value desc nulls last
;
-- List bring back dates in ascending order.
select bring_back_date
from book_library.borrowing
order by bring_back_date;
-- List history books.

-- List history or thriller books.
select title, topic
from book_library.books
where topic='History' OR topic='Thriller'
;

select title, topic
from book_library.books
where topic in ('History', 'Thriller')
;

select title, topic
from book_library.books
where lower(topic) in ('history', 'thriller')
;

select title, topic
from book_library.books
where lower(topic) not in ('history', 'thriller')
;
----------------------------------------------------
-- List books whose price is higher than 5000 or whose number of pages is more than 500.
select title, price, number_of_pages
from book_library.books
where price>5000 or number_of_pages>500
;
-- List books whose price is between 1000 and 5000
select title, price
from book_library.books
where price between 1000 and 5000
;
-- List books whose price is not between 1000 and 5000
select title, price
from book_library.books
where price not between 1000 and 5000
;

select title, price
from book_library.books
where price<1000 or price>5000
;
-- List books where the topic is literature, history or science
select title, topic
from book_library.books
where topic in ('Literature', 'History', 'Science')
;
-- List books where the topic is NOT literature, history or science
select title, topic
from book_library.books
where topic not in ('Literature', 'History', 'Science')
;
-- List books where the topic is unknown.
select title, topic
from book_library.books
where topic is null;
-- List books where the topic is known.
select title, topic
from book_library.books
where topic is not null;

------------------------------------------------------
--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'.     The......  -> The%
select title
from book_library.books
where lower(title) like 'the%'
;
-- List the books which title contains at least two 'a'.   ....a...a.... 
select title
from book_library.books
where lower(title) like '%a%a%'
;
-- List the books which title contains exactly two 'a'.
select title
from book_library.books
where lower(title) like '%a%a%' and lower(title) not like '%a%a%a%'
;
-- List the books which title ends with 'd'.    .....d
select title
from book_library.books
where lower(title) like '%d'
;
-- List the books which title begins with any character, BUT the second character is 'a'.
select title
from book_library.books
where lower(title) like '_a%'
;
---------------------------------------------------------
--round() 
--The ROUND() function rounds a number to a specified number of decimal places.
--SYNTAX: 	 ROUND(number, decimals, operation)
select price/number_of_pages, round(price/number_of_pages), round(price/number_of_pages, 3)
from book_library.books
;
-- trunc()
select price/number_of_pages, trunc(price/number_of_pages), trunc(price/number_of_pages, 3)
from book_library.books
;


select price/number_of_pages, round(price/number_of_pages,2), trunc(price/number_of_pages, 2)
from book_library.books
;
-- length()
select title, length(title)
from book_library.books;


--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.
select * from dual;

Examples: 
select 2*3 , length('apple'), round(5896/13,5)
from dual;

select sysdate 
from dual;
-- sysdate  - current system date and time


-- EXTRACT