--a) Selecionar todas as informações 
--sobre todos os a utores (authors);

select * from authors

--b) Selecionar o código, primeiro nome e
-- último nome
--de todos os autores (authors) em
--ordem alfabética pelo último nome 
--(au_lname);

select au_id, au_fname, au_lname
from authors
order by au_lname

--c) Selecionar todas as informações 
--sobre todos os autores (authors) 
--da cidade "Oakland";

select * 
from authors
where city = 'Oakland'

--d) Selecionar o código, título e preço 
--dos livros (titles) em ordem decrescente 
--de preço;

select title_id, title, price
from titles
order by price desc

--e) Selecionar o código, título e preço 
--dos livros (titles) cujo preço é 
--superior a $15;

select title_id, title, price
from titles
where price > 15

--f) Selecionar o código e título dos 
--livros (titles), primeiro e último 
--nome dos seus respectivos autores 
--(authors);

select t.title_id, t.title, 
       a.au_fname, a.au_lname
from titles as t 
     inner join titleauthor as ta
     on (t.title_id = ta.title_id)
     inner join authors as a
     on (ta.au_id = a.au_id)
order by t.title_id, t.title

--g) Selecionar o código e título 
--dos livros e quantidade de autores 
--de cada livro (titles);

select t.title_id, t.title, 
       count (*) as qtd
from titles as t 
     inner join titleauthor as ta
     on (t.title_id = ta.title_id)
     inner join authors as a
     on (ta.au_id = a.au_id)
group by t.title_id, t.title

--h) Selecionar o código e título dos 
--livros (titles)que foram escritos por 
--mais de 2 autores;

select t.title_id, t.title, 
       count (*) as qtd
from titles as t 
     inner join titleauthor as ta
     on (t.title_id = ta.title_id)
     inner join authors as a
     on (ta.au_id = a.au_id)
group by t.title_id, t.title
having count (*) > 2

select * from
(select t.title_id, t.title, 
       count (*) as qtd
from titles as t 
     inner join titleauthor as ta
     on (t.title_id = ta.title_id)
     inner join authors as a
     on (ta.au_id = a.au_id)
group by t.title_id, t.title) as tabela
where qtd > 2

--i) Selecionar o primeiro nome (fname), inicial do nome
-- do meio (minit) e o último nome (lname) dos 
-- empregados (employee);

select fname, minit, lname
from employee


--j) Selecionar o código e a descrição dos 
--cargos (jobs); 

select job_id, job_desc
from jobs

--k) Selecionar o primeiro nome, inicial do nome do 
-- meio, ultimo nome dos empregados (employee) 
--e a descrição do seu respectivo cargo (jobs);

select fname, minit, lname, job_desc
from employee as e, jobs as j
where e.job_id = j.job_id
order by fname

select fname, minit, lname, job_desc
from employee as e inner join jobs as j
     on (e.job_id = j.job_id)
order by fname

--l) Selecionar o código, nome e a cidade das editoras 
--(publishers), ordenado pelo nome;

select pub_id, pub_name, city
from publishers 
order by pub_name

--m) Selecionar o código e título do livro (titles),
-- código e nome da respectiva editora(publishers);

select t.title_id, t.title, 
       p.pub_id, p.pub_name
from titles as t, publishers as p
where t.pub_id = p.pub_id

select t.title_id, t.title, 
       p.pub_id, p.pub_name
from titles as t, publishers as p
where t.pub_id = p.pub_id
      and p.pub_name like 'A%'

delete from titles as t
using publishers as p
where t.pub_id = p.pub_id
      and p.pub_name like 'A%'


--n) Selecionar o código e título do livro (titles),
--código e nome da respectiva editora
--(publishers), cuja editora se localiza na cidade de
--“Boston”;

select t.title_id, t.title, 
       p.pub_id, p.pub_name
from titles as t, publishers as p
where t.pub_id = p.pub_id
      and p.city = 'Boston'


--o) Selecionar o nome, endereço e cidade das lojas (
--stores) do estado "CA"; 

select stor_name, stor_address, city
from stores
where state = 'CA'

--p) Selecionar código livro, titulo e a quantidade 
--total de livros vendidos, em ordem decrescente 
--da quantidade total de livros vendidos;

select t.title_id, t.title, sum (s.qty) as total
from titles as t inner join sales as s
     on (t.title_id = s.title_id)
group by t.title_id, t.title
order by total desc

--q) Selecionar código livro, titulo do livro, código
-- e nome da loja (stores) e a quantidade
-- total de livros vendidos pela loja (nas diversa ven
--das realizadas), em ordem
---decrescente da quantidade de livros vendidos;

select t.title_id, t.title, st.stor_id, s.qty
from titles as t inner join sales as s
     on (t.title_id = s.title_id)
     inner join stores as st
     on (s.stor_id = st.stor_id)
order by s.qty desc


--r) Selecionar o código e nome da loja (stores), e a
--sua respectiva média de livros vendidos;

select st.stor_id, st.stor_name, avg(s.qty)
from stores as st inner join sales as s
     on (st.stor_id = s.stor_id)
group by st.stor_id, st.stor_name

--s) Selecionar os livros (titles) que nunca foram ve
-- ndidos;

select title
from titles
where title_id not in (select title_id from sales)

select title_id, title
from titles as t
where not exists 
    (select * from sales as s 
     where s.title_id = t.title_id)

select t.title_id, t.title
from titles as t left join sales as s
     on (s.title_id = t.title_id)
where qty is null


--t) Selecionar as editoras (publishers) que não edit
--aram nenhum livro. 

select pub_name
from publishers
where pub_id not in (select pub_id from titles)

select pub_name
from publishers as p
where not exists 
     (select * from titles as t
      where t.pub_id = p.pub_id)

select pub_name
from publishers as p left join titles as t
     on (t.pub_id = p.pub_id)
where t.pub_id is null