--------------------------------------------------------
--SELECT column_name(s)
--FROM table_name
--[WHERE condition]
--[GROUP BY column_name(s)]
--[HAVING condition]
--[ORDER BY column_name(s)];

--desc, asc, nulls last, nulls first

--concatenation -> ||
--concat()

--conditions in WHERE clause:
--	<, >, =, <>, !=
--	between X and y
--	in, not in
--	is null, 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
-- LIKE 

-- AND
-- OR

--Functions:
--	round()
--	trunc()
--	substr()
--	instr()
--	length()
--	to_char()
--	to_date()

yyyy - year
mm - month
dd - day

hh24 - hour
mi - minute
ss - second

cc - century

d - day of the week
ddd - day of the year

w - week of the month
ww - week of the year

day, Day, DAY, dy, Dy, DY

month, Month, MONTH, mon, Mon, MON

ds
dl
ts

--Functions:
--	month_between()
--	extract(... from ...)
--	lower()
--	upper()
--	replace()
--	nvl()

--	max()
--	min()
--	count()
--	avg()
--	sum()

-- sysdate  - current system date and time

-- subquery ! 

-- distinct

-- Set operators:
--	UNION
--	UNION ALL
--	MINUS
--	INTERSECT

-- ROWNUM	

-- INNER JOIN
-- LEFT OUTER JOIN
-- RIGHT OUTER JOIN 
-- FULL OUTER JOIN

-- FETCH FIRST n ROWS ONLY
-- FETCH FIRST n ROWS WITH TIES
-- OFFSET n ROWS FETCH NEXT m ROWS WITH TIES


--CREATE VIEW view_name AS 
-----------------------------------------
--PRACTICE
-- 1. Who is the author (author id) of the book which title is Napoleon?

--2. Which books has less than 3 items?

--3.List the customer whose is more than 20 years and who bor-
--rows the book (and they did not bring back) which theoretical value is not
--null?

--4.List the name, birth date and category of all customers whose
--category is student, adult or supporter and the book item ids which they borrowed
--if they did any. Sort the list by the name of the customers and by the
--book item id.

--5.List the title of the books which title contain at least two ’e’
--characters (case-insensitive) or which second letter is ’o’. Sort the list by
--title.

--6.Create a view which lists the title, isbn and price per page of the books which topic is science and the isbn starts with 96 

--7.List the female or pensioner customers. Sort the list by gender descending. 

--8.List the books which title contains at least one 0 character. 

--9.List the books which titles third character is 'a'. 

--10.What is theoretical value and the book item id of the book items whose book id is 1116152201? 
--Sort the list by the theoretical value descending. 

--11. List the title of the books which number of pages is between 150 and 200 or whose 
--publisher is neither Arrow nor Signet and nor Apress? Sort the list. 

--12. List the title of the books. Put the 1 value into the second. 
--Put the a letter into the third column and into the last column put the current date and time. 

--13. List the address of customers, and the substring of the address from the 3rd character to the 7th character.

--14. List the address of customers, and in the second column replace the Str string in the address to the ******. 

--15. List the address of customers, and in the second column the number of the character from which the Str string starts. 

--16. List the address of customers, and in the second column the number of the character in the address. 

--17. List the topics of the books. In the second column list again the topics, but if the topic is null list ****** instead of it. 

--18. List the name and birth date of the customers who was born earlier than 1st January, 2000. 

--19. List the date which is 2 months later than the current date. 

--20. How many years did pass away from 1st January, 2000? 

--21. List the date which is 3 years later than the current date.