--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