Banco de dados de trabalho Sript de Criação



Baixar 90,61 Kb.
Encontro24.04.2018
Tamanho90,61 Kb.

Banco de dados de trabalho




Sript de Criação

-- MySQL Administrator dump 1.4


--
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;


/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Create schema loja


--

CREATE DATABASE IF NOT EXISTS loja;


USE loja;

--
-- Definition of table `cidade`


--

DROP TABLE IF EXISTS `cidade`;


CREATE TABLE `cidade` (
`cidCodigo` int(10) unsigned NOT NULL auto_increment,
`cidNome` text,
`cidUF` char(2) default NULL,
PRIMARY KEY (`cidCodigo`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cidade`


--

/*!40000 ALTER TABLE `cidade` DISABLE KEYS */;


INSERT INTO `cidade` (`cidCodigo`,`cidNome`,`cidUF`) VALUES
(1,'Formiga','MG'),
(2,'Ribeirão Preto','SP'),
(3,'Pains','MG'),
(4,'Arcos','MG'),
(5,'Campo Belo','MG'),
(6,'Franca','SP'),
(7,'Petrópolis','RJ'),
(8,'Brasília','DF');
/*!40000 ALTER TABLE `cidade` ENABLE KEYS */;

--
-- Definition of table `cliente`


--

DROP TABLE IF EXISTS `cliente`;


CREATE TABLE `cliente` (
`cliCodigo` int(10) unsigned NOT NULL auto_increment,
`cli_cidCodigoNascimento` int(10) unsigned NOT NULL,
`cli_cidCodigoEndereco` int(10) unsigned NOT NULL,
`cliNome` text,
`cliEndereco` text,
PRIMARY KEY (`cliCodigo`),
KEY `Cliente_FKIndex1` (`cli_cidCodigoEndereco`),
KEY `Cliente_FKIndex2` (`cli_cidCodigoNascimento`),
CONSTRAINT `cliente_ibfk_1` FOREIGN KEY (`cli_cidCodigoEndereco`) REFERENCES `cidade` (`cidCodigo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cliente_ibfk_2` FOREIGN KEY (`cli_cidCodigoNascimento`) REFERENCES `cidade` (`cidCodigo`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cliente`


--

/*!40000 ALTER TABLE `cliente` DISABLE KEYS */;


INSERT INTO `cliente` (`cliCodigo`,`cli_cidCodigoNascimento`,`cli_cidCodigoEndereco`,`cliNome`,`cliEndereco`) VALUES
(1,2,1,'Fernando Paim Lima','Rua A, 287, Centro'),
(2,3,1,'Joãozinho','Rua B, 32, Água Vermelha'),
(3,1,1,'Maria','Rua X, 322, Engenho de Serra');
/*!40000 ALTER TABLE `cliente` ENABLE KEYS */;

--
-- Definition of table `produto`


--

DROP TABLE IF EXISTS `produto`;


CREATE TABLE `produto` (
`proCodigo` int(10) unsigned NOT NULL auto_increment,
`proNome` text,
`proPrecoCusto` float default NULL,
`proPrecoVenda` float default NULL,
`proQtdEstoque` float default NULL,
PRIMARY KEY (`proCodigo`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `produto`


--

/*!40000 ALTER TABLE `produto` DISABLE KEYS */;


INSERT INTO `produto` (`proCodigo`,`proNome`,`proPrecoCusto`,`proPrecoVenda`,`proQtdEstoque`) VALUES
(1,'Caneta Vermelha OFFICE DEALER',2,2.5,1000),
(2,'Caneta Azul BIC',0.3,0.8,6999),
(3,'Caneta Azul OFFICE DEALER',2,2.5,500),
(4,'Grampeador EAGLE',3,5,200),
(5,'Estilete CIS',1,2,100),
(6,'Borracha MERCURIAL',0.2,1,1000),
(7,'Borracha VENUS',0.1,0.5,700);
/*!40000 ALTER TABLE `produto` ENABLE KEYS */;

--
-- Definition of table `tipovenda`


--

DROP TABLE IF EXISTS `tipovenda`;


CREATE TABLE `tipovenda` (
`tivCodigo` int(10) unsigned NOT NULL auto_increment,
`tivNome` text,
`tivDescricao` text,
PRIMARY KEY (`tivCodigo`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tipovenda`


--

/*!40000 ALTER TABLE `tipovenda` DISABLE KEYS */;


INSERT INTO `tipovenda` (`tivCodigo`,`tivNome`,`tivDescricao`) VALUES
(1,'Avista em dinheiro','Pagamento no ato da compra em dinheiro'),
(2,'A praso em dinheiro','Pagamento a praso, se cartões, aceitamos cheques ou dinheiro vivo.'),
(3,'A praso no Cartão','Recebemos o dinheiro na hora, mas, o cliente paga parcelado para a operadora do cartão'),
(4,'Avista no Cartão','Recebemos na hora, mas tem a porcentagem da operadora do cartão');
/*!40000 ALTER TABLE `tipovenda` ENABLE KEYS */;

--
-- Definition of table `venda`


--

DROP TABLE IF EXISTS `venda`;


CREATE TABLE `venda` (
`vedCodigo` int(10) unsigned NOT NULL auto_increment,
`ved_cliCodigo` int(10) unsigned NOT NULL,
`ved_tivCodigo` int(10) unsigned NOT NULL,
`ved_venCodigo` int(10) unsigned NOT NULL,
`vedDataVenda` date default NULL,
`vedHorario` time default NULL,
`vedComplemento` text,
PRIMARY KEY (`vedCodigo`),
KEY `Venda_FKIndex1` (`ved_venCodigo`),
KEY `Venda_FKIndex2` (`ved_tivCodigo`),
KEY `Venda_FKIndex3` (`ved_cliCodigo`),
CONSTRAINT `venda_ibfk_1` FOREIGN KEY (`ved_venCodigo`) REFERENCES `vendedor` (`venCodigo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `venda_ibfk_2` FOREIGN KEY (`ved_tivCodigo`) REFERENCES `tipovenda` (`tivCodigo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `venda_ibfk_3` FOREIGN KEY (`ved_cliCodigo`) REFERENCES `cliente` (`cliCodigo`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `venda`


--

/*!40000 ALTER TABLE `venda` DISABLE KEYS */;


INSERT INTO `venda` (`vedCodigo`,`ved_cliCodigo`,`ved_tivCodigo`,`ved_venCodigo`,`vedDataVenda`,`vedHorario`,`vedComplemento`) VALUES
(1,1,1,1,'2009-02-01','12:00:00','Compra realizada sem problemas, cliente muito bom'),
(2,3,2,1,'2009-02-01','08:00:00','Compra dividida em uma entrada mais 5 pagamentos, total de juros por pagamento 5%'),
(3,2,4,3,'2009-02-21','17:00:00','Compra efetuada com sucesso, obs: não houve desconto algum, pois, foi comprado com cartão e devemos pagar uma porcentagem para a operadora.');
/*!40000 ALTER TABLE `venda` ENABLE KEYS */;

--
-- Definition of table `venda_produto`


--

DROP TABLE IF EXISTS `venda_produto`;


CREATE TABLE `venda_produto` (
`vep_vedCodigo` int(10) unsigned NOT NULL,
`vep_proCodigo` int(10) unsigned NOT NULL,
`vepQuantidade` int(10) unsigned NOT NULL,
PRIMARY KEY (`vep_vedCodigo`,`vep_proCodigo`),
KEY `Venda_has_Produto_FKIndex1` (`vep_vedCodigo`),
KEY `Venda_has_Produto_FKIndex2` (`vep_proCodigo`),
CONSTRAINT `venda_produto_ibfk_1` FOREIGN KEY (`vep_vedCodigo`) REFERENCES `venda` (`vedCodigo`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `venda_produto_ibfk_2` FOREIGN KEY (`vep_proCodigo`) REFERENCES `produto` (`proCodigo`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `venda_produto`


--

/*!40000 ALTER TABLE `venda_produto` DISABLE KEYS */;


INSERT INTO `venda_produto` (`vep_vedCodigo`,`vep_proCodigo`,`vepQuantidade`) VALUES
(1,2,4),
(1,4,4),
(1,5,1),
(1,7,1),
(2,1,2),
(2,2,1),
(2,3,1),
(3,1,5),
(3,3,2);
/*!40000 ALTER TABLE `venda_produto` ENABLE KEYS */;

--
-- Definition of table `vendedor`


--

DROP TABLE IF EXISTS `vendedor`;


CREATE TABLE `vendedor` (
`venCodigo` int(10) unsigned NOT NULL auto_increment,
`venNome` text,
`venTelefone` text,
PRIMARY KEY (`venCodigo`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `vendedor`


--

/*!40000 ALTER TABLE `vendedor` DISABLE KEYS */;


INSERT INTO `vendedor` (`venCodigo`,`venNome`,`venTelefone`) VALUES
(1,'Roberval','1111 1111'),
(2,'Cirilo','2222 2222'),
(3,'Joaquim','3333 3333');
/*!40000 ALTER TABLE `vendedor` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Revisão
Para conseguirmos extrair os preços totais de cada venda podemos seguir o seguinte raciocínio


  • Primeiro listamos os códigos das vendas junto com a multiplicação de produtos comprados pelos seus preços.

SELECT


vedCodigo, (proPrecoVenda * vepQuantidade) as 'preco_por_produtos_comprados'

FROM


venda join venda_produto join produto

WHERE


vedCodigo = vep_vedCodigo and

proCodigo = vep_proCodigo

ORDER BY

vedCodigo;





  • Depois é só agrupar por venda e somar o total de cada grupo.

SELECT


vedCodigo, sum(proPrecoVenda * vepQuantidade) as 'TOTAL DE CADA VENDA'

FROM


venda join venda_produto join produto

WHERE


vedCodigo = vep_vedCodigo and

proCodigo = vep_proCodigo

GROUP BY

vedCodigo

ORDER BY

vedCodigo;





  • Para acrescentar mais dados de cada venda a esta consulta:

SELECT


vedDataVenda as 'Data de Venda', cliNome as 'Cliente', tivNome as 'Tipo da venda', sum(proPrecoVenda * vepQuantidade) as 'TOTAL DE CADA VENDA'

FROM


venda join venda_produto join produto join cliente join tipovenda

WHERE


vedCodigo = vep_vedCodigo and

proCodigo = vep_proCodigo and

cliCodigo = ved_cliCodigo and

tivCodigo = ved_tivCodigo

GROUP BY

vedCodigo

ORDER BY

vedCodigo;



Exercício de Revisão


  1. Escreva os seguintes scripts de “inserção” de dados da estrutura acima:

    1. a. Insira o vendedor : João Kleber, seu telefone é 33210000

    2. b. Insira o produto: Vinagre que custou para a empresa R$ 2,00 mas será vendido por R$ 3,00. A empresa comprou de uma só vez 30 unidades do fornecedor “Vinagres S.A.”




  1. 2. Escreva os seguintes scripts de deleção:

    1. a. Exclua o vendedor de código “1”.

    2. b. Exclua todas as vendas do vendedor de código “1”

    3. c. Exclua todos os registros da tabela fornecedor.

  2. 3. Escreva os seguintes scripts de atualização:

    1. a. Atualize o nome do vendedor de código 2 para José Roberto.

    2. b. Atualize o fornecedor do produto de código 1 para o fornecedor “Buxas S.A.”



Obs: O fornecedor “Buxas S.A.” terá o código 4.

  1. 4. Escreva os seguites scripts de consultas SQL:

    1. a. Retorne todas as informações de todos fornecedores.

    2. b. Retorne todas as informações do fornecedor de código 3.

    3. c. Retorne somente o nome do fornecedor de telefone “33210000”.

    4. d. Retorne somente os nomes de todos os produtos do fornecedor “Buxas S.A.”.

  2. Obs: O fornecedor “Buxas S.A.”

  3. 5. Escreva os seguites scripts de consultas SQL(A partir daqui, consultas utilizando junções)

    1. a. Retorne o nome dos produtos que já foram vendidos e seu preço que custam menos de R$30,00.

    2. b. Retorne o nome dos Vendedores e os dados de suas vedas (código, data e horário) .

    3. c. Retorne o nome e o preço de todos os produtos que forma vendidos “a prazo no cartão”.



  4. 6. Escreva os seguites scripts de consultas SQL(A partir daqui, consultas utilizando junções e funções agregadas)

    1. a. Retorne o nome dos Clientes que compraram e a quantidade dos produtos que eles compraram.

    2. b. Retorne o nome dos Vendedores e soma total (preço) de suas vendas no dia 01/02/2009.

    3. c. Retorne o nome e a média de todos os produtos que forma vendidos “a prazo”.





©livred.info 2017
enviar mensagem

    Página principal