query_Michailidis.txt

-- A All the records from a table
select *
from books;

-- B A projection with 3 columns
select fname, lname, gender
from authors
Order by fname, lname, gender desc;

-- C Restriction Queries

select *
from books 
where genre = 'HORROR';

select *
from borrowers 
where lname like 'J%SON%';

select *
from authors 
where country_origin not like '%GREECE%';

select *
from copies 
WHERE pur_date >= '01-MAY-2017' AND pur_date <= '01-MAY-2019'; 

-- D Join Queries

-- d two tables
select *
from  loans l join copies c on l.cop_id= c.cop_id

-- d three tables
select *
from loans l join copies c on l.cop_id =  c.cop_id join books b on c.book_isbn = b.isbn;

-- d more tables
select *
from (authors a join books b on a.id =  b.author_id  join copies c on b.isbn = c.book_isbn) left outer join loans l on c.cop_id = l.cop_id;


-- E Aggregate Functions

--e1
select * from authors
where lname like 'K%G%' ;

--e2
select genre, count(*) from books
group by genre ;

--e3
select book_isbn, count(*)
from  loans l join copies c on l.cop_id= c.cop_id
group by book_isbn
having count(*) > = all (select count(*)
from  loans l join copies c on l.cop_id= c.cop_id
group by book_isbn);

--e4
select *
from authors a join books b on a.id = b.author_id
order by a.lname;

--e5
select COUNT (*)
from books
where to_char(pub_date, 'YYYY') LIKE '1981'

--e6
select a.fname, a.lname, b.genre
from authors a join books b on a.id = b.author_id
where b.genre = 'SCI-FI' or b.genre = 'HORROR';

--e7
select b.title
from (loans l join copies c on l.cop_id =  c.cop_id) join books b on c.book_isbn = b.isbn
where to_char(pub_date, 'MON') LIKE 'OCT';

--e8
select *
from (select count(*) dead
from authors
where date_death is not null) d_authors, (select count(*) alive
from authors
where date_death is null) a_authors;

--e9
select fname, lname
from authors
where date_death is not null and date_death - date_birth = (select max(date_death - date_birth) from authors where date_death is not null);

--e10

select fname, lname, date_birth
from authors
where date_birth = (select min(date_birth) from authors where date_death is null);

--e11
select a.lname, count(*)
from authors a join books b on a.id = b.author_id
group by a.lname;

--e12
select a.fname, a.lname
from (authors a join books b on a.id =  b.author_id  join copies c on b.isbn = c.book_isbn) left outer join loans l on c.cop_id = l.cop_id
where l.loan_date is null;

--e13
select b.isbn, b.title, b.genre
from (books b join copies c on b.isbn = c.book_isbn) left outer join loans l on c.cop_id = l.cop_id
where l.loan_date is null;

--e14
select to_char(pub_date, 'YYYY'), sum(price)
from books
group by to_char(pub_date, 'YYYY')
order by to_char(pub_date, 'YYYY') asc;

--e15
select gender, sum(price)
from books b join authors a on a.id = b.author_id
group by gender;

--e16
select isbn, count(*)
from  books b join copies c on b.isbn = c.book_isbn
group by isbn
having count(*) > = all (select count(*) "Total Copies"
from  books b join copies c on b.isbn = c.book_isbn
group by isbn);

--e17

select *
from authors 
where country_origin not like 'ENGLAND';

--e18

select bor.fname, bor.lname
from books b join copies c on b.isbn = c.book_isbn join loans l on c.cop_id = l.cop_id join borrowers bor on l.b_id = bor.id
where b.title  like 'BLUES FOR A BLACK CAT';

--e19

select a.fname, sum(n_pages) "SUM OF PAGES"
from authors a join books b on a.id = b.author_id
group by a.fname
order by sum(n_pages) desc;

--e20
select b.isbn, b.title
from authors a join books b on a.id = b.author_id
where a.country_origin like 'ENGLAND' and b.price = (select max(b.price) from authors a join books b on a.id = b.author_id where a.country_origin like 'ENGLAND');

-- F Bonus

--f1
with temp1 as 
(select bor.id, sum(b.n_pages) as sum_pages from books b join copies c on b.isbn = c.book_isbn join loans l on c.cop_id = l.cop_id join borrowers bor on l.b_id = bor.id
group by bor.id)
select bor.id, bor.fname, bor.lname, sum_pages from temp1 t join borrowers bor on t.id = bor.id
where sum_pages >= all (select max(sum_pages) from temp1);

--f2
with temp1 as 
(select b_id, count(*) as total_loans from loans
group by b_id)
select bor.id, bor.fname, bor.lname, total_loans from temp1 t join borrowers bor on t.b_id = bor.id
where total_loans >= all (select max(total_loans) from temp1);

--f3
select bor.gender, count(*) as num_loans from loans l join borrowers bor on l.b_id = bor.id
group by bor.gender

--f4
select fname, lname, concat(SUBSTR(fname,0,3), SUBSTR(lname,0,4)) as username
from borrowers;

--f5
select loan_date, loan_date+14 as Return_Date
from loans;

--f6
with temp1 as 
(select author_id, sum(n_pages) as sum_pages from books group by author_id)
select author_id, a.fname, a.lname from temp1 t join authors a on t.author_id = a.id
where sum_pages >= all (select max(sum_pages) from temp1);

--f7
select genre, count(*)
from books
group by genre
having count(*) >= all (select count(*)
from books
group by genre);

--f8
with temp1 as 
(select to_char (loan_date, 'YYYY') as year, COUNT(*) as n_loans from loans group by to_char (loan_date, 'YYYY'))
select year from temp1 
where n_loans >= all (select max(n_loans) from temp1);

--f9
select *
from books b join authors a on b.author_id = a.id
where a.country_origin like 'GREECE';

-- f10

select *
from books 
where title like '%MISERY%';