SQL aula 2 by Iranildo Junior

27/05/2012

Banco de Dados I SQL – Parte 2 e 3 Ludmila de Almeida Pedrosa ludmila.apedrosa@gmail.com

Manipulando Dados - DML CONSULTA

SELECT

INCLUSÃO

INSERT

ALTERAÇÃO

UPDATE

EXCLUSÃO

DELETE

27/05/2012

SQL - Consultas • Sintaxe para consultar dados em tabela - ANSI 89: SELECT atributo1, atributo2, ... FROM tabela 1, tabela 2, ... [WHERE condição] • Onde: – SELECT: Especifica as colunas e expressões exibidas no resultado da consulta. – FROM: Especifica as tabelas que contêm os dados exibidos no resultado da consulta. – WHERE: Especifica as condições usadas para filtrar registros no resultado da consulta, sendo uma cláusula opcional.

SQL - Consultas • Todos os exemplos usarão o seguinte Modelo:

27/05/2012

SQL - Consultas • Para selecionar todos os dados (atributos): Usar * • Exibir todos os dados de todos os clientes: SELECT * FROM Cliente • Exibir todos os dados de todos os funcionários: SELECT * FROM Funcionario • Exibir todos os dados de todos os produtos: SELECT * FROM Produto

SQL - Consultas • Exibir código, nome e telefone de todos os clientes: SELECT codigo, nome, fone FROM Cliente • Exibir código, nome, estado civil e data de nascimento de todos os funcionários: SELECT codigo, nome, estcivil, datanasc FROM Funcionario • Exibir código, nome, preço de custo e preço de venda de todos os produtos : SELECT codigo, nome, custo, venda FROM Produto

27/05/2012

SQL – Eliminar Duplicação • Sintaxe: SELECT DISTINCT <lista de colunas> FROM <tabela>

SQL – Eliminar Duplicação • Exemplo1 - Exibir o código de todos os clientes que já fizeram pedido à empresa, sem repetições: SELECT DISTINCT cliente FROM Pedido • Exemplo2 - Exibir todos os bairros com os respectivos códigos da cidade dos funcionários da empresa, sem repetições: SELECT DISTINCT bairro, cidade FROM Funcionário

27/05/2012

SQL – Exibir Colunas Calculadas • Sintaxe: SELECT <lista de colunas, expressão> FROM <tabela>

SQL – Exibir Colunas Calculadas • Exemplo1 - Exibir código, nome, quantidade em estoque, preço de venda e valor total (quantidade x preço de venda) para cada produto: SELECT Codigo, Nome, Quantest, Venda, ‘Total’= quantest*venda FROM Produto

• Resultado da Consulta: Código

Nome

Quantest

Venda

Total

Toalhas Artex

250,00

12.500,00

Iogurte Nestle

200

3,20

640,00

Abajur List

364,00

4.368,00

TV Plasma

3,65

43,85

Liquidificador

150

92,00

13.800,00

Computador Positivo

1.980,00

27/05/2012

SQL – Exibir Colunas Calculadas • Exemplo2 - Exibir código, nome, salário e o salário com 32% de aumento para todos os funcionários: SELECT Codigo, Nome, Salario, Salario*1.32 AS Novo_Salario FROM Funcionario

• Resultado da Consulta: Codigo

Nome

Salario

Novo_Salario

João da Silva

300,00

396,00

Maria de Souza

300,00

396,00

Luiza Costa

950,00

1.254,00

Francisco da Silva

390,00

514,80

Carla Tavares

290,00

382,80

SQL – Exibir Colunas Calculadas • Funções do SQL Server: – SUM: soma valores de um atributo – AVG: calcula a média dos valores de um atributo – MAX: exibe o maior valor de um atributo – MIN: exibe o menor valor de um atributo – COUNT: contar registros

27/05/2012

SQL – Exibir Colunas Calculadas • Exemplo1 – Exibir a soma dos salários dos funcionários da empresa: SELECT SUM(salario) AS Soma_Salarios FROM Funcionario • Exemplo2 – Exibir a média salarial dos funcionários da empresa: SELECT AVG(salario) AS Media_Salarios FROM Funcionario

SQL – Exibir Colunas Calculadas • Exemplo3 – Exibir o menor salário entre os funcionários: SELECT MAX(salario) AS Maior_Salario FROM Funcionario • Exemplo4 – Exibir o maior salário entre os funcionários: SELECT MIN(salario) AS Menor_Salario FROM Funcionario

27/05/2012

SQL – Exibir Colunas Calculadas • Exemplo5 – Exibir quantos clientes têm cadastro na empresa: SELECT COUNT(*) AS Qtde_Clientes FROM Cliente • Exemplo6 – Exibir quantos funcionário têm e_mail: SELECT COUNT(e_mail) AS Qtde_Email FROM Funcionário • Observação: para contar registros diferentes deve-se usar COUNT combinado com o DISTINCT.

SQL – Exibir Colunas Calculadas • Exemplo7 – Exibir a quantas cidades a empresa atende os clientes: SELECT COUNT(DISTINCT cidade) AS Qtde_Cidade FROM Cliente • Exemplo8 – Exibir quantos vendedores já atenderam a pedidos: SELECT COUNT(DISTINCT vendedor) AS Qtde_Vendedor FROM Pedido

27/05/2012

SQL – Cláusula From • Para cruzar dados que estão em tabelas diferentes: Estilo

Construção

ANSI 89

SELECT <campos> FROM TabelaA, TabelaB WHERE TabelaA.Coluna = TabelaB.Coluna

ANSI 92

SELECT <campos> FROM TabelaA INNER JOIN TabelaB ON TabelaA.Coluna = TabelaB.Coluna

SQL – Cláusula From • Exemplo 1 – Exibir o nome do cliente e a data que ele realizou um pedido, para todos os pedidos da base: SELECT C.nome, P.dataPedido FROM Cliente C, Pedido P WHERE C.codigo = P.cliente ou SELECT C.nome, P.dataPedido FROM Cliente C INNER JOIN Pedido P ON C.codigo = P.cliente •

Observações: – Para atributos que possuem o mesmo nome, em mais de uma tabela, é obrigatório incluir o nome da tabela (ou apelido) antes do nome do atributo. – Caso contrário não é obrigatório, mas é recomendado.

27/05/2012

SQL – Cláusula From • Exemplo 2 – Exibir o nome do funcionário, o seu salário, o nome do setor onde trabalha e o nome da função exercida para todos os funcionários: SELECT F.nome, F.salario, S.nome AS Setor, FU.nome AS funcao FROM Funcionario F, Setor S , Funcao FU WHERE F.setor = S.sigla AND F.funcao = FU.codigo ou SELECT F.nome, F.salario, S.nome AS Setor, FU.nome AS funcao FROM Funcionario F INNER JOIN Setor S ON F.setor = S.sigla INNER JOIN Funcao FU ON F.funcao = FU.codigo

SQL – Parte 2

Fim Próxima aula: Exercícios

27/05/2012

SQL – Cláusula Where • Usando operadores na cláusula WHERE: – Os operadores relacionais <, <=, >, >=, =, <> podem ser usados para testar valores de atributos. – Os operadores lógicos AND, NOT, OR podem ser usados para combinar condições.

SQL – Cláusula Where • Exemplo1 – Exibir o nome e o salário dos funcionários que ganham mais do que R$ 500,00: SELECT nome, salario FROM Funcionario WHERE salario > 500.00 • Exemplo 2 – Exibir o nome e o salário dos funcionários que ganham mais do que R$ 200,00 e são homens: SELECT nome, salario FROM Funcionario WHERE salario > 200.00 AND sexo = ‘M’

27/05/2012

SQL – Cláusula Where • Exemplo3 – Exibir o nome dos produtos, cujo preço de custo seja menor ou igual a R$ 100,00: SELECT nome FROM Produto WHERE custo <= 100.00 • Exemplo4 – Exibir o nome e o cargo dos clientes pessoa física: SELECT nome, cargo FROM Cliente WHERE tipo = ‘PF’

SQL – Cláusula Where • Exemplo5 – Exibir o preço médio de venda dos produtos do tipo 1: SELECT AVG(venda) AS Media FROM Produto WHERE tipo = 1 • Exemplo6 – Exibir a data de nascimento da funcionária mais nova da empresa: SELECT MAX(dataNasc) AS Mais_Nova FROM Funcionario WHERE sexo = ‘F’

27/05/2012

SQL – Cláusula Where • Usando operadores na cláusula WHERE: – Operador BETWEEN: usado para verificar se o valor de um atributo está em um intervalo de valores. – Operador IN: usado para verificar se o valor de um atributo está em um conjunto de valores. – Operador LIKE: usado para operações de comparação de strings.

SQL – Cláusula Where • Exemplo1 – Exibir o nome e o salário dos funcionários que ganham entre R$ 500,00 e R$ 1.500,00: SELECT nome, salario FROM Funcionario WHERE salario BETWEEN 500 AND 1500 • Exemplo 2 – Exibir o nome e o salário dos funcionários que ganham R$ 500,00 ou R$ 1.500,00: SELECT nome, salario FROM Funcionario WHERE salario IN (500, 1500)

27/05/2012

SQL – Cláusula Where • Exemplo3 – Exibir código e nome de todos os funcionários que tenham o nome começando por “M”: SELECT codigo, nome FROM Funcionario WHERE nome LIKE ‘M%’ • Resultado da Consulta: Código

Nome

Maria de Souza

SQL – Cláusula Where • Exemplo4 – Exibir código e nome de todos os funcionários que tenham o nome terminando por “Silva”: SELECT código, nome FROM Funcionario WHERE nome LIKE ‘%Silva’ • Resultado da Consulta: Código

Nome

João da Silva

Francisco da Silva

27/05/2012

SQL – Cláusula Where • Exemplo5 – Exibir código e nome de todos os funcionários que tenham “Costa” em qualquer parte do nome:

SELECT codigo, nome FROM Funcionario WHERE nome LIKE ‘%Costa%’ • Exemplo 6 – Exibir código e nome de todos os funcionários que tenham o nome começando por “Mar” seguido de um caractere qualquer e terminando por “a”:

SELECT codigo, nome FROM Funcionario WHERE nome LIKE 'Mar_a%'

SQL – Ordenando Resultados • Sintaxe: SELECT <lista de colunas> FROM <tabela(s)> ORDER BY <coluna> [ASC] [DESC] • Observação: – O default é a ordenação ascendente (ASC).

27/05/2012

SQL – Ordenando Resultados • Exemplo1 – Exibir código, nome e telefone de todos os clientes, ordenados ascendentemente pelo nome do cliente:

SELECT codigo, nome, fone FROM Cliente ORDER BY nome • Resultado da Consulta: Codigo

Nome

Fone

João Paulo Correia

3245-8999

JR Representações

5841-9871

Larissa Maia

3227-9622

LT Comércio

3256-9981

Marcos Andrade

3338-2100

Maria Helena Ferreira

3225-6987

SQL – Ordenando Resultados • Exemplo2 – Exibir código, nome, estado civil e data de nascimento de todos os funcionários, descendentemente pela data de nascimento:

ordenados

SELECT codigo, nome, estCivil, dataNasc FROM Funcionario ORDER BY dataNasc DESC

• Resultado da Consulta: Código

Nome

EstCivil

DataNasc

Luiza Costa

1980-04-04

João da Costa

1978-02-05

Francisco da Silva

1970-06-03

Maria de Souza

1970-01-01

Carla Tavares

1969-01-08

27/05/2012

SQL – Ordenando Resultados • Exemplo3 – Exibir código, nome e tipo de todos os produtos existentes na empresa, ordenados pelo tipo em ordem ascendente e pelo nome do produto em ordem descendente: SELECT P.codigo, T.nome AS Tipo, P.nome AS Produto FROM Produto P, Tipo T WHERE P.tipo = T.codigo ORDER BY T.nome ASC, P.nome DESC

•

Resultado da Consulta: Código

Tipo

Produto

Alimentação

Pizza Sadia

Cama

Toalhas Artex

Decoração

Abajur Alist

Eletro

TV Plasma

Eletro

Liquidificador

Eletro

Computador Positivo

Lacticínios

Iorgute Nestlé

SQL – Agrupando Informações • Sintaxe: SELECT <lista de colunas> FROM <tabela(s)> GROUP BY <coluna(s)> [HAVING <condição>] • Observação: – A cláusula HAVING é opcional.

27/05/2012

SQL – Agrupando Informações • Exemplo1 – Exibir a quantidade de homens e mulheres da empresa: SELECT Sexo, COUNT(*) AS Quantidade FROM Funcionario GROUP BY sexo • Resultado da Consulta: Sexo

Quantidade

SQL – Agrupando Informações • Exemplo2 – Para cada cliente da empresa (nome), apresentar a quantidade de pedidos realizados: SELECT C.nome, COUNT(*) AS Qtde_Pedidos FROM Cliente C, Pedido P WHERE P.cliente = C.codigo GROUP BY C.nome

• Resultado da Consulta: Nome

Qtde_Pedidos

João Paulo Correia

JR Representações

Larissa Maia

LT Comércio

Marcos Andrade

Maria Helena Ferreira

27/05/2012

SQL – Agrupando Informações • Exemplo3 – Para cada cliente da empresa (nome), apresentar a quantidade de pedidos realizados. Exibir os resultados, apenas se a quantidade de pedidos for superior a cinco pedidos: SELECT C.nome, COUNT(*) AS Qtde_Pedidos FROM Cliente C, Pedido P WHERE P.cliente = C.codigo GROUP BY C.nome HAVING COUNT(*) > 5

• Resultado da Consulta: Nome

Qtde_Pedidos

JR Representações

Maria Helena Ferreira

SQL – Agrupando Informações • Exemplo4 – Para cada setor da empresa (sigla e nome), apresentar a quantidade de funcionários e o somatório dos seus salários: SELECT F.Setor, F.Nome, COUNT(*) AS Qtde_Func, SUM(salário) AS Total FROM Funcionario F, Setor S WHERE F.setor = S.sigla GROUP BY F.Setor, F.Nome

• Resultado da Consulta: Setor

Nome

Qtde_Func

ADM

Administração

Total (salário) 950,00

COV

Compra e Venda

590,00

MKT

Marketing

300,00

SEG

Segurança

390,00

27/05/2012

SQL – Agrupando Informações • Exemplo5 – Para cada setor da empresa (sigla e nome), apresentar a quantidade de funcionários e o somatório dos seus salários. Exibir os resultados, apenas se a soma dos salários for maior do que R$ 500,00: SELECT F.Setor, F.Nome, COUNT(*) AS Qtde_Func, SUM(salário) AS Total FROM Funcionario F, Setor S WHERE F.setor = S.sigla GROUP BY F.Setor, F.Nome HAVING SUM(salario) > 500

• Resultado da Consulta: Setor

Nome

Qtde_Func

ADM

Administração

Total (salário) 950,00

COV

Compra e Venda

590,00

SQL – Sub-Consultas • O que é? – Uma sub-consulta é uma consulta SQL cujo resultado é utilizado por outra consulta mais externa, de forma encadeada e contida no mesmo comando SELECT. – Uma sub-consulta deve ser delimitada parênteses e é avaliada apenas uma vez.

entre

– A consulta mais externa (principal) depende da avaliação e resultado da sub-consulta. – Portanto em consultas encadeadas, as consultas internas (sub-consultas) são realizada antes.

27/05/2012

SQL – Sub-Consultas • Retorno de uma sub-consulta: – Uma sub-consulta de valor único retorna apenas um valor (registro). – Uma sub-consulta de valores múltiplos retorna uma lista de valores. Neste caso, deve-se utilizar as clausulas IN ou NOT IN.

SQL – Agrupando Informações • Exemplo1 – Exibir o nome e data de nascimento do funcionário(a) mais novo da empresa: SELECT nome, dataNasc Retorna apenas um valor/registro. FROM Funcionario WHERE dataNasc = (SELECT MAX(dataNasc) FROM Funcionario)

• Resultado da Consulta: Nome

DataNasc

Luiza Costa

04/04/1980

27/05/2012

SQL – Agrupando Informações • Exemplo2 – Exibir o código, o nome e a quantidade em estoque do produto que tem a menor quantidade em estoque da empresa: SELECT codigo, nome, quantest Retorna apenas um valor/registro. FROM Produto WHERE quantest = (SELECT MIN(quantest) FROM Produto) • Resultado da Consulta: Código

Nome

Quantest

Abajur Alist

TV Plasma

SQL – Agrupando Informações • Exemplo3 – Exibir o código e o nome dos funcionários que nunca realizaram uma venda: SELECT codigo, nome Retorna vários registros. FROM Funcionario WHERE codigo NOT IN (SELECT vendedor FROM Pedido)

27/05/2012

SQL - Consultas SELECT Especifica as colunas e expressões exibidas no resultado da consulta. FROM

Especifica as tabelas que contêm os dados exibidos no resultado da consulta.

WHERE

Especifica as condições usadas para filtrar registros no resultado da consulta.

GROUP BY

Agrupa as linhas da consulta com base nos valores de uma das colunas.

HAVING

Especifica as condições usadas para filtrar agrupamentos de dados no resultado da consulta. Só deve ser usado com GROUP BY.

ORDER BY

Classifica os resultados da consulta com base nos dados de uma ou mais colunas.

SQL – Parte 3

Fim Próxima aula: Exercícios