DCC011 Introdução a Banco de Dados SQL selectmirella/DCC011/aula14.pdf · Consultas Básicas em SQL C. SELECT FROM sem o WHERE SELECT ssn, lname, salary FROM EMPLOYEE; SELECT lname,

DCC011Introdução a Banco de Dados

SQL selectSQL selectMirella M. Moro

Departamento de Ciência da Computação

Universidade Federal de Minas Gerais

[email protected]

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.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

2DCC011 - profa. Mirella M. Moro

SQL: ConsultasSQL: Consultas

0. Exemplos

1. Consultas Básicas

2. Facilidades Adicionais

3. Consultas Complexas

Problema

• Dado um conjunto de tabelas e seus dados

• Como recuperar os dados?

• Como realizar consultas a

Matr Nome Sexo Cr

1 A F CC

2 B M CC

3 C M CC

4 D F MC

5 E M MC

ALUNOS

• Como realizar consultas a esses dados?

• “Me diz aí os nomes das alunas do curso de Ciência da Computação ordenados alfabeticamente”

5 E M MC

6 F M SI

7 G F SI

8 H F SI

9 I M SI

10 J M ECA


SQL select-from

1. SELECT Matr

FROM ALUNOS;

2. SELECT Cr

FROM ALUNOS;

3. SELECT Matr, Cr

Matr Nome Sexo Cr

1 A F CC

2 B M CC

3 C M CC

4 D F MC

5 E M MC

ALUNOS

3. SELECT Matr, Cr

FROM ALUNOS;

4. SELECT Matr, Nome, Sexo, Cr

FROM ALUNOS;

5. SELECT *

FROM ALUNOS;


5 E M MC

6 F M SI

7 G F SI

8 H F SI

9 I M SI

10 J M ECA

SQL select-from-where

1. SELECT Matr

FROM ALUNOS

WHERE Sexo = ‘F’;

2. SELECT Nome

FROM ALUNOSFROM ALUNOS

WHERE Sexo = ‘F’

AND Cr = ‘SI’;

3. SELECT Matr

FROM ALUNOS

WHERE Sexo = ‘F’

AND Cr <> “CC”;6DCC011 - profa. Mirella M. Moro

Solução: SQL select-from-where

“Me diz aí os nomes das alunas do curso de Ciência da Computação ordenados alfabeticamente”

SELECT Nome


SELECT NomeFROM ALUNOSWHERE Sexo = ‘F’

AND Cr = ‘CC’ORDER BY Nome;

Mais Problemas

1. Nomes de todos os alunos acompanhados dos nomes de seus cursos

2. Nomes de todos os alunos acompanhados

Matr Nome Sexo Cr

1 A F CC

2 B M CC

3 C M CC

4 D F MC

5 E M MC

ALUNOS

alunos acompanhados das disciplinas matriculadas em 20162


5 E M MC

6 F M SI

7 G F SI

8 H F SI

9 I M SI

10 J M ECA

ALUNOS

Matr Nome Sexo Cr

1 A F CC

2 B M CC

3 C M CC

4 D F MC

5 E M MC

Cr Nome Depto Coord

CCCiência da

Computação DCC RG

MCMatemática

Computacional DCC TN

SISistemas de Informação DCC CDJ

ECAEngenharia de

Controle e Automação

ENG XYZ

CURSOS

MATRICULAS5 E M MC

6 F M SI

7 G F SI

8 H F SI

9 I M SI

10 J M ECA

Matr Disc T Sem

1 DCC011 Z 20162

1 DCC851 A 20162

1 DCC834 A 20161

2 DCC011 Z 20161

... ... ... ...

MATRICULAS


Em formato textoALUNOS (Matr, Nome, Sexo, Cr)

Cr referencia CURSOS

CURSOS (Cr, Nome, Depto, Coord)

Depto referencia DEPARTAMENTOS

Coord referencia PROFESSORES

MATRICULAS (Matr, Disc, T, Sem)MATRICULAS (Matr, Disc, T, Sem)

Matr referencia ALUNOS

Disc referencia DISCIPLINAS

DEPARTAMENTOS (...)

PROFESSORES (...)

DISCIPLINAS (...)


Mais Problemas

1. Nomes de todos os alunos acompanhados dos nomes de seus cursos


Mais Problemas1. Nomes de todos os alunos acompanhados

dos nomes de seus cursos

SOLUÇÃO A

SELECT Nome, Nome

FROM ALUNOS, CURSOS;

SOLUÇÃO B

SELECT ALUNOS.Nome, CURSOS.NomeSELECT ALUNOS.Nome, CURSOS.Nome

FROM ALUNOS, CURSOS;

SOLUÇÃO C

SELECT A.Nome, C.Nome

FROM ALUNOS as A, CURSOS as C;

SOLUÇÃO D

SELECT A.Nome, C.Nome

FROM ALUNOS A, CURSOS C

WHERE A.Cr = C.Cr;


Mais Problemas

2. Nomes de todos os alunos acompanhados das disciplinas matriculadas em 20162


Mais Problemas

2. Nomes de todos os alunos acompanhados das disciplinas matriculadas em 20162

SOLUÇÃO ASELECT Nome, DiscFROM ALUNOS, MATRICULAS;

SOLUÇÃO BSOLUÇÃO BSELECT Nome, DiscFROM ALUNOS, MATRICULASWHERE Sem=“20162”;

SOLUÇÃO CSELECT Nome, DiscFROM ALUNOS, MATRICULASWHERE Sem=“20162” ANDALUNOS.Matr = MATRICULAS.Matr;


Organizando as Ideias

1. Consultas Básicas em SQL

• Formato básico do comando SELECT:

SELECT <lista de atributos>FROM <lista de tabelas>[ WHERE <condição>; ]

• Exemplo:

EM PORTUGUÊS

EMPREGADO (ssn, pnome, minicial, unome,

datanasc, endereco, sexo, salario, superssn, dno)

superssn REFERENCIA EMPREGADO

dno REFERENCIA DEPARTAMENTO• Exemplo:

SELECT datanast, enderecoFROM EMPREGADOWHERE pnome=‘John’ AND

minicial=‘B’ ANDunome=‘Smith’;

π Bdate,AddressσFname=‘John’ AND Minit=‘B’ AND Lname=‘Smith’ (EMPLOYEE)

dno REFERENCIA DEPARTAMENTO

DEPARTAMENTO (dnumero, dnome, gerssn,

gerdatainicio)

gerssn REFERENCIA EMPREGADO.ssn


Consultas Básicas em SQLB. Atributos Ambíguos e Pseudônimos (alias)

SELECT dname, dlocationFROM DEPARTMENT, DEPT_LOCATIONSWHERE DEPARTMENT.dnumber =

DEPT_LOCATIONS.dnumber;

SELECT e.fname, e.lname, s.fname, s.lnameFROM EMPLOYEE AS E, EMPLOYEE AS SFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE e.superssn=s.ssn;

EM INGLÊSEMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)

superssn REFERENCIA EMPLOYEEdno REFERENCIA DEPARTMENT

DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)mgrssn REFERENCIA EMPLOYEE.ssn

PROJECT (pnumber, pname, plocation, dnum)dnum REFERENCIA DEPARTAMENT

DEPT_LOCATIONS (dnumber,dlocation)dnumber REFERENCIA DEPARTAMENT

(Q8)


Consultas Básicas em SQLB. Atributos Ambíguos e Pseudônimos (alias)

• SELECT pnumber, dnum, lname, address, bdate FROM PROJECT P, DEPARTMENT D, EMPLOYEE EWHERE plocation=‘Stafford’ AND

D.dnum=P.dnum AND D.mgrssn=E.ssn;

condição de seleção

(Q2)

π pnumber,dnum,lname,address,bdate σplocation=‘Stafford’

(EMPLOYEE ⋈ ⋈ ⋈ ⋈ ssn=mgrssn (DEPARTMENT ⋈⋈⋈⋈ PROJECT))

condição de junção

EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)superssn REFERENCIA EMPLOYEEdno REFERENCIA DEPARTMENT

DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)mgrssn REFERENCIA EMPLOYEE.ssn PROJECT (pnumber, pname, plocation, dnum)

dnum REFERENCIA DEPARTAMENT 18DCC011 - profa. Mirella M. Moro

Consultas Básicas em SQLC. SELECT FROM sem o WHERE

SELECT ssn, lname, salaryFROM EMPLOYEE;

SELECT lname, dnameFROM EMPLOYEE, DEPARTMENT

(Q10)

Atenção! A consulta em vermelho corresponde a um produto cartesiano das tabelas EMPLOYEE e DEPARTMENT :

π lname,dname (EMPLOYEE x DEPARTMENT)

WHERE dno=dnumber; �� com a junção




Consultas Básicas em SQLD. TODOS OS ATRIBUTOS

• Consultas a todos os atributos

SELECT *FROM EMPLOYEEWHERE Dno=5;

SELECT *

(Q1C)

SELECT *FROM EMPLOYEE, DEPARTMENTWHERE Dname=‘Research’ AND Dno=Dnumber;



(Q1D)


Consultas Básicas em SQLE. 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)• 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, : EXCEPT, ∩: INTERSECT


Tabelas como Conjuntos

SELECT salaryFROM EMPLOYEE;

SELECT DISTINCT salaryFROM EMPLOYEE;

Não elimina linhas (tuplas) duplicatasPara eliminar precisa usar DISTINCT, por exemplo:

(Q11)



PROJECT (pnumber, pname, plocation, dnum)dnum REFERENCIA DEPARTAMENT

FROM EMPLOYEE;

(SELECT pnumberFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE dnum=dnumber AND mgrssn=ssn AND lname=‘Smith’)UNION(SELECT pnumberFROM PROJECT, WORKS_ON, EMPLOYEEWHERE pnumber=pno AND essn=ssn AND

lname=‘Smith’);

(Q4)


2. Facilidades AdicionaisA. JOINS

• Uso do operador JOIN, na cláusula FROM

• SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE JOIN DEPARTMENT

ON DNO=DNUMEBR)

WHERE DNAME=‘Research’;WHERE DNAME=‘Research’;

• A cláusula FROM contém então uma única tabela resultante da junção de Empregado e Departamento

EMPLOYEE(ssn, fname, lname, address,bdate, superssn, dno)

superssn REFERENCIA EMPLOYEE

dno REFERENCIA DEPARTMENT

DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)

mgrssn REFERENCIA EMPLOYEE.ssn 23DCC011 - profa. Mirella M. Moro

Facilidades AdicionaisA. 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• SELECT DNAME, DLOCATION

FROM (DEPARTMENT NATURAL JOIN

DEPT_LOCATIONS);

DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)

mgrssn REFERENCIA EMPLOYEE.ssn

DEPT_LOCATIONS (dnum, dlocal)

dnum REFERENCIA DEPARTAMENT



Renomeando atributos para o natural join:

• SELECT pnome, unome, dnome

FROM (EMPREGADO NATURAL JOIN

(DEPARTMENTO AS DEPT (dno, dnome, (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 EMPREGADO.ssn



• SELECT fname, lname, dependent_name

FROM (EMPLOYEE JOIN DEPENDENT ON ssn=essn);

• [INNER] JOIN..ON: pares empregado/dependente

• OUTER JOIN..ON: pares + empregados que não têmdependentedependente

� [NATURAL] LEFT/RIGHT/FULL OUTER JOIN .. ON

• SELECT fname, lname, dependent_name

FROM (EMPLOYEE LEFT OUTER JOIN

DEPENDENT ON ssn=essn);


Outer Join

INSERE dois cursos {ENF,MED}

Matr Cr

1 CC

2 CC

3 CC

4 MC

5 MC

6 SIINSERE dois cursos {ENF,MED}

• select A.Matr, C.Cr from ALUNOS A

RIGHT OUTER JOIN CURSOS C

ON A.Cr=C.Cr

• SE a única coluna em comum fosse Cr:select A.Matr, C.Cr from ALUNOS A

NATURAL RIGHT OUTER JOIN CURSOS C

DCC011 - profa. Mirella M. Moro 27

6 SI

7 SI

8 SI

9 SI

10 ECA

null ENF

null MED

Cuidado com o número de colunas do

resultado quando se realiza

junção natural

NATURAL JOIN

NATURAL LEFT OUTER JOIN

NATURAL RIGHT OUTER JOIN

A palavra OUTER é opcional


PraticandoSQL

Item(Cod_Item, Descricao, Preco_Unit)Pedido(Num_Pedido, Data, Cod_Cliente, Valor_Total),

Cod_Cliente --> ClienteItem_Pedido(Num_Pedido, Cod_Item, Quantidade)

Num_Pedido --> Pedido, Cod_Item --> Item

SQL #Colunas #Linhas

select * from Item, Item_Pedido 6 234

select * from Item I, Item_Pedido IPwhere I.Cod_Item=IP.Cod_Item

6 18


select * from Item I, Item_Pedido IPwhere I.Cod_Item=IP.Cod_Item

6 18

select * from Item NATURAL JOIN Item_Pedido 5 18

select * from Item I JOIN Item_Pedido IPON I.Cod_Item=IP.Cod_Item

6 18

select * from (Item I LEFT OUTER JOIN Item_Pedido IP ON I.Cod_Item=IP.Cod_Item)

6 19

select * from (Item NATURAL LEFT OUTER JOIN Item_Pedido) 5 19

?

A B BA

BAA B

A B

SQL JOINSSELECT …FROM A LEFT JOIN BON A.key = B.key

SELECT …FROM A RIGHT JOIN BON A.key = B.key

SELECT …FROM A INNER JOIN B

A B A B

SELECT …FROM A LEFT JOIN BON A.key = B.keyWHERE B.key IS NULL

SELECT …FROM A RIGHT JOIN BON A.key = B.keyWHERE A.key IS NULL

FROM A INNER JOIN BON A.key = B.key

SELECT …FROM A FULL OUTER JOIN BON A.key = B.key

SELECT …FROM A FULL OUTER JOIN BON A.key = B.keyWHERE A.key IS NULL

OR B.key IS NULL


Facilidades AdicionaisB. FUNÇÕES DE AGREGAÇÃO

• Funções de agregação: COUNT, SUM, MAX, MIN, AVG

• Sempre aparecem na cláusula SELECT ou HAVING

• Ou seja, não existe “where max(salario)=5000”

• COUNT, duas possibilidades• count(*) : retorna quantas linhas existe no resultado da consulta

• count(coluna): retorna quantas linhas possuem valores válidos para coluna

• SUM(coluna): retorna a soma de todos os valores válidos para a coluna especificada

• AVG(coluna): retorna o valor médio de todos os valores válidos para a coluna especificada

• MAX(coluna) e MIN(coluna): retornam os valores máximo e mínimo (respectivamente) da coluna especificada

• NULL não conta como mínimo, pois não é valor válido


Exemplos funções agregação

• select count(*) from ALUNOS;

• select count(*) as Qtde

from ALUNOS

where sexo = ‘F’;

• select max(Matr) as max,

count(*)

10

Qtde

4

• select max(Matr) as max,

min(Matr) as min

from ALUNOS;

• select sum(Matr) as sum,

avg(Matr) as avg

from ALUNOS

where sexo = ‘F’;DCC011 - profa. Mirella M. Moro 32

max min

10 1

sum avg

20 5

FUNÇÕES DE AGREGAÇÃO – exemplos do livro

• SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)FROM EMPLOYEE;

• SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER AND DNAME=‘Research’;

• SELECT COUNT(*)FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER AND DNAME=‘Research’;


Exercíciospodem ser executados no site www.lbd.dcc.ufmg.br/praticandosqlselecionar o banco de dados COMPANY

DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) mgrssn é o gerente

MGRSSN referencia EMPLOYEE

DEPENDENT(ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP)

ESSN referencia EMPLOYEE

DEPT_LOCATIONS(DNUMBER, DLOCATION)

DNUMBER referencia DEPARTMENT

EMPLOYEE(FNAME, MINIT, LNAME,SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)

SUPERSSN referencia EMPLOYEE; DNO referencia DEPARTMENT

PROJECT(PNAME, PNUMBER, PLOCATION, DNUM)

DNUM referencia DEPARTMENT

WORKS_ON(ESSN, PNO, HOURS) ESSN referencia EMPLOYEE ; PNO referencia PROJECT

selecionar o banco de dados COMPANY1. Retorne o SSN dos empregados:

a. que trabalham no departamento 4;

b. que trabalham no departamento 4 ou 5;

c. que trabalham no departamento gerenciado pelo empregado 987654321;

d. que trabalham mais de 9 horas no projeto 10;

2. Retorne o total de horas que o empregado 333445555 trabalha em projetos;

3. Retorne o máximo, mínimo e média de horas que os empregados trabalham no projeto 30;

4. Retorne todos os projetos (incluindo os sem empregados) seguidos do ssn dos seus empregados;


SSN dos empregados:

a. que trabalham no departamento 4;

b. que trabalham no departamento 4 ou 5;departamento 4 ou 5;


SSN dos empregados:

c. que trabalham no departamentogerenciado peloempregado 987654321;

d. que trabalham mais de 9 horas no projeto 10;


2. Retorne o total de horasque o empregado333445555 trabalhaem projetos;

3. Retorne o máximo, mínimo e média de horas que osempregadostrabalham no projeto30;

4. Retorne todos osprojetos (incluindo ossem empregados) seguidos do ssn dos seus empregados;


Exercícios: BD Universidade-simplificadoALUNOS (Matr, Nome, Sexo, Cr)

Cr referencia CURSOSCURSOS (Cr, Nome, Depto, Coord)

Depto referencia DEPARTAMENTOSCoord referencia PROFESSORES

MATRICULAS (Matr, Disc, T, Sem)Matr referencia ALUNOSDisc referencia DISCIPLINASDisc referencia DISCIPLINAS

DEPARTAMENTOS (Dep, NomeDep, Localização, Chefe)Chefe referencia PROFESSORES

PROFS (CPF, NomeProf, DataAdm, Nível)DISCIP (Disc, NomeDisc, Creditos, CH)PROF-DISC (CPFprof, Disc, T, Sem)

CPFprof referencia PROFSDisc referencia DISCIP


Escrever o comando SQL para as consultas 1. Obter todos os dados de Disciplinas

2. Obter os nomes das Disciplinas e o respectivo número de créditos

3. Obter os nomes das Disciplinas para as quais existem alunos matriculados SEM utilizar o operador de junção

4. Refazer a consulta 3 utilizando o operador de junção

5. Obter os nomes dos professores e os nomes das disciplinas que ensinam

6. Obter os nomes dos professores e os nomes das disciplinas que 6. Obter os nomes dos professores e os nomes das disciplinas que ensinam no semestre atual

7. Obter os nomes dos professores e os nomes das disciplinas que ensinam no semestre atual e no anterior

8. Obter os nomes dos professores que são chefes de departamento e as disciplinas que ensinam no semestre atual

9. Obter os nomes cursos do Departamento DCC ordenados alfabeticamente

10. Obter os nomes cursos que começam com EngenhariaDCC011 - profa. Mirella M. Moro 39

Documents

DCC011 Introdução a Banco de Dados SQL selectmirella/DCC011/aula14.pdf · Consultas Básicas em SQL C. SELECT FROM sem o WHERE SELECT ssn, lname, salary FROM EMPLOYEE; SELECT lname,