Exercícios SQL II

Prévia do material em texto

I. Considere o esquema relacional abaixo que representa o controle de publicações em eventos científicos.
As chaves primárias estão sublinhadas.
a) Escreva os comandos SQL para criar as tabelas Autor, Publicação e PublicaçãoAutor, incluindo as restrições de integridade que se aplicam.
CREATE TABLE AUTOR(
CODAUTOR INT NOT NULL,
NOME VARCHAR(30),
CODDEPTO INT,
PRIMARY KEY( CODAUTOR),
FOREIGN KEY CODDEP TO REFERENCES DEPARTAMENTO(CODDEPTO));
CREATE TABLE PUBLICAÇÃO ( 
CODPUB INT NOT NULL,
TITULO VARCHAR(30),
ANO INT NOT NULL,
CODCONF INT,
PRIMARY KEY( CODPUB),
FOREIGN KEY(CODCONF) REFERENCES CONFERENCIA(CODCONF)
ON DELET E RESTRICT ON UPDATE CASCADE);
CREATE TABLE PUBLICAÇÃOAUTOR(
CODAUTOR INT NOT NULL,
CODPUB INT NOT NULL,
PRIMARY KEY (CODAUTOR, CODPUB),
FOREIGN KEY (CODAUTOR) REFERENCES AUTOR( CODAUTOR),
ON DELETE CASCADE
FOREIGN KEY (CODPUB) REFERENCES PUBLICACAO (CODPUB),
ON DELETE CASCADE,
)
b) Escreva comandos SQL para incluir uma nova publicação no banco de dados, com os seguintes dados: Publicação código 111 do autor 05 (assuma que o autor já está cadastrado) na conferência 122 (assuma que a conferência já está cadastrada). Os detalhes da publicação são: título “SQL”, ano 2010.
INSERT INTO PUBLICACAO (CODPUB, TITULO, ANO, CODCONF)
VALUES( 111, ‘SQL’, 2010, 122);
INSERT INTO PUBLICACAOAUTOR (CODAUTOR,CODPUB)
VALUES( 05, 111);
II. Considere o esquema relacional abaixo de uma companhia de ônibus da cidade do Rio de Janeiro. 
As chaves primárias estão sublinhadas.
a) Escreva um comando SQL para excluir todos os clientes cujo nome comece por “Marta” e termine com “Mattoso”.
DELETE FROM CLIENTE
WHERE CH_NOME LIKE ‘MARTA%MATTOSO’
b) Escreva um comando SQL que atualize o nome do município “Frorianopolis” para “Florianópolis”
UPDATE MUNICIPIO 
 SET CHNOME = ‘FLORIANÓPOLIS’
WHERE CH_NOME = ‘FRORIANOPOLIS’
III. Considere a seguinte base de dados, usada pela empresa XPTO para controle de vendas e entregas em domicílio. 
As chaves primárias estão sublinhadas.
Sobre esta base de dados, resolver as consultas a seguir usando SQL. Não utilize mais tabelas que o necessário nas consultas.
a) Selecionar todas as informações de todos os clientes
SELECT * FROM cliente;
b) Selecionar o nome de todos os produtos
SELECT nome FROM produto;
c) Selecionar todos os pedidos do cliente cujo código seja igual a 09 e cujo valor total seja maior que 50,00.
SELECT * FROM pedido
WHERE codCliente=09 AND valorTotal > 50,0;
d) Selecionar todos os pedidos cujo valor total seja menor que 100,00 ou maior que 500,00
SELECT * FROM pedido
WHERE valorTotal < 100 OR valorTotal > 500;
e) Selecionar todos os pedidos cuja data de entrada seja igual a 04/12/2012.
SELECT * FROM pedido
WHERE dtEntrada = ‘04/12/2012’;
f) Selecionar todos os pedidos cuja data de entrada seja igual a 02/12/1999 e cujo valor total esteja entre 20,00 e 50,00
SELECT * FROM pedido
WHERE(valorTotal BETWEEN 20 AND 50) AND dtEntrada = ‘02/12/1999’;
g) Selecionar todos os clientes cujo código NÃO estejam entre 05 e 25.
SELECT * FROM CLIENTE
WHERE codCliente NOT BETWEEN 5 AND 25;
h) Selecionar todos os produtos cujo nome possua a primeira letra igual a “P”. Utilize o operador LIKE.
SELECT * FROM PRODUTOS
WHERE nome LIKE ‘P%’;
i) Selecionar todos os produtos cujo nome possua o trecho “an” em qualquer posição. Por exemplo, deverão ser selecionados os produtos “antena”, “manga”, etc. Utilize o operador LIKE.
SELECT * FROM PRODUTOS
WHERE nome LIKE ‘%AND%’;
j) Selecionar todos os produtos cujo nome comece com “C” ou “F” ou “M”, independente do resto. Utiliza o operador LIKE.
SELECT * FROM produto
WHERE nome LIKE ‘C %’ OR
nome LIKE ‘F%’ OR
nome LIKE ‘M%’;
k) Selecionar todos os clientes cujo código do estado seja “MG” ou “ES”.
SELECT * FROM cliente 
WHERE codEstado IN(‘MG’, ‘ES’);
l) Selecionar todos os produtos cujo código da unidade de estoque seja “KG” ou “UM” e o preço seja maior que 10.
SELECT * FROM produto
WHERE preco > 10 AND ( codUniade = ‘KG’ OR codUidade = ‘UM’); 
m) Selecionar todos os produtos cujo preço seja maior que 5, incluindo os produtos cujo preço esteja NULL.
SELECT * FROM produto
WHERE preco > 5 OR preco is null;
n) Mostrar os dados dos produtos para os quais não existe nenhum pedido.
SELECT * FROM produto
 WHERE CodPRODUTO NOT IN (SELECT DISTINCT CodProduto 
IV. Considere a seguinte base de dados, usada por uma empresa de computadores (Dell, HP, por exemplo) e que disponibiliza manutenção de computadores e upgrades. 
As chaves primárias estão sublinhadas.
Sobre esta base de dados, resolver as consultas a seguir usando SQL. Não utilize mais tabelas que o necessário nas consultas.
a) Escreva uma instrução SQL para inserir um computador de número de série M4N68T-M, modelo igual a Acer Aspire, e CPF igual a 1010.
INSERT INTO COMPUTADOR (no_serie, modelo, cpf)
VALUES (‘M4N68 T-M’, ‘ACER ASPIRE’, ‘1010’);
b) Escreva uma instrução SQL para excluir a tabela Cliente.
DROP TABLE Cliente;