Revisão de Consultas SQL e Lista de Exercícios para a Prova 2 de Banco de Dados

SQL: SELECT "ID", nome, fone FROM clientes WHERE "ID" IN (SELECT cliente FROM locacoes WHERE filme IN (SELECT "ID" FROM filmes WHERE estilo IN (SELECT "ID" FROM estilos WHERE nome IN ('ação', 'suspense')))) AND "ID" NOT IN (SELECT cliente FROM locacoes WHERE filme IN (SELECT "ID" FROM filmes WHERE estilo IN (SELECT "ID" FROM estilos WHERE nome NOT IN ('ação', 'suspense'))));

SQL: SELECT f.titulo, c.nome FROM filmes f JOIN categorias c ON f.categoria = c."ID" WHERE NOT EXISTS (SELECT * FROM filmes ff WHERE ff.ano < f.ano AND ff."ID" != f."ID");

SQL: SELECT DISTINCT titulo FROM filmes f JOIN locacoes l ON f."ID" = l.filme WHERE NOT EXISTS (SELECT * FROM locacoes ll WHERE ll."dataR" > l."dataR" AND ll.filme != l.filme);

SQL: SELECT nome, endereco FROM clientes c WHERE NOT EXISTS (SELECT * FROM filmes f WHERE NOT EXISTS (SELECT * FROM locacoes l WHERE l.filme = f."ID" AND l.cliente = c."ID"));

SQL: UPDATE reservas r SET "dataR" = (SELECT "dataD" FROM locacoes l JOIN filmes f ON l.filme = f."ID" WHERE l."ID" = r."ID" AND f.titulo = 'X-Men 3' ORDER BY "dataD" DESC LIMIT 1) WHERE filme IN (SELECT "ID" FROM filmes WHERE titulo = 'X-Men 3');

SELECT nome, endereco FROM Clientes c WHERE
    NOT EXISTS (SELECT * FROM Produtos p WHERE
        NOT EXISTS (SELECT * FROM Vendas v WHERE
            v.codigoCliente = c.codigoCliente AND
            v.codigoProduto = p.codigoProduto))

SELECT nome, codigoProduto FROM Produtos WHERE
    precoUnitario > ALL (SELECT precoUnitario FROM Produtos
        WHERE produtoSubstituto
            IN (SELECT codigoProduto FROM Produtos WHERE
                 nome = "Parafuso XYZ"))

SELECT nome, codigo FROM Produtos WHERE precoUnitario =
    (SELECT MAX(precoUnitario) FROM Produtos)

SELECT nome, codigo FROM Produtos
EXCEPT
SELECT p1.nome, p1.codigo FROM Produtos p1 JOIN
    Produtos p2 ON p1.precoUnitario < p2.precoUnitario

SELECT nome, codigo FROM Produtos p WHERE precoUnitario >= ALL
    (SELECT pp.precoUnitario FROM Produtos pp WHERE
      pp.codigoProduto != p.codigoProduto)

CREATE VIEW vEstoque30 AS
SELECT p.codigoProduto, p.nome, s.codigoProduto, s.nome
FROM Produtos p JOIN Produtos s ON p.produtoSubstituto = s.codigoProduto
WHERE qtdEstoque < 30

CREATE OR REPLACE FUNCTION verifica_igualdade()
RETURNS trigger AS $$
BEGIN
    IF new.codProd = new.produtoSubstituto THEN RAISE
        EXCEPTION 'O produto não pode ser seu próprio substituto!';
    END IF;
    RETURN new;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER verifica_substituto BEFORE INSERT ON
produtos FOR EACH ROW EXECUTE PROCEDURE verifica_igualdade();

CREATE INDEX "ixPreco" ON Produtos(precoUnitario) USING btree;
CLUSTER Produtos USING "ixPreco";

// Criou-se um índice de árvore B agrupado (clusterizado).
A árvore B foi escolhida pois consultas por intervalo têm melhor desempenho com busca em árvores do que em estruturas hash. Decidiu-se agrupar a relação Produtos por este índice, pois, uma vez que os dados estariam ordenados por preço no disco, bastaria ao otimizador um único acesso ao índice para encontrar o primeiro dado do intervalo. A partir deste dado, os preços subsequentes seriam percorridos sequencialmente no disco, conforme a ordenação.

No buffer de memória são mantidos os dados processados pela transação (cache), os logs da transação e resultados temporários de processamento. Quando a transação passa ao estado ATIVA, os dados que serão processados pela transação são recuperados do disco. Neste mesmo momento, o buffer aloca espaço para armazenar o log da transação, que neste primeiro momento estará apenas em memória (sem existir no disco). Quando a transação atinge o estado EFETIVADA, significa que todos os dados de log que até então eram mantidos pelo buffer estarão persistidos em disco. No estado CONCLUÍDA, todos os dados processados pela transação e que foram alterados ou criados por ela são persistidos em disco.

Atomicidade e durabilidade são as propriedades básicas para garantir a característica do SGBD de ser tolerante a falhas, uma vez que elas possibilitam que uma transação possa ser desfeita ou refeita. Isso é possível pois um estado atômico da transação se encontra persistido (durável) em disco. O subsistema de Recovery tem a responsabilidade de tornar o SGBD tolerante a falhas, de forma que seja capaz de conduzi-lo a um estado passado consistente após a ocorrência de uma falha que o deixou em um estado inconsistente. Durante o funcionamento normal do SGBD, o Recovery deve realizar cópias de backup periódicas e manter as informações das atualizações efetuadas no BD. Após a ocorrência de uma falha, o subsistema deve utilizar uma de suas técnicas de recovery para tratar a falha e retornar o BD a um estado consistente.