Expressões
Utilizadas para retornar um cálculo no SQL
Exemplo:
Q13) Listar o nome e os salários resultantes de um aumento
de 10% para os funcionários do projeto ’Productx’
SELECT fname, minit, lname, salary*1.1 as NewSalary
FROM employee, project, works_on
WHERE ssn=essn AND pno=pnumber
AND pname=’ProductX’
Cláusula BETWEEN
Retorna os dados entre intervalos fechados definidos.
Q14) Listar todos os empregados no departamento 5 cujo
salário está entre 30000 e 40000
SELECT *
FROM employee
WHERE dno=’5’
AND salary BETWEEN 30000 AND 40000
Cláusula Order By
Ordena o resultado pela(s) coluna(s) escolhida(s), da maneira desejada: Ascendente ou Descendente
Q 15) Listar os empregados e projetos em que eles estão
trabalhando, ordenados pelo departamento e, dentro de
cada departamento, ordenado pelo último e primeiro nome
SELECT dno, fname, lname, pno
FROM employee, works on
WHERE essn=ssn
ORDER BY dno, fname, lname
Consultas Complexas
- consultas aninhadas e comparação de conjuntos
- tabelas obtidas de junção
- funções de agregação
- cláusulas “group by” e having
A consulta Q4 abaixo pode ser reformulada, removendo a cláusula UNION e incluindo a cláusula IN
Q4) Listar todos os números de projetos que envolvam um empregado cujo último nome é ’Smith’ sendo que o empregado deve ser trabalhador ou gerente do departamento que controla o projeto.
(SELECT DISTINCT pnumber FROM project, department, employee
WHERE dnum=dnumber AND msgrssn=ssn AND lname=’Smith’)
UNION
(SELECT DISTINCT pnumber FROM project, works_on, employee
WHERE pno=pnumber AND essn=ssn AND lname=’Smith’)
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 FROM works on,employee
WHERE essn=ssn AND lname=’Smith’);
Comparando conjuntos com ALL, ANY ou SOME
Outras cláusulas para comparação de conjuntos:
ALL
ANY ou SOME (são sinônimos)
Ex: salary > ALL (SELECT salary FROM …);
salary < ANY (SELECT salary FROM …);
salary > SOME (SELECT salary FROM …);
Ambiguidade em consultas aninhadas – (apelidos de tabelas)
Q16) Listar o nome dos empregados com dependente(s) de mesmo ’first name’ e sexo que o empregado
SELECT e.fname,e.lname
FROM employee as e
WHERE e.ssn IN
(SELECT essn FROM dependent as d
WHERE fname=dependent_name
AND e.sex=d.sex);
SELECT e.fname,e.lname
FROM employee as e, dependent as d
WHERE ssn=essn
AND fname=dependent_name
AND e.sex=d.sex);
FUNÇÃO EXISTS – consultas aninhadas correlacionadas
Q16b) Listar o nome dos empregados com dependente(s) de mesmo ’first name’ e sexo que o empregado
SELECT e.fname,e.lname
FROM employee as e
WHERE EXISTS
(SELECT * FROM dependent d
WHERE e.ssn=d.essn
AND e.fname=d.dependent_name
AND e.sex=d.sex);
FUNÇÃO NOT EXISTS – consultas aninhadas correlacionadas
Q6) Listar os nomes de empregados sem dependentes
SELECT fname,lname
FROM employee
WHERE NOT EXISTS (SELECT *
FROM dependent
WHERE ssn=essn);
Aninhamento em dois níveis
Q3b) Listar o nome dos empregados que trabalham em todos
os projetos controlados pelo departamento número 4
OBS: Observe a expressão todos no enunciado da consulta. Esta é a divisão da álgebra relacional. Em SQL a divisão pode ser especificada de diversas formas.
SELECT lname, fname FROM employee
WHERE NOT EXISTS
(SELECT * FROM project WHERE dnum=4
AND NOT EXISTS
(SELECT * FROM works_on
WHERE essn=ssn
AND pnumber=pno));
Alternativas de Divisão para SQL
Alternativas de especificação de divisão em SQL:
Usando cláusulas NOT EXISTS e IN
Usando cláusula NOT EXISTS e EXCEPT
Usando cláusula FOREACH e EXISTS(não implementada no PostgreSql 8.3)
divisão usando cláusulas NOT EXISTS e IN
SELECT lname, fname FROM employee
WHERE NOT EXISTS
(SELECT * FROM works_on w1
WHERE (w1.pno IN (SELECT pnumber FROM
project WHERE dnum=4))
AND NOT EXISTS
(SELECT * FROM works_on w2
WHERE w2.essn=ssn AND w2.pno=w1.pno));
divisão usando cláusulas NOT EXISTS e EXCEPT
SELECT lname, fname FROM employee e
WHERE NOT EXISTS
(SELECT pnumber FROM project WHERE dnum=4
EXCEPT
(SELECT pno FROM works_on w
WHERE w.essn=e.ssn))
divisão usando cláusulas FOREACH e EXISTS
SELECT lname, fname FROM employee
WHERE FOREACH
(SELECT pnumber FROM project WHERE dnum=4)
EXISTS
(SELECT * FROM works_on
WHERE essn=ssn AND pno=pnumber));
ATENÇÃO: este comando não funciona no PostgreSql 8.3
Tabelas obtidas de junção
Q1a) Listar o nome e endereço dos empregados que
trabalham no departamento ’Research’
SELECT fname, minit, lname, address
FROM (employee JOIN department ON dno=dnumber)
WHERE dname=’Research’
Junção Natural
Na junção natural iguala-se atributos de mesmo nome
Qlb)Listar o nome e endereço dos empregados que trabalham no departamento ’Research’
SELECT fname, name, address
FROM (employee NATURAL JOIN
(department AS dept (dname, dno, mssn,
msdate)))
WHERE dname= ’Research’
OUTER JOIN
A cláusula {LEFT | FULL | RIGHT} OUTER JOIN mantêm no resultado todas as tuplas da tabela da esquerda, das duas tabelas ou da tabela da direita da junção, respectivamente, inserindo NULL quando não há casamento (matching)
Q8b)Para cada empregado, liste o seu primeiro acompanhado do primeiro nome de seu supervisor, mesmo se o empregado não tiver supervisor, liste seu nome
SELECT e.fname as employee_name,
s.fname as supervisor_name
FROM (employee AS e LEFT OUTER JOIN
employee AS s ON e.superssn =s.ssn)
Exemplo Q8b’)
Q8b’)Liste o primeiro nome do supervisor e o primeiro nome de seus supervisionado, ordenado pelo primeiro. Mesmo se o empregado não for supervisor de ninguém, liste seu nome na primeira coluna e mesmo se o empregado não tiver supervisor, liste seu nome na segunda coluna.
SELECT s.fname as supervisor_name,
e.fname as employee_name
FROM (employee AS e FULL OUTER JOIN
employee AS s ON e.superssn =s.ssn)
ORDER BY 1
Junções aninhadas
Q2a) Para todo projeto localizado em ’Stafford’, listar o número do projeto, o número do departamento que o controla e o último nome do gerente do departamento
SELECT pnumber, dnum, lname
FROM ((project JOIN department ON dnum=dnumber
(JOIN employee ON mgrssn=ssn))
WHERE plocation = ’Stafford’
Funções de agregação
• COUNT
• SUM
• MAX
• MIN
• AVG
• etc.
Exemplo Q19
Q19) Listar a soma de salários de todos os empregados,o maior salário e a média de salários
SELECT
SUM(salary), MAX(salary), MIN(salary) AVG(salary)
FROM employee;
Q20) Listar a soma de salários, o maior salário e a média de salários, somente para funcionários do departamento ‘Research’
SELECT
SUM(salary), MAX(salary), MIN(salary), AVG(salary)
FROM employee, department
WHERE dno=dnumber AND dname=’Research’;
Q21) Listar o número de empregados
SELECT COUNT(*) FROM employee;
Q23) Listar o número de salários distintos
SELECT COUNT(DISTINCT salary) FROM employee;
Funções de agregação em subconsultas
Q5) Listar o nome dos empregados que têm dois ou mais dependentes
SELECT lname, fname
FROM employee
WHERE (SELECT COUNT(*)
FROM dependent
WHERE essn=ssn) >= 2;
Cláusulas group by
Q24) Listar para cada departamento seu número, a quantidade de empregados e a média salarial de seus empregados
SELECT dnumber, COUNT(*), AVG(salary)
FROM department, employee
WHERE dno=dnumber
GROUP BY dnumber;
OBS: o agrupamento deve incluir todas as colunas da projeção que não incluem função de agregação
Group by com duas colunas
Q25) Listar para cada projeto seu número, nome e a quantidade de empregados que trabalham no projeto.
SELECT pnumber, pname, COUNT(*)
FROM project, works_on
WHERE pno=pnumber
GROUP BY pnumber, pname;
Cláusulas group by e having
Q26) Listar para cada projeto onde trabalham mais de dois empregados seu número e a quantidade de empregados que trabalham no projeto
SELECT pnumber, pname, COUNT(*)
FROM project, works_on
WHERE pno=pnumber
GROUP BY pnumber, pname
HAVING COUNT (* )> 2;
Cláusulas group by e consultas aninhadas com cláusula IN
Q28) Listar para cada departamento que tem mais que 5 empregados, o número do departamento e o número de empregados que ganham mais que 40000
SELECT dno, COUNT(*)
FROM employee
WHERE salary > 40000
AND dno IN
(SELECT dnumber FROM department
WHERE(SELECT COUNT(*)
FROM employee e2
WHERE e2 .dno=dnumber)>2)
SQL/DML e o PostgreSQL
EXERCÍCIOS DE IMPLEMENTAÇÃO
=> Exemplos de consultas simples no esquema company
=> Exemplos de consultas complexas no esquema company e exercícios usando o esquema SEE
Bibliografia:
[SK] Capítulos 3,4,5