EXERCÍCIOS PRÁTICOS DE SQL (GABARITO)

Prévia do material em texto

Banco de Dados II
Exercícios práticos de SQL em MySQL
(GABARITO)
Considere o esquema relacional abaixo que representa o controle de publicações em eventos científicos. As chaves primárias estão sublinhadas.
Escreva os comandos SQL para criar as tabelas Autor, Publicação e PublicaçãoAutor, incluindo as restrições de integridade que se aplicam. Assuma que quando um departamento é excluído, o código de departamento dos autores relacionados a ele deve ser alterado para NULL. Assuma ainda que ao excluir um autor, todas as suas publicações devem ser excluídas automaticamente. Assuma também que uma conferência não pode ser excluída se houver alguma publicação relacionada. Além disso, ao alterar o código de uma conferência, todas as publicações relacionadas devem ser alteradas automaticamente. Em uma publicação, assuma que o ano não pode ser nulo. Uma tupla na relação PublicaçãoAutor só deve existir se existir uma tupla correspondente na tabela Publicação. Se, por algum motivo, não for possível definir alguma restrição de integridade, justifique.
CREATE TABLE AUTOR ( 
CODAUTOR INT NOT NULL, 
NOME VARCHAR(30), 
CODDEPTO INT, 
PRIMARY KEY (CODAUTOR), 
FOREIGN KEY CODDEPTO REFERENCES DEPARTAMENTO (CODDEPT)
ON DELETE SET NULL 
) 
 CREATE TABLE PUBLICACAO ( 
CODPUB INT NOT NULL, 
TITULO VARCHAR(30), 
ANO INT NOT NULL, 
CODCONF INT, 
PRIMARY KEY (CODPUB),
FOREIGN KEY (CODCONF) REFERENCES CONFERENCIA (CODCONF)
ON DELETE RESTRICT ON UPDATE CASCADE 
) 
 
 CREATE TABLE PUBLICACAOAUTOR ( 
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 
) 
É possível definir todas as restrições de integridade.
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 “Many task computing for large clusters”, ano 2010.
INSERT INTO PUBLICACAO (CODPUB, TITULO, ANO, CODCONF) 
VALUES (111, ‘Many task computing for large clusters’, 2010, 122); 
INSERT INTO PUBLICACAOAUTOR (CODAUTOR, CODPUB) 
VALUES (05, 111);
 
Considere o esquema relacional abaixo de uma companhia de ônibus da cidade do Rio de Janeiro. As chaves primárias estão sublinhadas.
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’ 
Escreva um comando SQL que atualize o nome do município “Frorianopolis” para “Florianópolis”
UPDATE MUNICIPIO
SET CH_NOME = ‘FRORIANOPOLIS’
WHERE CH_NOME = ‘FLORIANÓPOLIS’
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.
Selecionar todas as informações de todos os clientes
SELECT * FROM cliente;
Selecionar o nome de todos os produtos
SELECT nome FROM produto;
Selecionar todos os pedidos do cliente cujo código seja igual a 09 e cujo o valor total seja maior que 50,00.
SELECT * FROM pedido
WHERE codCliente=09 AND valorTotal > 50.0;
Selecionar todos os pedidos cujo valor total seja maior que 100,00 ou maior que 500,00
SELECT * FROM pedido
WHERE valorTotal < 100 OR valorTotal > 500;
Selecionar todos os pedidos cuja data de entrada seja igual a 04/12/2012.
SELECT * FROM pedido
WHERE dtEntrada = ‘04/12/2012’;
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;
Selecionar todos os clientes cujo código NÃO estejam entre 05 e 25.
SELECT * FROM cliente
WHERE codCliente NOT BETWEEN 5 AND 25;
Selecionar todos os produtos cujo nome possua a primeira letra igual a “P”. Utilize o operador LIKE.
SELECT * FROM produto
WHERE nome LIKE 'P%';
Selecionar todos os produtos cujo o 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 produto
WHERE nome LIKE '%an%';
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%';
Selecionar todos os clientes cujo código do estado seja “MG” ou “ES”.
SELECT * FROM cliente
WHERE codEstado IN ('MG','ES');
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 (codUnidade = 'KG' OR codUnidade = 'UM');
Selecionar todos os produtos cujo preço seja maior que 5, incluindo os produtos cujo o preço esteja NULL.
SELECT * FROM produto
WHERE preco < 5 OR preco is null;
Mostrar os dados dos produtos para os quais não existe nenhum pedido.
SELECT CodProduto, nome
FROM produto
WHERE CodProduto NOT IN (SELECT DISTINCT CodProduto FROM item); 
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 primarias estão sublinhadas.
 
Sobre a base de dados acima apresentada, resolver as consultas a seguir usando SQL.
ATENÇÃO: Não usar mais tabelas que o estritamente necessário.
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 (‘M4N68T-M’, ‘Acer Aspire’, ‘1010’);
Escreva uma instrução SQL para excluir a tabela Cliente.
DROP TABLE Cliente;
Considere o esquema relacional abaixo. As chaves primárias estão sublinhadas.
Escreva um comando SQL para excluir a tabela Autor.
DROP TABLE Autor;
Escreva um comando SQL para incluir uma coluna nova, chamada “Endereco”, do tipo VARCHAR(50), na tabela Autor. 
ALTER TABLE Autor ADD Endereco VARCHAR(50);
Escreva o comando SQL necessário para excluir todas as obras que possuem algum volume que foi adquirido antes de 01/01/2001. 
DELETE FROM Obra 
WHERE NumObra IN (SELECT NumObra FROM Volume
 WHERE DataAquisicao < 01/01/2001);
Escreva um comando SQL para inserir um autor chamado “João Carlos”, nascido em “01/01/1980”, com NumAutor 1005.
INSERT INTO AUTOR (NumAutor, NomeAutor, DataNascimento) 
VALUES (1005, ‘João Carlos’, ‘01/01/1980’);
Considere a base de dados relacional com dados sobre pacientes e internações, cujo esquema encontra-se abaixo (as chaves primárias estão sublinhadas):
Escreva o comando SQL para criar a tabela Leito, incluindo as restrições de integridade que se aplicam.
CREATE TABLE Leito ( 
NumLeito integer NOT NULL,
NumQuarto integer,
TipoLeito char,
Primary key (NoLeito)
);
Escreva o comando SQL para adicionar a coluna Telefone na tabela Paciente. 
ALTER TABLE Paciente ADD Telefone VARCHAR(10);
Escreva um comando SQL para alterar a informação sobre Internação da paciente “Mariana Silva” cuja data de internação é “06/06/2010”. A alteração deve modificar a data de alta para “06/07/2010”.
UPDATE Internação 
SET DataAlta=’06/07/2010’
WHERE DataBaixa = ‘06/06/2010’ AND 
CodPac IN (SELECT CodPac 
 FROM Paciente
 WHERE NomePac = ’Marina Silva’);
Escreva um comando SQL para excluir todos os registros de internações que ocorreram em leito do tipo “A”.
DELETE FROM Internação 
WHERE NumLeito IN (SELECT NumLeito FROM Leito
 WHERE TipoLeito = ’A’);
Obteros números dos leitos que estão atualmente ocupados por alguma internação. Assuma que, quando um leito está ocupado, o atributo DataAlta é NULL.
SELECT NumLeito 
FROM Internação 
WHERE DataAlta IS NULL
Considere a seguinte base de dados, usada para gerenciar clínicas médicas. As chaves primárias estão sublinhadas.
Sobre esta base de dados, resolver as consultas a seguir usando SQL ou álgebra relacional, conforme solicitado no enunciado. Não usar mais tabelas que o estritamente necessário.
Escreva uma instrução SQL para inserir uma especialidade chamada “ORTOPEDIA”, com código “E023” e código de especialidade genérica “E001”.
INSERT INTO ESPECIALIDADE (CodEspeci, Nome, CodEspeciGenerica) 
VALUES (‘E023’, ‘ORTOPEDIA’, ‘E001’);
Escreva uma instrução SQL para excluir todos os médicos que trabalham na clínica “São Luiz”.
DELETE FROM MEDICO 
WHERE CodMed IN (SELECT CodMed 
 FROM Clinica c, ClinicaMedico cm
 WHERE c.codCli = cm.CodCli AND c.NomeCli = ‘São Luiz’);
Escreva uma instrução SQL para criar a tabela Médico, de forma que, ao remover a especialidade de um determinado médico, o médico seja automaticamente removido da tabela.
CREATE TABLE MEDICO ( 
CodMed INTEGER NOT NULL, 
NomeMed VARCHAR(30), 
CodEspeci INTEGER NOT NULL, 
PRIMARY KEY(CodMed), 
FOREIGN KEY (CodEspeci) REFERENCES Especialidade (CodEspeci)
ON DELETE CASCADE
);