Bancos de Dados
Mestrado em Engenharia de Computação
área de concentração Geomática
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
1
Structured Query Language (SQL)...
• Linguagem de Banco de Dados com comandos
para definição de dados, consulta e atualização
• Origens: SEQUEL (Structured English QUEry
Language)
• Projetada e implementada nos centros de pesquisa
IBM como interface para o SYSTEM R - um
Sistema de Banco de Dados Relacional.
• Linguagem de consulta do DB2 da IBM e de
vários outros bancos de dados
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
2
Structured Query Language (SQL)
• esforços da ANSI (American National Standards
Institute) e ISO (International Standards
Organization) para padronização:
– SQL1 (SQL ANSI 1986)
– SQL2 (SQL ANSI 1992)
– SQL3 (....)
• Inserida em várias hosts-languages (C, Pascal, etc.)
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
3
Definição de Dados em SQL...
• SQL usa os termos table, row e column para relação, tupla e
atributo, respectivamente.
• As primeiras versões do SQL não incluíam o conceito de schema
• Um schema SQL é identificada por um nome de schema e inclui
um identificador de autorização para indicar o usuário
proprietário do schema, bem como descritores para cada
elemento do schema.
• Elementos do schema: tabelas, visões, domínios e outros (ex.
autorizações)
• Um schema pode, ao ser criado, incluir todas as definições de
seus elementos, ou, alternativamente, incluí-las mais tarde.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
4
Definição de Dados em SQL...
•
•
•
•
CREATE SCHEMA COMPANY AUTHORIZATION SMITH;
Catálogo: representa uma coleção de schemas em um ambiente
SQL
Um catálogo sempre contém um elemento especial chamado
INFORMATION_SCHEMA, que fornece informações sobre todos
os descritores de elementos de todos os seus esquemas, para os
usuários autorizados.
Restrições de integridade podem ser definidas apenas entre as
relações pertencentes a um mesmo catálogo.
Schemas dentro de um mesmo catálogo podem compartilhar certos
elementos, como definições de domínios.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
5
Definição de Dados em SQL...
• O comando CREATE TABLE é utilizado para
especificar uma relação , incluindo o nome da
relação, seus atributos e restrições de integridade.
Especificam-se os atributos, fornecendo-se um
nome para cada um, bem como um tipo de dados
para determinar o domínio dos valores e,
possivelmente, restrições.
Especificam-se as restrições de chave, integridade
da entidade e integridade referencial.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
6
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
7
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
8
Exemplo: Banco de Dados “Companhia”
CREATE TABLE EMPLOYEE
(FNAME
VARCHAR(15)
NOT NULL,
MINIT
CHAR,
LNAME
VARCHAR(15)
NOT NULL,
SSN
CHAR(9)
NOT NULL,
BDATE
DATE,
ADDRESS
VARCHAR(30),
SEX
CHAR,
SALARY
DECIMAL(10,2),
SUPERSSN
CHAR(9),
DNO
INT
NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER));
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
9
Exemplo: Banco de Dados “Companhia”
CREATE TABLE DEPARTMENT
(DNAME
VARCHAR(15)
NOT NULL,
DNUMBER
INT
NOT NULL,
MGRSSN
CHAR(9)
NOT NULL,
MGRSTARTDATE
DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE (SSN));
CREATE TABLE DEPT_LOCATIONS
( DNUMBER
INT
NOT NULL,
DLOCATION
VARCHAR(15)
NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER));
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
10
Exemplo: Banco de Dados “Companhia”
CREATE TABLE PROJECT
(PNAME
VARCHAR(15)
NOT NULL,
PNUMBER
INT
NOT NULL,
PLOCATION
VARCHAR(15),
DNUM
INT
NOT NULL,
PRIMARY KEY (PNUMBER),
UNIQUE (PNAME),
FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER));
CREATE TABLE WORKS_ON
(ESSN
CHAR(9)
NOT NULL,
PNO
INT
NOT NULL,
HOURS
DECIMAL(3,1),
NOT NULL,
PRIMARY KEY (ESSN, PNO),
FOREIGN KEY (ESSN ) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER));
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
11
Exemplo: Banco de Dados “Companhia”
CREATE TABLE DEPENDENT
(ESSN
CHAR(9)
NOT NULL,
DEPENDENT_NAME VARCHAR(15)
NOT NULL,
SEX
CHAR,
BDATE
DATE,
RELATIONSHIP
VARCHAR(8),
PRIMARY KEY (ESSN, DEPENDENT _NAME),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN));
-------------------------------------------------------------------------------------------------------------Dedinição, em SQL2, do Schema Company
• Observação: i) O schema SQL nos quais as relações acima estão declaradas está
implicitamente especificado no ambiente no qual os comandos CREATE TABLE são
executados. Alternativamente poderíamos ter:
CREATE TABLE COMPANY.EMPLOYEE ...
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
12
Definição de Dados em SQL...
• Data types disponíveis: numeric, character-string,
bit-string, date, time.
• Numeric: números inteiros de diferentes tipos
(INTEGER, SMALLINT) e números reais de
diferentes precisões (FLOAT, REAL, DOUBLE
PRECISION).
• Números formatados: DECIMAL(i, j) ou DEC(i, j)
ou NUMERIC(i, j)
i - precisão, ou no total de dígitos decimais
j - escala, ou no de dígitos após o ponto decimal
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
13
Definição de Dados em SQL...
• Character-string:
– comprimento fixo (CHAR(n) ou CHARACTER(n))
– comprimento variável (VARCHAR(n) ou CHAR
VARYING(n) ou CHARACTER VARYING(n))
• Bit-string:
– comprimento fixo (BIT(n))
– comprimento variável (BIT VARYING(n))
• Date e Time:
– TIME(I), TIME com zona, TIMESTAMP (date + time),
INTERVAL
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
14
Definição de Dados em SQL...
• DEFAULT <value> - para especificar valores default para um
atributo.
• UNIQUE - especifica chaves alternativas
• O projetista do schema pode especificar ações a serem
efetivadas, no caso de violação das regras de integridade
referencial, vinculando uma cláusula de “referential triggered
action” a quaisquer rstrições de chaves estrangeiras (SET
NULL, CASCADE, SET DEFAULT).
• Pode-se dar um nome às restrições para referências futuras
• Base tables (base relations)
• Virtual relations (criadas através do comando CREATE VIEW)
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
15
Definição de Dados em SQL...
CREATE TABLE EMPLOYEE
(...,
DNO
INT
NOT NULL DEFAULT 1,
CONSTRAINT EMPPK
PRIMARY KEY (SSN),
CONSTRAINT EMPSUPERFK
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT ON UPDATE CASCADE);
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
16
Definição de Dados em SQL...
CREATE TABLE DEPARTMENT
(...,
MGRSSN
CHAR(9)
NOT NULL DEFAULT “88865555”,
...,
CONSTRAINT DEPTPK
PRIMARY KEY (DNUMBER),
CONSTRAINT DEPTSK
UNIQUE (DNAME),
CONSTRAINT DEPTMGRFK
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE (SSN)
ON DELETE SET DEFAULT ON UPDATE CASCADE);
CREATE TABLE DEPT_LOCATIONS
(...,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER)
ON DELETE CASCADE ON UPDATE CASCADE);
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
17
Definição de Dados em SQL...
• O comando DROP SCHEMA possui duas opções:
RESTRICT e CASCADE
CASCADE - remove o schema e todos os seus
elementos: tabelas, domínios, restrições, etc.
RESTRICT - neste caso o schema só é removido se não
mais possuir elementos.
Ex.: DROP SCHEMA COMPANY CASCADE;
• DROP TABLE também possui duas opções: RESTRICT
e CASCADE
Ex.: DROP TABLE DEPENDENT CASCADE;
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
18
Definição de Dados em SQL...
• ALTER TABLE - ações possíveis: adicionar ou excluir uma
coluna (atributo), alterar a definição de uma coluna e adicionar
ou excluir restrições de integridade.
Ex.: ALTER TABLE COMPANY.EMPLOYEE ADD
JOB VARCHAR(12);
Ex: ALTER TABLE COMPANY.DEPARTMENT
DROP ADDRESS CASCADE;
(todas as constraints e views que referenciam a coluna são
automaticamente excluídas do schema)
Ex.: ALTER TABLE COMPANY.EMPLOYEE DROP
CONSTRAINT EMPSUPERFK CASCADE;
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
19
Queries em SQL...
• SELECT FROM WHERE block
SELECT <attribute list>
FROM <table list>
WHERE <condition>
• QUERY Q0: Recupere a data de nascimento e endereço do
empregado cujo nome é John B. Smith.
SELECT BDATE, ADDRESS
FROM
EMPLOYEE
WHERE
FNAME=‘John’ AND MINIT=‘B’ AND
LNAME=‘SMITH’
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
20
Queries em SQL...
• O SELECT FROM WHERE block é semelhante ao par de
operações SELECT-PROJECT da Álgebra Relacional.
• A cláusula SELECT do SQL especifica os atributos a serem
projetados
• A cláusula WHERE especifica a condição de seleção
• Em SQL o resultado da query pode ter tupls duplicadas
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
21
Queries em SQL...
• QUERY Q1: Recupere o nome e o endereço de todos os
empregados que trabalham no departamento ‘Research’.
SELECT FNAME, LNAME, ADDRESS
FROM
EMPLOYEE, DEPARTMENT
WHERE
DNAME=‘Research’ AND DNUMBER=DNO
• Trata-se de uma query do tipo SELECT-PROJECT-JOIN da
Álgebra Relacional
A condição de seleção é DNAME=‘Research”
A condição de JOIN é DNUMBER=DNO
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
22
Queries em SQL...
• QUERY Q2: Para todo projeto localizado em ‘Stafford’, liste o no
do projeto, o no do dpto que o controla, o último nome do gerente e
a data de seu nascimento.
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM
PROJECT, EMPLOYEE, DEPARTMENT
WHERE
DNUM=DNUMBER AND MGRSSN=SSN AND
PLOCATION=‘Stafford’
A condição de JOIN DNUM=DNUMBER relaciona um projeto ao
depto que o controla;
A condição de JOIN MGRSSN=SSN relaciona o depto contolador
ao empregado que o gerencia;
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
23
Queries em SQL...
• Nomes de atributos ambíguos e aliases
• QUERY Q1A: Supor que os atributos DNO e LNAME da relação
EMPLOYEE fossem chamados de DNUMBER e NAME e que o
atributo DNAME da relação DEPARTMENT também fosse
chamado de NAME. Neste caso a query Q1 teria que resolver as
ambigüidades presentes.
SELECT FNAME, EMPLOYEE.NAME, ADDRESS
FROM
EMPLOYEE, DEPARTMENT
WHERE
DEPARTMENT.NAME=‘Research’ AND
DEPARTMENT.DNUMBER=EMPLOYEE.DNUMBER
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
24
Queries em SQL...
• QUERY Q8: Para cada empregado, recupere o primeiro e último
nome e o o primeiro e último nome do seu supervisor imediato.
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM
EMPLOYEE E, EMPLOYEE S
WHERE
E.SUPERSSN=S.SSN
• Um alias pode seguir de imediato o nome da relação, como
acima, ou seguir a keyword AS. É também possível renomear os
atributos da relação dentro da query.
EMPLOYEE AS E(FN, MI, LN, SSN, BD, ADDR, SEX, SAL,
SSSN, DNO) ...
• Como na Álgebra Relacional não se pode especificar uma query
com um nível arbitrário de recursividade em um único comando.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
25
Queries em SQL...
• Uma query sem a cláusula WHERE indica que não há condição de seleção
aplicada sobre as tuplas. Assim, todas serão selecionadas (WHERE TRUE).
• QUERY Q9: Recupere os SSNs de todos os empregados.
SELECT SSN
FROM EMPLOYEE
• Se mais de uma relação é especificada na cláusula FROM e não há uma
cláusula WHERE, então o resultado é o CROSS PRODUCT das duas
relações.
• QUERY Q10: Recupere todas as combinações possíveis de EMPLOYEE
SSN e DEPARTMENT DNAME.
SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT
(similar as operações da AR: CROSS PRODUCT seguido de um PROJECT)
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
26
Queries em SQL...
• ATENÇÃO! É extremamente importante especificar todas as
seleções e condições JOIN na cláusula WHERE, sem o que
poderão resultar relações imensas e incorretas.
• Para recuperar os valores de todos os atributos usa-se um ‘*’.
• QUERY Q1C: Recupere todos os valores de atributos das tuplas
de EMPLOYEE, que trabalham no depto de no 5.
SELECT *
FROM EMPLOYEE
WHERE DNO=5
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
27
Queries em SQL...
• QUERY Q1D: Recupere todos os valores de atributos dos
empregados que trabalham no departamento ‘Research’, bem
como os atributos deste depto.
SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=‘Research’ AND DNO=DNUMBER
• QUERY Q10A: Recuperar o CROSS PRODUCT das relações
EMPLOYEE e DEPARTMENT.
SELECT *
FROM EMPLOYEE, DEPARTMENT
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
28
Queries em SQL...
• SQL não elimina tuplas duplicatas do resultado das queries pelas
seguintes razões:
– o custo da operação é alto (solução: classificar as tuplas e eliminar as
duplicatas)
– o usuário pode desejar visualisar as tuplas duplicatas
– quando se aplica uma função agregada às tuplas, em geral não se deseja a
eliminação de duplicatas
• Para se eliminar tuplas duplicatas do resultado deve-se usar a
keyword DISTINCT na cláusula SELECT
• QUERY Q11A: Liste todos os salários diferentes dos empregados.
SELECT DISTINCT SALARY
FROM EMPLOYEE
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
29
Queries em SQL...
• SQL incorporou algumas das operações sobre conjuntos da Álgebra Relacional:
união (UNION), diferença (EXCEPT) e interseção (INTERSECT).
• Nestas operações as tuplas duplicatas são eliminadas, a menos que a operação
seja seguida da keyword ALL.
• QUERY Q4: Faça uma lista de todos os nos de projetos PARA PROJETOS que
envolvam um empregado cujo último nome seja ‘Smith’, ou como um simples
empregado, ou como um gerente do departamento que controla o projeto.
(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’)
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
30
Queries em SQL...
• Queries aninhadas (trata-se de uma query SELECT completa,
dentro de uma cláusula WHERE de uma outra query, chamada de
outer query)
• QUERY Q4A = QUERY Q4
(SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER IN (SELECT
FROM
WHERE
PNUMBER
PROJECT, DEPARTMENT, EMPLOYEE
DNUM=DNUMBER AND MGRSSN=SSN
AND LNAME=‘Smith’)
OR
PNUMBER IN (SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSN=SSN AND LNAME=‘Smith’)
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
31
Queries em SQL...
• O operador de comparação IN compara um valor v com um
conjunto (ou múltiplos conjuntos) de valores V e resulta em
TRUE se v é um dos elementos de V.
• QUERY Q4B: Selecione o SSN de todos os empregados que
trabalhem a mesma combinação (project, hours) em algum projeto
em que o empregado ‘John Smith’ também trabalhe.
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (PNO, HOURS) IN (SELECT PNO, HOURS
FROM WORKS_ON, EMPLOYEE
WHERE LNAME=‘Smith’ AND FNAME=‘John’ AND ESSN=SSN);
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
32
Queries em SQL...
• Além do operador IN, outros operadores de comparação podem
ser usados para comparar um valor singular v (tipicamente o nome
de um atributo) com um conjunto V (tipicamente uma nested
query).
• O operador = ANY (= SOME) retorna TRUE se o valor v é igual
a algum valor no conjunto V (é equivalente a IN).
• Outros operadores que podem ser combinados com as keywords
ANY e SOME: >,>=,<,<= e <>.
• A keyword ALL pode ser usada com quaisquer dos operadores
acima.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
33
Queries em SQL...
• QUERY QX0: Recupere o nome dos empregados cujo salário seja
maior que os salários de todos os empregados do departamento 5.
SELECT
FROM
WHERE
LNAME, FNAME
EMPLOYEE
SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
WHERE DNO=5);
• Em geral pode-se ter um no arbitrário de nested queries.
• Para se evitar ambigüidades aplica-se a regra: uma referência a um atributo não
qualificado refere-se sempre à relação declarada na nested query mais interna.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
34
Queries em SQL...
• QUERY Q12: Recupere o nome de cada empregado que tenha um dependente
com o mesmo first name e sexo do empregado.
SELECT
FROM
WHERE
LNAME, FNAME
EMPLOYEE E
E.SSN IN
(SELECT ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN AND
E.FNAME=DEPENDENT_NAME AND
SEX=E.SEX)
• Sempre que uma condição em uma cláusula WHERE de uma nested query
referencia algum atributo de uma relação declarada na outer query, as duas
queries são ditas correlacionadas.
• A nested query é avaliada uma vez para cada tupla (ou combinação de tuplas)
na outer query.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
35
Queries em SQL...
• Em geral uma query formulada com blocos aninhados SELECT ...
FROM ... WHERE e usando os operadores de comparação ‘=‘ ou
‘IN’ pode ser sempre expressa como uma query com um único
bloco.
• QUERY Q12A= QUERY Q12
SELECT
FROM
WHERE
UERJ - Agosto 2000
E.LNAME, E.FNAME
EMPLOYEE E, DEPENDENT D
E.SSN=D.ESSN AND E.SEX=D.SEX AND
E.FNAME=D.DEPENDENT_NAME
© Oscar Luiz Monteiro de Farias
36
Queries em SQL...
• A função EXISTS é utilizada para verificar se o resultado de
uma nested query correlacionada é vazio, i.e., não contém tuplas.
• QUERY Q12B (Q12 revisitada): Recupere o nome de cada empregado que
tenha um dependente com o mesmo first name e sexo do empregado.
SELECT
LNAME, FNAME
FROM
WHERE
EMPLOYEE E
EXISTS
(SELECT *
FROM DEPENDENT
WHERE E.SSN=ESSN AND
E.FNAME=DEPENDENT_NAME AND
SEX=E.SEX)
• EXISTS(Q) retorna TRUE se existe pelo menos uma tupla como
resultado da query Q e FALSE em caso contrário.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
37
Queries em SQL...
• QUERY 6: Recupere o nome de todos os empregados que não tenham
dependentes.
SELECT
FROM
WHERE
•
LNAME, FNAME
EMPLOYEE E
NOT EXISTS
(SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
A função UNIQUE(Q) retorna TRUE se não existem tuplas duplicatas no resultado da
query Q; em caso contrário retorna FALSE.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
38
Queries em SQL...
• QUERY 7: Liste o nome dos gerentes que tenham pelo menos um dependente.
SELECT
FROM
WHERE
UERJ - Agosto 2000
LNAME, FNAME
EMPLOYEE
EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
AND
EXISTS (SELECT *
FROM DEPARTMENT
WHERE SSN=MGRSSN)
© Oscar Luiz Monteiro de Farias
39
Queries em SQL...
• QUERY 3: Recupere o nome de cada empregado que trabalhe em todos os
propjetos controlados pelo depto de no 5 (operator CONTAINS - SQL original)
SELECT
FROM
WHERE
UERJ - Agosto 2000
LNAME, FNAME
EMPLOYEE
((SELECT
FROM
WHERE
CONTAINS
(SELECT
FROM
WHERE
PNO
WORKS_ON
SSN=ESSN)
PNUMBER
PROJECT
DNUM=5))
© Oscar Luiz Monteiro de Farias
40
Queries em SQL...
• QUERY 3A (paráfrase de Q3): Selecione cada empregado de tal forma que não
exista um projeto, controlado pelo depto 5 em que o empregado não trabalhe.
SELECT
FROM
WHERE
LNAME, FNAME
EMPLOYEE
NOT EXISTS
(SELECT
*
FROM
WORKS_ON B
WHERE
(B.PNO IN (SELECT PNUMBER
FROM
PROJECT
WHERE
DNUM=5))
AND
NOT EXISTS (SELECT
*
FROM
WORKS_ON C
WHERE
C.ESSN=SSN
AND C.PNO=B.PNO))
Universal quantifier () ou negated existential quantifier (not )
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
41
Queries em SQL...
• É possível explicitar um conjunto de valores em uma cláusula WHERE.
• QUERY 13: Recupere os SSNs de todos os empregados que trabalhem nos
projetos de nos 1, 2 ou 3.
SELECT
DISTINCT ESSN
FROM
WORKS_ON
WHERE
PNO IN (1, 2, 3)
• SQL permite queries que verificam se um valor é NULL (missing or
undefined or not applicable)
• Ao invés de usar os operadores = ou , SQL usa IS ou IS NOT.
• Isto porque SQL considera cada valor NULL distinto de todos os outros
valores NULL.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
42
Queries em SQL...
• QUERY 14: Recupere os nomes de todos os empregados que não tenham
supervisor.
SELECT
FROM
WHERE
LNAME, FNAME
EMPLOYEE
SUPERSSN IS NULL
• É possível renomear qualquer atributo que apareça no resultado de uma query,
adicionando-se o qualificador AS seguido pelo novo nome desejado.
• QUERY 8A ~ QUERY 8, porém trocando-se os últimos nomes de cada
empregado e de cada supervisor para EMPLOYEE_NAME e
SUPERVISOR_NAME, respectivamente:
SELECT
FROM
WHERE
UERJ - Agosto 2000
E.LNAME AS EMPLOYEE_NAME, S.LNAME AS
SUPERVISOR_NAME
EMPLOYEE AS E, EMPLOYEE AS S
E.SUPERSSN=S.SSN
© Oscar Luiz Monteiro de Farias
43
Queries em SQL...
• O conceito de joined table (or joined relation) foi incorporado em SQL2 para
permitir aos usuários especificar uma tabela que resultasse de uma operação
JOIN, na cláusula FROM de uma query.
• QUERY 1A ~ QUERY 1:
SELECT
FNAME, LNAME, ADDRESS
FROM
EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER
WHERE
DNAME=‘Research’
• Os atributos da tabela resultante do JOIN compreendem todos os atributos da
primeira tabela - EMPLOYEE - seguidos por todos os atributos da segunda
tabela - DEPARTMENT.
• Existem diferentes tipos de JOIN: NATURAL JOIN e OUTER JOINS
• NATURAL JOIN para duas relações R e S: nenhuma condição de JOIN é
especificada. Uma condição JOIN implícita (EQUI) é criada para cada par de
atributos com o mesmo nome em R e em S. Apenas um atributo permanece.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
44
Queries em SQL...
• Se os nomes dos atributos JOIN não são os mesmos nas relações
base, é possível renomear os atributos e, então, aplicar-se o
NATURAL JOIN.
• QUERY 1B ~ QUERY Q1: porém renomeou a relação DEPARTMENT para
DEPT e os atributos para DNAME, DNO (para ser igual ao atributo JOIN da
tabela EMPLOYEE), MSSN e MSDATE.
SELECT
FROM
WHERE
FNAME, LNAME, ADDRESS
(EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT
DNAME, DNO, MSNN, MSDATE)))
DNAME=‘Research’
A condição JOIN implícita é EMPLOYEE.DNO = DEPT.DNO
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
45
Queries em SQL...
• O tipo default de JOIN em uma tabela joined é o INNER JOIN,
em que uma tupla é incluída no resultado, apenas se existe uma
tupla na outra relação que satisfaz a condição do JOIN.
• QUERY 8B ~ QUERY 8A: porém desejamos incluir no resultado todos os
empregados, mesmo aqueles que não possuem um supervisor.
SELECT
FROM
E.LNAME AS EMPLOYEE_NAME, S.LNAME AS
SUPERVISOR_NAME
(EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON
E.SUPERSSN=S.SSN)
• Opções de JOIN: INNER JOIN (=JOIN), LEFT OUTER JOIN,
RIGHT OUTER JOIN, FULL OUTER JOIN.
• Pode-se omitir a keyword OUT.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
46
Queries em SQL...
• QUERY Q2A = Q2:
SELECT
PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM
((PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER)
JOIN EMPLOYEE ON MGRSSN=SSN)
WHERE
PLOCATION=‘Stafford’
• Funções agregadas (agregate functions) e agrupamento (grouping):
• built-in functions: COUNT, SUM, MAX, MIN, AVG. Aplicadas a um
conjunto ou vários conjuntos de valores.
• Podem ser usadas nas cláusulas SELECT OU HAVING.
• QUERY Q15: Recupere a soma dos salários de todos os empregados, o maior e
menor salário e o salário médio.
SELECT
SUM(SALARY), MAX (SALARY), MIN (SALARY),
AVG (SALARY)
FROM
EMPLOYEE
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
47
Queries em SQL...
• QUERY Q15: Ache a soma dos salários de todos os empregados do depto
‘Research’, como também os salários máximo, mínimo e médio neste depto.
SELECT
SUM(SALARY), MAX (SALARY), MIN (SALARY),
AVG (SALARY)
FROM
EMPLOYEE, DEPARTMENT
WHERE
DNO=DNUMBER AND DNAME = ’Research’
• QUERY Q17: Recupere o no total de empregados na companhia.
SELECT
COUNT(*)
FROM
EMPLOYEE
• A função COUNT( ) retorna o no de tuplas ou valores especificados na query.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
48
Queries em SQL...
• QUERY Q18: Recupere o no de empregados do depto ‘Research’.
SELECT
COUNT(*)
FROM
EMPLOYEE, DEPARTMENT
WHERE
DNO=DNUMBER AND DNAME=‘Research’
• Pode-se usar a função COUNT para contar valores em uma coluna, ao invés de
em tuplas.
• QUERY Q19: Conte o número de salários distintos no Banco de Dados
Companhia.
SELECT
COUNT (DISTINCT SALARY)
FROM
EMPLOYEE
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
49
Queries em SQL...
• Em alguns casos poderemos necessitar de usar funções para
selecionar tuplas particulares. Nestes casos especifica-se uma
nested query correlacionada com a função desejada e usa-se a
nested query na cláusula WHERE da outer query.
• QUERY Q5: Recuperar os nomes de todos os empregados que tenham dois ou
mais dependentes.
SELECT
FNAME, LNAME
FROM
EMPLOYEE
WHERE
(SELECT
COUNT(*)
FROM
DEPENDENT
WHERE
SSN = ESSN)  2
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
50
Queries em SQL...
• SQL possui uma cláusula GROUP BY com a finalidade de permitir o
agrupamento de atributos.
• Os atributos a serem grupados devem ser especificados na cláusula GROUP BY
e também na cláusula SELECT, de tal forma que os valores resultantes da
aplicação de funções a um grupo de tuplas apareçam juntamente com o valor
do atributo de grupamento.
• QUERY Q20: Para cada depto recupere o no do depto, o no de empregados no
depto e o seu salário médio.
SELECT
DNO, COUNT (*), AVG (SALARY)
FROM
EMPLOYEE
GROUP BY DNO
• A cláusula SELECT inclui somente o atributo a ser agrupado e as funções a
serem aplicadas em cada grupo de tuplas.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
51
Queries em SQL...
• QUERY Q21: Para cada projeto recupere o seu no, o seu nome e o no de
empregados que trabalham no projeto.
SELECT
PNUMBER, PNAME, COUNT(*),
FROM
PROJECT, WORKS_ON
WHERE
PNUMBER = PNO
GROUP BY PNUMBER, PNAME
Observação: O agrupamento e as funções são aplicadas depois do joining das
duas relações.
• Em alguns casos desejamos recuperar os valores destas funções apenas para
grupos que satisfazem determinadas condições. SQL provê uma cláusula
HAVING para esta finalidade, a qual pode aparecer em conjunto com uma
cláusula GROUP BY.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
52
Queries em SQL...
• HAVING provê uma condição no grupo de tuplas associadas com cada valor
do atributo de agrupamento. Somente os grupos que satisfazem esta condição
são recuperados no resultado da query.
• QUERY Q22: Para cada projeto no qual trabalham mais de dois empregados,
recupere o no do projeto, o nome do projeto e o no de empregados que
trabalham no projeto.
SELECT
PNUMBER, PNAME, COUNT(*)
FROM
PROJECT, WORKS_ON
WHERE
PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING
COUNT(*) > 2
Observação: a condição de seleção na cláusula WHERE limita as tuplas às
quais as funções são aplicadas; a cláusula HAVING limita grupos inteiros.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
53
Queries em SQL...
• QUERY Q23: Para cada projeto, recupere o no do projeto, o nome do projeto e
o no de empregados do depto 5 que trabalham no projeto.
SELECT
FROM
WHERE
GROUP BY
PNUMBER, PNAME, COUNT(*)
PROJECT, WORKS_ON, EMPLOYEE
PNUMBER = PNO AND SSN = ESSN AND DNO = 5
PNUMBER, PNAME
• Deve-se ser muito cuidadoso ao se aplicar duas diferentes condições (Uma para
a função na clúsula SELECT e, a outra, para a função na cláusula HAVING).
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
54
Queries em SQL...
• QUERY Q240: Contar o no total de empregados cujos salários excedem US$
40,000 em cada depto, porém apenas para os deptos em que mais de 5
empregados trabalham.
SELECT
DNAME, COUNT(*)
FROM
DEPARTMENT, EMPLOYEE
WHERE
DNUMBER = DNO AND SALARY > 40000
GROUP BY DNAME
HAVING
COUNT(*) > 5
• ERRADO! Selecionará apenas os deptos que tenham mais de 5 empregados,
cada um deles ganhando mais de US$ 40000.
• REGRA: A cláusula WHERE é executada primeiro, para selecionar as tuplas
individuais; depois executa-se a cláusula HAVING, para selecionar os grupos
individuais de tuplas.
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
55
Queries em SQL...
• QUERY Q24: para cada depto em que mais de 5 empregados trabalham,
recuperar o no do depto, e o no de empregados com salário maior que US$
40000.
SELECT
DNAME, COUNT(*)
FROM
DEPARTMENT, EMPLOYEE
WHERE
DNUMBER = DNO AND SALARY > 40000 AND
DNO IN
(SELECT
DNO
FROM
EMPLOYEE
GROUP BY
DNO
HAVING
COUNT(*) > 5)
GROUP BY DNAME
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
56
Queries em SQL...
•
•
•
•
•
Comparações de substrings, operadores aritméticos e classificação
O operador LIKE possibilita comparação entre strings.
% substitui um no arbitrário de caracteres.
_ substitui um único caracter arbitrário.
QUERY Q25: Recupere todos os empregados com endereço em ‘Houston, TX’
SELECT
FNAME, LNAME
FROM
EMPLOYEE
WHERE
ADDRESS LIKE ‘%Houston, TX%’
• QUERY Q26: Encontre todos os empregados nascidos na década de 50.
SELECT
FNAME, LNAME
FROM
EMPLOYEE
WHERE
BDATE LIKE ‘__5_______’
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
57
Queries em SQL...
• Operadores aritméticos podem ser aplicados a valores numéricos
em uma query.
• QUERY Q27: Mostre os salários que resultariam, se a todos os trabalhadores
que trabalham no projeto ‘ProductX’ fosse dado um aumento de 10%.
SELECT
FNAME, LNAME, 1.1*SALARY
FROM
EMPLOYEE, WORKS_ON, PROJECT
WHERE
SSN=ESSN AND PNO=PNUMBER AND
PNAME=‘ProductX’
• O operador ‘||’ para strings
• ‘+’ e ‘-’ podem ser usados com os tipos de dados date, time, timestamp e
interval
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
58
Queries em SQL...
• Classificação: a cláusula ORDER BY
• QUERY Q28: Recupere uma lista de empregados e dos projetos em que eles
trabalham, ordenada por deptos e, dentro de cada depto, ordenada
alfabeticamente por sobrenome e nome.
SELECT
DNAME, LNAME, FNAME, PNAME
FROM
DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE
SSN=ESSN AND DNUMBER=DNO AND
PNO=PNUMBER
ORDER BY DNAME, LNAME, FNAME
• As keywords ASC e DESC
UERJ - Agosto 2000
© Oscar Luiz Monteiro de Farias
59