Advertisement
Guest User

Lista 1.2

a guest
Mar 9th, 2017
481
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 12.50 KB | None | 0 0
  1. CREATE DATABASE db_lista;
  2.  
  3. USE db_lista;
  4.  
  5. -- QUESTOÕES 1 a 3 --
  6.  
  7. CREATE TABLE tb_empregado(
  8.     idt_empregado INT PRIMARY KEY AUTO_INCREMENT,
  9.     nme_empregado VARCHAR(50) NOT NULL,
  10.     end_empregado VARCHAR(100) NOT NULL,
  11.     tel_empregado VARCHAR(20) NOT NULL,
  12.     dta_nascimento_empregado DATE NOT NULL,
  13.     eml_empregado VARCHAR(50) NOT NULL,
  14.     vlr_salario_empregado DECIMAL(8,2) NOT NULL,
  15.     qtd_dependente INT NOT NULL
  16. );
  17.  
  18. CREATE TABLE tb_dependente(
  19.     idt_dependente INT PRIMARY KEY AUTO_INCREMENT,
  20.     cod_empregado INT NOT NULL,
  21.     nme_dependente VARCHAR(50) NOT NULL,
  22.     dta_nascimento_dependente DATE NOT NULL,
  23.     eml_dependente VARCHAR(50),
  24.     tel_dependente VARCHAR(20),
  25.     CONSTRAINT fk_d_empregado FOREIGN KEY (cod_empregado) REFERENCES tb_empregado(idt_empregado)
  26. );
  27.  
  28. INSERT INTO tb_empregado VALUES (1, 'Maria Naves Santos', 'SQS 304 BL E APT 203', '(61)981234356', '1987-12-12', 'maria_naves87@gmail.com', '8000.00', 2),
  29. (2, 'Mauro Almeida Borges', 'SQSW 301 BL A APT 106', '(61)983214576', '1983-03-21', 'mauro_borges83@gmail.com', '6500.00', 1),
  30. (3, 'José Costa Neto', 'SQN 107 BL B APT 602' , '(61)986742359', '1979-09-30', 'jose_neto79@gmail.com', '4900.00', 1),
  31. (4, 'Bianca Redes', 'SQS 103 BL C APT 405', '(61)994532675', '1988-5-26', 'bianca_redes88@gmail.com', '5250.00', 1),
  32. (5, 'Nilton Valério Fonseca', 'Shis Qi 13, entrada conj. 8/9, Bloco D APT 103', '(61)983425167', '1990-01-01', 'nilton_fonseca90@gmail.com', '3990.00', 1),
  33. (6, 'Alanderson Júnior', 'SQN 204 BLC E APT 202', '(61)983475692', '1971-10-07', 'alan_junior71@gmail.com', '3500.00', 1),
  34. (7, 'Sandro Moura', 'SQS 311 BL F APT 502', '(61)987456312', '1985-02-13', 'sandro_nalbert85@gmail.com', '11000.00', 2),
  35. (8, 'Fausto Cardoso', 'SHCGN 706 Norte, Conjunto A, Bloco B, APT 202', '(61)945236712', '1982-06-23', 'fausto_carodoso82@gmail.com', '5100.00', 1),
  36. (9, 'Victor Monteiro', 'SQN 310 BL I APT 101', '(61)994821463', '1974-07-29', 'victor_monteiro74@gmail.com', '4300.00', 1),
  37. (10, 'Souza Mendes', 'SQSW 303 BL F APT 402', '(61)945218329', '1982-06-23', 'souza_mendes82@gmail.com', '13000.00', 2)
  38. ;
  39.  
  40. SELECT * FROM tb_empregado;
  41.  
  42. INSERT INTO tb_dependente VALUES (1, 1, 'Nathan Naves', '1999-05-27', 'nathan_naves99@gmail.com', '(61)985326173'),
  43. (2, 2, 'Patricia Borges', '1986-06-17', 'patricia_borges86@gmail.com', '(61)998542346'),
  44. (3, 3, 'Henrique Costa Neto', '2000-04-10', 'henrique_costa00@gmail.com', '(61)98853472'),
  45. (4, 4, 'Murilo Redes', '1983-8-16', 'murilo_redes83@gmail.com', '(61)998317263'),
  46. (5, 5, 'Alice Fonseca', '2012-03-26', '',''),
  47. (6, 6, 'Alanderson Neto', '1998-03-21', 'alan_neto97@gmail.com', '(61)983673856'),
  48. (7, 7, 'Isabela Moura', '1989-07-19', 'isabela_moura89@gmail.com', '(61)988394456'),
  49. (8, 8, 'Natalia Cardoso', '1989-10-14', 'natalia_cardoso89@gmail.com', '(61)973342561'),
  50. (9, 9, 'Manuela Monteiro', '1991-05-26', 'manuela_monteiro91@gmail.com', '(61)988834526'),
  51. (10, 10, 'Julia Mendes', '1990-09-13', 'julia_mendes@gmail.com', '(61)983247721'),
  52. (11, 1, 'Laura Naves', '2005-11-17', 'laurinha05@gmail.com','(61)987341151'),
  53. (12, 7, 'Heitor Moura', '2015-01-12', '', ''),
  54. (13, 10, 'Sofia Mendes', '2013-07-25', '', '');
  55.  
  56. SELECT * FROM tb_dependente;
  57.  
  58. SELECT nme_empregado, dta_nascimento_empregado, qtd_dependente, group_concat(' ', nme_dependente, ' (', dta_nascimento_dependente, ')') AS 'dependentes' FROM tb_empregado JOIN tb_dependente ON idt_empregado= cod_empregado GROUP BY qtd_dependente DESC, idt_empregado;
  59.  
  60. SELECT nme_empregado, group_concat(nme_dependente) as dependentes, end_empregado FROM tb_empregado JOIN tb_dependente ON idt_empregado = cod_empregado GROUP BY idt_empregado;
  61.  
  62. SELECT nme_empregado, group_concat(nme_dependente, ' (R$:', vlr_salario_empregado * 0.05, ')') AS 'valor para dependente' FROM tb_empregado JOIN tb_dependente ON idt_empregado = cod_empregado GROUP BY idt_empregado;
  63.  
  64. -- QUESTÕES 4 a 7 --
  65.  
  66. CREATE TABLE tb_produto(
  67.     idt_produto INT PRIMARY KEY AUTO_INCREMENT,
  68.     nme_produto VARCHAR(50) NOT NULL,
  69.     vlr_produto DECIMAL(8,2) NOT NULL,
  70.     qtd_produto INT
  71. );
  72.  
  73. CREATE TABLE tb_cliente(
  74.     idt_cliente INT PRIMARY KEY AUTO_INCREMENT,
  75.     nme_cliente VARCHAR(50) NOT NULL,
  76.     cpf_cliente CHAR(14) NOT NULL,
  77.     end_cliente TEXT NOT NULL,
  78.     tel_cliente VARCHAR(20) NOT NULL
  79. );
  80.  
  81. CREATE TABLE ta_item(
  82.     idt_item INT PRIMARY KEY AUTO_INCREMENT,
  83.     qtd_item INT NOT NULL,
  84.     pct_desconto_item INT,
  85.     cod_cliente INT NOT NULL,
  86.     cod_produto INT NOT NULL,
  87.     dti_compra_item DATETIME NOT NULL,
  88.     CONSTRAINT fk_i_produto FOREIGN KEY (cod_produto) REFERENCES tb_produto(idt_produto),
  89.     CONSTRAINT fk_i_cliente FOREIGN KEY (cod_cliente) REFERENCES tb_cliente(idt_cliente)
  90. );
  91.  
  92. INSERT INTO tb_produto VALUES (1, 'Monitor', 500.00, 5), (2, 'Mouse', 150.00, 5), (3, 'Teclado', 230.00, 5),
  93. (4, 'Headphone', 200.00, 5), (5, 'Caixa de som', 100.00, 5), (6, 'Computador', 1500.00, 5), (7, 'Notebook', 2000.00, 5);
  94.  
  95. SELECT * FROM tb_produto;
  96.  
  97. INSERT INTO tb_cliente VALUES (1, 'José Almeida', '030.413.025-54', 'SQSW 301 BL A APT 502', '(61)983429876'),
  98. (2, 'Victor Monteiro', '394.563.567-27','SQN 309 BL E APT 401', '(61)997781463'),
  99. (3, 'Raissa Matos', '746.837.273-93', 'Shis Qi 13, entrada conj. 8/9, Bloco D APT 303', '(61)984687342'),
  100. (4, 'Laura Gonçalves', '134.756.203-82', 'SQN 205 BLC C APT 302', '(61)983475692'),
  101. (5, 'Mauro Teles', '653.156.129-13', 'SHCGN 706 Norte, Conjunto A, Bloco B, APT 202', '(61)983573184'),
  102. (6, 'Paulo Fontes', '423.745.831-72', 'SQN 504 BLC G APT 302', '(61)983985692'),
  103. (7, 'Guilherme ALves', '510.123.717-03', 'SQN 105 BL A APT 402' , '(61)981564359'),
  104. (8, 'Thiago Borges', '913.814.745-15', 'SQSW 304 BL B APT 406', '(61)984761374');
  105.  
  106. SELECT * FROM tb_cliente;
  107.  
  108. -- Nova compra --
  109. INSERT INTO ta_item VALUES (1, 2, 5, 1, 2, str_to_date('03/04/17 20:07:30' , '%m/%d/%y %H:%i:%s'));
  110.  
  111. SELECT nme_cliente, idt_item AS 'número da compra', qtd_item, nme_produto, group_concat('R$:', qtd_item * vlr_produto) AS 'preço total', group_concat(pct_desconto_item, '%') AS 'desconto', group_concat('R$:', qtd_item * vlr_produto - ((pct_desconto_item/100) * (qtd_item * vlr_produto))) AS 'valor a ser pago'  FROM tb_produto JOIN tb_cliente JOIN ta_item ON cod_produto = idt_produto AND  cod_cliente = idt_cliente  WHERE idt_item = 1;
  112.  
  113. UPDATE tb_produto SET qtd_produto = (qtd_produto - 2) WHERE idt_produto = 2;
  114.  
  115. SELECT * FROM tb_produto;
  116.  
  117. -- Alterando quantidade de itens --
  118. UPDATE ta_item SET qtd_item = (qtd_item + 3), dti_compra_item = str_to_date('03/04/17 20:09:25' , '%m/%d/%y %H:%i:%s') WHERE idt_item = 1;
  119.  
  120. SELECT nme_cliente, idt_item AS 'número da compra', qtd_item, nme_produto, group_concat('R$:', qtd_item * vlr_produto) AS 'preço total', group_concat(pct_desconto_item, '%') AS 'desconto', group_concat('R$:', qtd_item * vlr_produto - ((pct_desconto_item/100) * (qtd_item * vlr_produto))) AS 'valor a ser pago'  FROM tb_produto JOIN tb_cliente JOIN ta_item ON cod_produto = idt_produto AND  cod_cliente = idt_cliente  WHERE idt_item = 1;
  121.  
  122. UPDATE tb_produto SET qtd_produto = (qtd_produto - 3) WHERE idt_produto = 2;
  123.  
  124. SELECT * FROM tb_produto;
  125.  
  126. -- Cancelando a compra --
  127.  
  128. UPDATE ta_item SET qtd_item = 0 WHERE idt_item  = 1;
  129.  
  130. SELECT nme_cliente, idt_item AS 'número da compra', qtd_item, nme_produto, group_concat('R$:', qtd_item * vlr_produto) AS 'preço total', group_concat(pct_desconto_item, '%') AS 'desconto', group_concat('R$:', qtd_item * vlr_produto - ((pct_desconto_item/100) * (qtd_item * vlr_produto))) AS 'valor a ser pago'  FROM tb_produto JOIN tb_cliente JOIN ta_item ON cod_produto = idt_produto AND  cod_cliente = idt_cliente  WHERE idt_item = 1;
  131.  
  132. UPDATE tb_produto SET qtd_produto = (qtd_produto + 5) WHERE idt_produto = 2;
  133.  
  134. SELECT * FROM tb_produto;
  135.  
  136. -- Selects --
  137.  
  138. -- QUESTÕES DE 8 a 10 --
  139.  
  140. CREATE TABLE tb_time(
  141.     idt_time INT PRIMARY KEY AUTO_INCREMENT,
  142.     nme_time VARCHAR(50) NOT NULL,
  143.     dta_fundacao_time DATE NOT NULL
  144. );
  145.  
  146. CREATE TABLE td_posicao(
  147.     idt_posicao INT PRIMARY KEY AUTO_INCREMENT,
  148.     nme_posicao VARCHAR(50) NOT NULL
  149. );
  150.  
  151. CREATE TABLE td_tipo_ocorrencia(
  152.     idt_tipo_ocorrencia INT PRIMARY KEY AUTO_INCREMENT,
  153.     nme_tipo_ocorrencia VARCHAR(50) NOT NULL
  154. );    
  155.  
  156. CREATE TABLE tb_estadio(
  157.     idt_estadio INT PRIMARY KEY AUTO_INCREMENT,
  158.     nme_estadio VARCHAR(50) NOT NULL
  159. );    
  160.  
  161. CREATE TABLE tb_jogador(
  162.     idt_jogador INT PRIMARY KEY AUTO_INCREMENT,
  163.     nme_jogador VARCHAR(50) NOT NULL,
  164.     dta_nascimento_jogador DATE NOT NULL,
  165.     cod_posicao INT NOT NULL,
  166.     CONSTRAINT fk_jgd_posicao FOREIGN KEY (cod_posicao) REFERENCES td_posicao(idt_posicao)
  167. );
  168.  
  169. CREATE TABLE tb_contrato(
  170.     idt_contrato INT PRIMARY KEY AUTO_INCREMENT,
  171.     dta_ini_contrato DATE NOT NULL,
  172.     dta_fim_contato DATE NOT NULL,
  173.     cod_time INT NOT NULL,
  174.     cod_jogador INT NOT NULL,
  175.     CONSTRAINT fk_c_time FOREIGN KEY (cod_time) REFERENCES tb_time(idt_time),
  176.     CONSTRAINT fk_c_jogador FOREIGN KEY (cod_jogador) REFERENCES tb_jogador(idt_jogador)
  177. );
  178.  
  179. CREATE TABLE tb_jogo(
  180.     idt_jogo INT PRIMARY KEY AUTO_INCREMENT,
  181.     dti_jogo DATETIME NOT NULL,
  182.     cod_estadio INT NOT NULL,
  183.     cod_time_mandante INT NOT NULL,
  184.     cod_time_visitante INT NOT NULL,
  185.     CONSTRAINT fk_j_estadio FOREIGN KEY (cod_estadio) REFERENCES tb_estadio(idt_estadio),
  186.     CONSTRAINT fk_j_time_mandante FOREIGN KEY (cod_time_mandante) REFERENCES tb_time(idt_time),
  187.     CONSTRAINT fk_j_time_visitante FOREIGN KEY (cod_time_visitante) REFERENCES tb_time(idt_time)
  188. );
  189.  
  190. CREATE TABLE ta_ocorrencia(
  191.     idt_ocorrencia INT PRIMARY KEY AUTO_INCREMENT,
  192.     min_ocorrrencia INT NOT NULL,
  193.     cod_tipo_ocorrencia INT NOT NULL,
  194.     cod_jogador INT NOT NULL,
  195.     cod_jogo INT NOT NULL,
  196.     CONSTRAINT fk_o_tipo_ocorrencia FOREIGN KEY (cod_tipo_ocorrencia) REFERENCES td_tipo_ocorrencia(idt_tipo_ocorrencia),
  197.     CONSTRAINT fk_o_jogador FOREIGN KEY (cod_jogador) REFERENCES tb_jogador(idt_jogador),
  198.     CONSTRAINT fk_o_jogo FOREIGN KEY (cod_jogo) REFERENCES tb_jogo(idt_jogo)
  199. );
  200.  
  201. INSERT INTO tb_time VALUES (1, 'Atlético Mineiro', '1908-03-25'), (2, 'Vasco', '1898-08-21'), (3, 'Grêmio', '1903-09-15'), (4, 'São Paulo', '1930-01-25'), (5, 'Chapecoense', '1973-05-10'), (6, 'Internacional', '1909-04-04'), (7, 'América-MG', '1912-04-30'), (8, 'Fluminense', '1902-07-21');
  202.  
  203. SELECT * FROM tb_time;
  204.  
  205. INSERT INTO td_posicao VALUES (1, 'Goleiro'), (2, 'Zagueiro'), (3, 'Lateral'), (4, 'Meio-campo'), (5, 'Atacante');
  206.  
  207. SELECT * FROM td_posicao;
  208.  
  209. INSERT INTO td_tipo_ocorrencia VALUES (1, 'Cartão Amarelo'), (2, 'Cartão Vermelho'), (3, 'Gol'), (4, 'Assistência');
  210.  
  211. SELECT * FROM td_posicao;
  212.  
  213. INSERT INTO tb_estadio VALUES (1, 'Independência'), (2, 'São Januário'), (3, 'Arena do Grêmio'), (4, 'Morumbi'), (5, 'Arena Condá'), (6, 'Beira-rio'), (7, 'Maracanã');
  214.  
  215. SELECT * FROM tb_estadio;
  216.  
  217. INSERT INTO tb_jogador VALUES (1, 'Victor', '1983-01-21', 1), (2, 'Leonardo Silva', '1979-06-22', 2), (3, 'Gabriel', '1995-03-14', 2), (4, 'Marcos Rocha', '1988-12-11', 3),
  218. (5, 'Fábio Santos', '1985-09-16', 3), (6, 'Rafael Carioca', '1989-06-18', 4),
  219. (7, 'Elias', '1985-05-16', 4), (8, 'Robinho', '1984-01-25', 5), (9, 'Otero', '1992-11-09', 5), (10, 'Fred', '1983-10-03', 5), (11, 'Danilo', '1991-08-17', 4),
  220. (12, 'Martin Silva'), (13, 'Gilberto'), (14, 'Rafael Marques'), (15, 'Rodrigo'), (16, 'Henrique'), (17, 'Jean'), (18, 'Douglas Luiz' ), (19, 'Nenê'), (20, 'Escudero'), (21, 'Kelvin'), (22, 'Thalles'),
  221. (23, 'Marcelo Grohe'), (24, 'Léo Moura'), (25, 'Rafael Thyere'), (26, 'Kannemann'), (27, 'Marcelo Oliveira'), (28, 'Jailson'), (29, 'Michel'), (30, 'Ramiro'), (31, 'Bolaños'), (32, 'Pedro Rocha'), (33, 'Luan'),
  222. (34, 'Denis'), (35, 'Bruno'),(36, 'Breno'), (37, 'Rodrigo Caio'), (38, 'Júnior Tavares'), (39, 'Thiago Mendes'), (40, 'João Schimidt'), (41, 'Cícero'), (42, 'Cueva'), (43, 'Luiz Araújo'), (44, 'Lucas Pratto'),
  223. (45, 'Danilo'), (46, 'Caramelo'), (47, 'Neto'), (48, 'Thiego'), (49, 'Dener Assunção'), (50, 'Josimar'), (51, 'Gil'), (52, 'Cleber Santana'), (53, 'Tiaguinho'), (54, 'Kempes'), (55, 'Ananias'),
  224. (56, 'Danilo Fernandes'), (57, 'Alemão'), (58, 'Léo Ortiz'), (59, 'Paulão'), (60, 'Iago'), (61, 'Anselmo'), (62, 'Valdívia'), (63, 'D`Alessandro'), (64, 'Uendel'), (65, 'Nico López'), (66, 'Brenner'),
  225. (67, 'João Ricardo'), (68, 'Auro'), (69, 'Rafael Lima'), (70, 'Messias'), (71, 'Pará'), (72, 'Pará'), (73, 'Ernandes'), (74, 'Juninho'), (75, 'Gérson Magrão'), (76, 'Renan Oliveira'), (77, 'Gustavo Blanco'),
  226. (78, 'Júlio César'), (79, 'Lucas'), (80, 'Renato Chaves'), (81, 'Henrique'), (82, 'Léo'), (83, 'Orejuela'), (84, 'Douglas'), (85, 'Sornoza'), (86, 'Richarlison'), (87, 'Henrique Dourado'), (88, 'Wellington Silva');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement