CREATE DATABASE ex9
GO
USE ex9
GO
CREATE TABLE editora (
codigo			INT				NOT NULL,
nome			VARCHAR(30)		NOT NULL,
site			VARCHAR(40)		NULL
PRIMARY KEY (codigo)
)
GO
CREATE TABLE autor (
codigo			INT				NOT NULL,
nome			VARCHAR(30)		NOT NULL,
biografia		VARCHAR(100)	NOT NULL
PRIMARY KEY (codigo)
)
GO
CREATE TABLE estoque (
codigo			INT				NOT NULL,
nome			VARCHAR(100)	NOT NULL	UNIQUE,
quantidade		INT				NOT NULL,
valor			DECIMAL(7,2)	NOT NULL	CHECK(valor > 0.00),
codEditora		INT				NOT NULL,
codAutor		INT				NOT NULL
PRIMARY KEY (codigo)
FOREIGN KEY (codEditora) REFERENCES editora (codigo),
FOREIGN KEY (codAutor) REFERENCES autor (codigo)
)
GO
CREATE TABLE compra (
codigo			INT				NOT NULL,
codEstoque		INT				NOT NULL,
qtdComprada		INT				NOT NULL,
valor			DECIMAL(7,2)	NOT NULL,
dataCompra		DATE			NOT NULL
PRIMARY KEY (codigo, codEstoque, dataCompra)
FOREIGN KEY (codEstoque) REFERENCES estoque (codigo)
)
GO
INSERT INTO editora VALUES
(1,'Pearson','www.pearson.com.br'),
(2,'Civilização Brasileira',NULL),
(3,'Makron Books','www.mbooks.com.br'),
(4,'LTC','www.ltceditora.com.br'),
(5,'Atual','www.atualeditora.com.br'),
(6,'Moderna','www.moderna.com.br')
GO
INSERT INTO autor VALUES
(101,'Andrew Tannenbaun','Desenvolvedor do Minix'),
(102,'Fernando Henrique Cardoso','Ex-Presidente do Brasil'),
(103,'Diva Marilia Flemming','Professora adjunta da UFSC'),
(104,'David Halliday','Ph.D. da University of Pittsburgh'),
(105,'Alfredo Steinbruch','Professor de Matematica da UFRS e da PUCRS'),
(106,'Willian Roberto Cereja','Doutorado em Linguistica Aplicada e Estudos da Linguagem'),
(107,'William Stallings','Doutorado em Ciencias da Computacão pelo MIT'),
(108,'Carlos Morimoto','Criador do Kurumin Linux')
GO
INSERT INTO estoque VALUES
(10001,'Sistemas Operacionais Modernos ',4,108.00,1,101),
(10002,'A Arte da Política',2,55.00,2,102),
(10003,'Calculo A',12,79.00,3,103),
(10004,'Fundamentos de Fisica I',26,68.00,4,104),
(10005,'Geometria Analitica',1,95.00,3,105),
(10006,'Gramática Reflexiva',10,49.00,5,106),
(10007,'Fundamentos de Fisica III',1,78.00,4,104),
(10008,'Calculo B',3,95.00,3,103)
GO
INSERT INTO compra VALUES
(15051,10003,2,158.00,'04/07/2021'),
(15051,10008,1,95.00,'04/07/2021'),
(15051,10004,1,68.00,'04/07/2021'),
(15051,10007,1,78.00,'04/07/2021'),
(15052,10006,1,49.00,'05/07/2021'),
(15052,10002,3,165.00,'05/07/2021'),
(15053,10001,1,108.00,'05/07/2021'),
(15054,10003,1,79.00,'06/08/2021'),
(15054,10008,1,95.00,'06/08/2021')


SELECT
    e.nome AS Editora,
    a.nome AS Autor,
    s.nome AS Livro,
    s.valor AS Valor_Unitario
FROM 
    compra c
INNER JOIN estoque s ON c.codEstoque = s.codigo
INNER JOIN autor a ON s.codAutor = a.codigo
INNER JOIN editora e ON s.codEditora = e.codigo
WHERE 
    c.qtdComprada > 0
GROUP BY 
    e.nome, a.nome, s.nome, s.valor
ORDER BY 
    e.nome, a.nome, s.nome;

SELECT
    s.nome AS Nome_do_Livro,
    SUBSTRING(e.site, 4, LEN(e.site) - 3) AS Site_Editora
FROM 
    estoque s
INNER JOIN editora e ON s.codEditora = e.codigo
WHERE 
    e.nome = 'Makron Books';

		SELECT
		s.nome AS Nome_do_Livro, 
		a.biografia AS Breve_Biografia
FROM estoque AS s
JOIN autor AS a ON s.codAutor = a.codigo
WHERE a.nome = 'David Halliday';

		select
		c.codigo as codigo_de_Compra,
		c.qtdComprada as Quantidade_de_Compra
		from compra c
		inner join estoque as s on c.codEstoque = s.codigo
		where s.nome = 'Sistemas Operacionais Modernos'

	SELECT 
	s.codigo AS Código_do_Livro,
	s.nome AS Nome_do_Livro
FROM estoque AS s
LEFT JOIN compra AS c ON s.codigo = c.codEstoque
WHERE c.codigo IS NULL;

SELECT DISTINCT 
c.codEstoque AS Código_do_Livro_Vendido, 
TRIM(s.nome) AS Nome_do_Livro_Vendido
FROM compra AS c
LEFT JOIN estoque AS s ON c.codEstoque = s.codigo
WHERE s.codigo IS NULL;

SELECT
    e.nome AS Nome_Editora,
    CASE
        WHEN LEN(e.site) > 10 THEN SUBSTRING(e.site, 5, LEN(e.site) - 3)
        ELSE e.site
    END AS Site_Editora
FROM 
    editora e
LEFT JOIN estoque s ON e.codigo = s.codEditora
WHERE 
    s.codigo IS NULL

	SELECT DISTINCT 
	a.nome AS Nome_do_Autor, 
                CASE 
                    WHEN a.biografia LIKE 'Doutorado%' THEN CONCAT('Ph.D.', SUBSTRING(a.biografia, 10))
                    ELSE a.biografia 
                END AS Biografia_do_Autor
FROM autor AS a
LEFT JOIN estoque AS s ON a.codigo = s.codAutor
WHERE s.codigo IS NULL;

select 
a.nome as Nome_do_autor,
max(s.valor) as Maior_Valor_do_Livro
from autor a
join estoque as s on a.codigo = s.codAutor
group By a.nome
order by Maior_Valor_do_Livro Desc;

Select 
e.nome as editora_nome,
avg(s.valor)as media_de_valores
from editora e
join estoque as s on e.codigo= s.codEditora
group by e.nome
order by media_de_valores asc;


SELECT 
codigo AS Código_da_Compra, 
       SUM(qtdComprada) AS Total_de_Livros_Comprados, 
       SUM(valor) AS Soma_dos_Valores_Gastos
FROM compra
GROUP BY codigo
ORDER BY codigo ASC;

select 
 s.nome as nome_do_livro,
 s.quantidade as quantidade, 
 e.nome as nome_editora,
 case
 when len(e.site)>10 then substring (e.site,5,len(e.site)-3)
 else e.site
 end as site_editora,
 case 
 when s.quantidade<5 then 'produto em ponto de pedido'
 when s.quantidade>=5 and s.quantidade<=10 then 'produto acabando'
 else 'estoque suficiente'
 end as status
 from estoque s
 inner join editora e on s.codEditora=e.codigo
 order by s.quantidade asc

 select
 s.nome as nome_livro,
 s.codigo as codigo_livro,
 a.nome as nome_autor,
 CONCAT(e.nome, 
 case when e.site is not null then concat( ' - ', e.site)
 else ''
 end) as Info_editora
 from estoque as s
 join autor as a on s.codautor= a.codigo
 join editora as e on s.codEditora = e.codigo;
 
 SELECT  c.codigo AS Codigo_Compra,
		DATEDIFF(DAY, c.dataCompra, GETDATE()) AS Dia,
		DATEDIFF(MONTH, c.dataCompra, GETDATE()) AS Mes
FROM compra AS c
 
SELECT codigo AS Codigo_da_Compra,
       SUM(valor) AS Soma_dos_Valores_Gastos
FROM compra
GROUP BY codigo
HAVING SUM(valor) > 200.00;