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