· 6 years ago · Dec 26, 2019, 04:56 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5-- -----------------------------------------------------
6-- Schema mydb
7-- -----------------------------------------------------
8-- -----------------------------------------------------
9-- Schema SportHealthSolutions
10-- -----------------------------------------------------
11
12-- -----------------------------------------------------
13-- Schema SportHealthSolutions
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `SportHealthSolutions` ;
16USE `SportHealthSolutions` ;
17
18-- -----------------------------------------------------
19-- Table `SportHealthSolutions`.`MODALIDADE`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`MODALIDADE` (
22 `id_modalidade` INT(11) NOT NULL,
23 `designacao` VARCHAR(45) NOT NULL,
24 PRIMARY KEY (`id_modalidade`))
25ENGINE = InnoDB
26DEFAULT CHARACTER SET = utf8;
27
28
29-- -----------------------------------------------------
30-- Table `SportHealthSolutions`.`ATLETA`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`ATLETA` (
33 `id_atleta` INT(11) NOT NULL,
34 `nome` VARCHAR(100) NOT NULL,
35 `morada` VARCHAR(250) NOT NULL,
36 `codigo_postal` VARCHAR(8) NOT NULL,
37 `data_nascimento` DATE NOT NULL,
38 `peso` DECIMAL(5,2) NOT NULL,
39 `altura` INT(11) NOT NULL,
40 #`id_modalidade` INT(11) NOT NULL,
41 PRIMARY KEY (`id_atleta`),
42 #INDEX `fk_mod_idx` (`id_modalidade` ASC),
43 INDEX `fk_cod_idx` (`codigo_postal` ASC),
44 #CONSTRAINT `fk_modalidade`
45 #FOREIGN KEY (`id_modalidade`)
46 #REFERENCES `SportHealthSolutions`.`MODALIDADE` (`id_modalidade`),
47 CONSTRAINT `fk_cod_postal`
48 FOREIGN KEY (`codigo_postal`)
49 REFERENCES `SportHealthSolutions`.`CODIGO_POSTAL` (`codigo_postal`))
50ENGINE = InnoDB
51DEFAULT CHARACTER SET = utf8;
52
53
54-- -----------------------------------------------------
55-- Table `SportHealthSolutions`.`CATEGORIA`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`CATEGORIA` (
58 `id_categoria` INT(11) NOT NULL,
59 `designacao` VARCHAR(100) NOT NULL,
60 `salario_base` DECIMAL(7,2) NOT NULL,
61 PRIMARY KEY (`id_categoria`))
62ENGINE = InnoDB
63DEFAULT CHARACTER SET = utf8;
64
65
66-- -----------------------------------------------------
67-- Table `SportHealthSolutions`.`ESPECIALIDADE`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`ESPECIALIDADE` (
70 `id_especialidade` INT(11) NOT NULL,
71 `designacao` VARCHAR(100) NOT NULL,
72 PRIMARY KEY (`id_especialidade`))
73ENGINE = InnoDB
74DEFAULT CHARACTER SET = utf8;
75
76
77-- -----------------------------------------------------
78-- Table `SportHealthSolutions`.`FUNCIONARIO`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`FUNCIONARIO` (
81 `id_funcionario` INT(11) NOT NULL,
82 `id_categoria` INT(11) NOT NULL,
83 `id_especialidade` INT(11) NOT NULL,
84 `nome` VARCHAR(100) NOT NULL,
85 `morada` VARCHAR(250) NOT NULL,
86 `codigo_postal` VARCHAR(8) NOT NULL,
87 `data_nascimento` DATE NOT NULL,
88 `data_inicio_servico` DATE NOT NULL,
89 PRIMARY KEY (`id_funcionario`),
90 INDEX `fk_cat_idx` (`id_categoria` ASC),
91 INDEX `fk_esp_idx` (`id_especialidade` ASC),
92 INDEX `fk_cod_idx` (`codigo_postal` ASC),
93 CONSTRAINT `fk_categoria`
94 FOREIGN KEY (`id_categoria`)
95 REFERENCES `SportHealthSolutions`.`CATEGORIA` (`id_categoria`),
96 CONSTRAINT `fk_especialidade`
97 FOREIGN KEY (`id_especialidade`)
98 REFERENCES `SportHealthSolutions`.`ESPECIALIDADE` (`id_especialidade`),
99 CONSTRAINT `fk_cod_postal2`
100 FOREIGN KEY (`codigo_postal`)
101 REFERENCES `SportHealthSolutions`.`CODIGO_POSTAL` (`codigo_postal`))
102ENGINE = InnoDB
103DEFAULT CHARACTER SET = utf8;
104
105
106-- -----------------------------------------------------
107-- Table `SportHealthSolutions`.`CONSULTA`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`CONSULTA` (
110 `id_funcionario` INT(11) NOT NULL,
111 `id_atleta` INT(11) NOT NULL,
112 `data_hora` DATETIME NOT NULL,
113 `id_especialidade` INT NOT NULL,
114 `preco` DECIMAL(6,2) DEFAULT 0,
115 `estado` CHAR(1) NOT NULL,
116 PRIMARY KEY (`id_funcionario`, `id_atleta`, `data_hora`),
117 INDEX `fk_atleta_idx` (`id_atleta` ASC),
118 CONSTRAINT `fk_atleta2`
119 FOREIGN KEY (`id_atleta`)
120 REFERENCES `SportHealthSolutions`.`ATLETA` (`id_atleta`),
121 CONSTRAINT `fk_funcionario2`
122 FOREIGN KEY (`id_funcionario`)
123 REFERENCES `SportHealthSolutions`.`FUNCIONARIO` (`id_funcionario`),
124 CONSTRAINT `fk_especialidade3`
125 FOREIGN KEY (`id_especialidade`)
126 REFERENCES `SportHealthSolutions`.`ESPECIALIDADE` (`id_especialidade`))
127ENGINE = InnoDB
128DEFAULT CHARACTER SET = utf8;
129
130
131-- -----------------------------------------------------
132-- Table `SportHealthSolutions`.`EXAME`
133-- -----------------------------------------------------
134CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`EXAME` (
135 `id_funcionario` INT(11) NOT NULL,
136 `id_atleta` INT(11) NOT NULL,
137 `data_hora` DATETIME NOT NULL,
138 `preco` DECIMAL(8,2) DEFAULT 0,
139 `resultado` VARCHAR(500) NULL DEFAULT NULL,
140 `id_especialidade` INT NOT NULL,
141 `comentario` VARCHAR(200) NULL DEFAULT NULL,
142 `estado` CHAR(1) NOT NULL,
143 PRIMARY KEY (`id_funcionario`, `data_hora`, `id_atleta`),
144 INDEX `fk_atleta_idx` (`id_atleta` ASC),
145 CONSTRAINT `fk_atleta`
146 FOREIGN KEY (`id_atleta`)
147 REFERENCES `SportHealthSolutions`.`ATLETA` (`id_atleta`),
148 CONSTRAINT `fk_funcionario1`
149 FOREIGN KEY (`id_funcionario`)
150 REFERENCES `SportHealthSolutions`.`FUNCIONARIO` (`id_funcionario`),
151 CONSTRAINT `fk_especialidade4`
152 FOREIGN KEY (`id_especialidade`)
153 REFERENCES `SportHealthSolutions`.`ESPECIALIDADE` (`id_especialidade`))
154ENGINE = InnoDB
155DEFAULT CHARACTER SET = utf8;
156
157-- -----------------------------------------------------
158-- Table `Clinica`.`CODIGO_POSTAL`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`CODIGO_POSTAL` (
161 `codigo_postal` VARCHAR(8) NOT NULL,
162 `localidade` VARCHAR(100) NOT NULL,
163 PRIMARY KEY (`codigo_postal`))
164ENGINE = InnoDB
165DEFAULT CHARACTER SET = utf8;
166
167/*
168CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`MATERIAL` (
169 `id_material` INT(11) NOT NULL,
170 `stock` INT(11) NOT NULL DEFAULT 0,
171 `custo` DECIMAL(10,2),
172 `proxima_verificacao` DATE,
173 PRIMARY KEY (`id_material`))
174ENGINE = InnoDB
175DEFAULT CHARACTER SET = utf8;
176*/
177
178CREATE TABLE IF NOT EXISTS `SportHealthSolutions`.`ATLETA_MODALIDADE` (
179 `id_atleta` INT(11) NOT NULL,
180 `id_modalidade` INT(11) NOT NULL,
181 `data_inicio` DATE NOT NULL,
182PRIMARY KEY (`id_atleta`, `id_modalidade`),
183CONSTRAINT `fk_atleta3`
184 FOREIGN KEY (`id_atleta`)
185 REFERENCES `SportHealthSolutions`.`ATLETA` (`id_atleta`),
186CONSTRAINT `fk_modalidade2`
187 FOREIGN KEY (`id_modalidade`)
188 REFERENCES `SportHealthSolutions`.`MODALIDADE` (`id_modalidade`))
189ENGINE = InnoDB
190DEFAULT CHARACTER SET = utf8;
191
192SET SQL_MODE=@OLD_SQL_MODE;
193SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
194SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
195
196
197
198SET GLOBAL log_bin_trust_function_creators = 1;
199
200/* SAMPLE DO POVOAMENTO DAS TABELAS */
201
202
203INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4750-002', 'BARCELOS');
204INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4700-001', 'BRAGA');
205INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4700-002', 'BRAGA');
206INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4700-003', 'BRAGA');
207INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4700-004', 'BRAGA');
208INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4700-005', 'BRAGA');
209INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4780-302', 'SANTO TIRSO');
210INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4780-306', 'SANTO TIRSO');
211INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4780-325', 'SANTO TIRSO');
212INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4780-405', 'SANTO TIRSO');
213INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4765-076', 'VILA NOVA DE FAMALICÃO');
214INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4765-125', 'VILA NOVA DE FAMALICÃO');
215INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4765-184', 'VILA NOVA DE FAMALICÃO');
216INSERT INTO SportHealthSolutions.CODIGO_POSTAL (codigo_postal, localidade) VALUES ('4765-304', 'VILA NOVA DE FAMALICÃO');
217
218
219INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(1, 'Oftalmologia');
220INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(2, 'Ortopedia');
221INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(3, 'Cardiologia');
222INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(4, 'Neurologia');
223INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(5, 'Radiologia');
224INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(6, 'Análises Clínicas');
225INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(7, 'Recursos Humanos');
226INSERT INTO SportHealthSolutions.ESPECIALIDADE (id_especialidade, designacao) VALUES(8, 'Urologia');
227
228INSERT INTO SportHealthSolutions.CATEGORIA (id_categoria, designacao, salario_base) VALUES (1, 'Medico', 1000);
229INSERT INTO SportHealthSolutions.CATEGORIA (id_categoria, designacao, salario_base) VALUES (2, 'Técnico Especializado', 800);
230INSERT INTO SportHealthSolutions.CATEGORIA (id_categoria, designacao, salario_base) VALUES (3, 'Enfermeiro', 900);
231INSERT INTO SportHealthSolutions.CATEGORIA (id_categoria, designacao, salario_base) VALUES (4, 'Administrativo', 650);
232
233INSERT INTO SportHealthSolutions.FUNCIONARIO (id_funcionario,id_categoria, id_especialidade, nome, morada, codigo_postal,data_nascimento, data_inicio_servico) VALUES (123456, 3, 6, 'Jose Maria','Rua de Cima n.º 223, 5.º DTO', '4750-002','1970-01-23', '1990-02-23');
234INSERT INTO SportHealthSolutions.FUNCIONARIO (id_funcionario,id_categoria, id_especialidade, nome, morada, codigo_postal,data_nascimento, data_inicio_servico) VALUES (223456, 2, 5, 'Antonio Pinto','Rua de Baixo n.º 125, 2.º ESQ', '4700-001','1991-11-21', '1990-01-23');
235INSERT INTO SportHealthSolutions.FUNCIONARIO (id_funcionario,id_categoria, id_especialidade, nome, morada, codigo_postal,data_nascimento, data_inicio_servico) VALUES (323456, 1, 4, 'Manuel Maria Neves','Rua de Sul n.º 789, 9.º DTO', '4700-002','1964-03-24', '1979-07-23');
236INSERT INTO SportHealthSolutions.FUNCIONARIO (id_funcionario,id_categoria, id_especialidade, nome, morada, codigo_postal,data_nascimento, data_inicio_servico) VALUES (423456, 1, 3, 'Pedro Pinto Silva','Rua de Este n.º 456, 1.º DTO', '4780-302','1978-02-13', '1980-01-23');
237INSERT INTO SportHealthSolutions.FUNCIONARIO (id_funcionario,id_categoria, id_especialidade, nome, morada, codigo_postal,data_nascimento, data_inicio_servico) VALUES (523456, 4, 7, 'Marta Catarina','Rua de Cima n.º 120, 3.º ESQ', '4780-405', '1960-09-11', '1971-05-23');
238INSERT INTO SportHealthSolutions.FUNCIONARIO (id_funcionario,id_categoria, id_especialidade, nome, morada, codigo_postal,data_nascimento, data_inicio_servico) VALUES (199999, 2, 1, 'João Rodrigues','Rua da Universidade n.º 12, 4.º ESQ', '4765-076', '1998-09-11', '2010-05-23');
239
240INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (1,'Salto em Comprimento');
241INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (2,'Lançamento do peso');
242INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (3,'Triplo Salto');
243INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (4,'1000m');
244INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (5,'400m');
245INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (6,'Salto à Vara');
246INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (7,'1000m Estafeta');
247INSERT INTO SportHealthSolutions.MODALIDADE (id_modalidade,designacao) VALUES (8,'50km marcha');
248
249INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (123456, 'Manuel Marques', 'Rua de Cima n.º 753, 4.º DTO', '4700-001', '1970-01-23',50.20,178);
250INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (223456, 'Pedro Paulo', 'Rua de Sul n.º 89, 5.º DTO', '4700-002','1991-04-22',50.20,156);
251INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (323456, 'Maria Jose Silva', 'Rua de Este n.º 765, 5.º ESQ', '4700-005', '2012-11-01',50.20,180);
252INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (423456, 'Jose Manuel Marques', 'Rua de Oeste n.º 324, 5.º DTO', '4780-325', '1970-09-26',50.20,134);
253INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (523456, 'Jacinto Homem', 'Rua de Este n.º 456, 5.º ESQ', '4780-405', '1982-12-23',50.20,178);
254INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (623456, 'Sara Catarina Costa', 'Rua de Sul n.º 256, 9.º DTO', '4780-306', '1986-11-02',50.20,178);
255INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (723456, 'Jose Carlos Moreira', 'Rua de Oeste n.º 96, 4.º DTO', '4765-125', '1970-09-07',50.20,164);
256INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (823456, 'Joana Silva', 'Rua de Este n.º 285, 2.º ESQ', '4765-184', '1997-04-03',50.20,192);
257INSERT INTO SportHealthSolutions.ATLETA (id_atleta, nome, morada, codigo_postal,data_nascimento,peso,altura) VALUES (923456, 'Rosa Maria Carvalho', 'Rua de Sul n.º 48, 7.º DTO', '4765-304', '2001-11-13',50.20,169);
258
259INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (123456, 123456, '2016-01-23 14:30', 203, 'Saudável', 6,'R');
260INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 223456, '2016-01-23 14:30', 221, 'Saudável', 5,'R');
261INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 223456, '2016-03-21 14:30', 162, 'Saudável', 5,'R');
262INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (423456, 123456, '2016-06-01 08:30', 123, 'Saudável', 3,'R');
263INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (423456, 323456, '2015-06-13 15:30', 280, 'Saudável', 3,'R');
264INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (323456, 323456, '2016-05-21 16:30', 393, 'Saudável', 4,'R');
265INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 523456, '2015-04-27 17:30', 440, 'Saudável', 5,'R');
266INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (123456, 123456, '2015-04-27 17:30', 230, 'Saudável', 6,'R');
267INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (123456, 423456, '2016-02-27 14:30', 260, 'Saudável', 6,'R');
268INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 223456, '2016-01-12 15:30', 228, 'Saudável', 5,'R');
269INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 223456, '2015-02-11 11:30', 103, 'Saudável', 5,'R');
270INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (523456, 623456, '2015-03-01 14:30', 173, 'Saudável', 7,'R');
271INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 123456, '2015-09-30 14:30', 230, 'Saudável', 5,'R');
272INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (323456, 623456, '2015-08-23 11:30', 228, 'Saudável', 4,'R');
273INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (123456, 623456, '2016-01-23 15:30', 216, 'Saudável', 6,'R');
274INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 723456, '2016-03-22 10:30', 225, 'Saudável', 5,'R');
275INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 623456, '2016-03-21 15:30', 191, 'Saudável', 5,'R');
276INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (423456, 623456, '2016-06-01 09:30', 153, 'Saudável', 3,'R');
277INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (423456, 923456, '2015-06-13 16:30', 240, 'Saudável', 3,'R');
278INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (323456, 923456, '2016-05-21 17:30', 362, 'Saudável', 4,'R');
279INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, preco, resultado, id_especialidade,estado) VALUES (223456, 823456, '2015-04-27 18:30', 420, 'Saudável', 5,'R');
280INSERT INTO SportHealthSolutions.EXAME (id_funcionario, id_atleta, data_hora, id_especialidade,estado) VALUES (223456, 823456, '2020-01-27 18:30',2,'A');
281
282INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (123456, 123456, '2016-01-23 14:30',6, 203,'R');
283INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 223456, '2016-01-23 14:30',5, 221,'R');
284INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 223456, '2016-03-21 14:30',5, 162,'R');
285INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (423456, 123456, '2016-06-01 08:30',3, 123,'R');
286INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (423456, 323456, '2015-06-13 15:30',3, 280,'R');
287INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (323456, 323456, '2016-05-21 16:30',4, 393,'R');
288INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 523456, '2015-04-27 17:30',5, 440,'R');
289INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (123456, 123456, '2015-04-27 17:30',6, 230,'R');
290INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (123456, 423456, '2016-02-27 14:30',6, 260,'R');
291INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 223456, '2016-01-12 15:30',5, 228,'R');
292INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 223456, '2015-02-11 11:30',5, 103,'R');
293INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 123456, '2015-09-30 14:30',5, 230,'R');
294INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (323456, 623456, '2015-08-23 11:30',4, 228,'R');
295INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (123456, 623456, '2016-01-23 15:30',6, 216,'R');
296INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 723456, '2016-03-22 10:30',5, 225,'R');
297INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 623456, '2016-03-21 15:30',5, 191,'R');
298INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (423456, 623456, '2016-06-01 09:30',3, 153,'R');
299INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (423456, 923456, '2015-06-13 16:30',3, 240,'R');
300INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (323456, 923456, '2016-05-21 17:30',4, 362,'R');
301INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (223456, 823456, '2015-04-27 18:30',5, 420,'R');
302INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade,estado) VALUES (223456, 823456, '2020-01-15 18:30',5,'A');
303INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade,estado) VALUES (323456, 623456, '2020-02-02 14:30',4,'A');
304INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade,estado) VALUES (123456, 423456, '2018-02-27 14:30',3,'D');
305INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade,estado) VALUES (223456, 623456, '2018-02-27 14:30',5,'D');
306INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (123456, 123456, '2018-02-24 14:30',6, 203,'R');
307INSERT INTO SportHealthSolutions.CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade, preco,estado) VALUES (123456, 123456, '2019-01-5 14:30',6, 203,'R');
308
309
310INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (123456,1,'2015-04-20');
311INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (123456,2,'2015-03-20');
312INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (123456,4,'2014-07-20');
313INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (223456,1,'2015-06-20');
314INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (223456,5,'2012-08-20');
315INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (223456,7,'2016-09-20');
316INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (223456,8,'2017-02-20');
317INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (323456,2,'2012-03-20');
318INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (423456,4,'2013-04-20');
319INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (523456,1,'2015-02-20');
320INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (623456,5,'2015-05-20');
321INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (723456,2,'2015-06-20');
322INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (523456,7,'2015-07-20');
323INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (823456,3,'2015-01-20');
324INSERT INTO SportHealthSolutions.ATLETA_MODALIDADE(id_atleta,id_modalidade,data_inicio) VALUES (923456,6,'2015-02-20');
325
326# cria a função idade que retorna a diferença de tempo
327DELIMITER //
328DROP FUNCTION IF EXISTS idade //
329
330CREATE FUNCTION idade(dta date) RETURNS int(11)
331BEGIN
332RETURN TIMESTAMPDIFF(YEAR, dta, CURDATE());
333END //
334DELIMITER ;
335
336# cria uma função que verifica se um funcionario deu consulta ou exame
337DELIMITER //
338DROP FUNCTION IF EXISTS deu_consulta_exame //
339
340
341
342CREATE FUNCTION deu_consulta_exame(id_fun INT(11)) RETURNS tinyint(1)
343BEGIN
344DECLARE contador INT; DECLARE contador2 INT;
345SELECT COUNT(*) INTO contador FROM CONSULTA where id_funcionario=id_fun;
346SELECT COUNT(*) INTO contador2 FROM EXAME where id_funcionario=id_fun;
347RETURN (contador>0 OR contador2>0);
348END //
349DELIMITER ;
350
351DELIMITER //
352DROP PROCEDURE IF EXISTS desmarcar_consulta //
353CREATE PROCEDURE desmarcar_consulta(IN id_atl INT, IN datac DATETIME)
354BEGIN
355UPDATE CONSULTA SET estado = 'D' where id_atleta =id_atl and data_hora=datac;
356END //
357DELIMITER ;
358
359
360# adicionar consulta + procedimento
361
362DELIMITER //
363#DROP FUNCTION IF EXISTS jaexisteconsulta_exame //
364CREATE FUNCTION jaexisteconsulta_exame(IN datac DATETIME,IN id_func INT ,IN id_atl INT) RETURNS tinyint
365BEGIN
366DECLARE exames INT; DECLARE consultas INT;
367SELECT COUNT * INTO consultas FROM CONSULTA where data_hora BETWEEN BETWEEN datac - INTERVAL 15 MINUTE and datac + INTERVAL 15 MINUTE and id_funcionario = id_func and id_atleta = id_atl;
368SELECT COUNT * INTO exames FROM EXAME where data_hora BETWEEN datac - INTERVAL 15 MINUTE and datac + INTERVAL 15 MINUTE and id_funcionario = id_func and id_atleta = id_atl;
369RETURN exames>0 OR consultas>0;
370END
371DELIMITER ;
372
373DELIMITER //
374DROP PROCEDURE IF EXISTS marcar_consulta //
375CREATE PROCEDURE `marcar_consulta_exame`(IN id_func INT,IN id_atleta INT, IN datac DATETIME)
376BEGIN
377DECLARE id INT; DECLARE temp INT;
378SET temp = jaexisteconsulta_exame(datac,id_func,id_atleta);
379SELECT id_especialidade INTO id from FUNCIONARIO f where f.id_funcionario=id_func;
380IF !temp and datac > CURDATE() and tipo='C' THEN
381INSERT INTO CONSULTA (id_funcionario, id_atleta, data_hora,id_especialidade,preco,estado)
382VALUES(id_func,id_atleta,datac,id,0,'A');
383ELSEIF ! temp and tipo = 'E' then
384INSERT INTO EXAME (id_funcionario, id_atleta, data_hora,preco,id_especialidade,designacao,estado)
385VALUES(id_func,id_atleta,datac,0,id,"",'A');
386END IF;
387END //
388DELIMITER ;
389
390
391SET SQL_SAFE_UPDATES = 0;
392SET GLOBAL log_bin_trust_function_creators = 1;
393
394# Criação de uma nova coluna (total_faturado) na tabela dos funcionarios
395ALTER TABLE FUNCIONARIO ADD total_faturado DECIMAL(10,2);
396
397# Atualizar a coluna total_facturado dos funcionarios com os dados ja existentes na base de dados
398
399UPDATE FUNCIONARIO fun SET fun.total_faturado = (SELECT SUM(con.preco) FROM CONSULTA con
400where fun.id_funcionario = con.id_funcionario);
401
402# Função que atualiza o total_faturado dado o ID do funcionario, e preço a adicionar
403DELIMITER //
404DROP PROCEDURE IF EXISTS Actualizar_Total_Facturado //
405
406CREATE PROCEDURE Actualizar_Total_Facturado( IN id_fun INT(11), IN preco DECIMAL(10,2))
407BEGIN
408UPDATE FUNCIONARIO fun
409SET total_faturado = total_faturado + preco where fun.id_funcionario = id_fun;
410END //
411DELIMITER ;
412
413 ############################ Criação dos Triggers ############################
414
415 #### Triggers CONSULTAS ####
416
417# Trigger INSERT
418DELIMITER //
419DROP TRIGGER IF EXISTS Consulta_After_Insert //
420
421CREATE TRIGGER Consulta_After_Insert AFTER INSERT ON CONSULTA
422FOR EACH ROW
423BEGIN
424CALL Actualizar_Total_Facturado(new.id_funcionario, new.preco);
425END //
426DELIMITER ;
427
428# Trigger UPDATE
429DELIMITER //
430DROP TRIGGER IF EXISTS Consulta_After_Update //
431
432CREATE TRIGGER Consulta_After_Update AFTER UPDATE ON CONSULTA
433FOR EACH ROW
434BEGIN
435CALL Actualizar_Total_Facturado(old.id_funcionario, old.preco*(-1));
436CALL Actualizar_Total_Facturado(new.id_funcionario, new.preco);
437END //
438DELIMITER ;
439
440# Trigger DELETE
441DELIMITER //
442DROP TRIGGER IF EXISTS Consulta_After_Delete //
443
444CREATE TRIGGER Consulta_After_Delete AFTER DELETE ON CONSULTA
445FOR EACH ROW
446BEGIN
447CALL Actualizar_Total_Facturado(old.id_funcionario, old.preco*(-1));
448END //
449DELIMITER ;
450
451######################################################
452
453 #### Triggers EXAME ####
454
455# Trigger INSERT
456DELIMITER //
457DROP TRIGGER IF EXISTS Exame_After_Insert //
458
459CREATE TRIGGER Exame_After_Insert AFTER INSERT ON EXAME
460FOR EACH ROW
461BEGIN
462CALL Actualizar_Total_Facturado(new.id_funcionario, new.preco);
463END //
464DELIMITER ;
465
466# Trigger UPDATE
467DELIMITER //
468DROP TRIGGER IF EXISTS Exame_After_Update //
469
470CREATE TRIGGER Exame_After_Update AFTER UPDATE ON EXAME
471FOR EACH ROW
472BEGIN
473CALL Actualizar_Total_Facturado(old.id_funcionario, old.preco*(-1));
474CALL Actualizar_Total_Facturado(new.id_funcionario, new.preco);
475END //
476DELIMITER ;
477
478# Trigger DELETE
479DELIMITER //
480DROP TRIGGER IF EXISTS Exame_After_Delete //
481
482CREATE TRIGGER Exame_After_Delete AFTER DELETE ON EXAME
483FOR EACH ROW
484BEGIN
485CALL Actualizar_Total_Facturado(old.id_funcionario, old.preco*(-1));
486END //
487DELIMITER ;
488
489###############################################################################################################
490
491# Criação de uma nova tabela com o valor mensal acumulado para cada atleta
492CREATE TABLE IF NOT EXISTS ATLETA_ACUMULADO_MENSAL(
493 id_atleta INT(11) NOT NULL,
494 ano INT(11) NOT NULL,
495 mes INT(11) NOT NULL,
496 despesa_mensal DECIMAL(8,2) NOT NULL DEFAULT 0.0,
497 PRIMARY KEY (id_atleta,ano,mes),
498 CONSTRAINT fk_paciente2 FOREIGN KEY (id_atleta)
499 REFERENCES ATLETA(id_atleta)
500);
501
502# Povoar a tabela com os valores ja existentes na base de dados (caso a soma der NULL colocamos 0 na tabela)
503INSERT INTO ATLETA_ACUMULADO_MENSAL (id_atleta,ano,mes,despesa_mensal)
504SELECT id_atleta,ano,mes,coalesce(sum(soma),0) as soma FROM
505 (SELECT id_atleta,date_format(data_hora,"%Y") as ano,date_format(data_hora,"%m") as mes,
506 coalesce(sum(preco),0) as soma from CONSULTA c
507 where c.estado='R'
508 GROUP BY id_atleta,ano,mes
509UNION ALL
510 SELECT id_atleta,date_format(data_hora,"%Y") as ano,date_format(data_hora,"%m") as mes,
511 coalesce(sum(preco),0) as soma from EXAME ex
512 where ex.estado='R'
513 GROUP BY id_atleta,ano,mes) as p
514GROUP BY p.id_atleta,ano,mes
515ORDER BY p.id_atleta,ano,mes;
516
517# Função que que atualiza o valor mensal acumulado de um atleta
518DELIMITER //
519DROP PROCEDURE IF EXISTS Actualizar_Atleta_Acumulado_Mensal //
520
521CREATE PROCEDURE Actualizar_Atleta_Acumulado_Mensal(IN id_atl INT(11), IN ano_atl INT(11), IN mes_atl INT(11), IN valor_atl DECIMAL(8,2))
522BEGIN
523DECLARE existe_linha INT(11) DEFAULT 0;
524SELECT COUNT(*) INTO existe_linha FROM ATLETA_ACUMULADO_MENSAL atl_am where atl_am.id_atleta = id_atl AND atl_am.ano = ano_atl AND atl_am.mes = mes_atl;
525IF existe_linha = 1 THEN
526UPDATE ATLETA_ACUMULADO_MENSAL atl_am SET atl_am.despesa_mensal = atl_am.despesa_mensal + valor_atl
527where atl_am.id_atleta = id_atl AND atl_am.ano = ano_atl AND atl_am.mes = mes_atl;
528ELSE
529INSERT INTO ATLETA_ACUMULADO_MENSAL (id_atleta,ano,mes,despesa_mensal) VALUES (id_atl,ano_atl,mes_atl,valor_atl);
530END IF;
531END //
532DELIMITER ;
533
534 ############################ Criação dos Triggers ############################
535
536 #### Triggers CONSULTAS ####
537
538# Trigger INSERT
539DELIMITER //
540DROP TRIGGER IF EXISTS ATLETA_Acumulado_After_Insert //
541
542CREATE TRIGGER ATLETA_Acumulado_After_Insert AFTER INSERT ON CONSULTA
543FOR EACH ROW
544BEGIN
545CALL Actualizar_Atleta_Acumulado_Mensal(new.id_atleta,date_format(new.data_hora,'%Y'),date_format(new.data_hora,'%m'),new.preco);
546END //
547DELIMITER ;
548
549# Trigger UPDATE
550DELIMITER //
551DROP TRIGGER IF EXISTS ATLETA_Acumulado_After_Update //
552
553CREATE TRIGGER ATLETA_Acumulado_After_Update AFTER UPDATE ON CONSULTA
554FOR EACH ROW
555BEGIN
556CALL Actualizar_Atleta_Acumulado_Mensal(old.id_atleta,date_format(old.data_hora,'%Y'),date_format(old.data_hora,'%m'),old.preco*(-1));
557CALL Actualizar_Atleta_Acumulado_Mensal(new.id_atleta,date_format(new.data_hora,'%Y'),date_format(new.data_hora,'%m'),new.preco);
558END //
559DELIMITER ;
560
561# Trigger DELETE
562DELIMITER //
563DROP TRIGGER IF EXISTS ATLETA_Acumulado_After_DELETE //
564
565CREATE TRIGGER ATLETA_Acumulado_After_DELETE AFTER DELETE ON CONSULTA
566FOR EACH ROW
567BEGIN
568CALL Actualizar_Atleta_Acumulado_Mensal(old.id_atleta,date_format(old.data_hora,'%Y'),date_format(old.data_hora,'%m'),old.preco*(-1));END //
569DELIMITER ;
570
571######################################################
572
573 #### Triggers EXAMES ####
574
575# Trigger INSERT
576DELIMITER //
577DROP TRIGGER IF EXISTS ATLETA_Acumulado_After_Insert_Exame //
578
579CREATE TRIGGER ATLETA_Acumulado_After_Insert_Exame AFTER INSERT ON EXAME
580FOR EACH ROW
581BEGIN
582CALL Actualizar_Atleta_Acumulado_Mensal(new.id_atleta,date_format(new.data_hora,'%Y'),date_format(new.data_hora,'%m'),new.preco);
583END //
584DELIMITER ;
585
586# Trigger UPDATE
587DELIMITER //
588DROP TRIGGER IF EXISTS ATLETA_Acumulado_After_Update_Exame //
589
590CREATE TRIGGER ATLETA_Acumulado_After_Update_Exame AFTER UPDATE ON EXAME
591FOR EACH ROW
592BEGIN
593CALL Actualizar_Atleta_Acumulado_Mensal(old.id_atleta,date_format(old.data_hora,'%Y'),date_format(old.data_hora,'%m'),old.preco*(-1));
594CALL Actualizar_Atleta_Acumulado_Mensal(new.id_atleta,date_format(new.data_hora,'%Y'),date_format(new.data_hora,'%m'),new.preco);
595END //
596DELIMITER ;
597
598# Trigger DELETE
599DELIMITER //
600DROP TRIGGER IF EXISTS ATLETA_Acumulado_After_DELETE_Exame //
601
602CREATE TRIGGER ATLETA_Acumulado_After_DELETE_Exame AFTER DELETE ON EXAME
603FOR EACH ROW
604BEGIN
605CALL Actualizar_Atleta_Acumulado_Mensal(old.id_atleta,date_format(old.data_hora,'%Y'),date_format(old.data_hora,'%m'),old.preco*(-1));END //
606DELIMITER ;
607
608# 3 Consultar qual o funcionário(excepto administrativos) que menos facturaram num ano (por exemplo 2015)
609DELIMITER //
610DROP PROCEDURE IF EXISTS Pior_funcionario //
611
612CREATE PROCEDURE Pior_funcionario (IN ano INT)
613BEGIN
614 SELECT fun.id_funcionario,fun.nome,fun.designacao,COALESCE(soma.Soma,0) AS Soma FROM
615 (SELECT id_funcionario,nome,cat.designacao FROM FUNCIONARIO fun,CATEGORIA cat WHERE fun.id_categoria=cat.id_categoria
616 AND cat.designacao!='Administrativo') AS fun
617 LEFT JOIN
618 (SELECT id_funcionario,SUM(Soma) AS Soma FROM (
619 (SELECT id_funcionario,SUM(preco) AS Soma FROM CONSULTA
620 WHERE DATE_FORMAT(data_hora,"%Y")=ano AND estado!='A' GROUP BY id_funcionario
621 UNION ALL
622 SELECT id_funcionario,SUM(preco) AS Soma FROM EXAME
623 WHERE DATE_FORMAT(data_hora,"%Y")=ano AND estado!='A' GROUP BY id_funcionario
624 ORDER BY id_funcionario)) p
625 GROUP BY id_funcionario) AS soma
626 ON fun.id_funcionario=soma.id_funcionario ORDER BY Soma LIMIT 1;
627END //
628DELIMITER ;
629
630
631# 8 Especialidades que mais facturaram em determinado ano (neste exemplo 2015)
632DELIMITER //
633DROP PROCEDURE IF EXISTS Mais_Faturado_Especialidade //
634
635CREATE PROCEDURE Mais_Faturado_Especialidade (IN ano INT)
636BEGIN
637 SELECT designacao,SUM(preco) AS soma FROM
638 (SELECT esp.designacao,SUM(c.preco) AS preco
639 FROM CONSULTA c,ESPECIALIDADE esp WHERE DATE_FORMAT(c.data_hora,"%Y")=ano
640 AND esp.id_especialidade=c.id_especialidade GROUP BY esp.designacao
641 UNION ALL
642 (SELECT esp.designacao,SUM(ex.preco) AS preco
643 FROM EXAME ex,ESPECIALIDADE esp WHERE DATE_FORMAT(ex.data_hora,"%Y")=ano
644 AND esp.id_especialidade=ex.id_especialidade GROUP BY esp.designacao)) AS p
645 GROUP BY designacao ORDER BY soma DESC LIMIT 2;
646
647END //
648DELIMITER ;
649
650# 2 Relação entre total facturado mensal e despesa com funcionários
651### Função que calcula o saldo entre datas (pode ter alguma falha dependendo das datas inseridas)
652DELIMITER //
653DROP FUNCTION IF EXISTS saldo //
654
655CREATE FUNCTION saldo (dtaI DATE,dtaF DATE) RETURNS DECIMAL(10,2)
656BEGIN
657DECLARE facturado_consultas DECIMAL(10,2);
658DECLARE facturado_exames DECIMAL(10,2);
659DECLARE dispesas_salarios DECIMAL(10,2);
660DECLARE numeroMeses INT;
661
662# calcular o total facturado exames/consultas entre 2 datas
663SELECT SUM(preco) INTO facturado_consultas FROM CONSULTA c WHERE (c.data_hora BETWEEN dtaI AND dtaF) AND c.estado!='A';
664SELECT SUM(preco) INTO facturado_exames FROM EXAME ex WHERE (ex.data_hora BETWEEN dtaI AND dtaF) AND c.estado!='A';
665
666# calcular a soma do salario mensal de todos os funcionarios
667SELECT SUM(salario_base) INTO dispesas_salarios FROM FUNCIONARIO fun,CATEGORIA cat
668 WHERE fun.id_categoria=cat.id_categoria;
669
670# calcular o numero de meses entre as 2 datas
671SELECT TIMESTAMPDIFF(MONTH, dtaI, dtaF) INTO numeroMeses;
672RETURN ((facturado_consultas+facturado_exames)-(dispesas_salarios*numeroMeses));
673END //
674DELIMITER ;
675
676# script cursor que para um atleta, devolve as modalidades que ele pratica
677
678DELIMITER //
679CREATE PROCEDURE atleta()
680BEGIN
681 DECLARE done INT DEFAULT FALSE;
682 DECLARE atl INT;
683 DECLARE id_atl CURSOR FOR SELECT id_atleta FROM ATLETA_MODALIDADE;
684 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
685
686 OPEN id_atl;
687
688 read_loop: LOOP
689 FETCH id_atl INTO atl;
690 IF done THEN
691 LEAVE read_loop;
692 ELSE
693 SELECT nome,moda.designacao from ATLETA_MODALIDADE atlmoda,MODALIDADE moda,ATLETA atle
694 where atlmoda.id_atleta= atl and atlmoda.id_modalidade = moda.id_modalidade and atlmoda.id_atleta = atle.id_atleta;
695 END IF;
696 END LOOP;
697
698 CLOSE id_atl;
699END //
700DELIMITER ;
701
702
703#VIEWS
704
705DROP VIEW IF EXISTS med1_consultas;
706CREATE VIEW med1_consultas AS
707SELECT nome,data_hora,estado from ATLETA a ,CONSULTA c where id_funcionario=123456 and estado !='D'
708and a.id_atleta=c.id_atleta;
709
710DROP VIEW IF EXISTS med1_exames;
711CREATE VIEW med1_exames AS
712SELECT nome,data_hora,estado,resultado,comentario from ATLETA a ,EXAME e where id_funcionario=123456 and estado !='D'
713and a.id_atleta=e.id_atleta;
714
715
716DROP VIEW IF EXISTS recepcao_consultas_exames;
717CREATE VIEW recepcao_consultas_exames AS
718SELECT fun.nome as funcionario, atl.nome as atleta,esp.designacao as especialidade, data_hora as data, estado
719 FROM CONSULTA con, FUNCIONARIO fun, ATLETA atl, ESPECIALIDADE esp
720 where con.id_atleta=atl.id_atleta
721 and con.id_funcionario=fun.id_funcionario and con.id_especialidade=esp.id_especialidade
722UNION ALL
723SELECT fun.nome as funcionario, atl.nome as atleta,esp.designacao as especialidade, data_hora as data, estado
724 FROM EXAME ex, FUNCIONARIO fun, ATLETA atl, ESPECIALIDADE esp
725 where ex.id_atleta=atl.id_atleta
726 and ex.id_funcionario=fun.id_funcionario and ex.id_especialidade=esp.id_especialidade;
727
728
729# 11 Ordenar por ordem crescente/decrescente o numero de atletas de cada localidade
730DELIMITER //
731DROP PROCEDURE IF EXISTS atletas_por_localidade //
732CREATE PROCEDURE atletas_por_localidade()
733BEGIN
734SELECT localidade, COUNT(*) AS Numero_Atletas FROM CODIGO_POSTAL cod,ATLETA atl WHERE atl.codigo_postal=cod.codigo_postal
735GROUP BY localidade ORDER BY Numero_Atletas;
736END //
737DELIMITER //
738
739 # 5 Quais os funcionarios que não fizeram consultas nem exames
740DELIMITER //
741DROP PROCEDURE IF EXISTS func_sem_trabalho //
742CREATE PROCEDURE func_sem_trabalho()
743BEGIN
744SELECT id_funcionario,nome from FUNCIONARIO fun,CATEGORIA c where fun.id_categoria=c.id_categoria
745AND c.designacao != 'Administrativo' AND !deu_consulta_exame(fun.id_funcionario);
746END //
747DELIMITER //
748
749
750# 7 Filtrar atletas por modalidade "Triplo Salto"
751DELIMITER //
752DROP PROCEDURE IF EXISTS atleta_triplo_salto //
753CREATE PROCEDURE atleta_triplo_salto()
754BEGIN
755SELECT atlmod.id_atleta,nome,designacao FROM ATLETA_MODALIDADE atlmod, ATLETA atl, MODALIDADE moda
756where atlmod.id_atleta=atl.id_atleta and moda.id_modalidade=atlmod.id_modalidade
757and designacao='Triplo Salto' and idade(data_nascimento)>20;
758END //
759DELIMITER //
760
761
762# 9 Quantas consultas/exames foram realizados para cada especialidade
763DELIMITER //
764DROP PROCEDURE IF EXISTS consultas_exames_especialidade //
765CREATE PROCEDURE consultas_exames_especialidade()
766BEGIN
767SELECT t1.designacao,t1.exames,t2.consultas,COALESCE(t1.exames + t2.consultas, t1.exames, t2.consultas) AS soma
768FROM (SELECT esp.designacao, COUNT(*) AS exames FROM EXAME ex, ESPECIALIDADE esp WHERE
769 ex.id_especialidade=esp.id_especialidade
770 GROUP BY esp.designacao) AS t1
771LEFT JOIN (SELECT esp.designacao, COUNT(*) AS consultas FROM CONSULTA con, ESPECIALIDADE esp WHERE
772 con.id_especialidade=esp.id_especialidade
773 GROUP BY esp.designacao) AS t2 ON t1.designacao = t2.designacao
774UNION
775SELECT t1.designacao,t1.exames,t2.consultas,COALESCE(t1.exames + t2.consultas, t1.exames, t2.consultas) AS soma
776FROM (SELECT esp.designacao, COUNT(*) AS exames FROM EXAME ex, ESPECIALIDADE esp WHERE
777 ex.id_especialidade=esp.id_especialidade
778 GROUP BY esp.designacao) AS t1
779RIGHT JOIN (SELECT esp.designacao, COUNT(*) AS consultas FROM CONSULTA con, ESPECIALIDADE esp WHERE
780 con.id_especialidade=esp.id_especialidade
781 GROUP BY esp.designacao) AS t2 ON t1.designacao = t2.designacao;
782END //
783DELIMITER ;
784
785# Media das idades dos atletas
786DELIMITER //
787DROP PROCEDURE IF EXISTS media_idades_atletas //
788CREATE PROCEDURE media_idades_atletas()
789BEGIN
790SELECT avg(idade(data_nascimento)) as Media_Idades from ATLETA;
791END //
792DELIMITER ;
793
794# 1 Verificar quais os 2 atletas que menos gastaram no ultimo ano de forma a oferecer um check-up grátis
795DELIMITER //
796DROP PROCEDURE IF EXISTS atletas_menos_consumo //
797CREATE PROCEDURE atletas_menos_consumo()
798BEGIN
799SELECT p.id_atleta,SUM(p.preco) as preco FROM (
800SELECT id_atleta,SUM(preco) as preco FROM CONSULTA where date_format(data_hora,"%Y")=(YEAR(curdate()-1)) GROUP BY id_atleta
801UNION ALL
802SELECT id_atleta,SUM(preco) as preco FROM EXAME where date_format(data_hora,"%Y")=(YEAR(curdate()-1)) GROUP BY id_atleta) as p
803GROUP BY p.id_atleta order by preco Limit 2;
804END //
805DELIMITER ;
806
807# 10 Quais as especialidades sem consultas/exames
808DELIMITER //
809DROP PROCEDURE IF EXISTS espec_sem_consulta_exame //
810CREATE PROCEDURE espec_sem_consulta_exame()
811BEGIN
812SELECT t2.designacao,COALESCE(t1.numero,0) AS numero FROM (SELECT designacao,COUNT(*) AS numero FROM ESPECIALIDADE e, CONSULTA c WHERE e.id_especialidade=c.id_especialidade
813AND c.estado='R' GROUP BY designacao) AS t1
814RIGHT JOIN ESPECIALIDADE t2 ON t1.designacao=t2.designacao WHERE t2.designacao!='Recursos Humanos' ORDER BY numero LIMIT 1;
815END //
816DELIMITER ;
817
818CREATE USER 'medico1'@'localhost' IDENTIFIED BY 'medicoadmin';
819CREATE USER 'recep1'@'localhost' IDENTIFIED BY 'recepadmin';
820
821GRANT SELECT ON med1_consultas TO 'medico1'@'localhost';
822GRANT SELECT ON med1_exames TO 'medico1'@'localhost';
823
824GRANT SELECT ON recepcao_consultas_exames TO 'recep1'@'localhost';