A Linguagem SQL e a Álgebra Relacional

1- Álgebra Relacional e SQL- 1

Pedro Sousa, IST/INESC

A Linguagem SQLe a

Álgebra RelacionalA NOTAÇÃO NA ALGEBRA USADA

NESTES SLIDES É DIFERENTE DA DO LIVRO, MAS IGUALMENTE

ÓBVIA



SELECT

• A WHERE c

• A [x,y,....,z]

• A TIMES B

• A UNION B

• A MINUS B

• SELECT * FROM A WHERE C

• SELECT DISTINCT x,y,...,z FROM A

• SELECT a1,..an,b1,…,bn FROM A,B

• SELECT * FROM A UNION SELECT * FROM B

• SELECT * FROM A EXCEPT SELECT * FROM B



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]



JOIN

A={a1,..,an,c1,...,ck}B={b1,..,bm,c1,...,ck}A JOIN B

SELECT DISTINCT a1,..,an, A.c1,..., A.ck, b1,...,bn

FROM A, B WHERE A.c1 = B.c1 AND ... AND A.ck = B.ck

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]



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 )



• Detalhes de todos os projectos– P– SELECT * FROM P

F (F#,Fnome, Nível, Cidade) PJ(J#,Pjnome,Cidade)

P (P#, Pnome,Cor,Peso,Cidade) FPJ(F#,P#,J#,QTD)

1º Exemplo



• 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 ?



2º Exemplo



• 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#



3º Exemplo



• 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´)



4º Exemplo



• 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



5º Exemplo



A sequência “SELECT-FROM-WHERE-GROUPBY-HAVING-

ORDERBY”

SELECT a1,a2,...,anFROM Tx,Ty,...Tz WHERE condições1GROUPBY ai,…,aj HAVING condições2ORDERBY ap,…,aq

T1= Tx TIMES Ty ...TIMES TZ

T2 =T1 WHERE Condições1

T3 =T2 GROUPBY ai,…,aj T4 =

T3 WHERE condições 2

T5 =T4 [a1,a2,...,an](ou EXTEND )

T6 = T5 ordered by ap,…,aq



Agrupar Pora1 a2 a31 a x1 b y 1 b z2 a x 3 e x4 b w4 b x

a1 a2 a3 a x1 b y b z2 a x 3 e x4 b w b x

GROUPBY a1

GROUPBY a1,a2

a1 a2 a31 a x1 b y 1 z2 a x 3 e x4 b w4 b x



Exemplo 1Pessoas que vivem na mesma Cidade

Pessoa CidadeAntónio LisboaJoão Porto Pedro CoimbraZé LisboaRui Coimbra

Vivem

SELECT tx.Pessoa, ty,PessoaFROM Vivem AS Tx, Vivem As TyWHERE Tx.Cidade = Ty.Cidade

Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.CidadeAntónio Lisboa António LisboaAntónio Lisboa João Porto António Lisboa Pedro CoimbraAntónio Lisboa Zé LisboaAntónio Lisboa Rui Coimbra João Porto António LisboaJoão Porto João Porto João Porto Pedro CoimbraJoão Porto Zé LisboaJoão Porto Rui Coimbra…………………...Rui Coimbra António LisboaRui Coimbra João Porto Rui Coimbra Pedro CoimbraRui Coimbra Zé LisboaRui Coimbra Rui Coimbra

T1 = (Vivem AS Tx) TIMES (Vivem As Ty)





T2 = T1 WHERE Tx.Cidade = Ty.Cidade

Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.CidadeAntónio Lisboa António LisboaAntónio Lisboa Zé LisboaPedro Coimbra Pedro Coimbra Pedro Coimbra Rui Coimbra Zé Lisboa António Lisboa Zé Lisboa Zé Lisboa Rui Coimbra Pedro Coimbra Rui Coimbra Rui Coimbra

Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.CidadeAntónio Lisboa António LisboaAntónio Lisboa João Porto António Lisboa Pedro CoimbraAntónio Lisboa Zé LisboaAntónio Lisboa Rui Coimbra João Porto António LisboaJoão Porto João Porto João Porto Pedro CoimbraJoão Porto Zé LisboaJoão Porto Rui Coimbra…………………...Rui Coimbra António LisboaRui Coimbra João Porto Rui Coimbra Pedro CoimbraRui Coimbra Zé LisboaRui Coimbra Rui Coimbra





T5 = T4 = T3 = T2 [tx.Pessoa, ty,Pessoa]

Tx.Pessoa Ty.Pessoa António AntónioAntónio ZéPedro PedroPedro RuiZé AntónioZé ZéRui PedroRui Rui

Tx.Pessoa Tx.Cidade Ty.Pessoa Ty.CidadeAntónio Lisboa António LisboaAntónio Lisboa Zé LisboaPedro Coimbra Pedro Coimbra Pedro Coimbra Rui Coimbra Zé Lisboa António Lisboa Zé Lisboa Zé Lisboa Rui Coimbra Pedro Coimbra Rui Coimbra Rui Coimbra



Exemplo 2Nº de pessoas em cada Cidade, que não seja Porto e que

tenham mais do que um cliente, ordenado por ordem decrescente

Cliente CidadeAntónio LisboaJoão Porto Pedro CoimbraZé LisboaRui CoimbraAdriano Lisboa

Vivem

SELECT Cidade , count (*)FROM VivemWHERE Cidade <> ‘Porto’GROUPBY CidadeHAVING count(*) > 2ORDERBY 2

T1 = Vivem

T2 = Vivem WHERE Cidade <> ‘Porto’

Cliente CidadeAntónio LisboaPedro Coimbra Zé LisboaRui CoimbraAdriano Lisboa



Exemplo 2Nº de pessoas em cada Cidade, que não seja FARO e que


T2

SELECT Cidade , count (*)FROM VivemWHERE Cidade <> ‘Faro’GROUPBY CidadeHAVING count(*) > 2ORDERBY 2

T3 = T2 GROUPBY Cidade

Cliente CidadeAntónio LisboaPedro Coimbra Zé LisboaRui CoimbraAdriano Lisboa

Cliente CidadeAntónio LisboaZéAdrianoPedro Coimbra Rui

T4 = T3 WHERE count(*) > 2

Cliente CidadeAntónio LisboaZéAdriano



Exemplo 2Nº de pessoas em cada Cidade, que não seja FARO e que


SELECT Cidade , count (*)FROM VivemWHERE Cidade <> ‘Faro’GROUPBY CidadeHAVING count(*) > 2ORDERBY 2

T5 = T4 [Cidade], EXTEND count(*)

Cidade, count(*)Lisboa 3

Cliente CidadeAntónio LisboaZéAdriano

T4

T6 = T5 ORDERED BY count(*)

Cidade, count(*)Lisboa 3

Documents

A Linguagem SQL e a Álgebra Relacional