Ficha 01 · DDL e DML · Ficha de trabalho

Aluno(a)

Turma

Nº

Data

Objectivos da ficha

Criar tabelas com restrições
Inserir dados
Actualizar com WHERE
Apagar com cuidado

Parte I · DDL

Exercício 1 · Criar tabela (15 pts)

Escreve CREATE TABLE Aluno com: - id PK auto-increment. - numero único, 10 caracteres, não nulo. - nome 150 caracteres, não nulo. - email único, 150 caracteres. - idade CHECK >= 0 e <= 25. - data_inscricao data com default hoje.

CREATE TABLE Aluno (
  id INT PRIMARY KEY AUTO_INCREMENT,
  numero VARCHAR(10) UNIQUE NOT NULL,
  nome VARCHAR(150) NOT NULL,
  email VARCHAR(150) UNIQUE,
  idade INT CHECK (idade >= 0 AND idade <= 25),
  data_inscricao DATE DEFAULT (CURRENT_DATE)
);

Exercício 2 · ALTER (15 pts)

Para a tabela do exercício 1, escreve comandos para:

a) Adicionar coluna telefone VARCHAR(20).

b) Mudar nome para 200 caracteres.

c) Adicionar índice no email.

d) Adicionar FK para Turma(id) na coluna turma_id (criar coluna primeiro).

ALTER TABLE Aluno ADD COLUMN telefone VARCHAR(20);

ALTER TABLE Aluno MODIFY nome VARCHAR(200) NOT NULL;

CREATE INDEX idx_aluno_email ON Aluno(email);

ALTER TABLE Aluno ADD COLUMN turma_id INT;
ALTER TABLE Aluno
  ADD CONSTRAINT fk_turma
  FOREIGN KEY (turma_id) REFERENCES Turma(id);

Parte II · INSERT

Exercício 3 · Inserir dados (15 pts)

Considerando a tabela Aluno, escreve:

a) INSERT de 1 aluno. b) INSERT de 3 alunos numa única instrução. c) INSERT que copia todos os alunos de AlunoArquivo para Aluno.

-- a)
INSERT INTO Aluno (numero, nome, email, idade)
VALUES ('A001', 'Ana Silva', 'ana@ex.pt', 17);

-- b)
INSERT INTO Aluno (numero, nome, email, idade) VALUES
  ('A002', 'João Costa', 'joao@ex.pt', 18),
  ('A003', 'Maria Lopes', 'maria@ex.pt', 17),
  ('A004', 'Pedro Santos', 'pedro@ex.pt', 19);

-- c)
INSERT INTO Aluno (numero, nome, email, idade)
SELECT numero, nome, email, idade FROM AlunoArquivo;

Exercício 4 · Detectar erro (10 pts)

Esta query dá erro:

INSERT INTO Aluno (nome, email) VALUES ('Ana', 'ana@ex.pt');

Porquê?

Falta a coluna numero que tem NOT NULL e não tem default. Sem valor explícito a BD não sabe o que pôr.

Solução: incluir numero:

INSERT INTO Aluno (numero, nome, email)
VALUES ('A001', 'Ana', 'ana@ex.pt');

Parte III · UPDATE / DELETE

Exercício 5 · UPDATE seguro (15 pts)

a) Como confirmar que UPDATE só vai afectar as linhas certas, antes de o executar?

b) Escreve UPDATE que adiciona "Silva" ao nome de todos os alunos chamados "Ana" (Ana → Ana Silva).

a) Correr o WHERE como SELECT primeiro:

SELECT id, nome FROM Aluno WHERE nome = 'Ana';

Verifica quantas linhas e quais antes do UPDATE.

UPDATE Aluno SET nome = 'Ana Silva' WHERE nome = 'Ana';

Em ambiente real, talvez melhor com email único:

UPDATE Aluno SET nome = 'Ana Silva' WHERE id = 42;

Exercício 6 · DELETE perigoso (10 pts)

Qual a diferença entre estes três comandos? Qual é destrutivo?

DELETE FROM Aluno WHERE id = 5;       -- (a)
DELETE FROM Aluno;                     -- (b)
TRUNCATE TABLE Aluno;                  -- (c)

(a) Apaga só o aluno com id=5. Seguro se WHERE estiver correcto.

(b) Apaga todas as linhas da tabela. Mantém estrutura e auto-increment. DESTRUTIVO.

(c) Apaga todas as linhas, reset do auto-increment, mais rápido (não regista cada DELETE no log). Não pode ser feito ROLLBACK.

Soluções alternativas mais seguras: - Soft delete (UPDATE SET apagado_em = NOW()). - Mover para tabela de arquivo antes de DELETE. - Sempre fazer backup antes.

Parte IV · Cenário

Exercício 7 · Esquema completo (20 pts)

Escreve SQL completo para criar um esquema de biblioteca:

Autor (id, nome, nacionalidade)
Livro (id, titulo, isbn único, ano, autor_id FK)
Socio (id, nome, email único)
Emprestimo (id, livro_id FK, socio_id FK, data_saida, data_retorno nullable)

Inclui restrições apropriadas e acções referenciais.

CREATE TABLE Autor (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(150) NOT NULL,
  nacionalidade VARCHAR(50)
);

CREATE TABLE Livro (
  id INT PRIMARY KEY AUTO_INCREMENT,
  titulo VARCHAR(200) NOT NULL,
  isbn VARCHAR(20) UNIQUE,
  ano INT CHECK (ano > 0 AND ano <= YEAR(CURRENT_DATE)),
  autor_id INT,
  FOREIGN KEY (autor_id) REFERENCES Autor(id) ON DELETE SET NULL
);

CREATE TABLE Socio (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(150) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE Emprestimo (
  id INT PRIMARY KEY AUTO_INCREMENT,
  livro_id INT NOT NULL,
  socio_id INT NOT NULL,
  data_saida DATE NOT NULL DEFAULT (CURRENT_DATE),
  data_retorno DATE,
  CHECK (data_retorno IS NULL OR data_retorno >= data_saida),
  FOREIGN KEY (livro_id) REFERENCES Livro(id) ON DELETE RESTRICT,
  FOREIGN KEY (socio_id) REFERENCES Socio(id) ON DELETE RESTRICT,
  INDEX idx_emprestimo_socio (socio_id),
  INDEX idx_emprestimo_livro (livro_id)
);

Decisões: - ON DELETE SET NULL em Livro.autor_id — livro pode ficar sem autor identificado. - ON DELETE RESTRICT em Emprestimo — não apagar livro/sócio com histórico. - CHECK garante que data_retorno é depois da saída. - Índices em FKs para acelerar JOINs.