SHARE
TWEET

Lista 05 (PABD) (GABRIEL)

clucasg Dec 6th, 2019 104 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ############################################ QUESTÃO 1 ############################################
  2. ######## Cria o database e as tabelas
  3. CREATE DATABASE dbRH;
  4.  
  5. CREATE TABLE Departamento (CoDepto INT AUTO_INCREMENT, NoDepartamento VARCHAR(20) NOT NULL , PRIMARY KEY (CoDepto));
  6. CREATE TABLE Funcionario (Matricula INT AUTO_INCREMENT, Nome VARCHAR(30) NOT NULL, CoDepto INT NOT NULL, Salario FLOAT NOT NULL , PRIMARY KEY (Matricula), FOREIGN KEY (CoDepto) REFERENCES Departamento(CoDepto));
  7.  
  8. ######## INSERE OS VALORES
  9. ##### DOS DEPARTAMENTOS
  10. INSERT INTO Departamento (NoDepartamento) VALUES ('Contabilidade');
  11. INSERT INTO Departamento (NoDepartamento) VALUES ('Faturamento');
  12. INSERT INTO Departamento (NoDepartamento) VALUES ('TI');
  13. INSERT INTO Departamento (NoDepartamento) VALUES ('Financeiro');
  14.  
  15. ##### DOS FUNCIONÁRIOS
  16. INSERT INTO Funcionario (Nome, CoDepto, Salario) VALUES ('Carlos', 1, 1000);
  17. INSERT INTO Funcionario (Nome, CoDepto, Salario) VALUES ('Pedro', 1, 2000);
  18. INSERT INTO Funcionario (Nome, CoDepto, Salario) VALUES ('Maria', 2, 1300);
  19. INSERT INTO Funcionario (Nome, CoDepto, Salario) VALUES ('Rita', 3, 1100);
  20. INSERT INTO Funcionario (Nome, CoDepto, Salario) VALUES ('Renata', 1, 1500);
  21. INSERT INTO Funcionario (Nome, CoDepto, Salario) VALUES ('Camila', 3, 4570,23);
  22.  
  23. ############################################ QUESTÃO 2 ############################################
  24. a) SELECT Nome FROM Funcionario WHERE Salario < 2000;
  25. b) SELECT COUNT(Matricula) FROM Funcionario WHERE Salario <= 1500;
  26. c) SELECT MIN(Salario) FROM `Funcionario` WHERE CoDepto=1;
  27. d) SELECT AVG(Salario) FROM `Funcionario` WHERE CoDepto=3;
  28. e) SELECT Nome FROM `Funcionario` WHERE CoDepto=1 AND Salario<=1500 ORDER BY Nome ASC;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top