/* 
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 
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 birth_date||' '||address
from book_library.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
select concat(first_name, last_name)
from book_library.customers;

select concat(first_name,concat(' ',last_name))
from book_library.customers;

select first_name||' '||last_name
from book_library.customers;
-------------------------------------------------
select columnN  ....
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;

--ascending order
select title, price
from book_library.books
order by price;

select title, price
from book_library.books
order by price asc;

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

-----
select title, price
from book_library.books
order by title;

select title, price
from book_library.books
order by title 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 title, number_of_pages
from book_library.books
order by number_of_pages asc nulls first;

select title, number_of_pages
from book_library.books
order by number_of_pages 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 book_id, topic
from book_library.books
where topic='History'
;
-- price is less than 2000
select book_id, price
from book_library.books
where price<2000;
-- price is less or equal than 2000
select book_id, 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 book_id, price, topic
from book_library.books
where topic='History' and price<2000;