Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
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