#1. Crie o banco de dados apresentado e suas tabelas (apresentar código CREATE)
CREATE DATABASE DB_SAMU;

USE DB_SAMU;

CREATE TABLE TB_CATEGORIAS (
	CAT_CODIGO INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    CAT_CATEGORIA VARCHAR(50) NOT NULL
);

CREATE TABLE TB_EVENTOS (
	EVE_CODIGO INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    EVE_NOME VARCHAR(50) NOT NULL,
    EVE_CAT_CODIGO INT NOT NULL,
    EVE_DATA DATE NOT NULL,
    EVE_QTDPESSOAS INT NOT NULL,
    CONSTRAINT FK_CAT_EVE FOREIGN KEY (EVE_CAT_CODIGO) REFERENCES TB_CATEGORIAS (CAT_CODIGO)
);

CREATE TABLE TB_MEDICOS (
	MED_CODIGO INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    MED_NOME VARCHAR(50) NOT NULL,
    MED_TELEFONE VARCHAR(11) NOT NULL
);

CREATE TABLE TB_ATENDIMENTOS (
	ATE_CODIGO INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    ATE_EVE_CODIGO INT NOT NULL,
    ATE_MED_CODIGO INT NOT NULL,
    ATE_DESCRICAO TEXT NOT NULL,
    CONSTRAINT FK_MED_ATE FOREIGN KEY (ATE_MED_CODIGO) REFERENCES TB_MEDICOS (MED_CODIGO),
    CONSTRAINT FK_EVE_ATE FOREIGN KEY (ATE_EVE_CODIGO) REFERENCES TB_EVENTOS (EVE_CODIGO)
);

#1.1. Adicione pelo menos 2 novas colunas na TB_MEDICOS.
ALTER TABLE TB_MEDICOS ADD COLUMN MED_EMAIL VARCHAR(50);
ALTER TABLE TB_MEDICOS ADD COLUMN MED_SEXO ENUM('m', 'f') DEFAULT 'm' COMMENT 'm = masculinom, f = feminino';

#1.2. Altere o a coluna ATE_DESCRICAO para um varchar (2000).
ALTER TABLE TB_ATENDIMENTOS MODIFY COLUMN ATE_DESCRICAO VARCHAR(2000) NOT NULL;

#1.3. Faça alteração nas colunas EVE_NOME e MED_NOME para permitir inserir até 100 letras.
ALTER TABLE TB_EVENTOS MODIFY COLUMN EVE_NOME VARCHAR(100);
ALTER TABLE TB_MEDICOS MODIFY COLUMN MED_NOME VARCHAR(100);

#2. Crie o código INSERT para inserir pelo menos 8 registros em cada tabela.
INSERT INTO TB_CATEGORIAS (CAT_CODIGO, CAT_CATEGORIA) VALUES
(1, 'Pressão'),
(2, 'Febre Baixa'),
(3, 'Febre Moderada'),
(4, 'Febre Alta'),
(5, 'Fratura'),
(6, 'Corte'),
(7, 'Infarto'),
(8, 'Derrame');

INSERT INTO TB_MEDICOS (MED_CODIGO, MED_NOME, MED_TELEFONE, MED_EMAIL, MED_SEXO) VALUES
(1, 'Renata', '8485984521', 'renata@gmail.com', 'f'),
(2, 'Alex Barbosa', '8548518954', 'alexbarbosa@hotmail.com', 'm'),
(3, 'Will Max', '8445286654', 'willmax@gmail.com', 'm'),
(4, 'Teo', '8445828965', 'teo@hotmail.com', 'm'),
(5, 'Sabrina', '8445824568', 'sabrina@yahoo.com.br', 'f'),
(6, 'Ludimila', '8445284664', 'ludimila@yahoo.com.br', 'f'),
(7, 'Alan', '8487524562', 'alan@gmail.com', 'm'),
(8, 'Max', '84458289560', 'max85@unp.com.br', 'm');

INSERT INTO TB_EVENTOS (EVE_CODIGO, EVE_NOME, EVE_CAT_CODIGO, EVE_DATA, EVE_QTDPESSOAS) VALUES
(1, 'Jogo de futebol', 5, '2017-05-06', 20),
(2, 'Cinema', 3, '2017-05-10', 3),
(3, 'Show de rock', 2, '2017-04-11', 10),
(4, 'Micareta', 2, '2017-05-11', 15),
(5, 'Festival gastronômico', 8, '2017-10-10', 85),
(6, 'Corrida da saúde', 7, '2017-09-01', 100),
(7, 'Teatro', 6, '2017-08-08', 45),
(8, 'Corrida de kart', 4, '2017-05-05', 7);

INSERT INTO TB_ATENDIMENTOS (ATE_CODIGO, ATE_EVE_CODIGO, ATE_MED_CODIGO, ATE_DESCRICAO) VALUES
(1, 1, 1, 'Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográfica'),
(2, 2, 2, 'É um fato conhecido de todos que um leitor se distrairá com o conteúdo de texto legível'),
(3, 3, 3, 'Existem muitas variações disponíveis de passagens de Lorem Ipsum'),
(4, 4, 4, 'Ao contrário do que se acredita, Lorem Ipsum não é simplesmente um texto randômico'),
(5, 5, 5, 'O trecho padrão original de Lorem Ipsum, usado desde o século XVI'),
(6, 6, 6, 'Lorem Ipsum na internet tendem a repetir pedaços predefinidos'),
(7, 7, 7, 'Várias versões novas surgiram ao longo dos anos, eventualmente por acidente'),
(8, 8, 8, 'A vantagem de usar Lorem Ipsum é que ele tem uma distribuição');

#3. Crie códigos para UPDATE para:
#3.1. Alterar o nome e o telefone do médico 2.
UPDATE TB_MEDICOS SET MED_NOME='Isaias', MED_TELEFONE='84987524585' WHERE MED_CODIGO=8;
UPDATE TB_MEDICOS SET MED_NOME='Isabela', MED_TELEFONE='8485235445' WHERE MED_CODIGO=6;

#3.2. Alterar todos os eventos que eram do código 3 para serem do código 2.
#Aqui ficou bastante confuso, acho que o sr. se fefere em alterar os atendimentos que foram feitos com o evento de código 3 para o código 2
UPDATE TB_ATENDIMENTOS SET ATE_EVE_CODIGO=2 WHERE ATE_EVE_CODIGO=3;

#4. Crie códigos DELETE para:
#4.1. Apagar os eventos com menos de 10 pessoas.
DELETE A.* FROM TB_ATENDIMENTOS AS A
INNER JOIN TB_EVENTOS AS E ON E.EVE_CODIGO=A.ATE_EVE_CODIGO AND E.EVE_QTDPESSOAS < 10;
DELETE FROM TB_EVENTOS WHERE EVE_QTDPESSOAS < 10;

#4.2. Apagar médicos que tenham telefone começando com 76
DELETE FROM TB_MEDICOS WHERE MED_TELEFONE REGEXP '^76';

#5. Faça consultas para listar os seguintes dados:
#5.1. Nome e data de todos os eventos.
SELECT E.EVE_NOME, E.EVE_DATA FROM TB_EVENTOS AS E;

#5.2. Nome e o telefone dos médicos, em ordem alfabética.
SELECT M.MED_NOME, M.MED_TELEFONE FROM TB_MEDICOS AS M ORDER BY M.MED_NOME ASC;

#5.3. Código e descrição dos atendimentos realizados nos eventos 1, 2 e 6.
SELECT A.ATE_CODIGO, A.ATE_DESCRICAO FROM TB_ATENDIMENTOS AS A WHERE A.ATE_EVE_CODIGO IN (1,2,6);

#5.4. Listar o código, nome e a data de todos os eventos de futebol (categoria 2).
SELECT E.EVE_CODIGO, E.EVE_NOME, E.EVE_DATA FROM TB_EVENTOS AS E WHERE E.EVE_CAT_CODIGO=2;

#5.5. Listar código e data dos eventos com mais de 1000 pessoas.
SELECT E.EVE_CODIGO, E.EVE_DATA FROM TB_EVENTOS AS E WHERE E.EVE_QTDPESSOAS > 1000;

#5.6. Nome do evento, data, e nome da categoria, em ordem crescente de categoria.
# Obs. ordem alfabética do nome da categoria (deve ser)
SELECT E.EVE_CODIGO, E.EVE_DATA, C.CAT_CATEGORIA FROM TB_EVENTOS AS E
JOIN TB_CATEGORIAS AS C ON C.CAT_CODIGO=E.EVE_CAT_CODIGO
ORDER BY C.CAT_CATEGORIA ASC;

#5.7. Código do atendimento, nome do médico, descrição do atendimento e data do evento.
SELECT A.ATE_CODIGO, M.MED_NOME, A.ATE_DESCRICAO, E.EVE_DATA FROM TB_ATENDIMENTOS AS A
JOIN TB_MEDICOS AS M ON M.MED_CODIGO=A.ATE_MED_CODIGO
JOIN TB_EVENTOS AS E ON E.EVE_CODIGO=A.ATE_EVE_CODIGO;

#5.8. Nome do evento, nome da categoria, descrição do atendimento e nome do médico.
SELECT E.EVE_NOME, C.CAT_CATEGORIA, A.ATE_DESCRICAO, M.MED_NOME FROM TB_ATENDIMENTOS AS A
JOIN TB_MEDICOS AS M ON M.MED_CODIGO=A.ATE_MED_CODIGO
JOIN TB_EVENTOS AS E ON E.EVE_CODIGO=A.ATE_EVE_CODIGO
JOIN TB_CATEGORIAS AS C ON C.CAT_CODIGO=E.EVE_CAT_CODIGO;

#5.9. Nome e telefone dos médicos, em ordem alfabética, que nunca fizeram atendimento.
SELECT M.MED_NOME, M.MED_TELEFONE FROM TB_MEDICOS AS M
LEFT JOIN TB_ATENDIMENTOS AS A ON A.ATE_MED_CODIGO=M.MED_CODIGO
WHERE A.ATE_CODIGO IS NULL;

#5.10. Eventos que não tiveram atendimentos.
SELECT E.* FROM TB_EVENTOS AS E
LEFT JOIN TB_ATENDIMENTOS AS A ON A.ATE_EVE_CODIGO=E.EVE_CODIGO
WHERE A.ATE_CODIGO IS NULL;

#5.11. Média de pessoas por evento.
SELECT AVG(E.EVE_QTDPESSOAS) AS MEDIA FROM TB_EVENTOS AS E;

#5.12. Nome do evento e quantidade de atendimentos relacionados.
SELECT E.EVE_NOME, COUNT(A.ATE_CODIGO) AS QUANTIDADE_EVENTOS FROM TB_EVENTOS AS E
LEFT JOIN TB_ATENDIMENTOS AS A ON A.ATE_EVE_CODIGO=E.EVE_CODIGO;

#5.13. Nome da categoria e quantos eventos foram realizados deste tipo em janeiro de 2017.
SELECT C.CAT_CATEGORIA, COUNT(E.EVE_CODIGO) AS QUANTIDADE_JANEIRO_2017 FROM TB_CATEGORIAS AS C
LEFT JOIN TB_EVENTOS AS E ON E.EVE_CAT_CODIGO=C.CAT_CODIGO AND E.EVE_DATA BETWEEN '2017-01-01' AND '2017-01-31';

#5.14. Quantidade total de pessoas que frequentaram eventos de futebol.
# vou levar em consideração que "futebol" é o evento com id 2
SELECT SUM(E.EVE_QTDPESSOAS) FROM TB_EVENTOS AS E
WHERE E.EVE_CODIGO=2;

#5.15. Quantidade média de pessoas por tipo de evento, considerando apenas eventos que tiveram atendimentos.
#considerei "tipo de evento" como a categoria, adicionei o nome da caegoria na consulta
SELECT C.CAT_CATEGORIA, AVG(E.EVE_QTDPESSOAS) AS MEDIA FROM TB_CATEGORIAS AS C
JOIN TB_EVENTOS AS E ON E.EVE_CAT_CODIGO=C.CAT_CODIGO
JOIN TB_ATENDIMENTOS AS A ON A.ATE_EVE_CODIGO=E.EVE_CODIGO;