Banco de Dados Fabrica_max

Prévia do material em texto

create database fabrica_7517202
go
use fabrica_7517202
go
create table cliente(
	idCliente int identity (1,1),
	razao_social varchar(60) not null,
	nome_fantasia varchar(60),
	cnpj varchar(14) not null,
	constraint PK_cliente_idCliente primary key (idCliente)
)
go
create table lote(
	idLote int identity (1,1),
	idCliente int,
	dt_venda datetime not null,
	qtde_lote int not null,
	vlr_lote decimal(8,2),
	constraint PK_lote_idlote primary key (idLote),
	constraint FK_lote_idcliente foreign key (idCliente) REFERENCES cliente (idCliente)
)
go
create table produto
(	idProduto int identity(1,1),
	descricao varchar(100) not null,
	sabor varchar(50) not null,
	tipo_Embalagem varchar(100) not null,
	vlr_unitario decimal(8,2),
	constraint PK_produto_idProduto primary key (idProduto),
)
go
create table ingredientes(
	idIngredientes int identity(1,1),
	descricao varchar(100) not null,
	qtde_estoque int not null,
	dt_retirada date not null,
	unid_medida varchar(50),
	constraint PK_ingredientes_idIngredientes primary key (idIngredientes),
)
go
create table ItensLote(
	idProduto int,
	idLote int,
	qtde_prod int not null,
	constraint PK_Itens_lote primary key (idProduto,idlote),
	constraint FK_Itenslote_idlote foreign key (idLote) REFERENCES lote(idLote),
	constraint FK_Itenslote_idProduto foreign key (idProduto) REFERENCES produto(idProduto)
	)
go
create table formula(
	idProduto int,
	idIngredientes int,
	qtde_utilizada int not null,
	constraint PK_formula primary key (idProduto,idIngredientes),
	constraint FK_formula_idProduto foreign key (idProduto) REFERENCES produto(idProduto),
	constraint FK_formula_idIngredientes foreign key (idIngredientes) REFERENCES ingredientes(idIngredientes)
)
use fabrica_7517202
/*Inserindo Cliente*/
insert into cliente(razao_social,nome_fantasia, cnpj)
values ('frutiquelo', 'frutiquelo inc. ltda','123456')
insert into cliente(razao_social,nome_fantasia, cnpj)
values ('icegurt', 'icegurt é bom','789123')
insert into cliente(razao_social,nome_fantasia, cnpj)
values ('nestlé', 'Kibom','456789')
insert into cliente(razao_social,nome_fantasia, cnpj)
values ('raspades', 'zezinho panificadora ltda.','123456')
/*Setando formato de data em BR*/
set dateformat dmy
/*Inserindo Lotes */
insert into lote(idCliente,dt_venda, qtde_lote, vlr_lote)
values (1, '22/02/2019',200,600.4), (1, '15/04/2019',100,310), (1, '13/05/2019',100,315), (1, '10/07/2019',100,350)
insert into lote(idCliente,dt_venda, qtde_lote, vlr_lote)
values (2, '23/01/2019',130,600.2), (2, '16/04/2019',100,315), (2, '29/05/2019',100,317)
insert into lote(idCliente,dt_venda, qtde_lote, vlr_lote)
values (3, '22/05/2019',200,600.6), (3, '17/04/2019',100,318), (3, '29/06/2019',100,317)
insert into lote(idCliente,dt_venda, qtde_lote, vlr_lote)
values (4, '22/07/2019',200,600.8), (4, '15/04/2019',100,319), (4, '13/09/2019',100,314)
/*Inserindo Produtos*/
insert into produto(descricao,sabor, tipo_Embalagem, vlr_unitario)
values ('Picolé ao Leite','Morango','Palito', 3.1), ('Picolé de fruta','Uva','Palito', 3.15), ('Sorvete Trufado','Chocolate','Pote', 15.1),
		('Picolé ao Leite','Chocolate','Palito', 3.5), ('Picolé ao Leite','Geninpapo','Palito', 3.99), ('Sorvete Trufado','Uva','Pote', 20.5)
/*Inserindo Itens Lotes*/
insert into ItensLote(idProduto,idLote, qtde_prod)
values (1,1,150),(2,1,50),
		(1,2,150),(2,2,50),
		(3,3,50),(4,3,50),
		(4,4,50),(1,4,50),
		(5,5,80),(2,5,20),
		(2,6,30),(3,6,100),
		(3,7,70),(4,7,30),
		(2,8,60),(1,8,40),
		(3,9,170),(2,9,30),
		(4,10,70),(5,10,30),
		(2,11,40),(3,11,60),
		(1,12,175),(2,12,25),
		(3,13,50),(2,13,50),
		(4,14,85),(1,14,15)
/* Atualizando valor dos proddutos para acrescimo de 10%*/
update produto set							/*ou*/
vlr_unitario = vlr_unitario * 1.1 /* vlr_unitario = vlr_unitario + (vlr_unitario * 0.10) */
select razao_social,nome_fantasia from cliente 
select*from lote
select distinct dt_venda from lote
/* Clausula Where par pesquisar de acordo com um intervalo especifico*/
select*from lote where dt_venda between ('01/02/2019') and ('28/02/2019')
select*from lote where month(dt_venda) = '2' and year (dt_venda) = '2019' 
/*Pesquisando em Ordem alfabetica*/
select*from lote order by dt_venda desc,dt_venda
SELECT TOP 3 * FROM produto
select top 80 percent * from produto
select * from cliente where razao_social like '%a%'
/*Renomeando colunas*/
select idCliente as Código, razao_social as Razão, nome_fantasia as Fantasia, cnpj from cliente
/* relatório com Inner Join*/
select razao_social as 'Razão Social',
		nome_fantasia as ' Nome Fantasia', 
		convert(varchar, dt_venda,103) as 'Data de Venda',
	 qtde_lote as 'QTDE. Produtos', 
		vlr_lote as 'Valor Lote'
from cliente inner join lote on cliente.idCliente = lote.idCliente
/* Sem Inner Join*/
SELECT razao_social, nome_fantasia, dt_venda, qtde_lote, vlr_lote
FROM cliente, lote
WHERE cliente.idCliente = lote.idCliente and month(dt_venda) = '2' and year (dt_venda) = '2019'
select razao_social as 'Razão Social',
		nome_fantasia as ' Nome Fantasia', 
		convert(varchar, dt_venda,103) as 'Data de Venda',
	 qtde_lote as 'QTDE. Produtos', 
		vlr_lote as 'Valor Lote'
from cliente left join lote on cliente.idCliente = lote.idCliente
/*Consultando Produtos com Total com Inner Join*/
select lote.idLote as 'Identificação do Lote',
		convert (varchar, dt_venda, 103) as 'Data de Venda', 
		qtde_lote as 'Quantidade do Lote',
		vlr_lote as 'Valor do Lote',
		descricao as 'Descrição do Produto',
		vlr_unitario as 'Valor Unitário ',
		qtde_prod as 'Quantidade do Produto',
		vlr_unitario * qtde_prod as 'Valor Total'
from lote inner join ItensLote on lote.idLote = ItensLote.idlote inner join produto on produto.idProduto = ItensLote.idProduto
order by lote.idLote
/*consultando Produtos com Total Sem Inner Join*/
select lote.idLote as 'Identificação do Lote',
		convert (varchar, dt_venda, 103) as 'Data de Venda', 
		qtde_lote as 'Quantidade do Lote',
		vlr_lote as 'Valor do Lote',
		descricao as 'Descrição do Produto',
		vlr_unitario as 'Valor Unitário ',
		qtde_prod as 'Quantidade do Produto',
		vlr_unitario * qtde_prod as 'Valor Total'
from lote, ItensLote,produto
WHERE lote.idlote = ItensLote.idlote and produto.idProduto = ItensLote.idProduto
order by lote.idLote
/*Relatório aonde os clientes nunca compraram lote*/
select * from cliente
where idCliente not in (select idCliente from lote)
/*Relatório aonde os produtos nunca estiveram em nenhum lote*/
select * from produto
where idProduto not in (select idProduto from ItensLote)
/*Retorna o N° de linhas de uma tabela */
select count(*) from cliente
/*Retorna o maior valor de um determinado atributo*/
select max(idCliente) from cliente
/*Retorna o menor valor de um determinado atributo*/
select min(idcliente) from cliente
/*Retorna a Somatória de valores de um atributo*/
select SUM(vlr_lote) from lote
/*Retorna o Valor médio da soma de valores de um determinado atributo*/
select AVG(vlr_lote) from lote
/*Retornando relatório de lotes junto com a soma do valor total e valor médio de cada lote*/
select count(idlote) as 'Somatória do Total de Lotes', SUM(vlr_lote) AS 'Valor Lote', AVG(vlr_lote)as 'valor médio' from lote
/*Relatório de lotes de um determinado mês e ano*/
SELECT MONTH(dt_venda) AS 'MÊS',
		YEAR(dt_venda) AS 'ANO',
		SUM(vlr_lote) FROM lote GROUP BY MONTH(dt_venda), YEAR(dt_venda)
/*Agrupamento da descição de produtos contando o total de produtos de uma determinada descricão*/
select descricao, sabor, count(idLote) as 'Total'
from produto inner join ItensLote on ItensLote.idProduto = produto.idProduto group by descricao,sabor