Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel Introdução aos comandos DML Os comandos de manipulação de dados são: INSERT – para inserção de registros UPDATE – para atualização de registros DELETE – para exclusão de registros SELECT – para seleção de registros Estes comandos podem ser mesclados para se obter um determinado resultado. Por exemplo, pode-se utilizar um comando de inserção combinado com um comando de seleção, ou então um comando de atualização combinado com um de seleção. O comando de seleção aceita muitas variações como agrupamentos, uniões, sub-seleções, chamadas a funções e a procedimentos armazenados, etc. Em um SGBD, as operações sobre os registros normalmente ocorrem em sessões, de forma isolada. Um usuário só conseguirá ver o que outro usuário está fazendo quando o primeiro finalizar a sua sessão. Há duas formas de se finalizar uma sessão: Com sucesso finalizando e salvando tudo o que aconteceu. Quando a sessão termina com sucesso, todas as operações devem ser salvas com o comando COMMIT. Com erro finalizando e voltando ao estado anterior a primeira operação. Este erro não indica necessariamente que “alguma coisa deu errado”, Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel muitas vezes um erro pode ser esperado. Quando, por algum motivo, a sessão não deve terminar com sucesso, as operações devem ser finalizadas com ROLLBACK. Imagine que em uma sessão será feito um pedido de venda. Deve ser incluído um registro na tabela PEDIDO, um registro incluído na tabela PEDIDOITEM, um registro incluído na tabela ESTOQUE e o campo QT_DISPONIVEL da tabela ITEM deve ser atualizado decrescendo o valor pedido. Se, ao final, toda a operação puder ser salva, deve-se finaliza-la com COMMIT. Caso ocorra uma condição que não permita a finalização da sessão, a mesma deve ser abortada com ROLLBACK. Uma condição que não permita a finalização pode ser: tentativa de incluir um registro na tabela PEDIDO com um código de pessoa que não exista na tabela PESSOA. Tentativa de incluir na tabela PEDIDOITEM com um código de item que não exista na tabela ITEM. Não há saldo suficiente para fazer o pedido de venda. Comando INSERT – sintaxe básica: INSERT INTO nome_da_tabela (campo1, campo2,... campoN) VALUES (valor_campo1, valor_campo2,... valor_campoN); Para inserção de um registro na tabela CIDADE do modelo anterior INSERT INTO CIDADE (CD_CIDADE, NM_CIDADE, SG_UF) VALUES (1, ‘Pato Branco’, ‘SC’); Para todo campo do tipo caractere o valor deve estar entre aspas simples, campos do tipo número não precisam das aspas. Na verdade, esta é uma restrição de integridade, que garante que cada campo receba apenas valores válidos para aquele domínio. Por este motivo, é importante conhecer a estrutura da tabela que se deseja manipular as informações. Uma boa prática é sempre trabalhar com o modelo de dados em mente, caso não conheça bem o modelo, sinta-se à vontade em olhá-lo. Comando UPDATE – sintaxe básica: UPDATE nome_da_tabela SET campo2 = valor_campo2, campo3 = valor_campo3, campoN = valor_campoN WHERE (campo_chave = valor_campo_chave); Para atualização de um registro na tabela CIDADE UPDATE CIDADE SET SG_UF = ‘PR’ WHERE (CD_CIDADE = 1); O comando de atualização pode ser executado sem a cláusula WHERE, esta cláusula delimita o intervalo de registros a serem atualizados. Caso um comando seja executado sem a cláusula WHERE, todos os registros da tabela serão atualizados. No exemplo acima, a sigla da UF está sendo atualizada com o valor PR somente quando o código da cidade for igual a 1. Novamente é importante observar que a sigla está com aspas simples, porque este campo contem caracteres. Comando DELETE – sintaxe básica: DELETE FROM nome_da_tabela WHERE (campo1 = valor_campo1); Para exclusão de um registro na tabela CIDADE DELETE FROM CIDADE WHERE (CD_CIDADE = 1); O comando de exclusão também pode ser executado sem a Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel cláusula WHERE, mas da mesma forma que o UPDATE, o fará para toda a tabela. No exemplo acima, apenas o registro cujo campo CD_CIDADE = 1 será excluído. Comando SELECT – sintaxe básica: SELECT * FROM nome_da_tabela; SELECT campo1, campo2,..,campoN FROM nome_da_tabela; SELECT campo1, campo2,..,campoN FROM nome_da_tabela WHERE (campo1 = valor_campo1); Para seleção de registros na tabela CIDADE SELECT * FROM CIDADE; SELECT CD_CIDADE, NM_CIDADE FROM CIDADE; SELECT NM_CIDADE, SG_UF FROM CIDADE WHERE (CD_CIDADE = 1); O asterisco no comando indica que todos os campos serão selecionados. Ao passar uma lista de campos, estará indicando que selecionará apenas os campos escolhidos. ausência da cláusula WHERE indica que todos os registros serão selecionados. Além da sintaxe básica dos comandos SQL, podem aparecer na linguagem várias expressões e palavras reservadas. Como é de praxe, cada SGBD implementa um conjunto de funções para ser utilizado nos comandos SQL DML, DDL ou até mesmo procedurais. Devemos tomar um cuidado especial quando desejamos que um determinado registro não tenha valores. Para expressar ausência de valores devemos utilizar NULL. Muitos programadores costumam confundir NULL com duas aspas sem nenhum valor entre elas ‘’. Deve-se lembrar que para o SGBD as aspas ‘’ representam valor (neste caso, um espaço). NULL pode ser utilizado na inclusão, atualização ou exclusão de registros. Na inclusão, quando queremos que um determinado campo não tenha valor, devemos passar NULL no VALUES deste campo. Por exemplo: INSERT INTO CIDADE (CD_CIDADE, NM_CIDADE, SG_UF) VALUES (5, ‘Palmas’, NULL). Neste caso o registro será incluído sem valor no campo SG_UF. Na atualização, o NULL pode ser utilizado tanto para alteração de valores quanto na cláusula WHERE. Por exemplo: UPDATE CIDADE SET SG_UF = NULL WHERE (CD_CIDADE = 1); Neste caso o campo SG_UF está sendo atualizado com NULL no registro da tabela CIDADE cujo campo CD_CIDADE é igual a 1. Os comandos de manipulação de registros (DML) podem conter uma grande quantidade de variações. Estes são alguns dos elementos que podem aparecer: IS NULL cujo valor é nulo; IS NOT NULL cujo valor não é nulo; > cujo valor é maior; < cujo valor é menor; = cujo valor é igual; >= cujo valor é maior ou igual; <= cujo valor é menor ou igual; <> cujo valor é diferente; Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel AND equivalente à condição booleana e; OR equivalente à condição booleana ou; NOT equivalente à condição booleana não; CONTAINING contendo; LIKE como algo, deve ser utilizado com marcadores especiais; IN em um conjunto de valores NOT IN não está em um conjunto de valores; BETWEEN entre um valor e outro; || para concatenação; AS para renomear colunas; + - * / para as operações matemáticas; IS NULL SELECT * FROM CIDADE WHERE (SG_UF IS NULL); IS NOT NULL SELECT * FROM CIDADE WHERE (SG_UF IS NOT NULL); > SELECT * FROM CIDADE WHERE (CD_CIDADE > 1); < SELECT * FROM CIDADE WHERE (CD_CIDADE < 10); >= SELECT * FROM CIDADE WHERE (CD_CIDADE >= 3); <= SELECT * FROM CIDADE WHERE (CD_CIDADE <= 3); <> SELECT * FROM CIDADE WHERE (CD_CIDADE <> 4); AND SELECT * FROM CIDADE WHERE (CD_CIDADE > 2) AND (CD_CIDADE <= 6); OR SELECT * FROM CIDADE WHERE (CD_CIDADE = 3) OR (CD_CIDADE = 4); NOT SELECT * FROM CIDADE WHERE NOT (CD_CIDADE = 3); CONTAINING SELECT * FROM CIDADE WHERE (NM_CIDADE CONTAINING ‘Branco’); LIKE SELECT * FROM CIDADE WHERE (NM_CIDADE LIKE ‘%Branco%’); IN SELECT* FROM CIDADE WHERE (CD_CIDADE IN (1,6)); NOT IN SELECT * FROM CIDADE WHERE (CD_CIDADE NOT IN (1,6)); BETWEEN SELECT * FROM CIDADE WHERE (CD_CIDADE BETWEEN 1 AND 4); || SELECT NM_CIDADE||SG_UF FROM CIDADE; AS SELECT NM_CIDADE||SG_UF AS NOME FROM CIDADE; Comandos de Junção Uma questão essencial em SQL é retornar e/ou relacionar informações de várias tabelas em um mesmo comando SQL do tipo SELECT. Há várias abordagens para resolver este problema. Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel Tomando como base o modelo proposto anteriormente, imaginemos que em uma determinada situação, seria necessário retornar a descrição e o número de casas decimais da tabela UNIDADE e a descrição, a quantidade disponível do produto e a sigla da unidade de medida presentes na tabela ITEM. Uma solução possível seria: SELECT UNIDADE.DS_UNIDADE, UNIDADE.NR_DECIMAIS, ITEM.DS_ITEM, ITEM.SG_UNIDADE, ITEM.QT_DISPONIVEL FROM ITEM, UNIDADE WHERE (ITEM.SG_UNIDADE = UNIDADE.SG_UNIDADE); Na cláusula FROM foram listadas duas tabelas: ITEM e UNIDADE, separadas por vírgula e a cláusula WHERE indicando os campos envolvidos no relacionamento entre UNIDADE e ITEM. Isto garante que os campos das duas tabelas sejam visíveis no comando de seleção. Além disso, só serão retornados registros de ITEM e UNIDADE que existam nas duas tabelas. Este tipo de abordagem pode não ser o ideal para alguns casos, imaginemos a seguinte situação das tabelas no banco de dados: UNIDADE SG_UNIDADE# DS_UNIDADE NR_DECIMAIS UN Unidade 0 MT Metro 2 KG Quilograma 2 UN2 Unidade 0 ITEM CD_ITEM # DS_ITEM SG_UNIDADE (FK) 2506 Maçã fuji KG 3450 Chocolate BIS UN 1235 Energia elétrica NULL 8091 Banana caturra NULL Ao executar o comando SQL anterior, seriam retornados apenas dois registros (Maçã fuji e Chocolate BIS), pois há dois registros na tabela ITEM que não têm valor correspondente na tabela UNIDADE. Lembre-se que NULL é ausência de valor. O comando de junção JOIN é uma alternativa ao comando anterior. O JOIN tem algumas variações e, de acordo com a variação utilizada, pode produzir resultados diferentes. No exemplo anterior, o INNER JOIN retornaria o mesmo resultado do SELECT anterior. O INNER JOIN vincula dados de duas ou mais tabelas e retorna somente os registros que atenderem à condição de igualdade definida no comando. No exemplo anterior ficaria: SELECT UNIDADE.DS_UNIDADE, UNIDADE.NR_DECIMAIS, ITEM.DS_ITEM, ITEM.SG_UNIDADE, ITEM.QT_DISPONIVEL FROM ITEM INNER JOIN UNIDADE ON (ITEM.SG_UNIDADE = UNIDADE.SG_UNIDADE); Observe que a cláusula WHERE não está mais presente no Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel comando, porém lembre-se que o resultado produzido será o mesmo do comando anterior. A tabela após o FROM indica de qual tabela estão sendo selecionados os registros, a tabela após o INNER JOIN indica a tabela que está sendo vinculada no SELECT e a cláusula ON indica os campos que estão vinculando as duas tabelas. Agora imaginemos que desejamos selecionar os registros das duas tabelas, até mesmo os que têm NULL no campo SG_UNIDADE da tabela ITEM. O comando LEFT JOIN possibilita este resultado: SELECT UNIDADE.DS_UNIDADE, UNIDADE.NR_DECIMAIS, ITEM.DS_ITEM, ITEM.SG_UNIDADE, ITEM.QT_DISPONIVEL FROM ITEM LEFT JOIN UNIDADE ON (ITEM.SG_UNIDADE = UNIDADE.SG_UNIDADE); A sintaxe básica é a mesma do INNER JOIN. Importante observar que a tabela antes do JOIN é a tabela “à esquerda” e a tabela depois do JOIN é a tabela “à direita”. No exemplo, ITEM está à esquerda e UNIDADE à direita do SELECT e o comando está dando prioridade de seleção aos registros da tabela à esquerda (LEFT no inglês) do SELECT. Há também outros tipos de JOIN que são utilizados com menos frequência: LEFT OUTER JOIN: seleciona todos os registros da tabela á esquerda e seleciona qualquer registro da tabela á direita que corresponda à condição ON; RIGHT OUTER JOIN: seleciona todos os registros da tabela á direita e seleciona qualquer registro da tabela á esquerda que corresponda à condição ON; FULL OUTER JOIN: seleciona todos das tabelas á esquerda e à direita independentemente da condição ON. Vários JOIN’s podem ser utilizados no mesmo SELECT. Por exemplo, selecionar o nome da pessoa da tabela PESSOA, o código e tipo do pedido da tabela PEDIDO, o código do item e a quantidade pedida da tabela PEDIDOITEM pode apresentar algumas das seguintes soluções: SELECT PESSOA.NM_PESSOA,PEDIDO.CD_PEDIDO, PEDIDO.TP_PEDIDO, PEDIDOITEM.CD_ITEM, PEDIDOITEM.QT_PEDIDA FROM PESSOA INNER JOIN PEDIDO ON (PESSOA.CD_PESSOA = PEDIDO.CD_PESSOA) INNER JOIN PEDIDOITEM ON (PEDIDO.CD_PEDIDO = PEDIDOITEM.CD_PEDIDO) AND (PEDIDO.TP_PEDIDO = PEDIDOITEM.TP_PEDIDO); SELECT PESSOA.NM_PESSOA,PEDIDO.CD_PEDIDO, PEDIDO.TP_PEDIDO, PEDIDOITEM.CD_ITEM, PEDIDOITEM.QT_PEDIDA FROM PEDIDO LEFT JOIN PEDIDOITEM ON (PEDIDO.CD_PEDIDO = PEDIDOITEM.CD_PEDIDO) AND (PEDIDO.TP_PEDIDO = PEDIDOITEM.TP_PEDIDO) INNER JOIN PESSOA ON (PESSOA.CD_PESSOA = PEDIDO.CD_PESSOA); É importante observar que para o comando JOIN funcionar conforme o desejado, deve-se obedecer uma das regras do Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel modelo relacional: toda a chave primária deve ser comparada na cláusula ON. Nos exemplos anteriores, ao comparar as tabelas PEDIDO e PEDIDOITEM, foram comparados os campos CD_PEDIDO e TP_PEDIDO. Caso fosse comparado apenas o campo CD_PEDIDO, o SGBD retornaria todos os pedidos que tivessem CD_PEDIDO igual em ambas as tabelas e qualquer valor para o campo TP_PEDIDO. Este tipo de “esquecimento” retornaria registros que não existem fisicamente no banco de dados, porque o SGBD faria uma combinação entre os registros para dar um retorno (isto faz parte da álgebra relacional que é obedecida pelos SGBD’s). Outro detalhe digno de apontamento é que a combinação entre registros de tabelas é, em determinados momentos, esperada. No exemplo anterior, o SELECT retornaria tantos registros quanto existentes na tabela PEDIDOITEM. Imagine que haja um registro na tabela PEDIDO, e que haja três registros com o mesmo código e tipo de pedido na tabela PEDIDOITEM, o SELECT resultante retornaria três registros. Comandos de Agrupamento Em SQL, os comandos de agrupamento executam operações (como contagem, somas e médias, por exemplo) sobre os registros de uma ou mais tabelas. Como resultado, estas operações podem produzir um ou mais grupos de dados. Exemplo de funções de agrupamento: Funções de agrupamento COUNT Faz a contagem dos registros SUM Faz a soma dos valores de colunas do tipo numérico AVG Faz a média dos valores de colunas do tipo numérico MIN Seleciona o menor valor da coluna MAX Seleciona o maior valor da coluna Sintaxe básica: SELECT COUNT(*) FROM PESSOA; Este comando faz a contagem de todos os registros da tabela PESSOA. SELECT SUM(ITEM.QT_DISPONIVEL) FROM ITEM; Este comando soma todos os valores do campo QT_DISPONIVEL da tabela ITEM. SELECT AVG(PEDIDOITEM.VL_UNITARIO) FROM PEDIDOITEM; Este comando faz a média dos valores unitários registrados nos pedidos. A primeira impressão é que parece estranho fazer a soma ou a média de todos os registros de uma determinada tabela. Os comandos de agrupamento podem ser utilizados com a cláusula WHERE. Nos exemplos anteriores, poderíamos saber, por exemplo, apenas o valor médio de um determinado item. SELECT AVG(PEDIDOITEM.VL_UNITARIO) FROM PEDIDOITEM WHERE (PEDIDOITEM.ITEM = 11); Observe que este comando resultará apenas um registro e que este ainda pode não ser o resultado que você estava esperando. Imagine que o resultado esperado sejao código de cada um dos itens e sua respectiva média do lado. Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel SELECT PEDIDOITEM.CD_ITEM, AVG(PEDIDOITEM.VL_UNITARIO) FROM PEDIDOITEM GROUP BY PEDIDOITEM.CD_ITEM; Neste caso foi necessário utilizar a cláusula GROUP BY, que vai retornar o código do item e sua respectiva média. Caso não utilize a cláusula GROUP BY, neste caso o SGBD não conseguirá montar o agrupamento. Exercitando um pouco mais o GROUP BY, poderíamos olhar os registros da tabela de cidades. Imagine que desejaríamos saber o número de cidades de cada estado. SELECT CIDADE.SG_UF, COUNT(*) FROM CIDADE GROUP BY CIDADE.SG_UF; Ao observar o resultado, é possível perceber que nenhuma ordem de seleção é seguida, ou seja, as UF’s e as contagens são retornadas em qualquer ordem. Caso deseje selecionar ordenando pela UF, poderia fazer da seguinte forma: SELECT CIDADE.SG_UF, COUNT(*) FROM CIDADE GROUP BY CIDADE.SG_UF ORDER BY CIDADE.SG_UF; Caso deseje selecionar ordenando pela contagem (coluna COUNT), há pelo menos duas formas de fazer. A primeira delas é passando a posição da coluna no ORDER BY, observe que a primeira coluna do SELECT é a sigla da UF e a segunda coluna é o COUNT. SELECT CIDADE.SG_UF, COUNT(*) FROM CIDADE GROUP BY CIDADE.SG_UF ORDER BY 2 DESC; A segunda forma é dando um nome para a coluna e utilizando o nome dado no ORDER BY. Neste caso estamos renomeando a coluna COUNT para CONTA e utilizando este nome na ordenação. SELECT CIDADE.SG_UF, COUNT(*) AS CONTA FROM CIDADE GROUP BY CIDADE.SG_UF ORDER BY CONTA DESC; Outro detalhe importante é a filtragem de registros no resultado de um comando de agrupamento. A cláusula WHERE filtra registros antes que o agrupamento aconteça. Caso deseje filtrar registros nos dados agrupados, a cláusula HAVING deve ser utilizada. Por exemplo, filtrar todas as UF’s com mais de cinco cidades: SELECT CIDADE.SG_UF, COUNT(*) AS CONTA FROM CIDADE GROUP BY CIDADE.SG_UF HAVING COUNT(*) > 5 ORDER BY CONTA DESC; Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel Subselects Um subselect é um comando SELECT dentro de outro. Normalmente, um subselect é utilizado quando é necessário um processamento extra para obter um resultado no select principal. Deve-se tomar bastante cuidado na utilização de subselects, uma vez que este tipo de comando exige bastante processamento tanto do SDBD quanto do servidor. Imagine um comando que precise retornar o código, descrição do item, a quantidade total de pedidos de compra e a quantidade total de pedidos de venda para cada produto. Este comando pode ser feito pela utilização de SUM fazendo uma junção entre o item e o pedido. Porém, em determinadas situações, os comandos de agrupamento não são aplicáveis, principalmente quando não há como fazer o agrupamento (GROUP BY) ou então quando não há uma ligação direta entre as tabelas. Neste caso, os subselects podem ser utilizados. SELECT ITEM.CD_ITEM, ITEM.DS_ITEM, (SELECT SUM(PEDIDOITEM.QT_PEDIDA) FROM PEDIDOITEM WHERE (PEDIDOITEM.CD_ITEM = ITEM.CD_ITEM) AND (PEDIDOITEM.TP_PEDIDO = 'C')) AS QT_COMPRADA, (SELECT SUM(PEDIDOITEM.QT_PEDIDA) FROM PEDIDOITEM WHERE (PEDIDOITEM.CD_ITEM = ITEM.CD_ITEM) AND (PEDIDOITEM.TP_PEDIDO = 'V')) AS QT_VENDIDA FROM ITEM; Neste exemplo, os dados estão sendo selecionados da tabela ITEM (a tabela do FROM). Para cada registro retornado está sendo feito um subselect na tabela de itens de pedido primeiro somando as quantidades de pedidos de compra e em seguida as quantidades de pedidos de venda. Observe no exemplo que os subselects sempre estão se referenciando ao select principal, no caso cada pedido fará a soma para o item corrente (PEDIDOITEM.CD_ITEM = ITEM.CD_ITEM). Se esta comparação não existisse, o subselect retornaria a quantidade pedida total e não somente a quantidade do item corrente. Outro caso de utilização dos subselects é quando desejamos filtrar os dados baseando-se em informações que estão em outras tabelas. Para isto, pode-se utilizar as cláusulas IN, NOT IN, EXISTS, NOT EXISTS ou =. Por exemplo: selecionar o código e descrição somente dos itens que têm registros na tabela itens de pedido (PEDIDOITEM). SELECT ITEM.CD_ITEM, ITEM.DS_ITEM FROM ITEM WHERE (ITEM.CD_ITEM IN (SELECT PEDIDOITEM.CD_ITEM FROM PEDIDOITEM)); Este comando seleciona somente registros que atendam a cláusula IN, que por sua vez espera o resultado do SELECT interno. O comando compara os valores do campo ITEM.CD_ITEM com os valores do campo PEDIDOITEM.CD_ITEM retornados pelo subselect. A cláusula EXISTS tem função semelhante à cláusula IN, com a diferença de que EXISTS não compara o valores de campos, mas apenas verifica se o registro existe (como Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel podemos deduzir pelo nome da cláusula). SELECT ITEM.CD_ITEM, ITEM.DS_ITEM FROM ITEM WHERE (EXISTS (SELECT * FROM PEDIDOITEM)); Este comando seleciona somente registros da tabela ITEM que existam no subselect. “MULTIPLE ROWS IN SINGLETON SELECT”. Este é um erro bastante comum para os iniciantes no mundo dos subselects. Quando o igual (=) é utilizado ao invés de IN ou EXISTS, espera-se que o subselect retorne apenas um registro, caso haja mais de um registro retornado pelo subselect, o SGBD avisará que não pode fazer a operação, porque há mais de um registro retornado para fazer a comparação. SELECT ITEM.CD_ITEM, ITEM.DS_ITEM FROM ITEM WHERE (ITEM.CD_ITEM = (SELECT PEDIDOITEM.CD_ITEM FROM PEDIDOITEM)); Este comando não será executado porque há mais de um registro para o mesmo item na tabela de itens de pedido. Uma solução seria, por exemplo, selecionar utilizando a cláusula DISTINCT que traz um resultado único para cada valor. SELECT ITEM.CD_ITEM, ITEM.DS_ITEM FROM ITEM WHERE (ITEM.CD_ITEM = (SELECT DISTINCT PEDIDOITEM.CD_ITEM FROM PEDIDOITEM)); A exclusão de registros tem comportamento semelhante a seleção, uma vez que o subselect será utilizado apenas na cláusula WHERE. Por exemplo: excluir todas as naturezas de operação que não estão sendo utilizadas pelos pedidos: DELETE FROM NATUREZA WHERE (NOT EXISTS (SELECT * FROM PEDIDO WHERE (PEDIDO.CD_NATUREZA = NATUREZA.CD_NATUREZA)); Também pode-se utilizar subselects para a atualização de registros, principalmente quando o valor a ser atualizado não está na própria tabela de UPDATE. Uma situação prática é a atualização do custo médio dos itens. Todos os registros da tabela item estão com o campo VL_CUSTOMEDIO zerado, porém o custo médio (no nosso modelo) é obtido pela média dos valores unitários dos itens nos pedidos de compra. Observe que estamos envolvendo uma atualização na tabela ITEM e a média está na tabela PEDIDOITEM. O UPDATE para resolver este problema poderia ser: UPDATE ITEM SET ITEM.VL_CUSTOMEDIO = (SELECT AVG(PEDIDOITEM.VL_UNITARIO) FROM PEDIDOITEM WHERE (PEDIDOITEM.CD_ITEM = ITEM.CD_ITEM) AND (PEDIDOITEM.TP_PEDIDO = ‘C’)); Esta é uma operação bastante custosa para o SGBD, porque a cada registro atualizado deve ser feita uma varredura para obter a média na tabela PEDIDOITEM. Um caso bastante interessante é a utilização de INSERT junto com o SELECT. Esta combinação é utilizada para incluir Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel registros em uma tabela baseados em uma seleção. Observe que a tabela PEDIDO tem apenas pedidos de compra (TP_PEDIDO = ‘C’). Imagine que por algum motivo desejaríamos ter pedidos de venda com os mesmos dados (número, natureza de operação, pessoa,data de emissão e valor total) apenas o tipo será diferente (TP_PEDIDO = ‘V’). Neste caso, faríamos uma inclusão na tabela selecionando dados dela mesma: INSERT INTO PEDIDO SELECT CD_PEDIDO, 'V', CD_PESSOA, CD_NATUREZA, DT_EMISSAO, VL_TOTAL FROM PEDIDO WHERE (PEDIDO.TP_PEDIDO = 'C'); No exemplo estaremos incluindo registros na tabela pedido (INSERT INTO PEDIDO), selecionando o código do pedido, o valor ‘V’, o código da pessoa, o código da natureza de operação, a data de emissão e o valor total dos pedidos como tipo igual a ‘C’. Deve-se tomar muito cuidado na elaboração deste tipo de comando: o número e a ordem dos campos selecionados na lista deve ser igual ao número e a ordem dos campos na tabela. Observe que a inclusão só aconteceu porque não houve estouro de chave primária, uma vez que não existiam pedidos de venda (TP_PEDIDO = ‘V’). Uma outra forma de utilização de subselect com insert é incrementando a chave primária. INSERT INTO CIDADE (CD_CIDADE, NM_CIDADE, SG_UF) VALUES ((SELECT COALESCE(MAX(CD_CIDADE),0) +1 FROM CIDADE), 'TESTE', 'PR'); Algumas vezes duas ou mais tabelas têm estrutura idêntica ou colunas que contém dados similares. Quando a estrutura das tabelas coincide, informações destas tabelas podem ser combinadas para produzir um resultado. A cláusula UNION seleciona os dados de cada tabela, adiciona um resultado ao final do outro e procura eliminar os registros duplicados. O modelo de dados sugerido para o curso foi desenvolvido para evitar a existência de tabelas com estrutura repetida, mas poderíamos ter, por exemplo, uma tabela para os pedidos de compra e outra tabela para os pedidos de venda. Neste caso, na eventual necessidade de produzir algum resultado com os dados dos pedidos de compra e dos pedidos de venda, poderia ser utilizada a cláusula UNION. Para ilustrar esta situação, vamos utilizar as tabelas PEDIDOITEM e ESTOQUE. Observe que há alguma semelhança entre estas tabelas: ambas têm o código do item, uma data, um campo de quantidade e um campo de valor unitário. Imagine que desejássemos selecionar os pedidos dos itens e também os lançamentos de estoque. SELECT 'P' AS TIPO, PEDIDOITEM.CD_ITEM, ITEM.DS_ITEM, PEDIDOITEM.QT_PEDIDA AS QUANTIDADE, PEDIDOITEM.VL_UNITARIO AS VALOR FROM PEDIDOITEM LEFT JOIN ITEM ON (PEDIDOITEM.CD_ITEM = ITEM.CD_ITEM) UNION SELECT 'E' AS TIPO, ESTOQUE.CD_ITEM, ITEM.DS_ITEM, ESTOQUE.QT_MOV AS QUANTIDADE, ESTOQUE.VL_UNITARIO AS VALOR FROM ESTOQUE Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel LEFT JOIN ITEM ON (ESTOQUE.CD_ITEM = ITEM.CD_ITEM); O comando é composto de dois SELECTs separados por UNION. Observe que cada SELECT tem o mesmo número de campos selecionados (cinco ao todo). Cada campo em cada posição é do mesmo tipo nos dois SELECTs. CD_ITEM é o segundo campo no SELECT de cima e no de baixo e ambos são do tipo INTEGER. QT_PEDIDA e QT_MOV é o terceiro campo no SELECT de cima e no de baixo e ambos são do tipo DOUBLE PRECISION. O primeiro campo serve apenas para diferenciar no resultado se o registro retornado é do pedido ou do estoque. Em uma seleção pode ser necessário eliminar registros duplicados. Como é possível existirem vários lançamentos de estoque para o mesmo item, este comando retornaria um registro para cada combinação entre as tabelas ESTOQUE e ITEM. SELECT ESTOQUE.CD_ITEM, ITEM.DS_ITEM FROM ESTOQUE LEFT JOIN ITEM ON (ESTOQUE.CD_ITEM = ITEM.CD_ITEM); A cláusula DISTINCT elimina os registros duplicados no resultado: SELECT DISTINCT ESTOQUE.CD_ITEM, ITEM.DS_ITEM FROM ESTOQUE LEFT JOIN ITEM ON (ESTOQUE.CD_ITEM = ITEM.CD_ITEM); Exercícios INSERT, UPDATE, DELETE, SELECT 1) Selecionar todos os pedidos cujo código (CD_PEDIDO) esteja entre 100 e 150 e cujo tipo (TP_PEDIDO) seja igual a C. 2) Selecionar todos os itens cuja sigla da unidade de medida (SG_UNIDADE) comece com M ou com m. 3) Excluir os itens cujo código (CD_ITEM) seja maior do que 5320. 4) Alterar o tipo da pessoa (TP_PESSOA) para 1 somente das pessoas cujo código (CD_PESSOA) seja menor do que 10. 5) Selecionar todos os itens cujo código (CD_ITEM) seja maior do que 58 e menor ou igual a 23. Esta seleção de dados é possível ? Justifique. 6) Excluir o item cujo código (CD_ITEM) seja igual a 2350. A exclusão foi possível ? Justifique. Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel 7) Selecionar o código da pessoa (CD_PESSOA) cujo nome seja Basf S.A. Selecione o código da cidade (CD_CIDADE) cujo nome seja Palmas (Incluir a cidade caso não exista). Incluir um registro na tabela PESSOA_END para a pessoa e para a cidade encontradas, ou seja, crie um endereço novo para a pessoa. 8) Selecionar, na tabela ESTOQUE, o código do item (CD_ITEM), a quantidade movimentada (QT_MOV) dos itens cujo valor unitário (VL_UNITARIO) esteja entre 10,00 e 25,00. 9) Selecionar, na tabela ESTOQUE, o código do item (CD_ITEM), a quantidade movimentada (QT_MOV), o valor unitário (VL_UNITARIO) e uma nova coluna que é o resultado da multiplicação dos campos QT_MOV e VL_UNITARIO. Esta nova coluna deve se chamar VL_TOTAL. 10) Incluir um registro na tabela PESSOA. O código da pessoa (CD_PESSOA) deve ser 100, o nome da pessoa (NM_PESSOA) deve ser o seu nome. JOIN 11) Selecionar os itens cuja unidade de medida tenha menos de duas casas decimais (NR_DECIMAIS < 2). 12) Selecionar todos os pedidos cuja natureza de operação não atualize custo médio (ST_ATCUSTOMEDIO = ‘N’). 13) Selecionar todos os pedidos cuja pessoa seja do tipo 1 (TP_PESSOA = 1). 14) Selecionar o código do item, a descrição do item, a sigla da unidade de medida e o número de casas decimais da unidade de medida ordenando pela sigla da unidade de medida. 15) Selecionar o código do item, a descrição do item cuja data de movimentação em estoque seja maior do que 01/03/2002. A data deve ser passada como se fosse um caractere no formato mm/dd/aaaa (‘03/01/2002’). 16) Selecionar o código do pedido, o tipo do pedido, o nome da pessoa do pedido, o código do item pedido, a descrição do item pedido, a quantidade pedida e o valor unitário do item. AGRUPAMENTO 17) Selecionar os registros da tabela PEDIDO contando (COUNT(*)) e agrupando pela data de emissão (DT_EMISSAO). 18) Filtrar o resultado do exercício anterior trazendo apenas registros que tenham mais de 14 pedidos. Ordenar dos registros que tem mais pedidos para os que tem menos pedidos. Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel 19) Selecionar a contagem (COUNT(*)) dos registros da tabela ITEM, retornando e agrupando pela descrição da unidade de medida. Observe o modelo e atente para o detalhe de que é necessária uma junção entre a tabela ITEM e a tabela UNIDADE para que o comando seja escrito corretamente. 20) Selecionar o valor médio unitário (AVG) do item na tabela de itens do pedido (PEDIDOITEM) retornando e agrupando pela descrição do item. Neste caso deverá ser feita uma junção entre PEDIDOITEM e ITEM. 21) Selecionar a contagem dos registros da tabela PEDIDO, retornando e agrupando pelo nome da pessoa e ordenando da pessoa que tem mais pedidos para a pessoa que tem menos pedidos. Deve ser feita uma junção entre as tabelas PEDIDO e PESSOA. 22) Selecionar a soma do valor total (unitário * quantidade) dos pedidos da tabela de itens de pedidos (PEDIDOITEM), agrupando pelo código e tipo do pedido. Lembre-se que SQL permite operações matemáticas como adições, subtrações, multiplicações e divisões. SUBSELECT 23) Utilizando a cláusula IN, selecionar a sigla e a descrição das unidadesde medida que estão na tabela ITEM. 24) Utilizando a cláusula EXISTS, selecionar a sigla e a descrição das unidades de medida que não estão na tabela ITEM. 25) Selecionar o código e a descrição do item e um subselect que retorne a soma da quantidade movimentada do item na tabela estoque. 26) Selecionar o código e a descrição do item e um subselect que retorne o valor médio do item na tabela estoque (somente deve ser feita a média quando ST_ATCUSTOMEDIO = 'S'). 27) Utilizando a cláusula EXISTS Selecione todas as pessoas que não estão na tabela PEDIDO. 28) Atualizar para 'não utilizada' o nome de todas as pessoas que não estão na tabela PEDIDO. 29) Selecionar todas as naturezas de operação que não estão na tabela PEDIDO. 30) Atualizar para 'não utilizada' a descrição de todas as naturezas de operação que não estão na tabela PEDIDO. 31) Excluir todas as unidades de medida que não estão na tabela ITEM. 32) Selecionar o código e o nome da pessoa, um subselect que retorne a contagem dos pedidos de compra (TP_PEDIDO = 'C') Banco de Dados Linguagem SQL Prof. Fabiano Luiz Carniel e outro subselect que retorne a contagem dos pedidos de venda (TP_PEDIDO = 'V').
Compartilhar