SQL

PostgreSQL
Disciplina: Banco de Dados
Professora: Dayse de Almeida

Universidade Federal de Catalão

 2

Composição da SQL
• Linguagem de Definição de Dados (DDL):
• Comandos para criar, modificar e remover tabelas
• Além de criar e remover índices e visões

• Linguagem de Manipulação de Dados (DML):

• Comandos para inserir, consultar, atualizar e remover tuplas
SQL
I – Criação do Esquema
Disciplina: Banco de Dados
Professora: Dayse de Almeida
 4

Ferramentas Utilizadas
• PostgreSQL:
• [Link]

• PgAdmin4
 5

Ferramentas Utilizadas
• PostgreSQL:
• [Link]
• [Link]
• Escolher SO

• PgAdmin4
 6

Adicionar conexão com o servidor

 7

Adicionar conexão com o servidor

 8

Adicionar conexão com o servidor

 9

Create database
 10

Create database
 11

Create database
 12

Drop database
• drop database Empresa
 13

Create database
• Criar um banco de dados para outro usuário:
create database teste1 with owner=daysesa

• Excluir:
drop database teste1

• Obs.: login roles

 14

Create table
create table primeira_tabela(
primeiro_campo text,
segundo_campo integer)

• Drop table:
drop table primeira_tabela
 15

Create table
• Valor default para campos:
• Ao definir um valor default para um campo, ao ser cadastrado o
registro e este campo não for informado, o valor default é assumido

create table produtos (

produto_no integer,
descricao text,
preco numeric default 9.99
)

• insert into produtos(produto_no, descricao, preco) values (45, 'qquer', 32)

• insert into produtos (produto_no, descricao) values (45, 'qquer')

 16

Create table
• Ckeck:
• Ao criar uma tabela podemos prever que o banco exija que o valor de
um campo satisfaça uma expressão

create table produtos2 (

produto_no integer,
descricao text,
preco numeric check (preco > 0)
)

• insert into produtos2 (produto_no, descricao, preco) values (45, 'qquer', 0)

• ERRO: novo registro da relação "produtos2" viola restrição de verificação
"produtos2_preco_check"
 17

Create table
• Dar nome à restrição check:
• Isso ajuda a tornar mais amigável as mensagens de erro

CREATE TABLE produtos3 (

produto_no integer,
descricao text,
preco numeric CONSTRAINT preco_positivo CHECK (preco > 0)
)

• insert into produtos3 (produto_no, descricao, preco) values (45, 'qquer', 0)

• ERRO: novo registro da relação "produtos3" viola restrição de verificação
"preco_positivo“
 18

Create table
• Dar nome à restrição check:
• Isso ajuda a tornar mais amigável as mensagens de erro

CREATE TABLE produtos4 (

produto_no integer,
descricao text,
desconto numeric CHECK (desconto > 0 AND desconto < 0.10),
preco numeric CONSTRAINT preco_positivo CHECK (preco > 0),
CHECK (preco > desconto)
)

• insert into produtos4(produto_no, descricao, desconto, preco) values (45,

'qquer', 15, 100)

• insert into produtos4(produto_no, descricao, desconto, preco) values (45,

'qquer', 0.5, 0.4)
 19

Create table
• Restrição NOT NULL:
• Obriga o preenchimento de um campo
• Obs.: até um espaço em branco atende a esta restrição

CREATE TABLE produtos5 (

cod_prod integer NOT NULL CHECK (cod_prod > 0),
nome text NOT NULL,
preco numeric
)

• insert into produtos5 (cod_prod, nome, preco) values (-1, 'produtoX', 32)
• ERRO: novo registro da relação "produtos5" viola restrição de verificação
"produtos5_cod_prod_check“

• insert into produtos5 (nome, preco) values ('produtoX', 32)

• ERRO: valor nulo na coluna "cod_prod" viola a restrição não-nula
 20

Create table
• Restrição Unique:
• Valores exclusivos para cada campo em todos os registros;
• Obs.: nulos não são checados. UNIQUE não aceita valores repetidos, mas aceita vários nulos
(já que estes não são checados).

CREATE TABLE produtos6 (

cod_prod integer UNIQUE,
nome text,
preco numeric
)

CREATE TABLE produtos7(

cod_prod integer,
nome text,
preco numeric,
UNIQUE (cod_prod)
)

• insert into produtos7(cod_prod, nome, preco)values (45, 'produtoX', 34)

• insert into produtos7(cod_prod, nome, preco)values (45, 'produtoY', 23)

 21

Create table
• Restrição Unique:

CREATE TABLE exemplo (

a integer,
b integer,
c integer,
UNIQUE (a, c)
)

CREATE TABLE produtos8(

cod_prod integer CONSTRAINT unq_cod_prod UNIQUE,
nome text,
preco numeric
)
 22

Exercício
• Criar a tabela produtos9 com os seguintes atributos:
cod_prod, nome e preco. O atributo cod_prod deve ser
inteiro, único, não nulo e positivo. O atributo nome é do
tipo texto e o atributo preco é do tipo numérico.
 23

Exercício – Reposta
CREATE TABLE produtos9 (
cod_prod integer UNIQUE NOT NULL CHECK(cod_prod > 0),
nome text,
preco numeric
)
 24

Create table
• Chaves Primárias:
• A chave primária de uma tabela é formada internamente pela
combinação das restrições UNIQUE e NOT NULL

• Uma tabela pode ter no máximo uma chave primária

• A teoria de bancos de dados relacional dita que toda tabela deve

ter uma chave primária

• O PostgreSQL não obriga que uma tabela tenha chave primária,

mas é recomendável seguir, a não ser que esteja criando uma
tabela para importar dados de outra que contenha registros
duplicados para tratamento futuro, por exemplo
 25

Create table
• Chaves Primárias (Primary Key):

CREATE TABLE produtos10 (

cod_prod integer UNIQUE NOT NULL,
nome text,
preco numeric
)

CREATE TABLE produtos11 (

cod_prod integer PRIMARY KEY,
nome text,
preco numeric
)

• Se mais que um atributo forma a chave primária:

CREATE TABLE exemplo (

a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
)
 26

Create table
• Chave Estrangeira (Foreign Key):
• Criadas com o objetivo de relacionar duas tabelas, mantendo a
integridade referencial entre ambas

• Especifica que o valor da coluna (ou grupo de colunas) deve

corresponder a algum valor existente em um registro da outra
tabela

• Na tabela estrangeira deve existir somente registros que tenham

um registro relacionado na tabela principal

• Deve-se garantir que não se remova um registro na tabela principal

que tenha registros relacionados na estrangeira
 27

Create table
• Chave Estrangeira (Foreign Key):

• Tabela primária:
CREATE TABLE produtos11 (
cod_prod integer PRIMARY KEY,
nome text,
preco numeric
)

CREATE TABLE pedidos (

cod_pedido integer PRIMARY KEY,
cod_prod integer,
quantidade integer,
CONSTRAINT pedidos_fk FOREIGN KEY (cod_prod) REFERENCES
produtos11 (cod_prod)
)

*
 28

Create table
• Chave Estrangeira (Foreign Key):

CREATE TABLE t0 (
a integer,
b integer,
c integer,
PRIMARY KEY(a, b)
)

CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
d integer,
FOREIGN KEY (c, d) REFERENCES t0 (a, b)
)
 29

Create table
• Simulando Enum:

CREATE TABLE pessoa(

codigo integer PRIMARY KEY,
cor_favorita varchar(255) NOT NULL,
check (cor_favorita IN ('vermelha', 'verde', 'azul'))
)

• INSERT INTO pessoa (codigo, cor_favorita) values (1, 'vermelha')

• INSERT INTO pessoa (codigo, cor_favorita) values (2, 'amarela')

 30

Create table
• Herança:
Pode-se criar uma tabela que herda todos os campos de outra tabela
existente.

CREATE TABLE cidades (

nome text,
populacao float,
altitude integer
)

CREATE TABLE capitais (

estado char(2)
) INHERITS (cidades)

• Assim, capitais passa a ter também todos os campos da tabela

cidades
 31

Exercício
• Criar relações com atributos, chaves primárias e chaves
estrangeiras para:
 32

Exercício - Create schema

• CREATE SCHEMA Esquema2

• CREATE TABLE [Link](A1 int, A2 int)

 33

Create schema
 34

Tipos numéricos
Nome Tamanho Descrição Variação

smallint 2 bytes inteiro pequeno -32768 a +32767

integer 4 bytes escolha usual para inteiro -2147483648 a +2147483647

bigint 8 bytes inteiro grande -9223372036854775808 a
+9223372036854775807
decimal variável precisão especificada pelo sem limite
usuário, exata
numeric variável precisão especificada pelo sem limite
usuário, exata
real 4 bytes precisão variável, inexata precisão de 6 dígitos decimais
double 8 bytes precisão variável, inexata precisão de 15 dígitos
precision decimais
serial 4 bytes inteiro auto incrementado 1 a 2147483647
bigserial 8 bytes inteiro grande auto 1 a 9223372036854775807
incrementado
 35

Tipos para cadeias de caracteres

Nome Descrição
character varying(n), varchar(n) comprimento variável com limite
character(n), char(n) comprimento fixo, completado com brancos
text comprimento variável não limitado
 36

Tipos para data e hora

Nome Tamanho Descrição Menor Maior Resolução
valor valor
timestamp [ (p) ] [ 8 bytes data e hora 4713 AC 5874897 1 microssegundo
without time zone ] DC / 14 dígitos
timestamp [ (p) ] 8 bytes data e hora, 4713 AC 5874897 1 microssegundo
with time zone com zona DC / 14 dígitos
horária
interval [ (p) ] 12 bytes intervalo de -178000000 17800000 1 microssegundo
tempo anos 0 anos / 14 dígitos
date 4 bytes data 4713 AC 32767 DC 1 dia
time[ (p) ] [ without 8 bytes hora do dia 00:00:00.00 23:59:59.9 1 microssegundo
time •zone ] time, timestamp, e interval aceitam um valor opcional de9precisão p, que
Os tipos / 14 dígitos
especifica
o número de dígitos fracionários mantidos no campo de segundos. Por padrão não existe
time[ (p) ] with
limite time para
explícito 12 abytes hora
precisão. O do dia,
intervalo 00:00:00.00
permitido para p é de23:59:59.9
0 a 6 para os 1 microssegundo
zone tipos timestamp e interval. com zona +12 9+12 / 14 dígitos
horária
 37

Exercício – Resposta
CREATE TABLE EMPREGADO(
PNOME varchar(255) NOT NULL,
MINICIAL char(1),
UNOME varchar(255) NOT NULL,
SSN integer PRIMARY KEY,
DATANASC date,
ENDERECO varchar(255),
SEXO char(1),
CHECK (SEXO IN ('F', 'M')),
SALARIO numeric NOT NULL CHECK (SALARIO > 0),
SUPERSSN integer,
CONSTRAINT supervisor FOREIGN KEY (SUPERSSN) REFERENCES
EMPREGADO (SSN),
DNO integer CHECK (DNO > 0),
FOREIGN KEY (DNO) REFERENCES DEPARTAMENTO (DNUMERO)
)

• ERRO: relação "departamento" não existe

 38

Exercício – Resposta
CREATE TABLE EMPREGADO(
PNOME varchar(255) NOT NULL,
MINICIAL char(1),
UNOME varchar(255) NOT NULL,
SSN integer PRIMARY KEY,
DATANASC date,
ENDERECO varchar(255),
SEXO char(1),
CHECK (SEXO IN ('F', 'M')),
SALARIO numeric NOT NULL CHECK (SALARIO > 0),
SUPERSSN integer,
CONSTRAINT supervisor FOREIGN KEY (SUPERSSN)
REFERENCES EMPREGADO (SSN),
DNO integer CHECK (DNO > 0)
)
 39

Exercício – Resposta
CREATE TABLE DEPARTAMENTO(
DNOME varchar(255) NOT NULL,
DNUMERO integer PRIMARY KEY CHECK
(DNUMERO > 0),
GERSSN integer,
CONSTRAINT gerente FOREIGN KEY (GERSSN)
REFERENCES EMPREGADO (SSN),
GERDATAINICIO date
)
 40

Exercício – Resposta
• ALTER TABLE:

ALTER TABLE EMPREGADO

ADD CONSTRAINT numerodepto FOREIGN KEY
(DNO) REFERENCES DEPARTAMENTO (DNUMERO)
 41

ALTER TABLE
• Adicionar atributo:
ALTER TABLE EMPREGADO
ADD COLUMN atributo tipo_dado

• Remover atributo:
ALTER TABLE EMPREGADO
DROP COLUMN [ IF EXISTS ] atributo [ RESTRICT | CASCADE ]

• Alterar o nome do atributo:

ALTER TABLE EMPREGADO
RENAME COLUMN atributo TO novoNomeAtributo

• Alterar o nome da relação:

ALTER TABLE EMPREGADO
RENAME TO novoNomeRelacao

• Alterar o tipo do atributo:

ALTER TABLE EMPREGADO
ALTER COLUMN atributo SET DATA TYPE varchar(10)
 42

ALTER TABLE
• Remover chave primária:
ALTER TABLE Dependente
DROP CONSTRAINT Dependente_pkey

• Adicionar chave primária:

ALTER TABLE Dependente
ADD PRIMARY KEY (ESSN, NOME_DEPENDENTE)
 43

Exercício – Resposta
CREATE TABLE DEPTO_LOCALIZACOES(
DNUMERO integer,
DLOCALIZACAO varchar(255),
PRIMARY KEY(DNUMERO, DLOCALIZACAO),
CONSTRAINT numerodepartamento FOREIGN
KEY (DNUMERO) REFERENCES DEPARTAMENTO
(DNUMERO)
)
 44

Exercício – Resposta
CREATE TABLE PROJETO(
PJNOME varchar(255),
PNUMERO integer PRIMARY KEY,
PLOCALIZACAO varchar(255),
DNUM integer,
CONSTRAINT numerodepartamento FOREIGN KEY (DNUM)
REFERENCES DEPARTAMENTO (DNUMERO)
)
 45

Exercício – Resposta
CREATE TABLE TRABALHA_EM(
ESSN integer,
PNO integer,
PRIMARY KEY (ESSN, PNO),
HORAS real,
CONSTRAINT numeroprojeto FOREIGN KEY (PNO)
REFERENCES PROJETO (PNUMERO),
CONSTRAINT empregado FOREIGN KEY (ESSN) REFERENCES
EMPREGADO(SSN)
)
 46

Exercício – Resposta
CREATE TABLE DEPENDENTE(
ESSN integer,
NOME_DEPENDENTE varchar(255),
SEXO char(1) CHECK (SEXO IN ('F', 'M')),
DATANASC date,
PARENTESCO varchar(255),
PRIMARY KEY (ESSN, NOME_DEPENDENTE),
CONSTRAINT SSNempregado FOREIGN KEY (ESSN)
REFERENCES EMPREGADO (SSN)
)
SQL
II – Inserção e Atualização
Disciplina: Banco de Dados
Professora: Dayse de Almeida
 48

DML
• INSERT INTO ...
• insere dados em uma tabela.

• UPDATE ... SET ... WHERE ...

• altera dados específicos de uma tabela.
 49

Insert
INSERT INTO nome_tabela
VALUES (V1, V2, Vn)
• Ordem dos atributos deve ser mantida

INSERT INTO nome_tabela (A1, A2, An)

VALUES (V1, V2, Vn)
• Ordem dos atributos não precisa ser mantida
 50

Update
UPDATE nome_tabela
SET coluna = <valor>
WHERE predicado

• Cláusula WHERE
• É opcional
 51

Insert
INSERT INTO EMPREGADO (PNOME, MINICIAL,
UNOME, SSN, DATANASC, ENDERECO, SEXO,
SALARIO, SUPERSSN, DNO) values ('John', 'B', 'Smith',
123456789, '09/01/1965', '731 Fondren, Houston, Tx', 'M',
30000, null, null)

INSERT INTO EMPREGADO (PNOME, MINICIAL,

UNOME, SSN, DATANASC, ENDERECO, SEXO,
SALARIO, SUPERSSN, DNO) values (‘Franklin', ‘T',
‘Wong', 333445555, ‘08/12/1955', ‘638 Voss, Houston, Tx',
'M', 40000, null, null)
 52

Update
UPDATE EMPREGADO
SET SUPERSSN= 333445555
WHERE SSN = 123456789
 53

Exercício
• Inserir as seguintes tuplas:
 54

Exercício
• Inserir/modificar as seguintes tuplas:

• (a) Inserir < 'Robert', 'F', 'Scott', '943775543', '21/06/1942', '2365 Newcastle
Rd, Bellaire, TX', M, 58000, '888665555', 1 > em EMPREGADO.

• (b) Inserir < 'ProductA', 4, 'Bellaire', 2 > em PROJETO.

• (c) Inserir < 'Production', 4, '943775543', ’01/10/1988' > em DEPARTAMENTO.

• (d) Inserir < '677678989', null, '40.0' > em TRABALHA_EM.

• (e) Inserir < '453453453', 'John', M, '12/12/1960', ‘Cônjuge' > em

DEPENDENTE.

• (j) Modificar o valor do atributo SUPERSSN da tupla de EMPREGADO com

SSN='999887777' para '943775543'.

• (k) Modificar o valor do atributo HORAS da tupla de TRABALHA EM com

ESSN='999887777' e PNO= 10 para '5.0'.
SQL
III – Consulta
Disciplina: Banco de Dados
Professora: Dayse de Almeida
 56

Select
• SELECT ... FROM ... WHERE ...
• Lista atributos de uma ou mais tabelas de acordo com alguma
condição.
 57

Select
SELECT <lista de atributos e funções>
FROM <lista de tabelas>
[ WHERE predicado ]
[ GROUP BY <atributos de agrupamento> ]
[ HAVING <condição para agrupamento> ]
[ ORDER BY <lista de atributos> ]

• Cláusula SELECT
• Lista os atributos e/ou as funções a serem exibidos no resultado da consulta
• Cláusula FROM
• Especifica as relações a serem examinadas na avaliação da consulta
• Cláusula WHERE
• Especifica as condições para a seleção das tuplas no resultado da consulta
• As condições devem ser definidas sobre os atributos das relações que aparecem na
cláusula FROM
• Pode ser omitida
 58

Select
SELECT datanasc, endereco
FROM EMPREGADO
WHERE Pnome=‘Alicia’ AND Unome=‘Zelaya’

SELECT *
FROM EMPREGADO
 59

Select
• Ordem de apresentação dos atributos:
• SELECT
• Duas ou mais tuplas podem possuir valores idênticos de atributos
• Eliminação de tuplas duplicadas
• SELECT DISTINCT

• Cláusula ORDER BY
• Ordem de apresentação dos dados
• Ordem ascendente (ASC) ou descendente (DESC).
 60

Select
• Operadores:
• Conjunção de condições: AND
• Disjunção de condições: OR
• Negação de condições: NOT
• =, <>, >, <, >=, <=
• Entre dois valores: BETWEEN... AND
• Compara cadeias de caracteres: LIKE ou NOT LIKE
• % (porcentagem): substitui qualquer string
• _ (underscore): substitui qualquer caractere
• WHERE Pnome LIKE ‘Jo%’
• qualquer string que se inicie com ‘Jo’
• WHERE Pnome LIKE ‘Jo_’
• qualquer string de 3 caracteres que se inicie com ‘Jo’
 61

Select
SELECT Pnome, SSN
FROM EMPREGADO
WHERE Pnome LIKE ‘J%’

SELECT Pnome, SSN

FROM EMPREGADO
WHERE Pnome LIKE ‘Jo__’
 62

Select
SELECT Pnome, SSN
FROM EMPREGADO
WHERE Pnome LIKE ‘J%’ AND
Sexo = ‘F’ AND
Dno = 5
 63

Operações sobre conjuntos

• Operações sobre conjuntos:
• União: UNION
• Intersecção: INTERSECT
• Diferença: EXCEPT
 64

Union
• Exemplo:
• Liste os nomes dos projetos dos departamentos 4 e 5.
SELECT PJnome
FROM PROJETO
WHERE Dnum = 4
UNION
SELECT PJnome
FROM PROJETO
WHERE Dnum = 5
 65

Intersect
• INSERT INTO DEPENDENTE values (333445555, ‘Alicia’, ‘F’,
‘2013-05-27’, ‘Neta’)

• Exemplo:
• Liste os nomes dos dependentes que tem nome igual a de algum
empregado.
SELECT Nome_dependente
FROM DEPENDENTE
INTERSECT
SELECT Pnome
FROM EMPREGADO
 66

Except
• Exemplo:
• Liste os nomes dos empregados que não têm dependentes.
SELECT Pnome
FROM EMPREGADO
EXCEPT
SELECT Pnome
FROM EMPREGADO, DEPENDENTE
WHERE [Link] = [Link]
 67

Exercício
• Liste os nomes dos empregados que não têm filhos (filho e/ou filha).
 68

Exercício - Resposta
• Liste os nomes dos empregados que não têm filhos (filho e/ou filha).

SELECT Pnome
FROM EMPREGADO
EXCEPT
SELECT Pnome
FROM EMPREGADO, DEPENDENTE
WHERE [Link] = [Link] AND
[Link] LIKE 'Filh_'
 69

Operações sobre conjuntos

• UNION (R U S)
• Une todas as linhas selecionadas por duas consultas, eliminando
as linhas duplicadas;
• Gera uma relação que contém todas as tuplas pertencentes a R, a
S, ou a ambas R e S.
• UNION ALL
• Une todas as linhas selecionadas por duas consultas, inclusive as
linhas duplicadas.
• INTERSECT (R ∩ S)
• Gera uma relação que contém todas as tuplas pertencentes tanto a
R quanto a S.
• EXCEPT (R − S)
• Gera uma relação que contém todas as tuplas pertencentes
a R que não pertencem a S.
 70

Subconsultas aninhadas
• Subconsulta
• Expressão SELECT ... FROM ... WHERE ... aninhada dentro de outra
consulta.
• Aplicações mais comuns:
• Testes para membros de conjuntos;
• Comparações de conjuntos;
• Cardinalidade de conjuntos.
• IN
• Testa se um atributo ou uma lista de atributos é membro do conjunto.
• NOT IN
• Verifica a ausência de um membro em um conjunto.
• Conjunto:
• Coleção de valores produzidos por uma cláusula SELECT ... FROM ...
WHERE ...
 71

Subconsultas aninhadas
• Exemplo:
• Liste os nomes dos dependentes que tem nome igual a de algum
empregado.

SELECT Nome_dependente
FROM DEPENDENTE
WHERE Nome_dependente IN
(SELECT Pnome FROM EMPREGADO)
 72

Comparação de conjuntos
• SOME
• ... WHERE salario > SOME (lista)
• A condição é verdadeira quando salario for maior que algum dos
resultados presentes na lista (resultado de uma consulta).
 73

Comparação de conjuntos
• Exemplo:
• Liste os nomes do empregados que têm salário superior a algum
empregado do departamento 4.

SELECT Pnome
FROM EMPREGADO
WHERE salario > SOME
(SELECT salario
FROM EMPREGADO
WHERE Dno = 4)
 74

Comparação de conjuntos
• ALL
• ... WHERE salario > ALL (lista)
• A condição é verdadeira quando salario for maior que todos os
resultados presentes na lista (resultado de uma consulta).
 75

Comparação de conjuntos
• Exemplo:
• Liste os nomes do empregados que têm salário superior ao salário
de todos os empregados do departamento 4.

SELECT Pnome
FROM EMPREGADO
WHERE salario > ALL
(SELECT salario
FROM EMPREGADO
WHERE Dno = 4)
 76

Cardinalidade de conjuntos
• EXISTS
• ... WHERE EXISTS (lista)
• A condição é verdadeira quando a lista (resultado de uma consulta) não
for vazia.
• NOT EXISTS
• ... WHERE NOT EXISTS (lista)
• A condição é verdadeira quando a lista for vazia.
 77

Cardinalidade de conjuntos
• Exemplo:
• Liste os nomes dos dependentes que tem nome igual a de algum
empregado.

SELECT Nome_dependente
FROM DEPENDENTE
WHERE EXISTS
(SELECT Pnome
FROM EMPREGADO
WHERE DEPENDENTE.Nome_dependente =
[Link])
 78

Junção
• Ideia:
• Concatenar tuplas relacionadas de duas relações em tuplas
únicas.
• Passos:
• Formar um produto cartesiano das relações;
• Fazer uma seleção forçando igualdade sobre os atributos que
aparecem nas relações.
 79

Junção
• Usar SELECT e WHERE
• Atributos com mesmo nome são especificados usando nomes de
tabelas e atributos (nome_tabela.nome_atributo).

• Cláusula FROM
• Possui mais do que uma tabela

• Cláusula WHERE
• Inclui as condições de junção
 80

Junção
1. Nome do empregado juntamente com nome do seu
departamento:

SELECT Pnome, Dnome

FROM EMPREGADO, DEPARTAMENTO
WHERE [Link] = [Link]

2. Nome do empregado, nome do seu departamento e a

localização do departamento:

SELECT Pnome, Dnome, Dlocalizacao

FROM EMPREGADO, DEPARTAMENTO,
DEPTO_LOCALIZACOES
WHERE [Link] = [Link] AND
DEPTO_LOCALIZACOES. Dnumero = [Link]
 81

Junção
3. Nome do empregado, nome do seu departamento e
seus dependentes:

SELECT Pnome, Dnome, Nome_dependente

FROM EMPREGADO, DEPARTAMENTO, DEPENDENTE
WHERE [Link] = [Link] AND
[Link] = [Link]
 82

Join
1. Nome do empregado juntamente com o nome do seu
departamento:

SELECT Pnome, Dnome

FROM EMPREGADO, DEPARTAMENTO
WHERE [Link] = [Link]

SELECT Pnome, Dnome

FROM EMPREGADO JOIN DEPARTAMENTO
ON [Link] = [Link]
 83

Join
3. Nome do empregado, seu departamento e seus
dependentes:
SELECT Pnome, Dnome, Nome_dependente
FROM EMPREGADO
JOIN DEPARTAMENTO
ON [Link] = [Link]
JOIN DEPENDENTE
ON [Link] = [Link]
 84

As
• Renomear:
• Atributos
• Deve aparecer na cláusula SELECT
• Útil para a visualização das respostas na tela
• Relações
• Deve aparecer na cláusula FROM
• Útil quando a mesma relação é utilizada mais do que uma vez na
mesma consulta
• Sintaxe
• nome_antigo AS nome_novo
 85

As
• Nome do empregado, nome do seu departamento e
seus dependentes:

SELECT Pnome AS nome_empregado,

Dnome AS nome_departamento,
Nome_dependente AS dependente
FROM EMPREGADO AS emp,
DEPARTAMENTO AS depto,
DEPENDENTE AS dep
WHERE [Link] = [Link] AND [Link] = [Link]
 86

Order by
• Ordena as tuplas resultantes de uma consulta:
• ASC: ordem ascendente (padrão)
• DESC: ordem descendente

• Ordenação pode ser especificada em vários atributos:

• Ordenação referente ao primeiro atributo é prioritária
• Se houver valores repetidos, então é utilizada a ordenação
referente ao segundo atributo, e assim por diante
 87

Exercício
• Liste os dados dos empregados e seus departamentos.
Ordene o resultado pelo nome do departamento.
 88

Exercício - Resposta
• Liste os dados dos empregados e seus departamentos.
Ordene o resultado pelo nome do departamento.

SELECT *
FROM EMPREGADO, DEPARTAMENTO
WHERE [Link] = [Link]
ORDER BY Dnome
 89

Order by
• Se houver valores repetidos, então é utilizada a ordenação
referente ao segundo atributo, e assim por diante.

SELECT Pnome, Minicial, Unome, Dnome

FROM EMPREGADO, DEPARTAMENTO
WHERE [Link] = DEPARTAMENTO".Dnumero
ORDER BY Dnome

SELECT Pnome, Minicial, Unome, Dnome

FROM EMPREGADO, DEPARTAMENTO
WHERE [Link] = [Link]
ORDER BY Dnome, Pnome
 90

Funções de agregação
• Recebem uma coleção de valores como entrada;
• Retornam um único valor como saída.
• Média: AVG( )
• Mínimo: MIN( )
• Máximo: MAX( )
• Total: SUM( )
• Contagem: COUNT( )
•
• Obs.:
• DISTINCT: não considera valores duplicados
• ALL: inclui valores duplicados
 91

Exercício
• Qual a média dos salários dos empregados?

• Qual a soma dos salários dos empregados?

• Qual é o salário mais baixo dos salários dos

empregados?

• Qual é o salário mais alto dos salários dos

empregados?
 92

Exercício - Resposta
• Qual a média dos salários dos empregados?
SELECT AVG(salario)
FROM EMPREGADO

• Qual a soma dos salários dos empregados?

• Qual é o salário mais baixo dos salários dos

empregados?

• Qual é o salário mais alto dos salários dos empregados?

 93

Exercício - Resposta
• Qual a média dos salários dos empregados?

• Qual a soma dos salários dos empregados?

SELECT SUM(salario)
FROM EMPREGADO

• Qual é o salário mais baixo dos salários dos

empregados?

• Qual é o salário mais alto dos salários dos empregados?

 94

Exercício - Resposta
• Qual a média dos salários dos empregados?

• Qual a soma dos salários dos empregados?

• Qual é o salário mais baixo dos salários dos

empregados?
SELECT MIN(salario)
FROM EMPREGADO

• Qual é o salário mais alto dos salários dos empregados?

 95

Exercício - Resposta
• Qual a média dos salários dos empregados?

• Qual a soma dos salários dos empregados?

• Qual é o salário mais baixo dos salários dos

empregados?

• Qual é o salário mais alto dos salários dos empregados?

SELECT MAX(salario)
FROM EMPREGADO
 96

Exercício
• Quantos supervisores existem na relação EMPREGADO?
 97

Exercício - Resposta
• Quantos supervisores existem na relação EMPREGADO?
SELECT COUNT(SUPERSSN)
FROM EMPREGADO
 98

Exercício
• Quantos supervisores existem na relação EMPREGADO?
SELECT COUNT(SUPERSSN)
FROM EMPREGADO

• Quantos supervisores diferentes existem na relação

EMPREGADO?
 99

Exercício - Resposta
• Quantos supervisores existem na relação EMPREGADO?
SELECT COUNT(SUPERSSN)
FROM EMPREGADO

• Quantos supervisores existem na relação EMPREGADO?

SELECT COUNT(DISTINCT SUPERSSN)
FROM EMPREGADO
 100

Group by
• Permite aplicar uma função de agregação não somente a
um conjunto de tuplas, mas a um grupo de um conjunto
de tuplas

• Grupo de um conjunto de tuplas:

• Conjunto de tuplas que possuem o mesmo valor para os atributos
de agrupamento
 101

Group by
• Qual o maior salário, o menor salário e a média de
salários na relação EMPREGADO por supervisor?
SELECT MIN(salario), MAX(salario), AVG(salario)
FROM EMPREGADO
GROUP BY SUPERSSN

SELECT SUPERSSN, MIN(salario), MAX(salario), AVG(salario)

FROM EMPREGADO
GROUP BY SUPERSSN
 102

Having
• Especifica uma condição de seleção para grupos;

• Recupera os valores para as funções somente para

aqueles grupos que satisfazem à condição imposta na
cláusula HAVING.
 103

Having
• Qual o maior salário, o menor salário e a média de
salários na relação EMPREGADO por supervisor, para
médias salariais superiores a 30000?
SELECT MIN(salario), MAX(salario), AVG(salario)
FROM EMPREGADO
GROUP BY SUPERSSN
HAVING AVG(salario) > 30000

SELECT SUPERSSN, MIN(salario), MAX(salario), AVG(salario)

FROM EMPREGADO
GROUP BY SUPERSSN
HAVING AVG(salario) > 30000
SQL
IV – REMOÇÃO
Disciplina: Banco de Dados
Professora: Dayse de Almeida
 105

DML
• DELETE FROM ... WHERE ...
• Remove dados de tabelas já existentes
 106

Delete
DELETE FROM nome_tabela
WHERE predicado

• Cláusula WHERE
• É opcional:
• Todas as tuplas da tabela são eliminadas
• A tabela continua a existir
 107

Exercícios
1. Liste os nomes dos empregados e os projetos para os quais cada
empregado trabalha. Ordene o resultado pelo nome do projeto, em
ordem ascendente. Renomeie as colunas exibidas para Nome do
Empregado e Nome do Projeto.

2. Resolva o exercício anterior usando a cláusula JOIN.

3. Liste os nomes dos departamentos e seus respectivos projetos.

Ordene o resultado pelo nome do departamento, em ordem
ascendente. Renomeie as colunas exibidas para Nome do
Departamento e Nome do Projeto.

4. Resolva o exercício anterior usando a cláusula JOIN.

5. Liste os nomes dos departamentos que possuem projetos usando

a cláusula IN.
 108

Exercícios
6. Selecione os nomes e salários dos empregados que
ganham entre 40000 e 50000.

7. Resolva o exercício anterior usando a cláusula BETWEEN...

AND.

8. Selecione os nomes do empregados que têm dependentes,

usando a cláusula INTERSECT.

9. Selecione os nomes do empregados que têm dependentes,

usando apenas a cláusula IN.

10. Selecione os nomes do empregados que têm dependentes,

usando apenas a cláusula EXISTS.
 109

Exercícios
11. Liste a quantidade de empregados cadastrados.

12. Liste a quantidade de empregados por supervisor, bem

como o ssn do supervisor.

13. Liste a quantidade de empregados por supervisor e o ssn do

supervisor, de forma que apenas os supervisores com mais
de dois empregados supervisionados sejam listados.

14. Liste os números dos departamentos, bem como seus

gastos totais com salários de empregados, para os casos
em que os gastos são superiores à média dos gastos (com
salário de todos os empregados). Ordene o resultado de
forma descendente pelo gasto.
 110

Exercício – Delete
15. Remover as seguintes tuplas no banco de dados
EMPRESA:
• (f) Remover as tuplas com ESSN= '333445555‘ de
TRABALHA_EM.

• (g) Remover de EMPREGADO a tupla com SSN= '987654321'.

• (h) Remover de PROJETO a tupla com PJNOME= 'ProdutoX'.

 111

Extras
• Retornar bancos, dono, codificação, comentários e
tablespace:

SELECT [Link] AS banco,

[Link] AS dono,
pg_encoding_to_char(encoding) AS codificacao,
(SELECT description FROM pg_description pd WHERE
[Link]=[Link]) AS comentario,
(SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
[Link]=[Link]) AS tablespace

FROM pg_database pdb, pg_user pu

WHERE [Link] = [Link] ORDER BY [Link]
 112

Extras
• Selecionar colunas e tabelas:

SELECT
[Link],
[Link] AS "Column",
pg_catalog.format_type([Link], [Link]) AS "Datatype"

FROM pg_catalog.pg_attribute a
INNER JOIN pg_stat_user_tables c on [Link] = [Link]
WHERE
[Link] > 0 AND
NOT [Link]
ORDER BY [Link], [Link]
 113

Extras
• Selecionar colunas e tabelas:

SELECT
[Link] AS tabela,
[Link] AS coluna,
[Link] AS default,
pg_catalog.format_type([Link], [Link]) AS tipo

FROM pg_catalog.pg_attribute a
INNER JOIN pg_stat_user_tables c
ON [Link] = [Link]
LEFT JOIN pg_catalog.pg_attrdef d
ON [Link] = [Link] and [Link] = [Link]
WHERE [Link] > 0 AND NOT [Link]
ORDER BY [Link], [Link]
 114

Extras
• Retornar tabelas e esquemas do banco de dados atual:

SELECT [Link] AS esquema, [Link] AS tabela FROM

pg_namespace n, pg_class c
WHERE [Link] = [Link]
AND [Link] = 'r' -- no indices
AND [Link] NOT LIKE 'pg\\_%' -- no catalogs
AND [Link] != 'information_schema' -- no
information_schema
ORDER BY nspname, relname
 115

Extras
• Retornar tabelas do banco de dados e esquema atual:

SELECT schemaname AS esquema, tablename AS tabela,

tableowner AS dono
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema',
'pg_toast')
ORDER BY schemaname, tablename