use ex9
--1) Consultar nome, valor unitário, nome da editora e nome do autor dos livros do estoque que foram vendidos. Não podem haver repetições.
SELECT DISTINCT
    est.nome AS 'nome livro'
    , est.valor
    , edt.nome AS 'nome editora'
    , aut.nome AS 'nome autor'
FROM estoque AS est
    INNER JOIN compra AS cp
        ON cp.codEstoque = est.codigo
    INNER JOIN autor AS aut
        ON aut.codigo = est.codAutor
    INNER JOIN editora AS edt
        ON edt.codigo = est.codEditora
ORDER BY est.nome

--2) Consultar nome do livro, quantidade comprada e valor de compra da compra 15051
SELECT
    est.nome
    , com.qtdComprada
    , com.valor
FROM estoque AS est
    INNER JOIN compra as com
        ON com.codEstoque = est.codigo
WHERE com.codigo = 15051

--3) Consultar Nome do livro e site da editora dos livros da Makron books (Caso o site tenha mais de 10 dígitos, remover o www.).
SELECT
    est.nome AS 'nome livro'
    , CASE WHEN (LEN(edt.site) > 10)
        THEN
            SUBSTRING(edt.site,5,(LEN(edt.site)-4))
        ELSE
            edt.site
        END AS 'site'
FROM estoque AS est
    INNER JOIN editora AS edt
        ON edt.codigo = est.codEditora
WHERE edt.nome = 'Makron Books'

--4) Consultar nome do livro e Breve Biografia do David Halliday
SELECT
    est.nome AS 'nome do livro'
    ,   aut.nome AS 'nome do autor'
    ,   aut.biografia
FROM estoque AS est
    INNER JOIN autor AS aut
        ON aut.codigo = est.codAutor
WHERE aut.nome = 'David Halliday'

--5) Consultar código de compra e quantidade comprada do livro Sistemas Operacionais Modernos
SELECT
    com.codigo
    , com.qtdComprada
FROM compra AS com
    INNER JOIN estoque AS est
        ON com.codEstoque = est.codigo
WHERE est.nome = 'Sistemas Operacionais Modernos'

--6) Consultar quais livros não foram vendidos
SELECT
    est.codigo
    , est.nome
FROM estoque AS est
    LEFT OUTER JOIN compra AS com
        ON com.codEstoque = est.codigo
WHERE com.codigo IS NULL

--7) Consultar quais livros foram vendidos e não estão cadastrados
SELECT
    est.codigo
    , est.nome
    , com.codigo
    , com.codEstoque
    , com.qtdComprada
    , com.valor
    , com.dataCompra
FROM compra AS com
    LEFT OUTER JOIN estoque AS est
        ON com.codEstoque = est.codigo
WHERE est.codigo IS NULL
--8) Consultar Nome e site da editora que não tem Livros no estoque (Caso o site tenha mais de 10 dígitos, remover o www.)
SELECT
    edt.nome
    , CASE WHEN (LEN(edt.site) > 10)
        THEN
            SUBSTRING(edt.site,5,(LEN(edt.site)-4))
        ELSE
            edt.site
        END AS 'site'
FROM editora AS edt
    LEFT OUTER JOIN estoque AS est
        ON est.codEditora = edt.codigo
WHERE est.codigo IS NULL
--9) Consultar Nome e biografia do autor que não tem Livros no estoque (Caso a biografia inicie com Doutorado, substituir por Ph.D.)
SELECT
    aut.nome
    , CASE WHEN (aut.biografia LIKE 'Doutorado%')
        THEN
            'Ph.D.' + SUBSTRING(aut.biografia,10,(LEN(aut.biografia)-9))
        ELSE
            aut.biografia
        END AS 'biografia'
FROM autor as aut
    LEFT OUTER JOIN estoque AS est
        ON est.codAutor = aut.codigo
WHERE est.codigo IS NULL
--10) Consultar o nome do Autor, e o maior valor de Livro no estoque. Ordenar por valor descendente
SELECT
    aut.nome
    , MAX(est.valor) AS maior_valor
FROM autor AS aut
    INNER JOIN estoque AS est
        ON est.codAutor = aut.codigo
GROUP BY aut.nome
ORDER BY maior_valor DESC

--11) Consultar o código da compra, o total de livros comprados e a soma dos valores gastos. Ordenar por Código da Compra ascendente.
SELECT
    com.codigo
    , SUM(com.qtdComprada) AS 'total de livros comprados'
    , SUM(com.valor) AS 'soma dos valores gastos'
FROM compra AS com
GROUP BY com.codigo
ORDER BY com.codigo
--12) Consultar o nome da editora e a média de preços dos livros em estoque.Ordenar pela Média de Valores ascendente.
SELECT
    edt.nome
    , CAST(AVG(est.valor) AS DECIMAL(4,1)) AS media
FROM editora AS edt
    INNER JOIN estoque as est
        ON est.codEditora = edt.codigo
GROUP BY edt.nome
ORDER BY media
--13) Consultar o nome do Livro, a quantidade em estoque o nome da editora, o site da editora (Caso o site tenha mais de 10 dígitos, remover o www.), criar uma coluna status onde:
    --Caso tenha menos de 5 livros em estoque, escrever Produto em Ponto de Pedido
    --Caso tenha entre 5 e 10 livros em estoque, escrever Produto Acabando
    --Caso tenha mais de 10 livros em estoque, escrever Estoque Suficiente
    --A Ordenação deve ser por Quantidade ascendente
SELECT
    est.nome AS nome_livro
    , edt.nome AS nome_editora
    , CASE WHEN (LEN(edt.site) > 10)
        THEN
            SUBSTRING(edt.site,5,(LEN(edt.site)-4))
        ELSE
            edt.site
        END AS 'site'
    , CASE WHEN (est.quantidade < 5)
         THEN
            'Produto em Ponto de Pedido'
        WHEN (est.quantidade < 11)
         THEN
            'Produto Acabando'
        ELSE
            'Estoque Suficiente'
      END AS 'status'
FROM estoque AS est
    INNER JOIN editora AS edt
        ON edt.codigo = est.codEditora
ORDER BY est.quantidade

--14) Para montar um relatório, é necessário montar uma consulta com a seguinte saída: Código do Livro, Nome do Livro, Nome do Autor, Info Editora (Nome da Editora + Site) de todos os livros
    --Só pode concatenar sites que não são nulos
SELECT
    est.codigo AS cod_livro
    , est.nome AS nome_livro
    , aut.nome AS nome_autor
    , CASE WHEN (edt.site IS NOT NULL)
        THEN
            edt.nome + ' - ' + edt.site
        ELSE
            edt.nome
        END AS 'Info Editora'
FROM estoque AS est
    INNER JOIN autor AS aut
        ON aut.codigo = est.codAutor
    INNER JOIN editora AS edt
        ON edt.codigo = est.codEditora
--15) Consultar Codigo da compra, quantos dias da compra até hoje e quantos meses da compra até hoje
SELECT
    com.codigo
    , DATEDIFF(DAY,com.dataCompra,GETDATE()) AS 'dias da compra até hoje'
    , DATEDIFF(MONTH,com.dataCompra,GETDATE()) AS 'meses da compra até hoje'
FROM compra as com

--16) Consultar o código da compra e a soma dos valores gastos das compras que somam mais de 200.00
SELECT
    com.codigo
    , SUM(com.valor) as 'valor'
FROM compra AS com
GROUP BY com.codigo
HAVING SUM(com.valor) > 200