Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
Operações de 'DDL' e 'DML'/Banco de Dados, Tabelas e Carga de Dados.sql /* OBS.: Onde estiver a instrução "Pressione CTRL + Enter", execute esta instrução para efetuar a operação. Basta seguir a sequência de instruções conforme numeradas. */ -- Instrução para criar o Banco de Dados create database turma3003; -- 1. "Pressione CTRL + Enter" -- Instrução para "ativar" o Banco de Dados onde serão efetuadas as operações abaixo (categoria "DDL" e "DML") use turma3003; -- 2. "Pressione CTRL + Enter" -- -- Table structure for table `cliente` -- CREATE TABLE `cliente` ( `CliCod` varchar(5) NOT NULL, `CliNome` varchar(100) DEFAULT NULL, `PFPJ` char(1) DEFAULT NULL, `CPFCNPJ` varchar(18) DEFAULT NULL, `InclusaoDt` datetime DEFAULT NULL, `Cidade` varchar(50) DEFAULT NULL, `VendCod` int(11) DEFAULT NULL, `UFSigla` char(2) DEFAULT NULL, PRIMARY KEY (`CliCod`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 3. "Pressione CTRL + Enter" -- -- Dumping data for table `cliente` -- INSERT INTO `cliente` VALUES ('c0001','Adhemar Thomaz','f','251.760.080-50','2015-11-14 00:15:08','Rio de Janeiro',1,'RJ'), ('c0002','Petrobras','j','91.364.862/0001-59','2015-11-14 00:15:10','Rio de Janeiro',1,'RJ'), ('c0003','MARCOS MENDES VIEIRA DA SILVA','f','299.010.811-49','2015-11-14 00:15:11','Campinas',10,'SP'), ('c0004','Vale','j','72.538.175/0001-68','2015-11-14 00:15:12','Campinas',10,'SP'), ('c0005','MARCOS VINICIUS RODRIGUES MELO','f','362.231.715-95','2015-11-14 00:15:13','Belo Horizonte',20,'MG'), ('c0006','Pão de Açúcar','j','34.372.585/0001-92','2015-11-14 00:15:14','Belo Horizonte',20,'MG'), ('c0007','MORVAN MARQUES COUTINHO','f','776.676.175-03','2015-11-14 00:15:15','Manaus',30,'AM'), ('c0008','Gerdau','j','78.460.134/0001-82','2015-11-14 00:15:16','Manaus',30,'AM'), ('c0009','MURILLO RODRIGUES BORGES','f','183.406.506-29','2015-11-14 00:15:17','Salvador',40,'BA'), ('c0010','Petrobras','j','91.364.862/0001-59','2015-11-14 00:15:18','Santo Amaro',40,'BA'), ('c0011','THIAGO DIAS GEDDES','f','127.788.172-39','2015-11-14 00:15:19','Foz do Iguaçu',50,'PR'), ('c0012','Vale','j','72.538.175/0001-68','2015-11-14 00:15:20','Foz do Iguaçu',50,'PR'), ('c0013','VINICIUS FERNANDES CUNHA','f','487.295.554-42','2015-11-14 00:15:21','Uberaba',60,'MG'), ('c0014','Pão de Açúcar','j','34.372.585/0001-92','2015-11-14 00:15:22','Uberaba',60,'MG'), ('c0015','MARCELO DOS SANTOS','f','187.752.263-59','2015-11-14 00:15:23','Manaus',70,'AM'), ('c0016','Gerdau','j','78.460.134/0001-82','2015-11-14 00:15:24','Manaus',70,'AM'), ('c0017','Leonardo de Mello','f','233.616.378-05','2015-11-14 00:15:25','Rio de Janeiro',80,'RJ'), ('c0018','Petrobras','j','91.364.862/0001-59','2015-11-14 00:15:26','Rio de Janeiro',80,'RJ'), ('c0019','Marcos Vinicius','f','865.749.635-67','2015-11-14 00:15:27','São Paulo',90,'SP'), ('c0020','Vale','j','72.538.175/0001-68','2015-11-14 00:15:28','Santos',90,'SP'), ('c0021','Murillo Rodrigues','f','068.519.887-12','2015-11-14 00:15:29','Juiz de Fora',100,'MG'), ('c0022','Pão de Açúcar','j','34.372.585/0001-92','2015-11-14 00:15:32','Juiz de Fora',100,'MG'); -- 4. "Pressione CTRL + Enter" -- -- Table structure for table `gerente` -- CREATE TABLE `gerente` ( `IdGerente` int(11) NOT NULL AUTO_INCREMENT, `VendCod` int(11) DEFAULT NULL, PRIMARY KEY (`IdGerente`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 5. "Pressione CTRL + Enter" -- -- Dumping data for table `gerente` -- INSERT INTO `gerente` VALUES (1,1),(2,10),(3,20); -- 6. "Pressione CTRL + Enter" -- -- Table structure for table `pedido` -- CREATE TABLE `pedido` ( `PedSeq` int(11) NOT NULL AUTO_INCREMENT, `CliCod` varchar(5) DEFAULT NULL, `PedDt` datetime DEFAULT NULL, `VendCod` int(11) DEFAULT NULL, `PedTotal` decimal(18,2) DEFAULT NULL, PRIMARY KEY (`PedSeq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 7. "Pressione CTRL + Enter" -- -- Dumping data for table `pedido` -- INSERT INTO `pedido` VALUES (1,'c0001','2015-11-01 00:00:00',1,2499.98), (2,'c0001','2015-11-01 00:00:00',1,5300.00), (3,'c0003','2015-11-01 00:00:00',10,2200.98), (4,'c0002','2015-11-01 00:00:00',1,1600.50), (5,'c0002','2015-11-01 00:00:00',1,5099.97), (6,'c0003','2015-11-01 00:00:00',10,1699.99), (7,'c0005','2015-11-01 00:00:00',20,10000.00), (8,'c0005','2015-11-01 00:00:00',20,8999.95), (9,'c0006','2015-11-01 00:00:00',20,17099.94), (10,'c0006','2015-11-01 00:00:00',20,15399.86), (11,'c0008','2015-11-01 00:00:00',30,2499.98), (12,'c0009','2015-11-01 00:00:00',40,5300.00), (13,'c0010','2015-11-01 00:00:00',40,2200.98), (14,'c0010','2015-11-01 00:00:00',40,1600.50), (15,'c0011','2015-11-01 00:00:00',50,5099.97), (20,'c0015','2015-11-01 00:00:00',70,15399.86), (21,'c0014','2015-11-01 00:00:00',60,1499.99), (22,'c0015','2015-11-01 00:00:00',70,3700.00), (23,'c0014','2015-11-01 00:00:00',60,1999.98), (24,'c0016','2015-11-01 00:00:00',70,100.50), (25,'c0017','2015-11-01 00:00:00',80,3000.00), (26,'c0017','2015-11-01 00:00:00',80,699.99), (27,'c0018','2015-11-01 00:00:00',80,6000.00), (28,'c0019','2015-11-01 00:00:00',90,4000.00), (29,'c0020','2015-11-01 00:00:00',90,5999.94), (30,'c0021','2015-11-01 00:00:00',100,10499.93), (31,'c0011','2015-11-01 00:00:00',50,1499.99), (32,'c0021','2015-11-01 00:00:00',100,3700.00), (33,'c0010','2015-11-01 00:00:00',40,1999.98), (34,'c0001','2015-11-01 00:00:00',1,100.50), (35,'c0020','2015-11-01 00:00:00',90,3000.00), (36,'c0001','2015-11-01 00:00:00',1,699.99), (37,'c0007','2015-11-01 00:00:00',30,NULL), (38,'c0003','2015-11-01 00:00:00',10,NULL), (39,'c0004','2015-11-01 00:00:00',10,NULL), (40,'c0005','2015-11-01 00:00:00',20,NULL); -- 8. "Pressione CTRL + Enter" -- -- Table structure for table `pedidoitem` -- CREATE TABLE `pedidoitem` ( `PedItemSeq` int(11) NOT NULL AUTO_INCREMENT, `PedSeq` int(11) DEFAULT NULL, `ProdCod` varchar(6) DEFAULT NULL, `Qtde` int(11) DEFAULT NULL, `PedItemPreco` decimal(18,2) DEFAULT NULL, `PedItemTotal` decimal(18,2) DEFAULT NULL, PRIMARY KEY (`PedItemSeq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 9. "Pressione CTRL + Enter" -- -- Dumping data for table `pedidoitem` -- INSERT INTO `pedidoitem` VALUES (1,1,'p00001',1,1499.99,1499.99), (2,2,'p00002',2,1850.00,3700.00), (3,3,'p00003',2,999.99,1999.98), (4,4,'p00008',1,100.50,100.50), (5,5,'p00005',3,1000.00,3000.00), (6,6,'p00006',1,699.99,699.99), (7,7,'p00007',4,1500.00,6000.00), (8,8,'p00009',5,800.00,4000.00), (9,9,'p00010',6,999.99,5999.94), (10,10,'p00001',7,1499.99,10499.93), (11,11,'p00001',1,1499.99,1499.99), (12,12,'p00002',2,1850.00,3700.00), (13,13,'p00003',2,999.99,1999.98), (14,14,'p00008',1,100.50,100.50), (15,15,'p00005',3,1000.00,3000.00), (16,16,'p00006',1,699.99,699.99), (17,17,'p00007',4,1500.00,6000.00), (18,18,'p00009',5,800.00,4000.00), (19,19,'p00010',6,999.99,5999.94), (20,20,'p00001',7,1499.99,10499.93), (21,21,'p00001',1,1499.99,1499.99), (22,22,'p00002',2,1850.00,3700.00), (23,23,'p00003',2,999.99,1999.98), (24,24,'p00008',1,100.50,100.50), (25,25,'p00005',3,1000.00,3000.00), (26,26,'p00006',1,699.99,699.99), (27,27,'p00007',4,1500.00,6000.00), (28,28,'p00009',5,800.00,4000.00), (29,29,'p00010',6,999.99,5999.94), (30,30,'p00001',7,1499.99,10499.93), (31,31,'p00001',1,1499.99,1499.99), (32,32,'p00002',2,1850.00,3700.00), (33,33,'p00003',2,999.99,1999.98), (34,34,'p00008',1,100.50,100.50), (35,35,'p00005',3,1000.00,3000.00), (36,36,'p00006',1,699.99,699.99), (37,11,'p00010',1,999.99,999.99), (38,12,'p00009',2,800.00,1600.00), (39,13,'p00008',2,100.50,201.00), (40,14,'p00007',1,1500.00,1500.00), (41,15,'p00006',3,699.99,2099.97), (42,16,'p00005',1,1000.00,1000.00), (43,17,'p00005',4,1000.00,4000.00), (44,18,'p00003',5,999.99,4999.95), (45,19,'p00002',6,1850.00,11100.00), (46,20,'p00006',7,699.99,4899.93), (47,1,'p00010',1,999.99,999.99), (48,2,'p00009',2,800.00,1600.00), (49,3,'p00008',2,100.50,201.00), (50,4,'p00007',1,1500.00,1500.00), (51,5,'p00006',3,699.99,2099.97), (52,6,'p00005',1,1000.00,1000.00), (53,7,'p00005',4,1000.00,4000.00), (54,8,'p00003',5,999.99,4999.95), (55,9,'p00002',6,1850.00,11100.00), (56,10,'p00006',7,699.99,4899.93); -- 10. "Pressione CTRL + Enter" -- -- Table structure for table `produto` -- CREATE TABLE `produto` ( `ProdCod` varchar(6) NOT NULL, `ProdDesc` varchar(50) DEFAULT NULL, `ProdPreco` decimal(18,2) DEFAULT NULL, `ProdLinha` char(3) DEFAULT NULL, PRIMARY KEY (`ProdCod`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 11. "Pressione CTRL + Enter" -- -- Dumping data for table `produto` -- INSERT INTO `produto` VALUES ('p00001','TV Led 42',1499.99,'Elt'), ('p00002','Notebook',1850.00,'Inf'), ('p00003','Smartphone',999.99,'Tel'), ('p00004','Cabo MiniUsb 5 pinos',3.00,'Inf'), ('p00005','TV Monitor LCD LED 32',1000.00,'Elt'), ('p00006','Adega de Vinhos 28 Garrafas',699.99,'Elt'), ('p00007','iPhone 4',1500.00,'Tel'), ('p00008','Celular Samsung E1195',100.50,'Tel'), ('p00009','Câmera Digital SP810',800.00,'Fot'), ('p00010','Câmera Digital Superzoom Coolpix L810',999.99,'Fot'); -- 12. "Pressione CTRL + Enter" -- -- Table structure for table `uf` -- CREATE TABLE `uf` ( `UFSigla` char(2) NOT NULL, `UFDesc` varchar(50) DEFAULT NULL, PRIMARY KEY (`UFSigla`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 13. "Pressione CTRL + Enter" -- -- Dumping data for table `uf` -- INSERT INTO `uf` VALUES ('AM','Amazonas'), ('BA','Bahia'), ('MG','Minas Gerais'), ('PR','Paraná'), ('RJ','Rio de Janeiro'), ('SC','Santa Catarina'), ('SP','São Paulo'); -- 14. "Pressione CTRL + Enter" -- -- Table structure for table `vendedor` -- CREATE TABLE `vendedor` ( `VendCod` int(11) NOT NULL, `VendNome` varchar(50) DEFAULT NULL, `Comissao` decimal(18,4) DEFAULT NULL, `Meta` decimal(18,2) DEFAULT NULL, `IdGerente` int(11) DEFAULT NULL, PRIMARY KEY (`VendCod`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 15. "Pressione CTRL + Enter" -- -- Dumping data for table `vendedor` -- INSERT INTO `vendedor` VALUES (1,'Adhemar Thomaz',0.0700,1500.00,1), (10,'AMANDA DE SOUZA MUREBE',0.0500,1000.00,2), (20,'Daniel dos Santos',0.0450,500.00,3), (30,'Edmo Bernardes',0.0350,2500.00,1), (40,'IORRAN MARCOLINO DE CASTRO',0.0700,1500.00,2), (50,'JULIO CESAR PAIVA RIBEIRO',0.0600,1350.00,3), (60,'GUSTAVO FERREIRA VIEIRA',0.0300,800.00,1), (70,'MARCELO THIMOTEO DOS SANTOS',0.0650,1800.00,2), (80,'Leonardo de Mello',0.0200,2500.00,3), (90,'Marcos Vinicius',0.0450,1500.00,1), (100,'WELLINGTON DE FREITAS DUARTE',0.0300,1500.00,2); -- 16. "Pressione CTRL + Enter" /*!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 */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2016-04-28 6:46:05 Operações de 'DDL' e 'DML'/DML - Select, Insert, Update e Delete.sql /* Utilização de DML */ use turma3003; -- usem o banco "turma3009" (o banco utilizado em aula) /* Utilização do "select" Obs.: Uma instrução "SELECT" pode ser qualquer um dos exemplos ou exercícios passados em sala. Uma instrução "select" é quelquer tipo de solitação de dados (consulta) ao banco de dados ativo */ select * from Cliente; /* Utilização do "insert" */ insert into Cliente values('HEXA', 'Flamengo', 'f', '62.397.832/0001-87', CURDATE(), 'Rio de Janeiro', 1, 'RJ'); /* Ou podemos informar apenas os valores para os campos que queiramos inserir, onde podemos deixar algum (ns) campos em "branco". Mas para que isto seja possível, o campo tem que aceitar valores nulos (null) */ insert into Cliente(clicod, clinome, cidade) values('Fla', 'mengo', 'Rio de Janeiro'); /* Utilizando a operação de Atualização (update). Muito Importante: toda vez que formos utilizar uma operação de atualização (update) deveremos utilizar a cláusula "where" (uma condição) para que seja feita está operação de atualização. Se não informarmos uma condição (cláusula "where") os dados contidos no(s) campo(s) (colunas) sendo feita a operação serão alterados também. Esta mesma observação deverá ser atendida quando utilizarmos o operador "delete" Obs.: para alterarmos um campo deveremos informá-lo através do operador "set", que tem a função de definir um novo valor para o campo a ser alterado (podemos colocar quantos campos forem necessários), desde que seus valores sejam compatíveis com o mesmo tipo de dados definido no campo (coluna) no ato da criação da tabela*/ update Cliente set CliNome = 'MENGÃO' WHERE CliNome = 'Flamengo' or CliNome = 'mengo'; /* utilizando a operação de remoção (delete) - as mesmas instruções (condições) utilizadas com o operador "update" deverão ser utilizadas com o operador "delete" (são as mesmas condições para "satizfazer a operação - nese caso utilizarei os mesmos campos com os mesmos valores, pois quero remover todos os registros (linhas) que contenham o valor "MENGÃO" para o campo "clinome". */ delete from Cliente where CliNome = 'MENGÃO'; select * from Cliente; /* Instruções apenas para "popular" a tabela para que possamos manipular seus dados - Esta tabela pode ser criada no banco utilizado em aula. */ create table tabela1( id int not null, nome varchar(64), sexo char(1), data_inclusao date, constraint PK_Tabela1 primary key (id) ); /* Instrução para renomear a tabela - instrução "rename" - sempre utilizando a instrução alter "nome da tabela"*/ alter table tabela1 rename to funcionario; /* Instrução para adicionar um campo a tabela - instrução "add" - sempre utilizando a instrução alter "nome da tabela" */ alter table funcionario add salario decimal(10, 2); create table tabela2( id_endereco int not null comment 'Chave primária', -- A instrução "comment" tem apenas a funalidade de colocar um comentário para um campo. Não interfere em nada na criação da tabela. rua varchar(64) default null, numero int default null, bairro varchar(64) default null, cidade varchar(32) default 'Rio de Janeiro', -- A instrução "default" tem a finalidade de fornecer um valor padrão para um determinado campo (nesse caso o campo "cidade") para caso não venhamos a informar um valor para este campo na operação de inserção, o valor informado com a instrução "default" (nesse caso "Rio de Janeiro") será utilizado. constraint pk_id_endereco primary key(id_endereco) ); /* Instrução para renomear a tabela - instrução "rename" - sempre utilizando a instrução alter "nome da tabela"*/ alter table tabela2 rename to endereco; create table empregado( id_empregado int not null, nome varchar(64), sexo char(1), data_inclusao date, salario decimal(10, 2), constraint pk_id_empregado primary key(id_empregado) ); /* EXEMPLO 6 Instrução para alterar valores já definidos em uma tabela Vou utilizar como exemplo a tabela "tabela1" (que agora é funcionário). */ -- Inserindo valores com data padrão insert into funcionario(id, nome, sexo, data_inclusao) values(1, 'Tiago de Almeida', 'M', '2015-05-23'); -- update tabela1 set id = 1 where id = 2; -- Inserindo valores com data atual padrão utilizando a função de data do sistema 'curdate()', onde obtemos a data no formato ano/mês/dia. insert into funcionario values(2, 'Marcelo dos Santos', 'M', curdate()); select * from funcionario; -- update tabela1 set id = 2 where id = 3; -- Inserindo valores com data atual formatada com 'date_format()' e utilizando a função de data do sistema -- 'curdate()' -- OBS.: Podemos utilizar a função de formatação de datas, tanto para consultas quanto para a inserção. insert into funcionario(id, nome, sexo, data_inclusao) values(4, 'Maria das Dores', 'F', DATE_FORMAT(CURDATE(), '%Y-%m-%d')); select * from funcionario; alter table funcionario add salario double (8, 2); -- Atualização da tabela 'funcionario' para popular o campo 'salario' (atualize para todos os outros registros) update funcionario set salario = 3100.10 where id = 4; -- Atualização da tabela 'funcionario' para o campo 'salario' receber 10% de acréscimo update funcionario set salario = salario * 1.10; SELect * from funcionario; insert into funcionario values(7, 'Carla de Almeida', 'F', '2015-05-12', 1500.87); /* INSTRUÇÕES DE INSERÇÃO NA TABELA 'tabela2' (agora "endereco") */ /* A instrução "describe" é uma forma de visualizar todos os campos e tipos de dados dos campos de uma tabela (toda sua estrutura) */ describe endereco; -- inserção na tabela 'endereco' com o valor padrão definidi para o campo 'cidade' insert into endereco (id_endereco, rua, numero, bairro) values(1, 'Ardenas', 201, 'Jardim Carioca'); -- inserção na tabela 'endereco' com o valor padrão definidi para o campo 'cidade' -- Inserção na tabela 'endereco' utilizando outro valor para o campo com valor padrão insert into endereco values(2, 'Av. Maricá', 189, 'Centro', 'Barra de São João'); insert into endereco values(3, 'Joaquim Barbosa', 342, 'Joaquim Barbosa', 'Niterói'); insert into endereco (id_endereco, rua, numero, bairro) values(4, 'Cacuia', 741, 'Sargento João Lopes'); update endereco set cidade = 'Rio de Janeiro' where id_endereco = 3; update endereco set rua = 'Joaquim Barbosa', numero = 342, bairro = 'Jardim ouro Preto', cidade = 'Niterói' where id_endereco = 3; /* INSTRUÇÕES DA TABELA 'empregado' */ insert into empregado values(4, 'Flávia de Souza', 'F', '2015-03-17' , 2290.63); describe empregado; select * from empregado; -- UTILIZAÇÃO DO COMANDO 'select' para inserção insert into funcionario(id, nome, sexo, data_inclusao, salario) select id_empregado + 4, nome, sexo, data_inclusao, salario from empregado where id_empregado = 2; select * from funcionario; insert into empregado(id_empregado, nome, sexo, data_inclusao, salario) select id + 2, nome, sexo, data_inclusao, salario from funcionario where id = 4; select * from empregado; select * from funcionario; /* A tabela1 tem os seguintes valores: id nome sexo data_inclusão 1 Xandi M 2015-03-29 2 Alex M 2015-01-16 3 Sandra F 2013-09-04 */ /*Vamos fazer algumas alterações em relação aos valores contidos em cada registro (linha/tupla) desta tabela OBS.: sempre temos que informar uma condição (cláusula "where"), pois se não informarmos, todos os registros das respectivas colunas serão alterados também*/ /* Nesta instrução estou definindo um outro valor para a coluna "nome" ('Xandi Praxdes'), mas estou colocando uma condição para que seja satisfeita esta alteração (where id = 1) */ update funcionario set nome = 'Xandi Praxdes' where id = 1; select * from funcionario; -- Alterado a tabela 'empregado' adicionado o campo 'bonus' alter table empregado add bonus decimal(10,2); -- Atualizando a tabela 'empregado' adicionado ao bonus 10% sobre o salario de todos os empregados update empregado set bonus = salario * 1.10; -- Atualizando a tabela funcionário em 5% o salário de todos empregados update funcionario set salario = salario * 1.05; -- Atualizando a tabela funcionário em 10% o salário somente para empregados com salario entre R$ 1000 e R$ 2000 update funcionario set salario = salario * 1.10 where salario between 1000 and 2000; -- Atualizando a tabela funcionário em 3% o salário somente para empregados com salario superior à R$ 2000 update funcionario set salario = salario * 1.03 where salario > 2000; select * from empregado; update empregado set salario = bonus * 0.90; select * from empregado; /* Apenas mais um exemplo */ update tabela1 set nome = 'Alexander Fernandes Praxedes', data_inclusao = '2013-03-27' where id = 2; /* Instrução para remover um registro - as mesmas condições da instrução de alteração (update) valem para a instrução de remoção (delete), ou seja, temos que informar uma condição (cláusula "where"), pois se não informarmos, todos os registros serão removidos. */ delete from tabela1 where id = 5; /* SELECT -> recuperar dados do banco de dados (consulta) INSERT -> inserir dados em uma tabela UPDATE -> atualiza os dados existentes em uma tabela DELETE -> exclui registros de uma tabela */ /* CONCEITOS DE TRANSAÇÃO • Uma transação se inicia no momento da conexão ao banco ou no término de uma transação. • Uma transação se encerra – Na execução do comando COMMIT – Na execução do comando ROLLBACK – Na execução de um comando DDL • Atualizações não serão vistas por outras sessões enquanto não ocorrer commit. */ /* Exemplo OBS.: COMO ESTOU UTILIZANDO O "COMMIT AUTOMÁTICO" NÃO TENHO COMO UTILIZAR O "ROLLBACK" SQL> create table produto 2 (cod_prod number(5) primary key, 3 nome_prod varchar2(40)); Table created. SQL> insert into produto values (1,'Shampoo Babosa'); 1 row created. SQL> commit; Commit complete. SQL> insert into produto values (2,'Creme Rinse Amendoas'); 1 row created. SQL> select * from produto; COD_PROD NOME_PROD ---------- ---------------------------------------- 1 Shampoo Babosa 2 Creme Rinse Amendoas SQL> rollback; Rollback complete. SQL> select * from produto; COD_PROD NOME_PROD ---------- ---------------------------------------- 1 Shampoo Babosa */ /* COMANDO SAVEPOINT • Permite a marcação de um ponto em uma sequência de comandos, até o qual podemos fazer um rollback parcial. */ /* SQL> insert into produto values (1,'Shampoo Babosa'); 1 row created. SQL> insert into produto values (2,'Creme Rinse Amêndoas'); 1 row created. SQL> savepoint sv1; Savepoint created. SQL> insert into produto values (3,'Shampoo Capim Cheiroso'); 1 row created. SQL> select * from produto; COD_PROD NOME_PROD ---------- ---------------------------------------- 1 Shampoo Babosa 2 Creme Rinse Amêndoas 3 Shampoo Capim Cheiroso SQL> rollback to sv1; Rollback complete. SQL> select * from produto; COD_PROD NOME_PROD ---------- ---------------------------------------- 1 Shampoo Babosa 2 Creme Rinse Amêndoas */ -- UTILIZANDO O COMANDO "SAVE POINT" PARA AS TABELAS QUE CRIEI PARA OS EXEMPLOS DESTE TÓPICO -- Apenas utilizando uma instrução para que o comando "savepoint" possa ser utilizado update funcionario set salario = salario * 100; select * from funcionario; /* Efetuando commit (concretizando) de todas as operações realizadas até o momento */ commit; /* Iniciando uma transação para a utilização de um "ponto de restauração" (save point) */ start transaction; -- Criando um ponto de salvamento/restauração a partir da instrução acima savepoint sv1; -- Atualizando a tabela 'funcionario' para que o salário seja crescido de 50% para todos os funcionários update funcionario set salario = salario * 1.50; select * from funcionario; -- Utilizando ponto de salvamento/restauração que foi criado antes da atualização dos salários em 50% rollback to sv1; /* PARA ATUALIZAR A TABELA "PEDIDOITEM" Em QUE A TABELA "PRODUTO" RECEBA ESTA ATUALIZAÇÃO TAMBÉM Utilizando o comando "subselect" */ update PedidoItem set PedItemPreco = (select Produto.ProdPreco from Produto where Produto.ProdCod = 'p00002'), PedItemTotal = ((select Produto.ProdPreco from Produto where Produto.ProdCod = 'p00002') * Qtde) where PedidoItem.ProdCod = 'p00002'; Operações de 'DQL'/FUNÇÕES ESCALARES.sql /* FUNÇÕES ESCALARES */ /* TIPOS DE FUNÇÃO • Funções numéricas – Manipulam colunas do tipo Number • Funções de caracteres – Manipulam strings de caracteres • Funções de data • Funções de conversão – Convertem dados de um tipo para outro – Date -> String – String -> Date – String -> Numerico, etc */ /* FUNÇÃO ABS • ABS (valor) – retorna o valor absoluto do conteúdo de uma coluna • Exibir nome do produtos cadastrados e o seu respectivo preço menos R$ 20,00. – SELECT DESC_PROD, PRECO_PROD – 20 FROM PRODUTOS; • Repetir a query anterior exibindo o valor absoluto do preço subtraído de R$ 20,00. – SELECT DESC_PROD, ABS (PRECO_PROD – 20) FROM PRODUTOS;*/ select prod.ProdDesc as "produto", prod.prodpreco as "preço normal", (prod.prodpreco - 20) as "preço normal -20", abs(prod.prodpreco - 20) as "preço com valor absoluto -20", u.ufsigla as "Estado" from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.PedSeq inner join cliente cli on cli.clicod = ped.CliCod inner join uf u on cli.UFSigla = u.UFSigla where prod.ProdPreco > 150 group by u.UFSigla order by u.ufsigla; select distinct prod.ProdDesc as 'Produto', prod.ProdPreco * 1.76 as 'Preço normal * 1.76', abs(prod.ProdPreco * 1.76) as 'Preço com valor absoluto * 1.76', u.ufsigla as 'Estado' from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod inner join pedido ped on pitem.pedseq = ped.Pedseq inner join cliente cli on ped.CliCod = cli.CliCod inner join uf u on cli.UFSigla = u.UFSigla where prod.ProdPreco >= 100 and prod.ProdPreco <= 1000 group by cli.UFSigla; /* FUNÇÃO CEIL • CEIL (n) - Retorna o maior inteiro maior ou igual a n. • Ceiling – Teto • Suponha a cotação do dólar a R$ 2,35. Exibir o nome do cliente e seu limite de crédito convertido para dólares, arredondado para o inteiro superior mais próximo. – SELECT CEIL (LIMCRED_CLI / 2.35) FROM CLIENTES; */ select prod.prodpreco as "Valor em Real", cast(ceil(prod.prodpreco / 2.35) as decimal(10, 2)) as "Valor em dolar", prod.prodlinha as "Linhas de produto" from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod inner join pedido ped on pitem.Pedseq = ped.PedSeq where prod.ProdLinha <> 'ELT' group by prod.ProdLinha order by prod.prodpreco desc; select concat('R$ - ', cast(prod.ProdPreco as decimal(8, 2))) as 'Valor em Real', concat('U$ - ', ceil(prod.ProdPreco / 2.35)) as 'Valor em dolar', prod.ProdLinha as 'Linha de Produto' from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod inner join pedido ped on pitem.PedSeq = ped.PedSeq where prod.ProdLinha not like 'ELT%' group by prod.ProdLinha order by prod.ProdLinha desc; /* FUNÇÃO FLOOR • FLOOR (n) - Retorna o menor inteiro menor que n. • Floor – Piso • SELECT FLOOR (20 / 3) FROM CLIENTES; • SELECT CEIL (20 / 3) FROM CLIENTES; */ select cast(floor(prod.prodpreco) as decimal(10, 2)) as "Valor com 'Floor'", cast(ceil(prod.prodpreco) as decimal(8, 2)) as "Valor com 'Ceil'", pitem.qtde as "Qtde de Produtos", cast(sum(prod.ProdPreco) as decimal(8, 2)) as Soma, cli.ufsigla as "Estado" from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod inner join pedido ped on pitem.PedSeq = ped.PedSeq inner join cliente cli on ped.CliCod = cli.CliCod where cli.UFSigla <> 'SP' group by cli.UFSigla having Soma > 2000 order by cli.UFSigla; /* FUNÇÃO MOD • MOD (m,n) - Retorna o resto da divisão de m por n. • Sintaxe errada -> MOD (m/n) – Sintaxe correta utiliza vírgula e não barra • Tabela DUAL – Tabela dummy, utilizada para completar sintaxe do comando Select • Exibir o resto da divisão de 23 por 11 – SELECT MOD (23,11) FROM DUAL; • A tabela DUAL realmente existe ? Quem a criou ? Qual o seu lay-out ? */ select mod(10, 3) as "Resto da divisão de 10/3"; /* FUNÇÕES SYSDATE E USER • SYSDATE - Retorna a data do sistema. – Em ambiente C/S retorna a data do servidor e não do client – SELECT SYSDATE FROM DUAL;*/ select sysdate() as "Data e Hora com 'sysdate()'" from dual; select curdate() as "Data com 'curdate()'"; -- formatação de data select date_format(curdate(), '%d/%m/%Y') as "Data Formatada"; /* FUNÇÃO ROUND • ROUND(n,m) - Retorna o valor de n arredondado com m casas decimais (à direita do ponto). m precisa ser um inteiro. • Exibir o limite de crédito médio por unidade da federação, arredondado com 2 casas decimais. – SELECT UF_CLI, ROUND(AVG (LIMCRED_CLI), 2) FROM CLIENTES GROUP BY UF_CLI; - Observem o aninhamento de funções - Não existem restrições quanto ao número de níveis para o aninhamento de funções. - A única restrição é a dificuldade de compreensão do código. */ select distinct round(avg(prod.prodpreco), 2) as "Média dos preços", count(prod.prodcod) as "Qtd de Produtos", cast(sum(prod.prodpreco) as decimal(10, 2)) as Soma, concat(cast((prod.prodpreco * 100) / sum(prod.prodpreco) as decimal(10, 2)), ' %') as " % em relação ao Total", u.ufsigla as "Estados onde há clientes" from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod inner join pedido ped on pitem.PedSeq = ped.pedseq inner join cliente cli on ped.CliCod = cli.CliCod inner join uf u on cli.UFSigla = u.UFSigla where u.UFSigla <> 'AM' group by u.UFSigla having Soma between 1000 and 15000; /* FUNÇÕES SQRT e SIGN • SQRT(n) - Retorna a raiz quadrada de n. • Exibir o nome do cliente e a raiz quadrada de seu limite de crédito – SELECT DESC_CLI, SQRT (LIMCRED_CLI) FROM CLIENTES;*/ select cli.clinome as "cliente", cast(pitem.PedItemPreco as decimal(8, 2)) as "Valor do Preço do Item", cast(sqrt(pitem.peditempreco) as decimal(8, 2)) as "Raíz do preço do item" from pedidoitem pitem inner join pedido ped on pitem.pedseq = ped.PedSeq inner join cliente cli on ped.clicod = cli.CliCod; /*• SIGN(n) - Se n<o, retorna -1. Se n>o, retorna 1. Se n=0, retorna 0. • Exibir o nome do produto, e o sinal do preço subtraído de 10,75. – SELECT DESC_PROD, SIGN (PRECO_PROD – 10.75) FROM PRODUTOS;*/ select prod.ProdDesc as "produto", sign(prod.prodpreco - 1000) as "Preço com 'sign()'" from produto prod; /* FUNÇÃO TRUNC • TRUNC(n,m) - Retorna n truncado para m casas decimais. • Exibir para cada cliente o seu limite de crédito convertido para dólares, apresentado de 2 formas diferentes:arredondado sem casas decimais e truncado. – SELECT DESC_CLI, ROUND (LIMCRED_CLI/2.35, 0) FROM CLIENTES; – SELECT DESC_CLI, TRUNC (LIMCRED_CLI/2.35, 0) FROM CLIENTES;*/ select prod.proddesc as "Produto", round(prod.prodpreco / 2.35, 0) "Preço com valor 'Arredondado", truncate(prod.prodpreco / 2.35, 0) as "Preço com valor 'Truncado'" from produto prod; /* FUNÇÕES DE CARACTERES SQL> select primeiro_nome, lower(primeiro_nome) from empregados; PRIMEIRO_NOME LOWER(PRIMEIRO_NOME) -------------------- -------------------------------- Joao joao Maria maria Pedro pedro Rita rita Helio helio Joao joao */ select u.ufsigla as "Estado", lower(u.ufdesc) as "Municípios com letras minúsculas" from uf u; /*UPPER – Retorna o string com todas as letras maíusculas. SQL> select primeiro_nome, upper(primeiro_nome) from empregados; PRIMEIRO_NOME UPPER(PRIMEIRO_NOME) -------------------- ------------------------------- Joao JOAO Maria MARIA Pedro PEDRO Rita RITA Helio HELIO Joao JOAO*/ select u.ufsigla as "Estados", upper(u.ufdesc) as "Municípios com letras Maiúsculas" from uf u; select u.ufsigla as "Estados", lpad(u.ufdesc, 15, '$$$$') as "Completando espaços com 'lpad()'" from uf u; /* FUNÇÕES DE CARACTERES • RPAD (string,n,’c’) – Complementa o string à direita, com o caractere ‘c’, até o comprimento n. Se o caractere não for informado, complementa com spaces. SQL> select rpad (primeiro_nome, 7, '*') nome from empregados; NOME ------- Joao*** Maria** Pedro** Rita*** Helio** Joao*** */ select lpad(u.ufsigla, 5, '=') as "Utilizando 'lpad()'", rpad(u.ufdesc, 10, '!') as "Completando com 'rpad()'" from uf u; select rpad(lpad(u.UFSigla,7, '# '),12,' %') as "Completando com 'rpad() e lpad()'" from uf u; /* rpad com lpad */ select u.ufsigla as "Estados", rpad(lpad(u.ufdesc, 15, '$$$$'), 25, '%%%%') as "Completando espaços com 'lpad()'" from uf u; /* FUNÇÕES DE CARACTERES • LTRIM (string) – Remove todas os espaços à esquerda. */ select ltrim(' JOAO') as 'Removendo espaços à esquerda' from dual; /* FUNÇÕES DE CARACTERES • RTRIM (string) – Remove todas os espaços à direita. • Funcionamento semelhante ao LTRIM. */ select rtrim('Rio de Janeiro ') as 'Removendo espaços à direira' from dual; /* FUNÇÕES DE CARACTERES • LENGTH (string) – Retorna o comprimento do string. SQL> select primeiro_nome, length(primeiro_nome) compr from empregados; PRIMEIRO_NOME COMPR -------------------- ---------- Joao 4 Maria 5 Pedro 5 Rita 4 Helio 5 Joao 4 */ select u.ufdesc as "Nome da cidade", length(u.UFDesc) as "Comprimento dos nomes" from uf u; Operações de 'DQL'/Uso do Select e Alias (com Inner Join).sql /* -----------------------xxxxxxxxxxxxxxxxx Uso da Instrução Select xxxxxxxxxxxxxxxxx ----------------------- */ /* 1) Liste os clientes pessoa física */ select * from Cliente where PFPJ = 'f'; -- OU select * from Cliente where PFPJ <> 'j'; -- OU select * from Cliente where PFPJ != 'j'; /* 2) Liste os clientes pessoa física que tenham o código entre 'c0005' e 'c0013 ' ordenados pelo código decrescente*/ select * from Cliente where pfpj = 'F' and CliCod between 'c0005' and 'c0013' order by CliCod desc; -- OU (de forma diferente tendo o mesmo resultado) select * from Cliente where pfpj like 'F%' and CliCod >= 'c0005' and CliCod <= 'c0013' order by CliCod desc; select * from cliente where pfpj like 'F' and CliCod between 'c0005' and 'c0013' order by CliCod desc; /* 3) Informe os clientes pessoa jurídica de São Paulo. */ select * from Cliente where PFPJ = 'j' and UFSigla = 'SP'; -- OU select * from cliente where pfpj like 'J%' and ufsigla like 'SP%'; /* 4) Informe os clientes pessoa jurídica fora São Paulo e o código do vendedor sendo igual a 20, 70 e 80. */ select * from Cliente where PFPJ = 'j' and UFSigla <> 'SP' and VendCod in (20, 70, 80); -- OU (de forma diferente tendo o mesmo resultado) select * from Cliente where PFPJ = 'J' and UFSigla <> 'SP' and (VendCod = 20 or VendCod = 70 or VendCod = 80); /* NÃO ESQUECER DO USO DOS "PARÊNTESES" QUANDO TIVER QUE FAZER OUTRA COMPARAÇÃO, POIS ALTERA COMPLETAMENTE O RESULTADO FINAL DA CONSULTA! */ /* 5) Informe os clientes pessoa jurídica de São Paulo e pessoa física dos outros estados. */ select * from Cliente where (PFPJ = 'j' and UFSigla = 'SP') or (PFPJ = 'f' and UFSigla <> 'SP'); -- OU select * from cliente where (pfpj like 'J%' and ufsigla like 'SP%') or (pfpj like 'F%' and ufsigla not like 'SP%'); /* 6) Informe os clientes pessoa jurídica de São Paulo e pessoa física dos outros estados cujo código esteja entre 'c0007' e 'c0017 e o código do vendedor seja diferente de 30, 40, 60 e 100. */ select distinct * from Cliente where (PFPJ = 'j' and UFSigla = 'SP') or (PFPJ = 'f' and UFSigla <> 'SP') and (CliCod between 'c0007' and 'c0017') and (VendCod not in (30, 40, 60, 100)); -- OU (de forma diferente tendo o mesmo resultado) select distinct * from Cliente where (PFPJ = 'j' and UFSigla = 'SP') or (PFPJ = 'f' and UFSigla <> 'SP') and (CliCod >= 'c0007' and CliCod <= 'c0017') and (VendCod <> 30 and VendCod <> 40 and VendCod <> 60 and VendCod <> 100) order by vendcod desc; /* 7) Escreva uma consulta SQL que retorne o código dos produtos vendido nos pedidos 1,10,20 e 30. */ select distinct pitem.prodcod as 'Código dos Produtos' from PedidoItem pitem where pitem.PedSeq in (1, 10, 20, 30); -- OU (de forma diferente tendo o mesmo resultado) select distinct pitem.prodcod as 'Código dos Produtos' from PedidoItem pitem where (pitem.PedSeq = 1 or pitem.PedSeq = 10 or pitem.PedSeq = 20 or pitem.PedSeq = 30); /* 8) Escreva uma consulta SQL que retorne o código e o total dos produtos vendido nos pedidos diferentes de 1,10,20 e 30, o código do vendedor sendo igual a 1, 40 e 50 e o total do pedido sendo inferior ou igual a R$ 4000,00 ordenados pelo total crescente. */ select distinct pitem.prodcod as 'Código', pitem.peditemtotal as Total from PedidoItem pitem, Vendedor vend where pitem.PedSeq not in (1, 10, 20, 30) and vend.VendCod in (1, 40, 50) and pitem.PedItemTotal <= 4000 order by Total; -- OU (de forma diferente tendo o mesmo resultado) select distinct pitem.prodcod as 'Código', pitem.peditemtotal as Total from PedidoItem pitem, Vendedor vend where (pitem.PedSeq <> 1 and pitem.PedSeq <> 10 and pitem.PedSeq <> 20 and pitem.PedSeq <> 30) and (vend.VendCod = 1 or vend.VendCod = 40 or vend.VendCod = 50) and pitem.PedItemTotal <= 4000 order by Total; /* 9) Escreva uma consulta SQL que retorne o código e a descrição dos produtos vendido nos pedidos 1,10,20 e 30 ordenadas pela descrição do produto de forma crescente. */ select distinct prod.prodcod as codigo, prod.proddesc as produto from Produto prod, PedidoItem pitem where pitem.PedSeq in(1, 10, 20, 30) and prod.ProdCod = pitem.ProdCod order by produto; -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" select distinct prod.prodcod as 'Codigo', prod.proddesc as Produto from Produto prod inner join PedidoItem pitem on prod.ProdCod = pitem.ProdCod where (pitem.PedSeq = 1 or pitem.PedItemSeq = 10 or pitem.PedSeq = 20 or pitem.PedItemSeq = 30) order by Produto; -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" - ordenadas pela descrição do produto de forma decrescente. */ select distinct prod.ProdCod as 'Código', prod.ProdDesc as 'Produto' from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod where pitem.PedSeq in(1, 10, 20, 30) order by prod.ProdDesc desc; /* 10) Escreva uma consulta SQL que retorne o código, descrição e linha dos produtos vendido nos pedidos diferentes de 1,10,20 e 30 e a linha do produto sendo igual a 'Elt' */ select distinct prod.prodcod as Codigo, prod.proddesc as Produto, prod.prodlinha as 'Linha de Produto' from Produto prod, PedidoItem pitem where pitem.PedSeq not in (1, 10, 20, 30) and prod.ProdCod = pitem.ProdCod and prod.ProdLinha = 'Elt'; -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" select distinct prod.prodcod as codigo, prod.proddesc as 'Produto', prod.ProdLinha as 'Linha de Produto' from Produto prod inner join PedidoItem pitem on prod.ProdCod = pitem.ProdCod and (pitem.PedSeq <> 1 and pitem.PedSeq <> 10 and pitem.PedSeq <> 20 and pitem.PedSeq <> 30) and prod.ProdLinha like 'ELT%'; /* 11) Informe o nome e o código dos clientes que compraram os pedidos 1,10,20 e 30. */ select distinct cli.clinome as 'Cliente', cli.clicod as 'Código' from Cliente cli inner join Pedido ped on cli.CliCod = ped.CliCod inner join pedidoitem pitem on ped.PedSeq = pitem.PedSeq where pitem.PedSeq in(1, 10, 20, 30); -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" select distinct cli.clicod as 'Código', cli.clinome as 'Cliente' from Cliente cli inner join Pedido ped on cli.CliCod = ped.CliCod where (ped.PedSeq = 1 or ped.PedSeq = 10 or ped.PedSeq = 20 or ped.PedSeq = 30); /* 12) Informe o nome e o código dos clientes pessoa Jurídica que compraram os pedidos 1,10,20 e 30 e que o nome possua a letra 'a' em qualquer parte do nome. */ select distinct cli.clicod as Codigo, cli.clinome as Cliente from Cliente cli, Pedido ped where cli.PFPJ = 'j' and cli.CliCod = ped.CliCod and ped.PedSeq in (1, 10, 20, 30) and cli.CliNome like '%a%' order by cli.CliNome; -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" select distinct cli.clinome as 'Cliente', cli.clicod as Codigo from Cliente cli inner join Pedido ped on cli.CliCod = ped.CliCod where (ped.PedSeq = 1 or ped.PedSeq = 10 or ped.PedSeq = 20 or ped.PedSeq = 30) and cli.PFPJ = 'J' and cli.CliNome like '%a%' order by Cliente; /* 13) Liste todos os vendedores cuja alíquota de comissão seja maior que 3,5%. */ select distinct * from Vendedor where Comissao > 0.035; select distinct vend.vendnome as 'Vendedor', cast(vend.comissao as decimal(10, 3)) as 'Comissao', vend.meta as 'Meta', g.idgerente as 'Id - Gerente' from vendedor vend inner join gerente g on vend.IdGerente = g.IdGerente and vend.Comissao > 0.035; /* Liste todos os vendedores cuja alíquota de comissão seja maior que 3,5% e que tenham o gerente de id igual a 1 ou 2*/ select vend.* from vendedor vend inner join gerente g on vend.idgerente = g.idgerente where vend.comissao > 0.035 and g.IdGerente in(1, 2); select distinct vend.* from vendedor vend inner join gerente g on vend.idgerente = g.idgerente where vend.comissao > 0.035 and g.idgerente != 3; /* 14) Liste o id do Gerente, o nome, comissão e meta dos vendedores cuja alíquota de comissão seja maior ou igual que 3,5% e menor ou igual a 9,0 tendo como gerente de código diferente de 1 cujo nome tenha a letra 'a' em qualquer parte do nome*/ select distinct g.IdGerente as 'Id gerente', vend.VendNome as 'Vendedor', vend.Comissao as 'Comissão', vend.Meta as 'meta' from vendedor vend inner join gerente g on vend.IdGerente = g.IdGerente where vend.Comissao >= 0.035 and vend.Comissao <= 9 and g.IdGerente <> 1 and vend.VendNome like '%A%'; -- OU UTILIZANDO "INNER JOIN" select distinct ge.idgerente as 'Código - Gerente', vend.vendnome as 'Vendedor', vend.comissao as 'Comissão', vend.meta as 'Meta de venda a ser atingida' from Vendedor vend inner join Gerente ge on vend.IdGerente = ge.IdGerente where (vend.Comissao >= 0.035 and vend.Comissao <= 0.090) and vend.VendNome like '%a%' and ge.IdGerente <> 1; select distinct g.idgerente as 'Id - Gerente', vend.vendnome as 'vendedor', vend.meta as 'Meta', concat(cast(vend.meta * 0.32 / vend.Meta * 100 as decimal(2, 0)), '%') as 'Porcentagem(%)', concat('R$ ', cast((vend.Meta * 1.32) - vend.Meta as decimal(8, 2))) as 'Valor da porcentagem', cast(vend.meta * 1.32 as decimal(10, 2)) as 'Meta + Porcentagem' from vendedor vend inner join gerente g on vend.idgerente = g.idgerente where vend.comissao between 0.035 and 0.090 and g.idgerente <> 1 and vend.vendnome like '%A%'; /* 15) Relacione (código e descrição dos produtos e código do vendedor) vendidos pelo vendedor cujo código é 20. */ select distinct prod.prodcod, prod.proddesc from Produto prod, Pedido ped, PedidoItem pitem where VendCod = 20 and ped.PedSeq = pitem.PedSeq and pitem.ProdCod = prod.ProdCod; select distinct prod.prodcod as codigo, prod.proddesc as 'Produto' from produto prod, pedido ped, pedidoitem pitem, vendedor vend where pitem.prodcod = prod.prodcod and ped.pedseq =pitem.pedseq and vend.VendCod = ped.VendCod and vend.VendCod like 20; /* UTILIZANDO "INNER JOIN" (está de uma maneira mais consistete utilizando o "INNER JOIN"). */ select distinct prod.prodcod as 'Código', prod.proddesc as 'Produto', vend.vendcod as 'Código do Vendedor' from Produto prod inner join PedidoItem pitem on Prod.ProdCod = pitem.ProdCod inner join Pedido ped on pitem.PedSeq = ped.PedSeq inner join Vendedor vend on ped.VendCod = vend.VendCod where vend.VendCod = 20; /* 16) Relacione os produtos (código, descrição) vendidos pelo vendedor cujo código é 20 tendo como gerente de código 1 ou 3 */ select distinct prod.prodcod, prod.proddesc from Produto prod, Pedido ped, PedidoItem pitem, Gerente g where ped.VendCod = 20 and ped.PedSeq = pitem.PedSeq and prod.ProdCod = pitem.ProdCod and g.IdGerente in (1, 3); -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" select distinct prod.prodcod as 'Código', prod.proddesc as 'Produto' from Produto prod inner join PedidoItem pitem on prod.ProdCod = pitem.ProdCod inner join Pedido ped on pitem.PedSeq = ped.PedSeq inner join Vendedor vend on ped.VendCod = vend.VendCod inner join Gerente ge on vend.IdGerente = ge.IdGerente where vend.VendCod = 20 and (ge.IdGerente = 1 or ge.IdGerente = 3); select distinct prod.prodcod as codigo, prod.proddesc as produto from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq inner join vendedor vend on ped.vendcod = vend.vendcod inner join gerente g on vend.idgerente = g.idgerente where vend.vendcod = 20 and g.idgerente in(1, 3) order by codigo; /* 17) Liste o nome e o código dos clientes e o nome do Estado e cidade que compraram os pedidos 1,10,20 e 30 */ select distinct cli.clinome as 'Cliente', cli.clicod as 'Identificador', u.UFDesc as 'Cidade', u.UFSigla as 'Estado' from Cliente cli, uf u, Pedido ped where ped.PedSeq in (1, 10, 20, 30) and ped.CliCod = cli.CliCod and cli.UFSigla = u.UFSigla; -- OU (de forma diferente tendo o mesmo resultado) UTILIZANDO "INNER JOIN" select distinct cli.clicod as 'Código', cli.clinome as 'Cliente', u.ufdesc as 'Cidade', u.UFSigla as 'Estado' from Cliente cli inner join UF u on cli.UFSigla = u.UFSigla inner join Pedido ped on cli.CliCod = ped.CliCod where (ped.PedSeq = 1 or ped.PedSeq = 10 or ped.PedSeq = 20 or ped.PedSeq = 30); /* 18) Liste o nome e o código dos clientes que compram 'TV' ordenados pelo código dos clientes de forma decrescente*/ select distinct cli.clicod as codigo, cli.clinome as cliente, prod.proddesc as produto from Cliente cli, Produto prod, Pedido ped, PedidoItem pitem, Vendedor vend where prod.ProdDesc like 'TV%' and (pitem.PedSeq = ped.PedSeq and pitem.ProdCod = prod.ProdCod) and ped.CliCod = cli.CliCod and cli.VendCod = vend.VendCod order by produto desc; -- OU UTILIZANDO "INNER JOIN" select distinct cli.clinome as 'Cliente', cli.clicod as 'Código' from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq inner join cliente cli on ped.clicod = cli.clicod inner join vendedor vend on cli.VendCod = vend.vendcod where prod.proddesc like 'TV%' order by 'Código' desc; /* 19) Liste o nome o código dos clientes que não compraram da linha 'Elt' e os produtos sendo diferentes de 'iPhone 4' e 'Notebook' */ select distinct cli.clicod, cli.clinome from Cliente cli, Produto prod, Pedido ped, PedidoItem pitem where prod.ProdCod = pitem.ProdCod and pitem.PedSeq = ped.PedSeq and ped.CliCod = cli.CliCod and (prod.ProdLinha not like 'Elt' and prod.ProdDesc not like 'iPhone 4' and prod.ProdDesc not like 'Notebook'); -- OU UTILIZANDO "INNER JOIN" select distinct cli.clinome as 'Cliente', cli.clicod as 'Código' from Produto prod inner join PedidoItem pitem on prod.ProdCod = pitem.ProdCod inner join Pedido ped on pitem.PedSeq = ped.PedSeq inner join Cliente cli on ped.CliCod = Cli.CliCod where(prod.ProdLinha not like 'Elt' and prod.ProdDesc not like 'iPhone 4' and prod.ProdDesc not like 'Notebook'); /* O mesmo enunciado acima, mas trazendo todos os cliente e somente cliente que compraram sendo pessoa física - Utilizando o 'left join' */ select distinct cli.* from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq left join cliente cli on ped.clicod = cli.clicod where (prod.prodlinha not like 'ELT%' and prod.ProdDesc not like 'iPhone 4' and prod.proddesc not like 'Notebook') and cli.pfpj = 'F'; /* 20) Informe as vendas(código e descrição do produto, quantidade e preço) no estado do Rio de Janeiro */ select distinct prod.prodcod, prod.proddesc, pitem.qtde, pitem.peditempreco from Produto prod, PedidoItem pitem, Pedido ped, Cliente cli, uf u where u.UFSigla = 'RJ' and cli.UFSigla = 'RJ' and prod.ProdCod = pitem.ProdCod and ped.PedSeq = pitem.PedSeq and cli.CliCod = ped.CliCod and prod.ProdPreco = pitem.PedItemPreco; -- OU UTILIZANDO "INNER JOIN" select distinct prod.prodcod as 'Id - Produto', prod.proddesc as 'Produto', pitem.qtde as 'Qtde', pitem.peditempreco as 'Preço' from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq inner join cliente cli on ped.clicod = cli.clicod inner join uf u on cli.ufsigla = u.ufsigla where (u.ufsigla like 'RJ%'); /* 20.1) Informe as vendas(código e descrição do produto, quantidade, preço, preço com - 10% e preço com + 10%) no estado do Rio de Janeiro */ select distinct prod.prodcod as 'Código', prod.proddesc as 'Produto', pitem.qtde as 'Quantidade', round(pitem.peditempreco, 2) as 'Preço', cast((pitem.peditempreco * 0.90) as decimal (10, 2)) as 'Preço - 10%', round(pitem.peditempreco * 1.10, 2) as 'preço + 10%' from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq inner join cliente cli on ped.clicod = cli.clicod inner join uf u on cli.ufsigla = u.ufsigla where (u.ufsigla like 'RJ%'); select distinct prod.ProdCod as 'Código', prod.ProdDesc as 'Produto', pitem.Qtde as 'Quantidade', round(pitem.PedItemPreco, 2) as 'Preço', concat('R$ ', cast((pitem.PedItemPreco * 0.90) as decimal(8, 2))) as 'Preço - 10%', concat('R$ ', round((pitem.PedItemPreco * 1.10))) as 'Preço + 10%' from produto prod inner join pedidoitem pitem on prod.ProdCod = pitem.ProdCod inner join pedido ped on pitem.PedSeq = ped.PedSeq inner join cliente cli on ped.CliCod = cli.CliCod inner join uf u on cli.UFSigla = u.UFSigla where (u.UFSigla like 'RJ%'); /* 21) Informe as vendas(código e descrição do produto, quantidade, preço e preço com 20% de desconte) no estado do Rio de Janeiro cujo preço de cada item esteja entre R$ 100,00 e R$ 1000,00.*/ select distinct prod.prodcod as codigo, prod.proddesc as produto, pitem.qtde as quantidade, pitem.peditempreco as preco, round((pitem.peditempreco - (pitem.peditempreco * 0.20)), 2) as 'Desconto - 20%', u.UFSigla as Estado from Produto prod, Pedido ped, PedidoItem pitem, Cliente cli, UF u where u.UFSigla = 'RJ' and cli.UFSigla = 'RJ' and prod.ProdCod = pitem.ProdCod and ped.PedSeq = pitem.PedSeq and cli.CliCod = ped.CliCod and prod.ProdPreco = pitem.PedItemPreco and pitem.PedItemPreco >= 100 and pitem.PedItemPreco <= 1000; -- OU UTILIZANDO "INNER JOIN" select distinct prod.prodcod as Código, prod.proddesc as 'Prouto', pitem.qtde as 'Qtde.', pitem.peditempreco as 'Valor Unit.', cast((pitem.peditempreco - (pitem.peditempreco * 0.20)) as decimal(10, 2)) as 'Valor com 20% de Desconto', cast((pitem.PedItemPreco * pitem.qtde) as decimal(10, 2)) as Total, u.ufSigla as 'Estado' from Produto prod inner join PedidoItem pitem on prod.ProdCod = pitem.ProdCod inner join Pedido ped on pitem.PedSeq = ped.PedSeq inner join Cliente cli on ped.CliCod = cli.CliCod inner join UF u on cli.UFSigla = u.UFSigla where (pitem.PedItemPreco >= 100 and pitem.PedItemPreco <= 1000) and u.UFSigla = 'RJ'; /* 21) Informe o estado e as vendas(código, descrição do produto, quantidade, preço, preço com 10% de desconto, total com 10% de desconto) agrupados por estado cujo preço de cada item esteja entre R$ 100,00 e R$ 1000,00.*/ select distinct u.ufsigla as 'Estado', prod.prodcod as 'Código', prod.proddesc as 'Produto', pitem.qtde as 'Qtde', pitem.peditempreco as 'Preço Unit.', cast((pitem.peditempreco * 0.10) as decimal(10, 2)) as '10% de desconto no item separadamente', cast((pitem.PedItemPreco * 0.90) * pitem.Qtde as decimal(10, 2)) as 'Total com 10% de desconto' from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq inner join cliente cli on ped.clicod = cli.clicod inner join uf u on cli.ufsigla = u.ufsigla where (pitem.peditempreco >= 100 and pitem.peditempreco <= 1000) group by u.UFSigla; /* exercício agrupado por estado em que o estado seja diferente de São Paulo (SP) e Paraná (PR)*/ select distinct u.ufsigla as 'Estado', pitem.qtde as 'Qtde', pitem.peditempreco as 'Preço Unit.', u.ufsigla as 'Estado', cast((pitem.peditempreco * 0.10) as decimal(10, 2)) as '10% de desconto no item separadamente', cast((pitem.PedItemPreco * 0.90) * pitem.Qtde as decimal(10, 2)) as 'Total com 10% de desconto' from produto prod inner join pedidoitem pitem on prod.prodcod = pitem.prodcod inner join pedido ped on pitem.pedseq = ped.pedseq inner join cliente cli on ped.clicod = cli.clicod inner join uf u on cli.ufsigla = u.ufsigla where (pitem.peditempreco >= 100 and pitem.peditempreco <= 1000) and u.ufsigla not in('SP', 'PR') group by u.UFSigla; -- ESTE EXERCÍCIO ABAIXO É MUITO IMPORTANTE /* Informe todos os Gerentes e sua equipe (vendedores). Para obtermos o resultado esperado temos que pegar o nome do vendedor duas vezes pelo alias (ge e v) para que o resultado traga o nome do vendedor e seu gerente correspondente. OBS.: o exercício parece bem simples, mas temos que prestar bastante atenção para obtermos o resultado esperado */ select ge.idgerente, ge.vendnome as 'Gerente', v.vendnome as 'Vendedor' from Vendedor ge, Vendedor v, Gerente g where ge.Vendcod = g.VendCod and v.IdGerente = g.IdGerente order by ge.VendNome asc; -- EXERCÍCIO ACIMA UTILIZANDO "INNER JOIN" select ge.IdGerente as 'Código - Gerente', ge.vendnome as 'Gerente', v.vendnome as 'Vendedor', v.VendCod as 'Código - Vendedor' from Vendedor ge inner join Gerente g on ge.VendCod = g.VendCod inner join Vendedor v on g.IdGerente = v.IdGerente order by ge.VendNome, v.VendCod; select g.idgerente as 'Id - Gerente', ge.vendnome as 'Gerente', v.vendnome as 'vendedor', v.vendcod as 'Id - Vendedor' from vendedor ge inner join gerente g on ge.vendcod = g.VendCod inner join vendedor v on g.IdGerente = v.IdGerente order by ge.vendnome desc; /* 1 - Informe os clientes que compraram. */ select distinct cli.CliCod, cli.CliNome from Cliente cli, Pedido ped, PedidoItem pitm, Produto prod, Vendedor vend where cli.CliCod = ped.CliCod and pitm.ProdCod = prod.ProdCod and ped.PedSeq = pitm.PedSeq and cli.VendCod = ped.VendCod and cli.VendCod = vend.VendCod and ped.VendCod = vend.VendCod and pitm.PedItemSeq = ped.PedSeq; /* 2 - Informe os clientes que ainda não compraram. */ select Cliente.* from Cliente left join Pedido on cliente.CliCod = Pedido.CliCod where Pedido.CliCod is null; /* 2.1 Todos os clientes que compraram */ select distinct cli.* from cliente cli left join pedido ped on cli.clicod = ped.clicod where ped.clicod is not null; /* 3 - Selecione os clientes do vendedor 'Daniel dos Santos' */ select distinct cli.clinome as 'Cliente', vend.vendnome as 'vendedor' from cliente cli inner join vendedor vend on cli.vendcod = vend.vendcod where vend.vendnome like 'Daniel dos Santos%'; /* 4 - Informe os clientes (código e nome) do gerente 'Adhemar Thomaz' e sua equipe de vendas */ select distinct cli.clinome as 'Clientes', cli.clicod as 'Código do Cliente', v.vendnome as Vendedores from vendedor ge inner join gerente g on ge.vendcod = g.vendcod inner join vendedor v on g.idgerente = v.idgerente inner join cliente cli on v.vendcod = cli.vendcod where ge.vendnome like 'Adhemar Thomaz%'; /* 5 - Informe os Gerentes (código e nome) e sua equipe de vendas (vendedores)*/ select distinct ge.vendnome as 'Gerente', v.vendnome as 'Vendedor' from Vendedor ge, Vendedor v, Gerente g where ge.Vendcod = g.VendCod and v.IdGerente = g.IdGerente order by ge.VendNome; -- OU UTILIZANDO O "INNER JOIN" select distinct g.idgerente as 'Código', ge.vendnome as 'Gerentes', vend.vendnome as 'Vendedores' from Vendedor ge inner join Gerente g on g.VendCod = ge.VendCod inner join Vendedor vend on ge.IdGerente = vend.IdGerente order by ge.vendnome; select distinct g.idgerente as 'Id - Gerente', ge.vendnome as 'gerentes', v.vendnome as 'Vendedores', v.vendcod as 'Id - Vendedor' from vendedor ge inner join gerente g on ge.vendcod = g.vendcod inner join vendedor v on g.idgerente = v.idgerente order by ge.VendNome; /* 5 - Informe os Gerentes (código e nome) e sua equipe de vendas (vendedores) em que o nome do Gerente seja DIFERENTE de AMANDA DE SOUZA MUREBE. */ select distinct ge.idgerente as 'Código', g.vendnome as 'Gerentes', vend.vendnome as 'Vendedores' from Vendedor g inner join Gerente ge on g.VendCod = ge.VendCod inner join Vendedor vend on ge.IdGerente = vend.IdGerente where g.VendNome not like 'AMANDA DE SOUZA MUREBE%'; /* 6 - Informe os Gerentes (código e nome com letras minúsculas) e sua equipe de vendas (vendedores com letras maiúsculas) em que o nome do Gerente seja IGUAL de Daniel dos Santos. */ select distinct g.idgerente as 'Id - Gerente', lower(ge.vendnome) as 'Gerente', v.vendcod as 'Id - Vendedor', upper(v.vendnome) as 'vendedor' from vendedor ge inner join gerente g on ge.vendcod = g.vendcod inner join vendedor v on g.idgerente = v.idgerente where ge.vendnome like 'Daniel dos Santos%' order by g.IdGerente desc; select * from Cliente where CliCod = 'c0001'; select * from Vendedor where VendCod = 1; select * from Gerente where IdGerente = 1; select * from Pedido; select * from Produto;
Compartilhar