SQL

SQL
Structured Query Language
Ana Gavinho
Fevereiro 2001
Banco de Dados
1
SQL - Structured Query Language
• A álgebra relacional especifica uma seqüência de operações que
determina como obter os dados.
• SGBD comerciais provem uma linguagem declarativa de alto nível
para acesso a banco de dados, onde se especifica o que se deseja
como resposta. A mais conhecida é a SQL.
• Originalmente projetada e implementada pela IBM, possui as versões
SQL1 (86), SQL2 (92) e SQL3 (conceitos de OO).
• Usa os termos tabela / linha / coluna para relação / tupla / atributo.
• Possui comandos para:
> consulta de dados (query)
> manipulação de dados (inclusão, exclusão, alteração)
> definição de dados
Fevereiro 2001
Banco de Dados
2
Tabelas usadas
EMPREGADO
Nome Matricula DataNasc Endereco Sexo Salario Supervisor Depto
DEPARTAMENTO
DeptoNum DeptoNome MatrGerente
TRABALHA-EM
DEPTO-LOCAL
Matricula PCodigo Horas
Depto Localizacao
PROJETO
PNome PCodigo PLocalizacao Depto
DEPENDENTE
Matricula NomeDepend Sexo DataNasc Parentesco
Fevereiro 2001
Banco de Dados
3
Comandos para consulta de dados
• Uma consulta (query) em SQL consiste de 6 cláusulas, onde as
duas primeiras são obrigatórias.
• As cláusulas entre {...} são opcionais.
SELECT [ ALL / DISTINCT ] < lista-atributos >
FROM tabela1 { , tabela2 ... }
{ WHERE < condição > }
{ GROUP BY atributo1 { , atributo2 ... } }
{ HAVING < condição > }
{ ORDER BY atributo1 { , atributo2 ... } }
Fevereiro 2001
Banco de Dados
4
Select - seleção de campos
• A seleção de campos pode ser efetuada:
> pelos nomes dos campos
> por um asterisco (*), que seleciona todos os campos
»Liste todos os dados de todos os empregados.
SELECT *
FROM
EMPREGADO ;
» Liste o nome e matricula de todos os funcionários.
SELECT Nome, Matricula
FROM
EMPREGADO ;
» Liste o nome e salário de todos os funcionários.
SELECT Nome, Salario
FROM
EMPREGADO ;
Fevereiro 2001
Banco de Dados
5
Select - condição para a seleção de linhas
• A condição do ‘WHERE’ indica as linhas da tabela a serem
selecionadas.
»Liste o nome e matrícula dos funcionários do departamento 20.
SELECT Nome, Matricula
FROM EMPREGADO
WHERE Depto = 20 ;
»Liste o nome e matricula dos funcionários mulheres.
SELECT Nome, Matricula
FROM EMPREGADO
WHERE Sexo = `F` ;
»Liste o nome e matrícula das mulheres do departamento 20.
SELECT
FROM
WHERE
AND
Fevereiro 2001
Nome, Matricula
EMPREGADO
Depto = 20
Sexo = `F` ;
Banco de Dados
6
Select - seleção de linhas por substrings
• Recuperação por aproximação usando LIKE :
> “_” - uma ocorrência de qualquer carater
> “%” - nenhuma ou várias ocorrências de quaisquer caracteres
» Liste os nomes de funcionários que começam por `J`.
SELECT
FROM
WHERE
Nome
EMPREGADO
Nome LIKE ‘J%’ ;
»Liste os nomes de funcionários que tenham ‘Silva’ como parte do
nome.
SELECT
FROM
WHERE
Fevereiro 2001
Nome
EMPREGADO
Nome LIKE ‘%Silva%’
Banco de Dados
7
Select - eliminando linhas duplicadas
• As palavras-chave ‘distinct’ and ‘all’ especificam o que fazer com
linhas duplicadas no resultado da seleção:
> linhas duplicada são eliminadas pela palavra ‘DISTINCT’
> ‘ALL’ é o default e retorna todas as linhas selecionas
» Liste os salários dos empregados.
SELECT Salário
FROM EMPREGADO ;
ou
SELECT ALL Salário
FROM EMPREGADO ;
» Liste os diferentes salários pagos aos empregados.
SELECT DISTINCT Salario
FROM EMPREGADO ;
Fevereiro 2001
Banco de Dados
8
Select - ordenando linhas da consulta
• A clausula ORDER BY faz a ordenação de linhas a partir dos
valores de colunas em ordem ascendente (ASC), que é seu default, ou
em ordem descendente (DESC).
» Liste o nome e salário dos funcionários ordenados pelo nome do
funcionário.
SELECT
Nome, Salario
FROM
EMPREGADO
ORDER BY Nome ;
» Liste o nome e salário dos funcionários ordenados do maior ao
menor salário e pelo nome do funcionário.
SELECT
Nome, Salario
FROM
EMPREGADO
ORDER BY Salario DESC, Nome ;
Fevereiro 2001
Banco de Dados
9
Select - operações aritméticas com colunas
• Os operadores aritméticos + , - , * e / podem ser usados em
colunas numéricas.
• Os valores da tabela original NÃO são alterados
» Liste os diferentes salários pagos e o novo salário com aumento
de 10%.
SELECT
FROM
Salario, 1.1 * Salario
EMPREGADO ;
» Liste o nome e salário dos funcionários com aumento de 10%.
SELECT
FROM
Nome, 1.1 * Salario
EMPREGADO ;
» Liste o nome dos funcionários e o aumento a receber.
SELECT
FROM
Fevereiro 2001
Nome, (1.1 * Salario - Salario)
EMPREGADO ;
Banco de Dados
10
Select - renomeando colunas, incluindo textos
• Para facilitar o entendimento do resultado de uma pesquisa,
podemos definir um novo nome para uma coluna ou incluir um texto
no resultado.
» Liste os diferentes salários pagos e o novo salário com aumento de
10%.
SELECT
FROM
Salario
AS
1.1 * Salario AS
EMPREGADO ;
`Salario atual`,
`Novo salario`
» Liste o nome de funcionários e o aumento a receber.
SELECT
FROM
Fevereiro 2001
`O funcionário `,
Nome
AS `nome funcionario`,
`recebera um aumento de `,
0.1 * Salario AS `aumento`
EMPREGADO ;
Banco de Dados
11
Exercícios 1 - uso do comando Select
• Liste o nome e data de nascimento dos funcionários ordenados por data de
nascimento.
• Liste o nome e salário dos funcionários ordenados por nome.
• Liste o nome e parentesco dos dependentes.
• Liste o nome dos projetos e sua localização ordenados por localização.
• Liste a matricula, nome e salário dos empregados que ganham entre R$
200,00 e R$500,00.
• Liste o nome dos dependentes do sexo masculino.
• Liste o nome dos projetos localizados no Meier, em ordem decrescente.
• Liste o nome do departamento de numero 10.
• Liste o nome das mulheres que trabalham no departamento 10.
• Liste o número do departamento de Compras.
• Liste o numero e nome dos departamento começados pela letra ´C´.
Fevereiro 2001
Banco de Dados
12
Exercícios 1 - uso do comando Select (cont.)
• Liste a matrícula e nome dos funcionários que tenham ‘Silva’ como parte
do nome.
• Liste a matrícula e nome dos funcionários que tenham ‘Silva’ como
último sobrenome.
• Liste o numero dos departamentos que tenham algum projeto.
• Liste o numero dos departamentos que tenham algum projeto, sem
duplicação de linhas.
• Liste os parentescos que existem na tabela DEPENDENTE.
• Liste os diferentes parentescos da tabela DEPENDENTE.
• Liste as localizações dos departamentos.
• Liste as diferentes localizações dos departamentos.
• Liste as localizações dos projetos.
• Liste as diferentes localizações dos projetos.
Fevereiro 2001
Banco de Dados
13
Select - uso de funções de agregação
•Sumarizam valores em um conjunto de linhas.
»Liste o total de empregados, a soma de salários, o maior salário, o
menor salário e a média.
SELECT COUNT (*), SUM(Salario), MAX(Salario), MIN(Salario),
AVG (Salario)
FROM EMPREGADO ;
»Liste a média salarial dos funcionários do departamento 20.
SELECT AVG (Salario) AS ‘Media salarial do depto 20’
FROM EMPREGADO
WHERE Depto = 20 ;
» Liste o número de departamentos da empresa.
SELECT COUNT (*) AS ‘Quantidade de depto`
FROM DEPARTAMENTO ;
Fevereiro 2001
Banco de Dados
14
Select - uso de funções de agregação (cont.)
• A cláusula ‘Distinct’ pode ser usada nas funções ‘Count’, ‘Sum’ e
‘Avg’ .
» Liste o total de diferentes valores de salários.
SELECT COUNT (DISTINCT Salario)
AS ‘Quantidade de diferentes salários pagos’
FROM EMPREGADO ;
» Liste o total pago aos funcionários da empresa.
SELECT SUM (Salario)
AS
‘Valor total dos salários pagos’ `
FROM EMPREGADO ;
» Liste o valor total de diferentes salário pagos na empresa.
SELECT SUM (DISTINCT Salario)
AS
‘Valor total dos diferentes salários pagos’ `
FROM EMPREGADO ;
Fevereiro 2001
Banco de Dados
15
Select - uso de funções de agregação (cont.)
• As funções retornam um valor numérico, e não um conjunto (set) de
valores.
»Liste o nome dos funcionários que tem dois ou mais dependentes.
SELECT Nome
FROM EMPREGADO E
WHERE (SELECT COUNT (*)
FROM DEPENDENTE D
WHERE E.Matricula = D.Matricula)  2 ;
Fevereiro 2001
Banco de Dados
16
Select - agrupando resultados
• A cláusula ‘Group By’ pode aplicar funções em grupos de linhas
com os mesmos valores de atributos.
» Liste o número de empregados por departamento.
SELECT
Depto, COUNT(*)
FROM
EMPREGADO
GROUP BY Depto ;
» Liste, por departamento, o total de funcionários e a média salarial.
SELECT
Depto, COUNT (*), AVG(Salario)
FROM
EMPREGADO
GROUP BY Depto ;
» Liste a quantidade de homens e mulheres da empresa.
SELECT
Sexo, COUNT(*)
FROM
EMPREGADO
GROUP BY Sexo ;
Fevereiro 2001
Banco de Dados
17
Select - selecionando resultados agrupados
• A cláusula ‘Having’ seleciona as linhas a partir de condições
aplicadas sobre os valores de funções.
» Liste os departamentos que possuem mais de 2 funcionários.
SELECT
Depto
FROM
EMPREGADO
GROUP BY Depto
HAVING COUNT (*) > 2 ;
» Liste para cada departamento que tenha mais de dois funcionários,
o total de funcionários e a média salarial.
SELECT
Depto, COUNT (*), AVG(Salario)
FROM
EMPREGADO
GROUP BY Depto
HAVING COUNT(*) > 2 ;
Fevereiro 2001
Banco de Dados
18
Select - having X where
• Where: seleciona linhas antes da agregação.
• Having: seleciona linhas depois da agregação.
» Liste, por departamento, o número de funcionários que ganham
mais de 300,00.
SELECT
FROM
WHERE
GROUP BY
Depto, COUNT (*)
EMPREGADO
Salario > 300
Depto ;
» Liste, para os departamentos que tenham média salarial > 300,00,
o total de funcionários e a média salarial.
SELECT
FROM
GROUP BY
HAVING
Fevereiro 2001
Depto, COUNT (*), AVG(Salario)
EMPREGADO
Depto
AVG(Salario ) > 300 ;
Banco de Dados
19
Select - valor nulo
• Indica um campo que não tenha valor naquele registro
• Não participa nas funções:
> média (2, 4, 0
) = 2
> média (2, 4, NULL ) = 3
(6/3)
(6/2)
» Liste o nome dos funcionários que não tem um supervisor.
SELECT Nome
FROM
EMPREGADO
WHERE Supervisor IS NULL ;
» Liste total de funcionários da empresa, e o total de funcionário que
possuam um supervisor.
SELECT COUNT (*)
AS ‘Total de empregados’,
COUNT (Supervisor)
AS ‘Total de empregados supervisionados’
FROM
EMPREGADO ;
Fevereiro 2001
Banco de Dados
20
Exercícios 2 - uso do comando Select
• Liste o nome dos departamentos que não tem gerente.
• Liste o nome e matrícula dos funcionários que não tem supervisor.
• Liste a soma dos salários, o maior salário, o menor salário e a média salarial
dos empregados do departamento 10.
• Liste o total de funcionários.
• Liste o total de funcionários do departamento 10.
• Liste o total de funcionários por número de departamento.
• Liste o número identificador dos departamentos que tenham dois ou mais
funcionários.
• Liste o total de projetos por localização.
• As localizações que possuem mais de um projeto.
• Liste o total de departamentos por localização.
• As localizações que possuem mais de um departamento.
Fevereiro 2001
Banco de Dados
21
Exercícios 2 - uso do comando Select (cont.)
• Liste o total de diferentes parentescos da tabela DEPENDENTES.
• Liste o total de dependentes por tipo de parentesco.
• Liste o total de dependentes por sexo.
• Liste o total de dependentes do funcionário de matrícula 1001.
• A matrícula dos funcionários que possuam mais de dois dependentes.
• Liste a matricula dos supervisores e o número de funcionários que
supervisionam.
• Liste a matricula dos supervisores que supervisionam mais de 2
funcionários.
• Liste a matrícula dos gerentes e o número de departamentos que gerenciam.
• Liste a matricula dos gerentes que gerenciam mais de um departamento.
Fevereiro 2001
Banco de Dados
22
Select - produto cartesiano
• No produto cartesiano, temos mais de uma tabela declarada na
cláusula ‘FROM’.
• Temos a combinação de cada linha de uma tabela com cada uma
das linhas da outra tabela.
» Junte as linhas da tabela EMPREGADO com as linhas da tabela
DEPARTAMENTO.
SELECT *
FROM EMPREGADO, DEPARTAMENTO ;
» Junte as linhas da tabela EMPREGADO com as linhas da tabela
DEPENDENTE.
SELECT *
FROM EMPREGADO, DEPENDENTE ;
Fevereiro 2001
Banco de Dados
23
Select - junção de tabelas
• A junção de tabelas é executada ao se declarar uma condição na
cláusula ‘WHERE’ em um produto cartesiano.
• Ela em geral indica o relacionamento entre as tabelas
»Liste o nome dos empregados e do departamento em que trabalham.
SELECT Nome, DeptoNome
FROM EMPREGADO, DEPARTAMENTO
WHERE Depto = DeptoNum ;
»Liste nome e endereço dos empregados do departamento ‘Compras’.
SELECT Nome, Endereco
FROM EMPREGADO, DEPARTAMENTO
WHERE Depto = DeptoNum AND DeptoNome = ‘Compras’ ;
»Liste o nome dos departamentos e de seus gerentes.
SELECT DeptoNome , Nome
FROM EMPREGADO, DEPARTAMENTO
WHERE Matricula = MatrGerente ;
Fevereiro 2001
Banco de Dados
24
Select - junção de tabelas: apelidos
• Podemos criar apelidos para as tabelas utilizadas
» Liste o nome dos empregados e de seus dependentes.
SELECT Nome, NomeDepend
FROM EMPREGADO E, DEPENDENTE D
WHERE E.Matricula = D.Matricula ;
»Liste o nome dos empregados e dos projetos em que trabalharam 20
horas.
SELECT
FROM
WHERE
AND
Nome, PNome
EMPREGADO E, TRABALHA-EM T, PROJETO P
E.Matricula = T.Matricula AND T.PCodigo = P.PCodigo
Horas = 20 ;
»Liste o nome de funcionários e seus supervisores.
SELECT E.Nome AS NomeEmp,
S.Nome AS NomeSupervisor
FROM
EMPREGADO E, EMPREGADO S
WHERE E.Supervisor = S.Matricula ;
Fevereiro 2001
Banco de Dados
25
Select - junção de tabelas: outer join
• A junção de tabelas mostra apenas as linhas que satisfazem a condição
especificada mas, ocasionalmente, podemos quer ver as linhas de uma
tabela que não satisfaçam a condição.
• O OUTER JOIN mostra, alem das linhas que satisfazem a condição, as
linhas de uma das tabelas que não satisfazem.
• A implementação, dependendo do fabricante, pode ser feita na cláusula
FROM ou na cláusula WHERE.
» LEFT JOIN: inclui as linhas da 1ª tabela que não satisfazem a condição.
FROM
TAB1 LEFT [OUTER] JOIN
WHERE Tab1.Coluna *= Tab2.Coluna
» RIGHT JOIN: inclui as linhas da 2ª tabela que não satisfazem a condição
FROM
TAB1 RIGHT [OUTER] JOIN
WHERE Tab1.Coluna =* Tab2.Coluna
Fevereiro 2001
Banco de Dados
26
Select - junção de tabelas: outer join
• Liste o nome de matricula de todos os empregados, e de seus
dependentes, se houver.
SELECT Nome, E.Matricula, D.Matricula , NomeDepend
FROM
EMPREGADO E LEFT JOIN DEPENDENTE D
ON
E.Matricula = D.Matricula ;
ou
SELECT Nome, E.Matricula, D.Matricula , NomeDepend
FROM
EMPREGADO E, DEPENDENTE D
WHERE E.Matricula *= D.Matricula ;
Fevereiro 2001
Banco de Dados
27
Exercícios 3 - uso do comando Select
• Liste o nome das mulheres que trabalham no departamento ’Vendas’.
• Liste o nome do departamento e o nome das mulheres que trabalham no
departamento 10, ordenado por departamento.
• Liste os nomes dos departamentos e de seus gerentes em ordem crescente.
• Liste o nome do gerente do departamento ‘Compras’.
• Liste o nome e matricula dos funcionários homônimos.
• Liste o nome e matricula dos empregados que tenham um dependente
com o mesmo nome que seu.
• Liste o nome, matricula e data de nascimento dos funcionários que
nasceram no mesmo dia.
• Liste o nome do departamento e o nome dos funcionários subordinados ao
gerente ‘Mario Souza’.
• Liste o nome dos funcionários que trabalharam mais de 20 horas no
projeto ‘Varejo’.
Fevereiro 2001
Banco de Dados
28
Exercícios 3 - uso do comando Select (cont.)
• Liste o nome, endereço e data de nascimento dos gerentes dos
departamentos localizados no ‘Meier’.
• Liste todos os dados de projetos do departamento ´Compras´.
• Liste o nome, matricula e departamento dos funcionários que trabalham
no projeto ‘FornecNorte’ ou no projeto ‘FornecSul’.
• Liste o nome, matricula e departamento dos funcionários que trabalham
no projeto ‘FornecNorte’ ou no projeto ‘FornecSul’, sem duplicação de
linhas.
• Liste a soma do salário, o maior salário, o menor salário e a média salarial
dos empregados do departamento ‘Compras’.
• Liste o total de funcionários por nome de departamento.
• Liste o total de funcionários do departamento ‘Compras’.
• Liste o nome dos departamentos com mais de um funcionário.
Fevereiro 2001
Banco de Dados
29
Exercícios 3 - uso do comando Select (cont.)
• Liste o nome e matricula dos funcionários com mais de dois dependentes.
• Liste o total de dependentes do funcionário ‘José Silva’.
• Liste o nome dos funcionários que trabalham em dois ou mais projetos.
• Liste o nome dos funcionários que gerenciam mais de um departamento.
• Liste a matricula e nome dos supervisores.
• Liste a matricula e o nome dos supervisores que supervisionam mais de
2 funcionários.
• Liste, para cada projeto, seu código, nome, número de funcionários que
nele trabalham e a média de horas.
Fevereiro 2001
Banco de Dados
30
Select - queries aninhadas
• Em queries aninhadas, temos a cláusula ‘WHERE’ usando outra
query.
• A query interna retorna como resposta uma tabela ou conjunto (set),
que pode ser vazio.
• Utiliza diferentes operadores.
• Operador [NOT] IN
» compara um valor ‘v’ com um conjunto de valores ‘V’ e retorna
‘true’ se ‘v’ é um elemento de ‘V’.
• Operador [NOT] EXISTS
»retorna ‘true’ quando o valor de uma query aninhada é vazio
Fevereiro 2001
Banco de Dados
31
Select - queries aninhadas (cont.)
• Operador v > ALL
» compara um valor ‘v’ com um conjunto de valores ‘V’ e retorna
‘true’ se ‘v’ for maior que TODOS os elementos de ‘V’.
• Operador v > ANY
» compara um valor ‘v’ com um conjunto de valores ‘V’ e retorna
‘true’ se ‘v’ for maior que ALGUM dos elementos de ‘V’.
OBS: pode-se usar os comparadores lógicos:
. >
. >=
. <
. <=
. =
. <> ou
!=
Fevereiro 2001
Banco de Dados
32
Select - queries aninhadas, operador IN
» Liste o nome e endereço dos empregados do departamento ‘Compras’.
SELECT Nome, Endereco
FROM EMPREGADO
WHERE Depto IN
( SELECT Deptonum
FROM
DEPARTAMENTO
WHERE DeptoNome = ‘Compras’ ) ;
» Liste o nome dos funcionários que não tem dependentes.
SELECT Nome
FROM EMPREGADO E
WHERE Matricula NOT IN
( SELECT Matricula
FROM DEPENDENTE D
WHERE E.Matricula = D.Matricula ) ;
Fevereiro 2001
Banco de Dados
33
Select - queries aninhadas, operador EXISTS
» Liste o nome e endereço dos empregados do departamento ‘Compras’.
SELECT Nome, Endereco
FROM EMPREGADO E
WHERE EXISTS
( SELECT
FROM
WHERE
AND
*
DEPARTAMENTO D
DeptoNome = ‘Compras’
E.Depto = D. Deptonum ) ;
» Liste o nome dos funcionários que não tem dependentes.
SELECT Nome
FROM EMPREGADO E
WHERE NOT EXISTS
( SELECT *
FROM DEPENDENTE D
WHERE E.Matricula = D.Matricula ) ;
Fevereiro 2001
Banco de Dados
34
Select - queries aninhadas, operador ALL
» Liste o nome dos empregados que ganham os maiores salários da
empresa.
SELECT Nome
FROM EMPREGADO
WHERE Salario >= ALL
( SELECT Salario
FROM
EMPREGADO ) ;
» Liste o nome dos empregados que tem salário maior que o salário de
todos os empregados do departamento de ‘Compras’.
SELECT Nome
FROM EMPREGADO
WHERE Salario > ALL
( SELECT
FROM
WHERE
AND
Fevereiro 2001
Salario
EMPREGADO, DEPARATAMENTO
Depto = Deptonum
Deptonome = ‘Compras’ ) ;
Banco de Dados
35
Select - queries aninhadas, operador ANY
» Liste o nome dos empregados que ganham mais que o menor salário da
empresa.
SELECT Nome
FROM EMPREGADO
WHERE Salario > ANY
( SELECT Salario
FROM EMPREGADO ) ;
» Liste o nome dos empregados que não ganham o maior salário da
empresa.
SELECT Nome
FROM EMPREGADO
WHERE Salario < ANY
( SELECT Salario
FROM EMPREGADO ) ;
Fevereiro 2001
Banco de Dados
36
Select - sets explícitos
• Podemos usar um conjunto explícito de valores para comparação na
cláusula ‘WHERE’.
» Liste o nome e a matricula dos funcionários que trabalham no projeto
1, 2 ou 3.
SELECT
FROM
WHERE
AND
Nome, Matricula
EMPREGADO E, TRABALHAEM T
E.MATRICULA = T.MATRICULA
PCodigo IN ( 1, 2, 3) ;
» Liste o nome dos projetos localizados no ‘Meier’ ou no ‘Centro’.
SELECT PNome
FROM PROJETO
WHERE Plocalizacao IN ( ‘Meier’, ‘Centro’) ;
Fevereiro 2001
Banco de Dados
37
Select - união de tabelas
• Podemos unir o resultado de várias consultas, desde que o tipo dos
resultados sejam os mesmos.
» Liste o nome e matricula dos funcionários que ganham mais de R$100,00
juntamente com os funcionários do departamento de Vendas.
( SELECT
FROM
WHERE
UNION
( SELECT
FROM
WHERE
Nome, Matricula
EMPREGADO
Salario > 1000 )
Nome, Matricula
EMPREGADO, DEPARTAMENTO
Depto = DeptoNum AND DeptoNome = ‘Vendas’ ) ;
» Liste a localização dos departamentos juntamente com a localização dos
projetos.
( SELECT
FROM
UNION
( SELECT
FROM
Fevereiro 2001
DISTINCT Localizacao
DEPARTAMENTO )
DISTINCT PLocalizacao
PROJETO ) ;
Banco de Dados
38
Exercícios 4 - uso do comando Select
• Liste o nome das mulheres que trabalham no departamento ’Compras’.
• Liste o nome do gerente do departamento ‘Compras’.
• Liste a soma do salário, o maior salário, o menor salário e a média salarial
dos empregados do departamento ‘Compras’.
• Liste o total de funcionários do departamento ‘Compras’.
• Liste o nome dos departamentos com mais de um funcionário.
• Liste a matricula e nome dos supervisores e o numero de funcionários que
supervisionam.
• Liste a matricula e o nome dos supervisores que supervisionam mais de 2
funcionários.
• Liste o nome do departamento e o nome dos funcionários subordinados ao
gerente ‘Mario Souza’.
• Liste o nome dos funcionários que gerenciam mais de um departamento.
• Liste o nome, endereço e data de nascimento dos gerentes dos
departamentos localizados no ‘Meier’.
Fevereiro 2001
Banco de Dados
39
Exercícios 4 - uso do comando Select (cont.)
• Liste o nome e matricula dos funcionários com mais de dois dependentes.
• Liste o total de dependentes do funcionário ‘Mario Souza’.
• Liste o nome dos funcionários que tem algum dependente.
• Liste o nome dos gerentes que tem algum dependente.
• Liste o nome e matricula dos empregados que tenham um dependente
com o mesmo nome que o seu.
• Liste o nome dos funcionários que trabalham em dois ou mais projetos.
• Liste o nome dos funcionários que trabalharam mais de 20 horas no
projeto ‘FornecNorte’.
• Liste o nome, matricula e departamento dos funcionários que trabalham
no projeto ‘FornecNorte’ ou no projeto ‘FornecSul’.
• Liste o nome, matricula e departamento dos funcionários que trabalham
no projeto ‘FornecNorte’ e no projeto ‘FornecSul’.
Fevereiro 2001
Banco de Dados
40
Comandos para manipulação de dados
• INSERT
-
incluindo registros
• DELETE
-
removendo registros
• UPDATE
-
alterando registros
Fevereiro 2001
Banco de Dados
41
Comando INSERT
• Os valores devem seguir a ordem dos campos na criação da tabela.
INSERT INTO nome-tab
VALUES
(valor {, valor … } )
• Ao especificar o nome dos campos, os que tem valor NULL ou default
não precisam constar na lista.
INSERT INTO nome-tab ( campo1, {, campo2 …} )
VALUES
(valor1 {, valor2 … } )
• Varias linhas podem ser inseridas utilizando uma query.
INSERT INTO nome-tab ( campo1, {, campo2 …} )
SELECT <lista-atributos>
FROM
nome-tab {, nome-tab … }
WHERE <condição>
Fevereiro 2001
Banco de Dados
42
Comandos DELETE e UPDATE
• O comando ‘delete’ remove linhas de uma tabela. A clausula ‘WHERE’ é
opcional e quando não usada, todas as linhas da tabela são excluídas .
DELETE FROM nome-tab
{ WHERE <condição> }
• O comando ‘update’ modifica valores de campos para uma ou mais linhas
da tabela.
UPDATE nome-tab
SET
campo = valor {, campo = valor … }
{ WHERE <condição> }
Fevereiro 2001
Banco de Dados
43
Comandos para manipulação de dados - exemplos
» Inclua o departamento de Marketing que tem o numero 30 e é
gerenciado pelo funcionário de matricula 1020.
INSERT INTO Departamento
VALUES
(30, ‘Marketing’, 1020)
» Inclua o departamento de Pessoal que tem o numero 30 e não
possui gerente.
INSERT INTO Departamento (DeptoNome, DeptoNum)
VALUES
(‘Pessoal’, 30)
» Exclua o funcionário de matricula 1001.
DELETE FROM Empregado
WHERE
Matricula = 1001
» De 10% de aumento salarial aos funcionários do depto 10.
UPDATE Empregado
SET
Salario = 1.1 * Salario
WHERE Depto = 10
Fevereiro 2001
Banco de Dados
44
Exercícios - comandos para manipulação de dados
• Inclua as 20 horas de trabalho do funcionário de matricula 3001 no
projeto P21.
• Inclua o funcionário de matricula 3001 como trabalhador do projeto P20,
sem nenhuma hora trabalhada.
• Exclua os funcionários do departamento 30.
• Exclua os funcionários do departamento ‘Compras’.
• Exclua todos os dependentes.
• Altere o endereço do funcionário ‘Marcelo Sousa’ para ‘Rua da Matriz,
25’.
• Torne o funcionário de matricula 1002 o novo gerente do departamento de
‘Compras’ .
• Atualiza o número de horas trabalhas pelo empregado de matrícula 3001
no projeto 'P20' para 8 horas .
Fevereiro 2001
Banco de Dados
45
Comandos de definição de dados
• CREATE
> schema
> domain
> table
> index
> view
> assertion
Fevereiro 2001
• DROP
> schema
> domain
> table
> view
> index
> assertion
Banco de Dados
• ALTER
> schema
> table
> view
> index
46
Create schema
• O esquema de um banco de dados relacional agrupa um conjunto
de tabelas e outras construções que pertencem a uma mesma
aplicação.
• Possui o nome do esquema, o identificador de autorização que
define o dono do esquema, e opcionalmente a descrição de cada
elemento do esquema (tabelas, views, domínios, ...). Os elementos do
esquema podem ser definidos posteriormente.
Ex:
CREATE SCHEMA Empresa AUTHORIZATION Grupo;
Fevereiro 2001
Banco de Dados
47
Create domain
• O domínio define um tipo de dado.
• Pode ser usado por vários campos de tabela de um esquema.
• Facilita a mudança de um tipo de dado usado em vários atributos.
CREATE DOMAIN nome-dominio tipo-dado
{ DEFAULT valor }
{ CONSTRAINT nome-rotina }
{ CHECK ( condição ) } ;
Ex: CREATE DOMAIN Nome char (40);
CREATE DOMAIN CorValida char (6) DEFAULT ’???’
CONSTRAINT CoresValidas
CHECK ( value in (‘azul’, ‘amarelo’, ‘verde’, ‘???’) ) ;
CREATE TABLE T ( ...,
cor
);
CorValida ,
Fevereiro 2001
Banco de Dados
48
Create table
CREATE TABLE nome-tab
( nome-coluna tipo-coluna {NOT NULL} {DEFAULT valor},
{nome-coluna tipo-coluna {NOT NULL} {DEFAULT valor}, ... }
{tc} PRIMARY KEY (nome-coluna {, nome-coluna ...} ),
{tc} UNIQUE
(nome-coluna {, nome-coluna ...} ),
{tc} FOREIGN KEY (nome-coluna {, nome-coluna ...} )
REFERENCES nome-tab(nome-coluna {, nome-coluna ...} )
ON UPDATE CASCATE
ON DELETE SET [ NULL / DEFAULT / CASCATE ]
{tc}
)
tc = tab constraint: CONSTRAINT nome-rotina
Fevereiro 2001
Banco de Dados
49
Create table - exemplo 1
Exemplo 1:
CREATE TABLE
( DeptoNum
DeptoNome
MatricGerente
Departamento
INT
NOT NULL,
VARCHAR(15) NOT NULL,
CHAR(9),
CONSTRAINT DeptoPK
PRIMARY KEY (DeptoNum),
CONSTRAINT DeptoUN
UNIQUE
(DeptoNome),
CONSTRAINT DeptoFK
FOREIGN KEY (MatricGerente)
REFERENCES Empregado(Matricula)
ON UPDATE CASCATE
ON DELETE
SET NULL ) ;
Fevereiro 2001
Banco de Dados
50
Create table - exemplo 2
Exemplo 2:
CREATE TABLE TrabalhaEm
( Matricula
CHAR(9)
PCodigo
INT
Horas
DECIMAL (3,1),
NOT NULL,
NOT NULL,
PRIMARY KEY
(Matricula, PCodigo),
FOREIGN KEY
(Matricula)
REFERENCES Empregado(Matricula)
ON UPDATE CASCATE
ON DELETE
CASCATE,
FOREIGN KEY
(PCodigo)
REFERENCES Projeto(PCodigo)
ON UPDATE CASCATE
ON DELETE
CASCATE ) ;
Fevereiro 2001
Banco de Dados
51
Create index
• Um índice torna mais eficiente o acesso às linhas de uma tabela.
• A ordenação dos valores do índice é ascendente por default.
• Os campos que são chave (não possuem repetição de valores nas
linhas da tabela) são especificados como ‘UNIQUE’.
CREATE {UNIQUE} INDEX nome-índice
ON nome-tabela ( nome-coluna { ASC / DESC }
{ , nome-coluna { ASC / DESC } … }
)
Ex:
CREATE UNIQUE INDEX Nome-Depto-Index
ON Departamento ( DeptoNome )
Fevereiro 2001
Banco de Dados
52
Create view
• Uma view é uma tabela derivada de outras tabelas. Estas outras
tabelas podem ser tabelas base ou outras views pré-definidas.
• É considerada uma tabela virtual.
• Em sua criação é dado um nome para a tabela virtual, uma lista de
atributos (opcional) e uma query que determina o conteúdo da view.
CREATE VIEW nome-view { nome-coluna {, nome-coluna …} }
AS <comando select>
Ex:
CREATE VIEW Info-Depto (Nome-Depto, Num-Func, Total-Sal)
AS SELECT DeptoNome, COUNT(*), SUM(Salario)
FROM Departamento, Empregado
WHERE DeptoNum = Depto
GROUP BY DeptoNome
Fevereiro 2001
Banco de Dados
53
Exercícios - comandos de definição de dados
• Crie um domínio para o campo ‘Sexo’ da tabela ‘Empregado’, com
formato char(1), valores ‘M’ e ‘F’, e valor default ‘M’.
• Crie a tabela Empregado com todos os valores NOT NULL, formato INT
para os campos Matricula, Depto e Supervisor, formato DATE para
DataNasc, formato DECIMAL(10,2) para Salario, e formato VARCHAR
para os demais campos com o tamanho apropriado. Declarar Matricula
como chave primária, e Depto e Supervisor como chaves estrangeiras.
•Crie a tabela TrabalhaEm com todos os campos no formato INT, campos
Matricula e PCodigo como NOT NULL. Declarar a chave primária com os
campos Matricula e PCodigo, que são também chave estrangeira.
•Crie um índice com o campo Nome da tabela Empregado.
Fevereiro 2001
Banco de Dados
54
Drop Schema
DROP SCHEMA nome-esquema [ CASCATE / RESTRICT ]
• A opção ‘cascate’ remove também todos os elementos (tabelas,
domínios, ...) do esquema.
• A opção ‘restrict’ só permite a remoção do esquema se ele não
contiver nenhum elemento.
Ex :
DROP SCHEMA Empresa CASCATE ;
Fevereiro 2001
Banco de Dados
55
Drop Table
DROP TABLE nome-tab [ CASCATE / RESTRICT ] ;
• Na opção ‘cascate’, todas as views e constraints que referenciam a
tabela são também removidas..
• Na opção ‘restrict’, a tabela é removida apenas quando não é
referenciada em alguma constraint (FK) ou view.
Ex:
DROP TABLE nome-tab CASCATE ;
Fevereiro 2001
Banco de Dados
56
Drop View
-
Drop Index
DROP VIEW nome-view ;
Ex:
DROP VIEW Info-Depto ;
DROP INDEX nome-index ;
Ex:
DROP INDEX Nome-Depto-Index ;
Fevereiro 2001
Banco de Dados
57
Alter table
• Para incluir um novo campo:
ALTER TABLE nome-tab ADD nome-coluna tipo-coluna
{NOT NULL} {DEFAULT valor} ;
- not null: um valor deve ser atribuído ao novo campo das linhas que já
existem na tabela, pela clausula default ou pelo comando update.
- default: quando não especificada, o novo campo terá o valor NULL.
• Para remover um campo:
ALTER TABLE nome-tab DROP nome-coluna [ CASCATE /
RESTRICT ] ;
- cascate: todas as constraints e views que referenciam o campo serão
também removidas.
- restrict: o comando e’ executado quando nenhuma view ou constraint se
referencia ao campo.
Fevereiro 2001
Banco de Dados
58
Alter table
•Para alterar a clausula default de um campo:
ALTER TABLE nome-tab ALTER nome-coluna
[ DROP DEFAULT /
SET DEFAULT valor ] ;
• Para remover ou incluir uma constraint:
ALTER TABLE nome-tab DROP CONSTRAINT nome-rotina;
ALTER TABLE nome-tab ADD CONSTRAINT nome-rotina;
Fevereiro 2001
Banco de Dados
59
Alter table - exemplos
Exemplos:
ALTER TABLE Empregado ADD DataNasc DATE ;
ALTER TABLE Empregado DROP Endereco CASCATE ;
ALTER TABLE Empregado ALTER Sexo DROP DEFAULT ;
ALTER TABLE Empregado ALTER Sexo SET DEFAULT ‘F’ ;
ALTER TABLE Departamento DROP CONSTRAINT DeptoPK ;
ALTER TABLE Departamento ADD CONSTRAINT DeptoXVE ;
Fevereiro 2001
Banco de Dados
60
Update View
UPDATE nome-view
SET
coluna = {expressão}
WHERE condição
Ex:
UPDATE
InfoDepto
SET
NomeDepto = ‘Compras’
WHERE NomeDepto = ‘Compra’
Fevereiro 2001
Banco de Dados
61
Bibliografia
• Introdução aos Sistemas de Banco de Dados
J. C. Date.
• The practical SQL HandBook
Judith S. Bownman, sandra L. Emerson, Marcy Darnovsky
Fevereiro 2001
Banco de Dados
62