DCC011
Introdução a Bancos de Dados
SQL (Structured Query Language) – DQL (Data
Query Language)
Michele A. Brandão
[Link]@[Link]
Programa
◉ Introdução
○ Conceitos básicos, características da abordagem de
banco de dados, modelos de dados, esquemas e
instâncias, arquitetura de um sistema de banco de
dados, componentes de um sistema de gerência de
banco de dados.
◉ Modelos de dados e linguagens
○ Modelo entidade-relacionamento (ER), modelo
relacional, álgebra relacional, SQL.
◉ Projeto de bancos de dados
○ Fases do projeto de bancos de dados, projeto
lógico de bancos de dados relacionais,
normalização.
◉ Novas Tecnologias e Aplicações de Banco de
Dados
2
Introdução
◉ SQL é considerada a razão principal
para o sucesso de bancos de dados
relacionais comerciais
◉ Tornou-se a linguagem padrão para
bases relacionais
◉ Funciona entre diferentes produtos
◉ Embedded SQL: Java, C/C++, Cobol…
Introdução
◉ SQL = LDD + LMD + LCD + LC
○ LDD – Ling. Definição de Dados
■ CREATE SCHEMA / TABLE / VIEW
■ DROP SCHEMA / TABLE / VIEW
■ ALTER TABLE
○ LMD – Ling. Manipulação de Dados
■ INSERT, UPDATE, DELETE
○ LCD – Ling. Controle de Dados
■ GRANT, REVOKE
○ LC – Ling. de Consulta
■ SELECT
Introdução
◉ Conceitos:
○ Tabela/Table = Relação
○ Linha/Row = Tupla
○ Coluna/Column = Atributo
Consultas Básicas
em SQL
◉ Formato básico do comando SELECT:
SELECT <lista de atributos>
FROM <lista de tabelas>
[ WHERE <condição>; ]
◉ Exemplo:
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=‘John’ AND
MINIT=‘B’ AND
LNAME=‘Smith’;
πbdate,Address σFname=‘John’AND Minit=‘B’AND Lname=‘Smith’(EMPLOYEE)
Consultas básicas
e Álgebra
◉ Operações Básicas
○ Seleção (σ) Seleciona um sub-conjunto de linhas da
relação èFROM, WHERE
○ Projeção (π) Mantém apenas colunas específicas è
SELECT
○ Junção (×, ⋈): WHERE
Consultas Básicas em SQL
A. SELECT FROM WHERE
(Q1)
◉ SELECT LNOME, ENDERECO
FROM EMPREGADO, DEPARTAMENTO
WHERE DNOME=‘Research’ AND dno=dnumero;
condição de seleção condição de junção
πlnome, endereco σdnome=‘Research’ (EMPREGADO ⋈dno=dnumero DEPARTAMENTO)
EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)
superssn REFERENCIA EMPLOYEE
dno REFERENCIA DEPARTMENT
DEPARTMENT (dnum, dname, mgrssn, mgrinitialdate)
mgrssn REFERENCIA [Link]
Consultas Básicas em SQL
B. Atributos Ambíguos e Pseudônimos (alias)
SELECT dname, dlocation
FROM
DEPARTMENT AS D, DEPT_LOCATIONS AS DL
WHERE [Link] = [Link];
(Q8) SELECT [Link], [Link], [Link], [Link]
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE [Link]=[Link];
EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)
superssn REFERENCIA EMPLOYEE
dno REFERENCIA DEPARTMENT
DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)
mgrssn REFERENCIA [Link]
PROJECT (pnumber, pname, plocation, dnum)
dnum REFERENCIA DEPARTAMENT
DEPT_LOCATIONS (dnumber,dlocation)
dnumber REFERENCIA DEPARTAMENT
Consultas Básicas em SQL
B. Atributos Ambíguos e Pseudônimos (alias)
(Q2) ◉ SELECT pnumber, dnum, lname, address, bdate FROM
PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE plocation=‘Stafford’ AND
condição de seleção
[Link]=[Link] AND [Link]=[Link];
condição de junção
πpnumber,dnum,lname,address,bdate σplocation=‘Stafford’
(EMPLOYEE ⋈ ssn=mgrssn (DEPARTMENT ⋈ PROJECT))
EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)
superssn REFERENCIA EMPLOYEE
dno REFERENCIA DEPARTMENT
DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)
mgrssn REFERENCIA [Link]
PROJECT (pnumber, pname, plocation, dnum)
dnum REFERENCIA DEPARTAMENT
Consultas Básicas em SQL
C. SELECT FROM sem o WHERE
SELECT ssn, lname, salary
FROM EMPLOYEE;
(Q10)
SELECT lname, dname
FROM EMPLOYEE, DEPARTMENT
WHERE dno=dnumber; ç com a junção
Atenção! A consulta em vermelho corresponde a um produto
cartesiano das tabelas EMPLOYEE e DEPARTMENT :
πlname,dname (EMPLOYEE x DEPARTMENT)
EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)
superssn REFERENCIA EMPLOYEE
dno REFERENCIA DEPARTMENT
DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)
mgrssn REFERENCIA [Link]
Consultas Básicas em SQL
D. TODOS OS ATRIBUTOS
◉ Consultas a todos os atributos
(Q1C)
SELECT *
FROM EMPLOYEE
WHERE Dno=5;
(Q1D)
SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE Dname=‘Research’ AND Dno=Dnumber;
EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)
superssn REFERENCIA EMPLOYEE
dno REFERENCIA DEPARTMENT
DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)
mgrssn REFERENCIA [Link]
Consultas Básicas em SQL
E. TABELAS COMO CONJUNTOS
◉ SQL trata uma tabela como um multi-conjunto
◉ Tuplas duplicadas PODEM aparecer em uma tabela
○ E no resultado de uma consulta
◉ SQL não elimina automaticamente as duplicatas porque…
○ Eliminação de duplicatas é uma operação cara (ordenar)
○ O usuário pode estar interessado nelas
○ Funções de agregação utilizam duplicatas (funções de agregação
serão explicadas a seguir)
◉ Operações
○ SELECT DISTINCT, SELECT ALL
○ ∪: UNION, ∖ ou - : EXCEPT, ∩: INTERSECT
Tabelas como Conjuntos
EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)
superssn REFERENCIA EMPLOYEE
dno REFERENCIA DEPARTMENT
DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)
SELECT salary mgrssn REFERENCIA [Link]
PROJECT (pnumber, pname, plocation, dnumber)
FROM EMPLOYEE; dnumber REFERENCIA DEPARTAMENT
Não elimina linhas (tuplas) duplicadas
Para eliminar precisa usar DISTINCT, por exemplo:
(Q11)
SELECT DISTINCT salary
FROM EMPLOYEE;
(Q4)
(SELECT pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE dnum=dnumber AND mgrssn=ssn AND
lname=‘Smith’)
UNION
(SELECT pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE pnumber=pno AND essn=ssn AND
lname=‘Smith’);
Facilidades Adicionais
A. JOINS
◉ Uso do operador JOIN, na cláusula FROM
◉ SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE JOIN DEPARTMENT
ON DNO=DNUMEBR)
WHERE DNAME=‘Research’;
◉ A cláusula FROM contém então uma única tabela
resultante da junção de Empregado e
Departamento
EMPREGADO (ssn, pnome, minicial, unome,
datanasc, endereco, sexo, salario, superssn, dno)
superssn REFERENCIA EMPREGADO
dno REFERENCIA DEPARTAMENTO
DEPARTAMENTO (dnumero, dnome, gerssn,
gerdatainicio)
gerssn REFERENCIA [Link]
Facilidades Adicionais
A. JOINS
◉ Pode-se especificar outros tipos de junção na
cláusula FROM
◉ Junção natural: equijoin em cada par de atributos
com o mesmo nome
◉ SELECT DNAME, DLOCATION
FROM (DEPARTMENT NATURAL JOIN
DEPT_LOCATIONS);
DEPARTAMENTO (dnumero, dnome, gerssn, gerdatainicio)
gerssn REFERENCIA [Link]
DEPTO_LOCALIZACOES (dnumero, dlocalizacao)
dnumero REFERENCIA DEPARTAMENTO
Facilidades Adicionais
A. JOINS
Renomeando atributos para o natural join:
◉ SELECT pnome, unome, dnome
FROM (EMPREGADO NATURAL JOIN
(DEPARTMENTO AS DEPT (dno, dnome,
gerssn, gerdatainicio)))
WHERE dnome = ‘Pesquisa’;
EMPREGADO (ssn, pnome, minicial, unome, …superssn, dno)
superssn REFERENCIA EMPREGADO
dno REFERENCIA DEPARTAMENTO
DEPARTAMENTO (dnumero, dnome, gerssn, gerdatainicio)
gerssn REFERENCIA [Link]
Facilidades Adicionais
A. JOINS
◉ INNER JOIN: somente pares empregado/depto
◉ OUTER JOIN
○ LEFT/RIGHT/FULL OUTER JOIN
○ SELECT fname, lname, dependent_name
FROM (EMPLOYEE LEFT OUTER JOIN
DEPENDENT ON ssn=essn);
EMPREGADO (ssn, pnome, minicial, unome,
datanasc, endereco, sexo, salario, superssn, dno)
superssn REFERENCIA EMPREGADO
dno REFERENCIA DEPARTAMENTO
DEPARTAMENTO (dnumero, dnome, gerssn,
gerdatainicio)
gerssn REFERENCIA [Link]
A B A B
SQL JOINS
SELECT … SELECT …
FROM A LEFT JOIN B FROM A RIGHT JOIN B
ON [Link] = [Link] A B ON [Link] = [Link]
SELECT …
A B A B
FROM A INNER JOIN B
ON [Link] = [Link]
SELECT … SELECT …
FROM A LEFT JOIN B FROM A RIGHT JOIN B
ON [Link] = [Link] ON [Link] = [Link]
WHERE [Link] IS NULL WHERE [Link] IS NULL
A B A B
SELECT … SELECT …
FROM A FULL OUTER JOIN B FROM A FULL OUTER JOIN B
ON [Link] = [Link] ON [Link] = [Link]
WHERE [Link] IS NULL
OR [Link] IS NULL
Facilidades Adicionais
B. FUNÇÕES DE AGREGAÇÃO
◉ Funções de agregação: COUNT, SUM, MAX, MIN, AVG
◉ SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),
AVG(SALARY)
FROM EMPLOYEE;
◉ SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),
AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=‘Research’;
◉ SELECT COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=‘Research’;
Consultas Complexas
em SQL
◉ Consultas aninhadas (fetch valores existentes)
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=‘Research’);
é equivalente à consulta
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=‘Research’;
EMPREGADO (ssn, pnome, minicial, unome, …superssn, dno)
superssn REFERENCIA EMPREGADO
dno REFERENCIA DEPARTAMENTO
DEPARTAMENTO (dnumero, dnome, gerssn, gerdatainicio)
gerssn REFERENCIA [Link] 23
Consultas Complexas
em SQL
◉ Comparação de conjuntos
(Q4A)
SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER IN (SELECT PNUMBER
FROM PROJECT, DEPARTMENT,
EMPLOYEE
WHERE DNUM=DNUMBER AND
MGRSSN=SSN AND
LNAME=‘Smith’)
OR
PNUMBER IN (SELECT PNO
EMPREGADO (ssn, … superssn, dno) FROM WORKS_ON, EMPLOYEE
superssn REFERENCIA EMPREGADO
dno REFERENCIA DEPARTAMENTO
WHERE ESSN=SSN AND
DEPARTAMENTO (dnumero, dnome, gerssn, gerdatainicio) LNAME=‘Smith’);
gerssn REFERENCIA [Link]
PROJETO (pnumero, pjnome, plocalizacao, dnum) A primeira consulta seleciona números de
dnum REFERENCIA DEPARTAMENTO projetos que tem Smith como gerente; a
TRABALHA_EM (essn, pno, horas) segunda consulta seleciona número dos
essn REFERENCIA EMPREGADO
pno REFERENCIA PROJETO
projetos que tem Smith como empregado.
24
Consultas Complexas
em SQL
◉ Comparação de conjuntos
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (PNO, HOURS) IN (SELECT PNO, HOURS
FROM WORKS_ON
WHERE ESSN=‘123456789’);
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
EMPREGADO (ssn, … superssn, dno)
superssn REFERENCIA EMPREGADO WHERE DNO=5);
dno REFERENCIA DEPARTAMENTO
TRABALHA_EM (essn, pno, horas)
essn REFERENCIA EMPREGADO
pno REFERENCIA PROJETO
25
Consultas Complexas
em SQL
◉ Uso da função EXISTS
(Q16B) SELECT [Link], [Link]
FROM EMPLOYEE AS E
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE [Link]=ESSN AND
[Link]=SEX AND
[Link]=DEPENDENT_NAME);
(Q6)
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN);
26
Consultas Complexas:
Agrupamento
◉ Aplicar funções de agregação a subgrupos de tuplas em
uma relação
◉ Exemplo: média de salário em cada departamento
◉ SELECT DNO, COUNT(*), AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO;
27
Agrupamento
◉ SELECT DNO, COUNT(*), AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO;
◉ Se um dno = NULL?!
○ Cria-se um grupo separado para todas as tuplas nas
quais o atributo é NULL
28
Agrupamento
◉ SELECT Pnumber, Pname, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname;
◉ Neste caso, GROUP BY é aplicado após a
junção de PROJECT e WORKS_ON
29
Agrupamento condicional
◉ Agrupa as tuplas; dos grupos resultantes,
queremos as que satisfazem uma condição
◉ Agrupamento com a cláusula HAVING
◉ Somente os grupos que satisfazem a condição
especificada em HAVING são retornados
◉ SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT(*) > 2;
◉ WHERE à tuplas; HAVING à grupos de tuplas
30
SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT(*) > 2;
31
Exemplo
◉ Para cada departamento que tem mais de 5
empregados, retorne o número do departamento e o
número de seus empregados que ganham mais de
40.000.
EMPREGADO (ssn, pnome, minicial, unome, …,
salario, superssn, dno)
superssn REFERENCIA EMPREGADO
dno REFERENCIA DEPARTAMENTO
DEPARTAMENTO (dnumero, dnome, gerssn, gerdatainicio)
gerssn REFERENCIA [Link]
32
Exemplo
Para cada departamento que tem mais de 5 empregados, retorne o
número do departamento e o número de seus empregados que ganham
mais de 40.000.
◉SELECT dnumero, COUNT(*)
FROM DEPARTAMENTO, EMPREGADO
WHERE dnumero=dno AND salario>40000
GROUP BY dnumero
HAVING COUNT (*) > 5;
ERRADO: Queremos listar o número de empregados
(com salario > 40000) dos departamentos com mais
de 5 empregados, não dos departamentos com mais
de 5 empregados com salario > 40000
EXEMPLO: Um departamento com 7 empregados,
dos quais somente 2 têm salario > 40000, é válido!
33
Exemplo
Para cada departamento que tem mais de 5 empregados, retorne o
número do departamento e o número de seus empregados que ganham
mais de 40.000.
◉SELECT dnumero, COUNT(*)
FROM DEPARTAMENTO, EMPREGADO
WHERE dnumero=dno AND salario>40000
AND dno IN (SELECT dno
FROM EMPREGADO
GROUP BY dno
HAVING COUNT (*) > 5)
GROUP BY dnumero;
34
Considerações Finais
◉ As consultas são avaliadas conceitualmente na seguinte
ordem:
1. FROM, identifica as tabelas/junções
2. WHERE
3. GROUP BY
4. HAVING
5. ORDER BY
◉ Se a consulta não tem group by, having e order by
1. Para cada combinação de linhas (uma de cada relação
especificada em FROM)
2. Avalia a cláusula WHERE
3. SE é true, adiciona os atributos de SELECT da combinação de
linhas no resultado
35
DCC011
Introdução a Bancos de Dados
SQL (Structured Query Language) – DQL (Data Query
Language)
Slides baseados no material dos professores Mirella M. Moro,
Rodrygo Santos e Clodoveu Davis