-----OLYMPICS
--1. Return the name of each country which is located in the same continent as Hungary. 
--Omit Hungary from the output.

-- Solve the exercise joining tables.
select country_2.name
from olympics.o_countries country_1 inner join olympics.o_countries country_2 
on  country_1.continent=country_2.continent
where country_1.name='Hungary' and country_2.name!='Hungary'
;
-- Solve the exercise using subselects.
select name
from olympics.o_countries
where continent=(select continent from olympics.o_countries where name='Hungary')
and name!='Hungary'
;
--2. Return the name and the medals of each country which gained more medals than the average.
select country.name, gold+silver+bronze as "Medals"
from olympics.o_medal_table medal inner join olympics.o_countries country on medal.country_id=country.c_id
where gold+silver+bronze > (select avg(gold+silver+bronze) from olympics.o_medal_table)
;
--3. Return the name, continent and population of each country which is in the same continent
--as Hungary and its population is greater or equal to the population of Hungary. 
--Order the countries by their population in descending order.
select name, continent, population
from olympics.o_countries
where continent=(select continent from olympics.o_countries where name='Hungary')
and population >= (select population from olympics.o_countries where name='Hungary')
order by population desc
;
--4. Return the name of each African country which starts with the same letter as any European country.
select substr(name,1,1)
from olympics.o_countries
where continent='Europe'
;

select name
from olympics.o_countries
where continent='Africa'
and substr(name,1,1) in (select substr(name,1,1)
                from olympics.o_countries
                where continent='Europe')
;
--5. Return the name of each country which scored 51, 36 or 14 gold medals
select name, gold
from olympics.o_countries country inner join olympics.o_medal_table medal on country.c_id = medal.country_id
where gold in (51, 36, 14)
;
--6. Return the name, continent and population of each country which has the greatest population
--in its continent.
select max(population)
from olympics.o_countries
group by continent
;

select *
from olympics.o_countries c_1
where population = (select max(population)
                    from olympics.o_countries c_2
                    where c_1.continent=c_2.continent)
;
-- ANY, ALL, EXISTS 

-- The ANY operator:
--	returns a boolean value as a result
--	returns TRUE if ANY of the subquery values meet the condition
-- ANY means that the condition will be true if the operation is true for any of the values in the range.

-- The ALL operator:
--	returns a boolean value as a result
--	returns TRUE if ALL of the subquery values meet the condition
--	is used with SELECT, WHERE and HAVING statements
-- ALL means that the condition will be true only if the operation is true for all values in the range. 

-- The EXISTS operator is used to test for the existence of any record in a subquery.
-- The EXISTS operator returns TRUE if the subquery returns one or more records.

--7. Return the ID and name of those countries that have scored any medals
--(thus, they appear in the o_medal_table table).
select c_id
from olympics.o_countries c
where exists (select *
                from olympics.o_medal_table m
                where c.c_id=m.country_id)
                ;

select *
from olympics.o_countries
where c_id in (select country_id
                from olympics.o_medal_table )
;

--8. Return each country that is the most populous on its continent.

--9. Return each country that has greater population than one of the European countries has.
select *
from olympics.o_countries
where population > (select min(population) from olympics.o_countries where continent='Europe')
;

select *
from olympics.o_countries
where population > ANY (select population from olympics.o_countries where continent='Europe')
;
--10. Return 5 countries having the greatest areas.
select *
from olympics.o_countries
order by area desc nulls last
fetch first 5 rows only
;
--11. List those individual athletes (from the OLYMPICS.O_ATHLETES table) who
--are older than the youngest Hungarian athlete.
select c_id from olympics.o_countries where name='Hungary';

select max(birthdate)
from olympics.o_athletes
where country_id=(select c_id from olympics.o_countries where name='Hungary')
;

select *
from olympics.o_athletes 
where lower(team)='no'
and birthdate < (select max(birthdate)
                from olympics.o_athletes
                where country_id=(select c_id 
                                    from olympics.o_countries 
                                    where name='Hungary'))
;

select *
from olympics.o_athletes 
where lower(team)='no'
and birthdate < (select max(birthdate)
                from olympics.o_athletes athlete inner join olympics.o_Countries country
                on athlete.country_id=country.c_id
                where country.name='Hungary')
;
----------------------------
-------------------------------------------------------------------
--VIEW 
--In SQL, a view is a virtual table based on the result-set of an SQL statement.
--A view contains rows and columns, just like a real table. 
--The fields in a view are fields from one or more real tables in the database.
--You can add SQL statements and functions to a view and present the data as
--if the data were coming from one single table.
--A view is created with the CREATE VIEW statement. 

--CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

--1. Create a view which lists the title, isbn and price per page of
--the books which topic is thriller.
create view thriller_books_2 as
select title, isbn, price/number_of_pages as "price_per_page"
from book_library.books
where topic='Thriller'
;

select *
from thriller_books_2
;
--2. Create a view which lists the last 10 borrowing with the customer name and the book title.
select *
from book_library.borrowing
order by borrowing_date asc 
fetch first 10 rows only
;

create view borrowings as
select borrow.*, customer.first_name||' '||customer.last_name as "Name", book.title
from (select *
    from book_library.borrowing
    order by borrowing_date asc 
    fetch first 10 rows only) borrow
inner join book_library.customers customer on borrow.customer_id=customer.library_card_number
inner join book_library.book_items item on item.book_item_id=borrow.book_item_id
inner join book_library.books book on book.book_id=item.book_id
;
--3. Create a view which lists the title, isbn and price per page of the books which topic --is thriller. 

---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
--Grant: 
--SQL Grant command is specifically used to provide privileges 
--to database objects for a user. 
--This command also allows users to grant permissions to other users too. 

--Syntax: 
grant privilege_name on object_name to {user_name | public | role_name} 
;
grant select on house to panovics;
grant select, insert on house to panovics;
grant all on house to panovics;
grant delete(price) on house to panovics;

--Here privilege_name is which permission has to be granted, 
--object_name is the name of the database object, 
--user_name is the user to which access should be provided, 
--the public is used to permit access to all the users. 

--privilege_name: select, insert, update, delete, references, all


--create numbers table.
--example: 

--Revoke: 
--Revoke command withdraw user privileges on database objects if any granted. 
--It does operations opposite to the Grant command. 
--When a privilege is revoked from a particular user U, 
--then the privileges granted to all other users by user U will be revoked. 

--privilege_name: select, insert, update, delete, reference, all

--Syntax: 
revoke privilege_name on object_name from {user_name | public | role_name}
;
--example:
revoke select on house from panovics;
revoke all on house from public;

commit;
------------------------------------------------------
--1. Create the tenant table with the following columns: 
--   name: string 
--   house id: numeric, 
--   color of hair string. 
--The primary key of the table is the house id and the name together. The house id is a --foreign key which refers to the primary key of the house table. 

--2. Drop the constraint called tenant_fk of the tenant table. 

--3. Drop the primary key of the tenant table. 

--4. Add a primary key to the tenant table: the house id and the name together. 

--5. Change the type of the name column of the tenant table: it should be maximum 50 --character long string. 

--6. Rename the name column of the tenant table to name2; 


--7. Create a table called Student

--The columns of this table are:
--   ID: number max 5 digits
--   Name: char. sequence max 50, must be filled
--   B_Date: date, must be filled
--   Parent_ID: number max 6 digits
--   Height: number max 3 digits.
--The primary key of this is table is the ID column.
--The height values cannot be more than 250. The default value is 170 for the height.
--The Parent_ID is a foreign key referencing to the primary key of the HR.EMPLOYEES table.



--8.  Create the house table with the following columns: id: numeric type, 
--   name: string, it has to be filled, 
--   date of build: date type, 
--   zip code  fixed long string, 
--   price: numeric type. 
--The primary key of the table is the id.
--The name should be unique in the table. The price should be more than 100
--if it is filled.

--9.  Add a new column to the house table: house size which type is numeric.

--10. Drop the house size column of the house table.

--11. Rename the house table to house2;

----------------------------------
-----BOOK_LIBRARY

--1. List the name of the customer 
--   who has the highest  late_fee.
select first_name||' '||last_name
from book_library.customers customer inner join book_library.borrowing borrow 
on customer.library_card_number=borrow.customer_id
order by late_fee desc nulls last
fetch first rows with ties
;

select first_name||' '||last_name
from book_library.customers customer inner join book_library.borrowing borrow 
on customer.library_card_number=borrow.customer_id
where late_Fee = (select max(late_fee) from book_library.borrowing);

--2. List the second 3 book items which has largest theoretical value
select *
from book_library.book_items
order by theoretical_value desc nulls last
offset 3 rows fetch next 3 rows with ties
;
--3. Create a view which lists for each books (id, title)
--   how many times was it borrowed.