banco de dados distribuídosilmerio/sbd20132/sbd6sqldml.pdf · ufu/facom página 3 sql/dml def. a...

GBC043 - Sistemas de Banco de DadosSQL/DML no PostgreSQL

Ilmério Reis da [email protected]/~ilmerio/sbdUFU/FACOM/BCC

mailto:[email protected]

Prgrama Teórico/Prático - SQL

• Linguagem SQL– Comandos de criação e eliminação de tabelas– Especificação de restrições em bancos de dados– Evolução de esquemas de banco de dados– Comandos de inserção de tuplas em tabelas– Comandos de alteração e supressão de tuplas– Comandos de consulta (básicos e complexos)– Definição de visões

Álgebra Relacional Cálculo Relacional

UFU/FACOM Página 3

SQL/DML

Def. A SQL/DML(Data Manipulation Language) é um subconjunto da SQL usada para recuperar, inserir, atualizar e suprimir dados de tabelas do BD.

UFU/FACOM Página 4

SQL/DML - Principais Comandos • INSERT – inserção de linhas;• DELETE – supressão de linhas;• UPDATE – atualização de dados;• SELECT – recuperação de tabelas;

OBS: serão mostradas características do padrão SQL implementadas pelo PostgreSQL, omitindo funcionalidades adicionais e destacando eventuais omissões.

UFU/FACOM Página 5

SQL/DML - INSERT

INSERT INTO tabela [ ( coluna [, ...] ) ] { DEFAULT VALUES

|VALUES ( { expressão | DEFAULT } [, ...] ) [, ...]

| comando-select }

UFU/FACOM Página 6

SQL/DML - INSERT – Exemplo 1

INSERT INTO tabela VALUES (expressão [, ...] )INSERT INTO employee

VALUES ('John', 'B', 'Smith', '123456789',DATE '1965-01-09', '731 Fondren, Houston, TX','M', 30000, '333445555', 5);

Compatibilidade de tipos: employee (fname VARCHAR (15) NOT NULL, minit CHAR, lname VARCHAR (15) NOT NULL,

ssn CHAR(9) PRIMARY KEY, bdate DATE, address VARCHAR(30), sex CHAR CHECK (sex IN (’M’, ’F’)), salary DECIMAL(10,2), superssn CHAR(9), dno INT NOT NULL)

UFU/FACOM Página 7

SQL/DML- INSERT - Exemplo 2

INSERT INTO tabela ( coluna [, ...] ) VALUES (expressão [, ...] )

INSERT INTO employee(fname, minit, lname, ssn, dno)VALUES ('John', 'B', 'Smith', '123456789', 5);

UFU/FACOM Página 8

SQL/DML – INSERT – Exemplo 3

INSERT INTO tabela comando-select

INSERT INTO works_onSELECT ssn, pnumber, 0 FROM employee, project;

UFU/FACOM Página 9

SQL/DML– DELETE

DELETE FROM tabela [ [ AS ] alias ] [ WHERE condição

| WHERE CURRENT OF cursor_name ]

DELETE FROM employee WHERE ssn = '123456789'

OBS: o uso de cursores será visto posteriormente

UFU/FACOM Página 10

SQL/DML– UPDATE

UPDATE tabela [ [ AS ] alias ] SET {coluna = { expresssão | DEFAULT } | ( coluna [, ...] ) =

( { expressão | DEFAULT } [, ...] ) } [, ...]

[ WHERE condição | WHERE CURRENT OF cursor_name ]

OBS: o uso de cursores será visto posteriormente


SQL/DML– UPDATE – Exemplo 1

UPDATE employee SET address = 'Av. Joao Naves de Avila, 2121', salary = salary * 1.5WHERE ssn = '123456789'


SQL/DML– UPDATE – Contra Exemplo

UPDATE works_onSET (pno, hours) =

(SELECT pnumber, 10 FROM project WHERE pnumber = 1)

WHERE essn='123456789';OBS: o padrão permite associação de uma lista de

atributos com uma tupla resultante da saída de uma consulta, mas isto não foi implementado pelo PostgreSql 8.3, onde as expressões devem ser independentes. Logo o comando acima não funciona no PostgreSql 8.3 (teste isso nas versões mais recentes)


SQL/DML– UPDATE – Exemplo 2

UPDATE works_onSET (pno, hours) = (1, 10)WHERE essn='123456789';


SQL/DML– SELECT

SELECT [ALL | DISTINCT] * | expressão [ AS nome_saida ] [, ...] FROM item_from [, ...] [ WHERE condição ] [ GROUP BY expressão [, ...] ] [ HAVING condição [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expressão [ ASC | DESC | USING operador ] [ NULLS { FIRST | LAST } ] [, ...] ] [ FOR { UPDATE | SHARE } [ OF nome_tabela [, ...] ] [ NOWAIT ] [...] ]


SQL/DML–Consultas básicas e complexas Considerando os diversos parâmetros do comando SELECT, para efeito didático, vamos dividir nosso estudo em:

consultas básicas: na condição do WHERE não existe outro SELECT

consultas complexas: são consultas aninhadas, onde na condição do SELECT existe outra cláusula SELECT.


SQL/DML–SELECT-FROM-WHERE Formato de comando SELECT para consultas básicas:

SELECT lista-de-atributos FROM lista-de-tabelas WHERE condição

OBS:– condições sem cláusula SELECT;– os exemplos a seguir seguem a numeração de EN e estão

baseados no BD company;


SQL/DML–SELECT-FROM-WHERE (Esquema Company)


SQL/DML–SELECT-FROM-WHERE (Uma instância de Company)


SQL/DML - Seleção/projeção

QO) Listar a data de nascimento e o endereço dos empregados com nome : John B. Smith.


SQL/DML- Exemplo Q0

Q0) Listar a data de nascimento e o endereço dos empregados com nome : John B. Smith.

SELECT bdate, address FROM employeeWHERE fname=’John’

AND minit=’B’ AND lname=’Smith’;


SQL/DML– Seleção/projeção/junção

Q1) Listar o nome e endereço dos empregados que trabalham no departamento ’Research’


SQL/DML – Exemplo Q1

Q1) Listar o nome e endereço dos empregados que trabalham no departamento ’Research’

SELECT fname, minit, lname, address FROM employee, department

WHERE dno=dnumber AND dname=’Research’


SQL/DML- Junção com duas condições

Q2) Para todo projeto localizado em ’Stafford’, listar o número do projeto, o número do departamento que o controla e o último nome, endereço e data de nascimento do gerente do departamento.


SQL/DML- Exemplo Q2

Q2) Para todo projeto localizado em ’Stafford’, listar o número do projeto, o número do departamento que o controla e o último nome, endereço e data de nascimento do gerente do departamento.

SELECT pnumber, dnum, lname, address, bdateFROM project, department, employeeWHERE plocation=’Stafford’

AND dnum=dnumber AND ssn=mgrssn


SQL/DML-Ambiguidade de nomes de atributos

Suponha que DNUMBER e NAME são os nomes dos atributos DNO e LNAME em EMPLOYEE, respectivamente. Além disso, suponha que NAME é o nome do atributo DNAME em DEPARTMENT.

Então:

employee(fname, minit, name, ssn, bdate, address, sex, salary, superssn, dnumber)

department(name, dnumber, mgrssn, mgrstartdate)


SQL/DML – Qualificando atributos

Q1a) Listar o nome e endereço dos empregados que trabalham no departamento ’Research’ considerando os esquemas abaixo

employee(fname, minit, name, ssn, bdate, address, sex, salary, superssn, dnumber)

department(name, dnumber, mgrssn, mgrstartdate)


SQL/DML- Exemplo Q1a

Q1a) Listar o nome e endereço dos empregados que trabalham no departamento ’Research’ considerando os novos esquemas

SELECT fname, minit, employee.name FROM employee, department

WHERE employee.dnumber=department.dnumber AND department.name=’Research’


SQL/DML – Apelidos de Tabelas

Q8) Para cada empregado, liste o seu primeiro e o seu último nome acompanhados do último nome de seu supervisor.



Q8) Para cada empregado, liste o seu primeiro e o seu último nome acompanhados do último nome de seu supervisor.

SELECT e.fname, e.lname, s.lnameFROM employee AS e, employee AS sWHERE e.superssn=s.ssn


SQL/DML – Apelidos de tabelas

Q1b') Listar o nome e o endereço dos empregados que trabalham no departamento ’Research’ considerando as novas tabelas employee e department e usando apelidos


SQL/DML - Exemplo Q1b

Q1b') Listar o nome e o endereço dos empregados que trabalham no departamento ’Research’ considerando as novas tabelas employee e department e usando apelidos

SELECT fname, minit, e.name, addressFROM employee e, department dWHERE e.dnumber=d.dnumber

AND d.name=’Research’


SQL/DML-Exemplo Q1c–Omitindo WHERE e uso do * para consulta sem projeção

Qlc') Listar todos os valores de atributos de todos os empregados

SELECT * FROM employee


SQL/DML-Ex. Q10b – Produto Cartesiano

Q1Ob) Listar o produto cartesiano de empregados e departamentos

SELECT *FROM employee, department


SQL/DML - Tabela x relação// multset ou bags x set // Cláusula DISTINCT

Q11a) Listar todos os salários distintos

SELECT DISTINCT salaryFROM employee


SQL/DML – Cláusula UNION

Q4) Listar todos os números de projetos que envolvam umempregado cujo último nome é ’Smith’ sendo que o

empregado deve sertrabalhador ou gerente do departamento que controla o projeto.



Q4) Listar todos os números de projetos que envolvam umempregado cujo último nome é ’Smith’ sendo que o empregado deve ser

trabalhador ou gerente do departamento que controla o projeto.(SELECT DISTINCT pnumberFROM project, department, employeeWHERE dnum=dnumber AND msgrssn=ssn AND lname=’Smith’)UNION(SELECT DISTINCT pnumberFROM works_on, employeeWHERE essn=ssn AND lname=’Smith’)


SQL/DML – Cláusula LIKE

Q12) Listar o nome de todo empregado cujo endereço está em Houston, Texas



Q12) Listar o nome de todo empregado cujo endereço está em Houston, Texas

SELECT fname, minit, lnameFROM employeeWHERE address LIKE ’%Houston%TX%’


SQL/DML – Like usando underline _

Q12a) Listar o nome de todos os empregados nascidos na década de 50


SQL/DML - Exemplo Q12a

Q12a) Listar o nome de todos os empregados nascidos na década de 50

SELECT fname, minit, lnameFROM employeeWHERE bdate LIKE ’_ _ 5%’


SQL/DML – Expressões

Q13) Listar o nome e os salários resultantes de um aumento de 10% para os funcionários do projeto ’Productx’



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 NewSalaryFROM employee, project, works_onWHERE ssn=essn AND pno=pnumber

AND pname=’ProductX'


SQL/DML – Cláusula BETWEEN

Q14) Listar todos os empregados no departamento 5 cujo salário está entre 30000 e 40000



Q14) Listar todos os empregados no departamento 5 cujo salário está entre 30000 e 40000

SELECT *FROM employeeWHERE dno=’5’

AND salary BETWEEN 30000 AND 40000


SQL/DML – Cláusula ORDER BY

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



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, pnoFROM employee, works onWHERE essn=ssnORDER BY dno, fname, lname


SQL/DML - 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


SQL/DML- Cláusula IN (pertinência) A consulta Q4 abaixo pode ser reformulada, removendo a

cláusula UNION e incluindo a cláusula INQ4) Listar todos os números de projetos que envolvam umempregado 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, employeeWHERE dnum=dnumber AND msgrssn=ssn AND lname=’Smith’)

UNION(SELECT DISTINCT pnumber FROM project, works_on, employeeWHERE pno=pnumber AND essn=ssn AND lname=’Smith’)


SQL/DML Exemplo Q4a

SELECT DISTINCT pnumber FROM projectWHERE pnumber IN

(SELECT pnumber FROM project, department, employeeWHERE dnum=dnumber AND mgrssn=ssn

AND lname=’Smith’)OR pnumber IN(SELECT pno FROM works on,employeeWHERE essn=ssn AND lname=’Smith’);


SQL/DML – Comparando conjuntos com ALL, ANY ou SOME

Outras cláusulas para comparação de conjuntos: ALLANY ou SOME (são sinônimos)

Ex: salary > ALL (SELECT salary FROM ...); salary < ANY (SELECT salary FROM ...); salary > SOME (SELECT salary FROM ...);


SQL/DML – 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



Q16) Listar o nome dos empregados com dependente(s) de mesmo ’first name’ e sexo que o empregado

SELECT e.fname,e.lnameFROM employee as eWHERE e.ssn IN

(SELECT essn FROM dependent as dWHERE fname=dependent_name

AND e.sex=d.sex);


SQL/DML – Substituindo subconsulta por junção

Q16a) Listar o nome dos empregados com dependente(s) de mesmo ’first name’ e sexo que o empregado

SELECT e.fname,e.lnameFROM employee as e, dependent as dWHERE ssn=essn

AND fname=dependent_name AND e.sex=d.sex);


SQL/DML – Q16b - 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.lnameFROM employee as eWHERE EXISTS

(SELECT * FROM dependent dWHERE e.ssn=d.essn AND e.fname=d.dependent_name AND e.sex=d.sex);


SQL/DML– FUNÇÃO NOT EXISTS - consultas aninhadas correlacionadas

Q6) Listar os nomes de empregados sem dependentes



Q6) Listar os nomes de empregados sem dependentes

SELECT fname,lnameFROM employeeWHERE NOT EXISTS (SELECT *

FROM dependent WHERE ssn=essn);


SQL/DML – 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, que será estudada posteriormente. Em SQL a divisão pode ser especificada de diversas formas.


SQL/DML – Exemplo Q3b

Q3b) Listar o nome dos empregados que trabalham em todos os projetos controlados pelo departamento número 4

SELECT lname, fname FROM employeeWHERE NOT EXISTS (SELECT * FROM project WHERE dnum=4

AND NOT EXISTS (SELECT * FROM works_on

WHERE essn=ssn AND pnumber=pno));


SQL/DML – Alternativas para divisão em 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)


SQL/DML - divisão usando cláusulas NOT EXISTS e IN

SELECT lname, fname FROM employeeWHERE 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));


SQL/DML - divisão usando cláusulas NOT EXISTS e EXCEPT

SELECT lname, fname FROM employee eWHERE NOT EXISTS

(SELECT pnumber FROM project WHERE dnum=4EXCEPT (SELECT pno FROM works_on w

WHERE w.essn=e.ssn))


SQL/DML - divisão usando cláusulas FOREACH e EXISTS

SELECT lname, fname FROM employeeWHERE 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


SQL/DML – Tabelas obtidas de junção

Q1a) Listar o nome e endereço dos empregados que trabalham no departamento ’Research’


SQL/DML – Exemplo Q1a

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’


SQL/DML - 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, addressFROM (employee NATURAL JOIN

(department AS dept (dname, dno, mssn, msdate)))

WHERE dname= ’Research’


SQL/DML - 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 e o seu último nome acompanhado do último nome de seu supervisor, memo se o empregado não tiver supervisor, liste seu nome


SQL/DML – Exemplo Q8b

Q8b)Para cada empregado, liste o seu primeiro acompanhado do primeiro nome de seu supervisor, memo 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 JOINemployee AS s ON e.superssn =s.ssn)


SQL/DML – 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


SQL/DML - 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


SQL/DML – Exemplo Q2a

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’


SQL/DML- Funções de agregação

• COUNT• SUM• MAX• MIN• AVG• etc.



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, departmentWHERE dno=dnumber AND dname='Research';


SQL/DML – Exemplos Q21 e Q23

Q21) Listar o número de empregadosSELECT COUNT(*) FROM employee;

Q23) Listar o número de salários distintos

SELECT COUNT(DISTINCT salary) FROM employee;


SQL/DML - Funções de agregação em subconsultas

Q5) Listar o nome dos empregados que têm dois ou mais dependentes

SELECT lname, fnameFROM employeeWHERE (SELECT COUNT(*)

FROM dependentWHERE essn=ssn) >= 2;


SQL/DML - Cláusulas group by

Q24) Listar para cada departamento seu número, a quantidade de empregados e a média salarial de seus empregados.



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, employeeWHERE dno=dnumberGROUP BY dnumber;

OBS: o agrupamento deve incluir todas as colunas da projeção que não incluem função de agregação


SQL/DML – Group by com duas colunas

Q25) Listar para cada projeto seu número, nome e a quantidade de empregados que trabalham no projeto.



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;


SQL/DML - Cláusulas group by e having

Q26) Listar para cada projeto onde trabalham mais de doisempregados seu número e a quantidade de empregados que

trabalham no projeto

SELECT pnumber, pname, COUNT(*) FROM project, works_on WHERE pno=pnumberGROUP BY pnumber, pnameHAVING COUNT (* )> 2;


SQL/DML - 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


SQL/DML – Exemplo Q28Q28) 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 employeeWHERE salary > 40000

AND dno IN(SELECT dnumber FROM department

WHERE(SELECT COUNT(*) FROM employee e2

WHERE e2 .dno=dnumber)>2)GROUP BY dno;


SQL/DML e o PostgreSQLEXERCÍCIOS DE IMPLEMENTAÇÃO

=> Lab2 – Exemplos de consultas simples no esquema company=> Lab3 – Exemplos de consultas complexas no esquema company e exercícios usando o esquema SEE


Bibliografia• [EN] Capítulos 4,5• [SK] Capítulos 3,4,5 • [RG] Capítulos 5


FIM – SQL/DML e o PostgreSQL

FIM – SQL/DML e o PostgreSQL

banco de dados distribuídosilmerio/sbd20132/sbd6sqldml.pdf · ufu/facom página 3 sql/dml def. a...

Documents