1 SQL: Aula 3. 2 Roteiro cláusulas order by e group by Outer Joins e valores null Criação de...

1

SQL: Aula 3

2

Roteiro

• cláusulas order by e group by

• Outer Joins e valores null

• Criação de esquemas

• Modificação da base de dados

• Definição de Visões

3

Ordenação dos Resultados

SELECT categoryFROM ProductORDER BY pname

HitachiHousehold$203.99MultiTouch

CanonPhotography$149.99SingleTouch

GizmoWorksGadgets$29.99Powergizmo

GizmoWorksGadgets$19.99Gizmo

ManufacturerCategoryPricePName

?

4

Ordernação dos Resultados

SELECT DISTINCT categoryFROM ProductORDER BY category

Compare to:

Photography

Household

Gadgets

Category

SELECT DISTINCT categoryFROM ProductORDER BY pname ?

5

Agregação

Author(login,name)

Document(url, title)

Wrote(login,url)

Mentions(url,word)

6

Author(login,name)Wrote(login,url)

• Encontre os autores que escreveram pelo menos 10 documentos:

• Tentativa 1: com consulta aninhada

SELECT DISTINCT Author.nameFROM AuthorWHERE count(SELECT Wrote.url FROM Wrote WHERE Author.login=Wrote.login) > 10

Consulta escrita porum novato

7

Author(login,name)Wrote(login,url)

• Encontre os autores que escreveram pelo menos 10 documentos:

• Tentativa 2: com GROUP BY

SELECT Author.nameFROM Author, WroteWHERE Author.login=Wrote.loginGROUP BY Author.nameHAVING count(wrote.url) > 10

Escrita porum expert

Não há necessidade de DISTINCT: automático devido oGROUP BY

8

Author(login,name)Wrote(login,url)Mentions(url,word)

• Encontre os autores que tem um vocabulário acima de 10000 palavras:

SELECT Author.nameFROM Author, Wrote, MentionsWHERE Author.login=Wrote.login AND Wrote.url=Mentions.urlGROUP BY Author.nameHAVING count(distinct Mentions.word) > 10000

Preste atenção nas duas últimas consultas: embora elas possamser escritas utilizando consultas aninhadas, é melhor utilizarGROUP BY.

9

Valores Null

• Se x=Null então 4*(3-x)/7 resulta em Null

• Se x=Null então o teste x=“Joe” resulta em UNKNOWN

• Em SQL há 3 valores booleanos:FALSE = 0

UNKNOWN = 0.5

TRUE = 1

10

Valores Null

• C1 AND C2 = min(C1, C2)• C1 OR C2 = max(C1, C2)• NOT C1 = 1 – C1

Regra em SQL: inclui somente tuplas que resultam em TRUE

SELECT *FROM PersonWHERE (age < 25) AND (height > 6 OR weight > 190)

P.ex.age=20height=NULLweight=200

11

Valores Null

Comportamento não esperado:

Algumas pessoas não são incluídas no resultado !

SELECT *FROM PersonWHERE age < 25 OR age >= 25

12

Valores Null

É possível testar se um valor é nulo explicitamente:– x IS NULL– x IS NOT NULL

Agora o resultado contém todas as pessoas.

SELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL

13

Valores Null em Junções Product(name, category) Purchase(prodName, store)

O mesmo que:

Mas produtos que nunca foram vendidos não aparecem no resultado !

SELECT Product.name, Purchase.storeFROM Product JOIN Purchase ON Product.name = Purchase.prodName

SELECT Product.name, Purchase.storeFROM Product, PurchaseWHERE Product.name = Purchase.prodName

14

Valores Null e Outerjoins

Left outer joins em SQL:Product(name, category)

Purchase(prodName, store)

SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName

15

PhotoOneClick

PhotoCamera

gadgetGizmo

CategoryName

WizCamera

RitzCamera

WizGizmo

StoreProdName

NULLOneClick

WizCamera

RitzCamera

WizGizmo

StoreName

Product Purchase

16

Outer Joins

• Left outer join:– sempre inclui a tupla da esquerda

• Right outer join:– sempre inclui a tupla da direita

• Full outer join:– sempre inclui tanto a tupla da esquera como da

direita

17

Modificação da Base de Dados

Três tipos de modificações

• Inserções

• Deleções

• Atualizações

Frequentemente as três operações são chamadas de “atualizações”

18

InserçõesForma geral:

Atributo não existente NULL.Pode omitir os nomes dos atributos se especificados em ordem.

INSERT INTO R(A1,…., An) VALUES (v1,…., vn)

INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’)

Exemplo: Inserir uma nova compra na base de dados:

19

Inserções

INSERT INTO PRODUCT(name)

SELECT DISTINCT Purchase.product FROM Purchase WHERE Purchase.date > “10/26/01”

Uma consulta no lugar da palavra-chave VALUES.Várias tuplas são inseridas em PRODUCT

20

Inserção: um Exemplo

prodName é chave estrangeira em Product.name

Suponha que a base está inconsistente e precisamos consertá-la.

gadgets100gizmo

categorylistPricename

225Smithcamera

80Smithgizmo

200Johncamera

pricebuyerNameprodName

Tarefa: inserir em Product todos os prodNames em Purchase

Product

Product(name, listPrice, category)Purchase(prodName, buyerName, price)

Purchase

21

Inserção: um Exemplo

INSERT INTO Product(name)

SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)

--camera

Gadgets100gizmo

categorylistPricename

22

Inserção: um Exemplo

INSERT INTO Product(name, listPrice)

SELECT DISTINCT prodName, price FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)

-225 ??camera ??

-200camera

Gadgets100gizmo

categorylistPricename

Depende da implementação

23

Deleções

DELETE FROM PURCHASE

WHERE seller = ‘Joe’ AND product = ‘Brooklyn Bridge’

Exemplo:

24

Atualizações

UPDATE PRODUCTSET price = price/2WHERE Product.name IN (SELECT product FROM Purchase WHERE Date =‘Oct, 25, 1999’);

Exemplo:

25

Definição de Dados em SQLAté agora nós vimos a DML (Data Manipulation Language)Em seguida: DDL (Data Definition Language)

Tipos de dados: Define os tipos.

Definição dos dados: define o esquema

• Criação de tabelas• Remoção de tabelas• Modificação do esquema

Indexes: para melhorar o desempenho

26

Tipos de Dados em SQL

• Strings: – CHAR(20) -- fixed length– VARCHAR(40) -- variable length

• Numéricos:– INT, REAL plus variations

• Data / Hora: – DATE, DATETIME

• Para criar tipos:CREATE DOMAIN address AS VARCHAR(55)

27

Criação de Tabelas

CREATE TABLE Person(

name VARCHAR(30), social-security-number INT, age SHORTINT, city VARCHAR(30), gender BIT(1), Birthdate DATE

);

Exemplo:

28

Remoção ou Modificação de uma Tabela

Remoção:

ALTER TABLE Person ADD phone CHAR(16);

ALTER TABLE Person DROP age;

Alteração: (adição ou remoção de atributo)

O que acontece quando o esquema é alterado?

Exemplo:

DROP Person; Exemplo: Use com cuidade !!

29

Valores Default

Especificação de valores default:

CREATE TABLE Person( name VARCHAR(30), social-security-number INT, age SHORTINT DEFAULT 100, city VARCHAR(30) DEFAULT ‘Seattle’, gender CHAR(1) DEFAULT ‘?’, Birthdate DATE

O default dos defaults: NULL

30

ÍndicesMUITO importante para melhorar o tempo de processamento de uma consulta.

Considere a relação:

Person (name, age, city)

O processamento sequencial do arquivo Person pode levar muito tempo.

SELECT *FROM PersonWHERE name = “Smith”

31

• Criação de um índice sobre o atributo name:

• Árvores B+ tem um fan-out de 100s: max 4 níveis !

Índices

Smith ….….CharlesBettyAdam

32

Criação de Índices

CREATE INDEX nameIndex ON Person(name)

Sintaxe:

33

Criação de ÍndicesOs índices podem ser criados sobre mais que um atributo:

CREATE INDEX doubleindex ON Person (age, city)

SELECT * FROM Person WHERE age = 55 AND city = “Seattle”

SELECT * FROM Person WHERE city = “Seattle”

Ajuda em:

Mas não em:

Exemplo:

34

Criação de Índices

Os índices podem ser úteis para pesquisas em intervalos também:

Árvores B+ ajudam em:

Por que não criar índices em tudo?

CREATE INDEX ageIndex ON Person (age)

SELECT * FROM Person WHERE age > 25 AND age < 28

35

O Problema da Seleção de Índices

• É definido um workload = um conjunto de consultas SQL e a frequência que elas são executadas

• Quais os índices que devem ser criados para melhorar o desempenho do workload?

• cláusulas FROM/WHERE: favorecem a criação de um índice

• cláusulas INSERT/UPDATE: desencorajam a criação de índices

• Seleção de índices = normalmente feito por pessoas; mais recentemente feito automaticamente