Questão de Tecnologia da Informação

As questões 46, 47 e 48 compartilham dos mesmos comandos usados no MySQL para criar duas tabelas, fazer algumas inserções, restrições e uma remoção. Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.17 MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE EMPREL; Query OK, 1 row affected (0.01 sec) mysql> USE EMPREL; Database changed mysql> CREATE TABLE Empregado ( -> Matricula char(5) PRIMARY KEY, -> Nome varchar(30), -> numDepto int -> ); Query OK, 0 rows affected (0.06 sec) 13 mysql> CREATE TABLE Departamento ( -> numDepto int UNIQUE, -> nomeDepto varchar(30) -> ); Query OK, 0 rows affected (0.06 sec) mysql> ALTER TABLE Empregado ADD CONSTRAINT fk_empregado_depto -> FOREIGN KEY (numDepto) REFERENCES Departamento(numDepto); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO Departamento VALUES (1, 'Vendas'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO Departamento VALUES (2, 'Marketing'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO Departamento VALUES (3, 'Contabilidade'); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Departamento VALUES (4, 'RH'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Empregado VALUES ('11111', 'Ana', 1); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Empregado VALUES ('22222', 'Beth', 3); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO Empregado VALUES ('33333', 'Carla', 3); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Empregado VALUES ('44444', 'Danny', 4); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Empregado VALUES ('55555', 'Eliane', 4); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Empregado VALUES ('66666', 'Fatima', NULL); Query OK, 1 row affected (0.01 sec) mysql> ALTER TABLE Departamento ADD TotalEmpregados int; Query OK, 4 rows affected (0.14 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> /* IMPORTANTE! Nesse ponto, entra um comando SQL que será cobrado na questão 26. */ mysql> Delimiter // mysql> CREATE TRIGGER alteraDepto1 -> AFTER INSERT ON Empregado -> FOR EACH ROW -> BEGIN -> IF (NEW.numDepto IS NOT NULL) THEN -> UPDATE departamento -> SET TotalEmpregados = TotalEmpregados + 1 -> WHERE departamento.numDepto = NEW.numDepto; -> END IF; -> END// Query OK, 0 rows affected (0.00 sec) mysql> Delimiter ; mysql> INSERT INTO Empregado values ('77777', 'Gabriela', 3); Query OK, 1 row affected (0.03 sec) mysql> Delimiter // mysql> CREATE TRIGGER alteraDepto2 -> AFTER DELETE ON Empregado -> FOR EACH ROW -> BEGIN -> IF (OLD.numDepto IS NOT NULL) THEN -> UPDATE Departamento -> SET totalEmpregados = totalEmpregados - 1 -> WHERE Departamento.numDepto = OLD.numDepto; -> END IF; -> END// Query OK, 0 rows affected (0.03 sec) mysql> Delimiter ; mysql> DELETE FROM Empregado WHERE Matricula = '77777'; Query OK, 1 row affected (0.03 sec) 14