1- Álgebra Relacional e SQL- 1
A Linguagem SQL
ea
Álgebra Relacional
A NOTAÇÃO NA ALGEBRA USADA
NESTES SLIDES É DIFERENTE DA
DO LIVRO, MAS IGUALMENTE
ÓBVIA
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 2
SELECT
• A WHERE c
• SELECT * FROM A WHERE C
• A [x,y,....,z]
• SELECT DISTINCT x,y,...,z FROM A
• A TIMES B
• SELECT a1,..an,b1,…,bn FROM A,B
• A UNION B
• SELECT * FROM A UNION
SELECT * FROM B
• SELECT * FROM A EXCEPT
SELECT * FROM B
• A MINUS B
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 3
A sequência
“SELECT-FROM-WHERE”
De uma forma geral, a sequência:
SELECT DISTINCT c1,c2,...,cn
FROM T1,T2,...Tn
WHERE condições
é equivalente à seguinte expressão algébrica:
((T1 TIMES T2 ... TIMES Tn)
WHERE condições) [c1,c2,...,cn]
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 4
JOIN
A={a1,..,an,c1,...,ck}
B={b1,..,bm,c1,...,ck}
A JOIN B
A={a1,..,an,c1,...,ck}
B={b1,..,bm,c1,...,ck}
(A TIMES B WHERE A.c1 = B.c1 AND ... AND
A.ck = B.ck) [A.*, b1,...,bm]
SELECT DISTINCT a1,..,an, A.c1,..., A.ck, b1,...,bn
FROM A, B
WHERE A.c1 = B.c1 AND ... AND A.ck = B.ck
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 5
DIVIDEBY
A={a1,..,an,c1,...,ck}
B={c1,...,ck}
A DIVIDEBY B
SELECT a1,..,an FROM A, B
WHERE NOT EXISTS (
SELECT c1,...,ck FROM A, B WHERE
A.c1 =/= B.c1 OR ... OR A.ck =/= B.ck )
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 6
1º Exemplo
F (F#,Fnome, Nível, Cidade)
P (P#, Pnome,Cor,Peso,Cidade)
PJ(J#,Pjnome,Cidade)
FPJ(F#,P#,J#,QTD)
• Detalhes de todos os projectos
–P
– SELECT * FROM P
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 7
2º Exemplo
F (F#,Fnome, Nível, Cidade)
P (P#, Pnome,Cor,Peso,Cidade)
PJ(J#,Pjnome,Cidade)
FPJ(F#,P#,J#,QTD)
• Nome dos fornecedores que fornecem peça P1
– ((F JOIN FPJ) WHERE P# = P1) [Fnome]
– SELECT DISTINCT Fnome FROM F, FPJ
WHERE P# = P1 AND F.F# =FPJ.F#
Porquê o JOIN em vez do TIMES ?
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 8
3º Exemplo
F (F#,Fnome, Nível, Cidade)
P (P#, Pnome,Cor,Peso,Cidade)
PJ(J#,Pjnome,Cidade)
FPJ(F#,P#,J#,QTD)
• Números das Peças fornecidas por fornecedores de
Londres
– ((F JOIN FPJ) WHERE “cidade=Londres”) [P#]
– SELECT DISTINCT P# FROM F, PJ
WHERE “Cidade = Londres” AND
F.F# = JFP.F#
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 9
4º Exemplo
F (F#,Fnome, Nível, Cidade)
P (P#, Pnome,Cor,Peso,Cidade)
PJ(J#,Pjnome,Cidade)
FPJ(F#,P#,J#,QTD)
• Nomes de fornecedores que não fornecem a peça P2.
– SELECT Fnome FROM F
WHERE NOT EXISTS
(SELECT * FROM FPJ
WHERE F.F# = FPJ.F# AND SPJ.F# = ´P2´)
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 10
5º Exemplo
F (F#,Fnome, Nível, Cidade)
P (P#, Pnome,Cor,Peso,Cidade)
PJ(J#,Pjnome,Cidade)
FPJ(F#,P#,J#,QTD)
• Nomes de fornecedores que forneceram todas as peças.
– (( FPJ[F#,P#] DIVIDEBY P[P#]) JOIN F ) [Fnome]
– SELECT Fnome FROM F, FPJ WHERE NOT
EXISTS
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 11
A sequência
“SELECT-FROM-WHERE-GROUPBY-HAVINGORDERBY”
T5 =
T4 [a1,a2,...,an]
(ou EXTEND )
SELECT a1,a2,...,an
FROM Tx,Ty,...Tz
WHERE condições1
GROUPBY ai,…,aj
HAVING condições2
ORDERBY ap,…,aq
T1=
Tx TIMES Ty ...TIMES TZ
T2 =
T1 WHERE Condições1
T4 =
T3 WHERE condições 2
T3 =
T2 GROUPBY ai,…,aj
T6 =
T5 ordered by ap,…,aq
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 12
Agrupar Por
a1
1
1
1
2
3
4
4
a2
a
b
b
a
e
b
b
a3
x
y
z
x
x
w
x
GROUPBY a1
a1 a2
a
1 b
b
2 a
3 e
4 b
b
a3
x
y
z
x
x
w
x
GROUPBY a1,a2
a1
1
1
1
2
3
4
4
a2 a3
a x
b y
z
a x
e x
b w
b x
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 13
Exemplo 1
Pessoas que vivem na mesma Cidade
Vivem
Pessoa Cidade
António Lisboa
João
Porto
Pedro
Coimbra
Zé
Lisboa
Rui
Coimbra
SELECT tx.Pessoa, ty,Pessoa
FROM Vivem AS Tx, Vivem As Ty
WHERE Tx.Cidade = Ty.Cidade
T1 = (Vivem AS Tx) TIMES (Vivem As Ty)
Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.Cidade
António Lisboa
António Lisboa
António Lisboa
João
Porto
António Lisboa
Pedro
Coimbra
António Lisboa
Zé
Lisboa
António Lisboa
Rui
Coimbra
João
Porto
António Lisboa
João
Porto
João
Porto
João
Porto
Pedro
Coimbra
João
Porto
Zé
Lisboa
João
Porto
Rui
Coimbra
…………
………...
Rui
Coimbra
António Lisboa
Rui
Coimbra
João
Porto
Rui
Coimbra
Pedro
Coimbra
Rui
Coimbra
Zé
Lisboa
Rui
Coimbra
Rui
Coimbra
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 14
Exemplo 1
Pessoas que vivem na mesma Cidade
Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.Cidade
António Lisboa
António Lisboa
António Lisboa
João
Porto
António Lisboa
Pedro
Coimbra
António Lisboa
Zé
Lisboa
António Lisboa
Rui
Coimbra
João
Porto
António Lisboa
João
Porto
João
Porto
João
Porto
Pedro
Coimbra
João
Porto
Zé
Lisboa
João
Porto
Rui
Coimbra
…………
………...
Rui
Coimbra
António Lisboa
Rui
Coimbra
João
Porto
Rui
Coimbra
Pedro
Coimbra
Rui
Coimbra
Zé
Lisboa
Rui
Coimbra
Rui
Coimbra
SELECT tx.Pessoa, ty,Pessoa
FROM Vivem AS Tx, Vivem As Ty
WHERE Tx.Cidade = Ty.Cidade
T2 = T1 WHERE Tx.Cidade = Ty.Cidade
Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.Cidade
António Lisboa
António Lisboa
António Lisboa
Zé
Lisboa
Pedro
Coimbra
Pedro
Coimbra
Pedro
Coimbra
Rui
Coimbra
Zé
Lisboa
António Lisboa
Zé
Lisboa
Zé
Lisboa
Rui
Coimbra
Pedro
Coimbra
Rui
Coimbra
Rui
Coimbra
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 15
Exemplo 1
Pessoas que vivem na mesma Cidade
Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.Cidade
António Lisboa
António Lisboa
António Lisboa
Zé
Lisboa
Pedro
Coimbra
Pedro
Coimbra
Pedro
Coimbra
Rui
Coimbra
Zé
Lisboa
António Lisboa
Zé
Lisboa
Zé
Lisboa
Rui
Coimbra
Pedro
Coimbra
Rui
Coimbra
Rui
Coimbra
SELECT tx.Pessoa, ty,Pessoa
FROM Vivem AS Tx, Vivem As Ty
WHERE Tx.Cidade = Ty.Cidade
T5 = T4 = T3 =
T2 [tx.Pessoa, ty,Pessoa]
Tx.Pessoa Ty.Pessoa
António António
António Zé
Pedro
Pedro
Pedro
Rui
Zé
António
Zé
Zé
Rui
Pedro
Rui
Rui
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 16
Vivem
Exemplo 2
Nº de pessoas em cada Cidade, que não seja Porto e que
tenham mais do que um cliente, ordenado por ordem
decrescente
Cliente Cidade
António Lisboa
João
Porto
Pedro
Coimbra
Zé
Lisboa
Rui
Coimbra
Adriano Lisboa
SELECT Cidade , count (*)
FROM Vivem
WHERE Cidade <> ‘Porto’
GROUPBY Cidade
HAVING count(*) > 2
ORDERBY 2
T1 = Vivem
T2 = Vivem WHERE Cidade <> ‘Porto’
Cliente Cidade
António Lisboa
Pedro
Coimbra
Zé
Lisboa
Rui
Coimbra
Adriano Lisboa
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 17
T2
Exemplo 2
Nº de pessoas em cada Cidade, que não seja FARO e que
tenham mais do que um cliente, ordenado por ordem
decrescente
Cliente Cidade
António Lisboa
Pedro
Coimbra
Zé
Lisboa
Rui
Coimbra
Adriano Lisboa
T3 = T2 GROUPBY Cidade
Cliente Cidade
António Lisboa
Zé
Adriano
Pedro
Coimbra
Rui
T4 = T3 WHERE count(*) > 2
SELECT Cidade , count (*)
FROM Vivem
WHERE Cidade <> ‘Faro’
GROUPBY Cidade
HAVING count(*) > 2
ORDERBY 2
Cliente Cidade
António Lisboa
Zé
Adriano
Pedro Sousa, IST/INESC
1- Álgebra Relacional e SQL- 18
T4
Exemplo 2
Nº de pessoas em cada Cidade, que não seja FARO e que
tenham mais do que um cliente, ordenado por ordem
decrescente
Cliente Cidade
António Lisboa
Zé
Adriano
T5 = T4 [Cidade], EXTEND count(*)
Cidade, count(*)
Lisboa
3
T6 = T5 ORDERED BY count(*)
SELECT Cidade , count (*)
FROM Vivem
WHERE Cidade <> ‘Faro’
GROUPBY Cidade
HAVING count(*) > 2
ORDERBY 2
Cidade, count(*)
Lisboa
3
Pedro Sousa, IST/INESC