Banco De Dados - Exercícios SQL

-- Trabalho de IBD

-- Exercicio 1: Criar o banco com as tabelas propostas;
CREATE DATABASE faculdade;

USE faculdade;

CREATE TABLE alunos (
  ra INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100),
  cidade VARCHAR(40)
);

CREATE TABLE professor (
  codigoprofessor INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100),
  cidade VARCHAR(40)
);

CREATE TABLE disciplina (
  codigodisciplina INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  disciplina VARCHAR(100),
  cargahoraria FLOAT
);

CREATE TABLE historico (
  codigohistorico INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ra INT,
  CONSTRAINT fk_hist_aluno FOREIGN KEY (ra) REFERENCES alunos (ra),
  codigodisciplina INT,
  CONSTRAINT fk_hist_disc FOREIGN KEY (codigodisciplina) REFERENCES disciplina (codigodisciplina),
  codigoprofessor INT,
  CONSTRAINT fk_hist_prof FOREIGN KEY (codigoprofessor) REFERENCES professor (codigoprofessor),
  semestre INT,
  faltas INT,
  nota FLOAT
);

-- 02 Inserir informações em todas as tabelas (10 alunos, 4 disciplinas - Banco de dados, Sistemas Operacionais, Rede de Computadores e Estrutura de dados, 3professores e 15 históricos)
INSERT INTO
  alunos (nome, cidade)
VALUES
  ('Gabriel', 'Mogi Guaçu'),
  ('Marcos', 'Mogi Guaçu'),
  ('Adryelle', 'Mogi Mirim'),
  ('Brenda', 'Mogi Mirim'),
  ('Tabata', 'Mogi Guaçu'),
  ('Renan', 'Mogi Guaçu'),
  ('Thaito', 'Mogi Guaçu'),
  ('Fernando', 'Mogi Mirim'),
  ('Luis', 'Jaguariuna'),
  ('Ian', 'Estiva');

INSERT INTO
  disciplina (disciplina, cargahoraria)
VALUES
  ('Banco de Dados', 80),
  ('Sistemas Operacionais', 80),
  ('Redes de Computadores', 40),
  ('Estrutura de Dados', 80);

INSERT INTO
  professor (nome, cidade)
VALUES
  ('Maromo', 'Mogi Mirim'),
  ('Sandro', 'Mogi Mirim'),
  ('Ana Célia', 'Mogi Mirim');

INSERT INTO
  historico (
    ra,
    codigodisciplina,
    codigoprofessor,
    semestre,
    faltas,
    nota
  )
VALUES
  (1, 1, 2, 3, 4, 10),
  (1, 2, 2, 3, 8, 10),
  (1, 3, 1, 3, 12, 8.5),
  (1, 4, 3, 3, 20, 7.8),
  (2, 1, 2, 3, 4, 8.5),
  (2, 2, 2, 3, 4, 9),
  (2, 3, 2, 3, 4, 9.5),
  (2, 2, 2, 1, 5, 2.5),
  (4, 2, 1, 3, 4, 6.5),
  (5, 3, 2, 3, 3, 10.0),
  (6, 3, 3, 3, 3, 9.0),
  (7, 4, 1, 3, 5, 8.5),
  (8, 4, 2, 3, 2, 7.0),
  (4, 3, 1, 3, 4, 6.5),
  (9, 1, 3, 3, 4, 6.5);

--03 Encontre o nome e RA dos alunos com nota na disciplina de Banco de Dados no 2º semestre menor que 5.
SELECT
  A.ra as RA,
  A.nome as 'Nome Aluno'
FROM
  alunos A,
  historico H,
  disciplina D
WHERE
  A.ra = H.ra
  AND H.codigodisciplina = D.codigodisciplina
  AND D.disciplina = 'Banco de Dados'
  AND H.semestre = 2
  AND H.nota < 5;

-- 04 Alterar a tabela histórico e incluir um campo inteiro chamado ano, com o objetivo de armazenar o ano e semestre do registro de histórico dos alunos.
ALTER TABLE historico ADD ano INT;

UPDATE historico
SET
  ano = CASE
    WHEN semestre = 2 THEN 2024
    WHEN semestre = 3 THEN 2023
    WHEN semestre = 1 THEN 2024
  END
WHERE
  semestre IN (2, 3, 1);

-- 05 Alterar a tabela de histórico definindo o ano para cada um dos registros de histórico da tabela.
UPDATE historico
SET
  ano = CASE
    WHEN codigohistorico = 1 THEN 2009
    WHEN codigohistorico = 2 THEN 2010
    WHEN codigohistorico = 3 THEN 2011
    WHEN codigohistorico = 4 THEN 2009
    WHEN codigohistorico = 5 THEN 2012
    WHEN codigohistorico = 6 THEN 2013
    WHEN codigohistorico = 7 THEN 2004
    WHEN codigohistorico = 8 THEN 2015
    WHEN codigohistorico = 9 THEN 2016
    WHEN codigohistorico = 10 THEN 2017
    WHEN codigohistorico = 11 THEN 2018
    WHEN codigohistorico = 12 THEN 2019
    WHEN codigohistorico = 13 THEN 2020
    WHEN codigohistorico = 14 THEN 2021
    WHEN codigohistorico = 15 THEN 2022
  END
WHERE
  codigohistorico IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

-- 06 Apresente o nome dos professores de banco de dados que ministraram aulas em 2020 
SELECT DISTINCT
  P.nome
FROM
  professor P
  JOIN historico H ON P.codigoprofessor = H.codigoprofessor
  JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  D.disciplina = 'Banco de Dados'
  AND H.ano = 2020;

-- 07 Apresente a quantidae e nomes das disciplinas que cada professor ministrou em 2020
SELECT
  P.nome AS 'Nome do Professor',
  COUNT(DISTINCT (D.disciplina)) AS 'Quantidade Ministrada'
FROM
  professor P
  JOIN historico H on P.codigoprofessor = H.codigoprofessor
  JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  H.ano = 2020
GROUP BY
  P.nome;

-- 08 Encontre o nome, cidade dos alunos, código das disciplinas e nome da disciplina onde os alunos tiveram nota menor que 5 no 1º semestre de 2020.
SELECT
  A.nome as 'Nome do Aluno',
  A.cidade as 'Cidade',
  D.codigodisciplina as 'Codigo da Disciplina',
  D.disciplina as 'Disciplina'
FROM
  alunos A
  INNER JOIN historico H on A.ra = H.ra
  INNER JOIN disciplina D on H.codigodisciplina = D.codigodisciplina
WHERE
  H.nota < 5;

-- 09 Apresente o nome e RA dos alunos que frequentaram a disciplina de Estrutura de Dados com o professor Marcos em 2019.
SELECT
  A.nome as 'Nome do Aluno',
  A.ra as 'R.A.'
FROM
  alunos A
  INNER JOIN historico H ON A.ra = H.ra
  INNER JOIN disciplina D on H.codigodisciplina = D.codigodisciplina
  INNER JOIN professor P on H.codigoprofessor = P.codigoprofessor
WHERE
  D.disciplina = 'Estrutura de Dados'
  AND P.nome = 'Marcos'
  AND H.ano = 2019;

-- 10 Apresentar o histórico escolar do aluno Alex com informações do seu RA, nome, disciplinas, faltas, nota, ano e semestre.
SELECT
  A.ra as 'R.A.',
  A.nome as 'Nome do Aluno',
  D.disciplina as 'Disciplina',
  H.faltas as 'Faltas',
  H.nota as 'Nota',
  H.ano as 'Ano Letivo',
  H.semestre as 'Semestre'
FROM
  alunos A
  INNER JOIN historico H on A.ra = H.ra
  INNER JOIN disciplina D on H.codigodisciplina = D.codigodisciplina
WHERE
  A.nome = 'Alex';

-- 11 Encontre o nome dos professores que reside em Mogi Mirim.
SELECT
  nome AS 'Nome do Professor'
FROM
  professor
WHERE
  cidade = 'Mogi Mirim';

-- 12 Forneça o nome dos alunos e nome das disciplinas com carga horária menor que 60 horas.
SELECT
  A.nome as 'Nome do Aluno',
  D.disciplina as 'Disciplina',
  P.nome as 'Nome Professor'
FROM
  alunos A
  INNER JOIN historico H on A.ra = H.ra
  INNER JOIN disciplina D on H.codigodisciplina = D.codigodisciplina
  INNER JOIN professor P ON H.codigoprofessor = P.codigoprofessor
WHERE
  D.cargahoraria < 60;

-- 13 Localize o nome dos professores que lecionam matérias ao aluno "Pedro Paulo Cunha" que foi reprovado com nota inferior a 5
SELECT DISTINCT
  P.nome
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN professor P ON H.codigoprofessor = P.codigoprofessor
WHERE
  H.nota < 5
  AND A.nome = 'Pedro Paulo Cunha';

-- 14 Apresente o RA e nome dos alunos que frequentam disciplinas do professor Sandro
SELECT
  A.nome as 'Nome do Aluno',
  A.ra as 'R.A.'
FROM
  alunos A
  INNER JOIN historico H ON A.ra = H.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
  INNER JOIN professor P ON H.codigoprofessor = P.codigoprofessor
WHERE
  P.nome = 'Sandro'
GROUP BY
  A.nome;

-- 15 Encontre o RA, Nome e Média das notas dos alunos que cursaram as materias de professores de Mogi Mirim
SELECT
  A.nome as 'Nome do Aluno',
  A.ra as 'R.A.',
  AVG(H.nota) as 'Media'
FROM
  alunos A
  INNER JOIN historico H ON A.ra = H.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
  INNER JOIN professor P ON H.codigoprofessor = P.codigoprofessor
WHERE
  P.cidade = 'Mogi Mirim'
GROUP BY
  A.nome;

-- 16 Apresente o número de alunos que fizeram banco de dados e estrutura de dados em 2020 no primeiro semestre. 
SELECT
  COUNT(DISTINCT A.ra) as 'Quantidade de alunos que cursaram IBD e IED no primeiro Semestre de 2020'
FROM
  alunos A
  INNER JOIN historico H ON A.ra = H.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  D.disciplina = 'Banco de Dados'
  AND D.disciplina = 'Banco de Dados'
  AND H.ano = 2020
  AND H.semestre = 1;

-- 17 Apresente a média de notas por disciplina. Ordenar por média decrescente 
SELECT
  D.disciplina as 'Disciplina',
  AVG(H.nota) as Média
FROM
  historico H
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
GROUP BY
  D.disciplina
ORDER BY
  H.nota DESC;

-- 18 Apresente nome do aluno, cidade, código da disciplina e nome da disciplina que os alunos tiveram nota superior a 5 no 1º semestre de 2020. ORdenar por nome da disciplina. 
SELECT
  A.nome as 'Nome do Aluno',
  A.cidade as 'Cidade',
  D.codigodisciplina as 'Código da Disciplina',
  D.disciplina as 'Disciplina cuja nota foi maior que 5 no 1º semestre de 2020'
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  H.semestre = 1
  AND H.ano = 2020
  AND H.nota > 5
GROUP BY
  A.nome
ORDER BY
  D.disciplina ASC;

-- 19 Apresentar o Historico escolar do aluno Alex, contendo seu RA, nome, lista de disciplinas que já cursou contendo codigo e nome da disciplina, faltas, nota, ano e semestre. 
SELECT
  A.nome as 'Nome do Aluno',
  A.ra as 'R.A.',
  D.disciplina as 'Disciplina',
  D.codigodisciplina as 'Código da Disciplina',
  H.semestre as 'Cursada no semestre:',
  H.ano as 'no Ano:',
  H.faltas as 'Faltando ... Dias',
  H.nota as 'Nota Final:'
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  A.nome = 'Alex'
ORDER BY
  H.ano DESC;

-- 20 Apresente a quantidade que o aluno José da Silva cursou Banco de Dados 
SELECT
  A.nome as 'Nome do Aluno',
  D.disciplina as 'Disciplina',
  COUNT(H.codigodisciplina) as 'Vezes que cursou'
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  A.nome = 'José da Silva'
  AND D.disciplina = 'Banco de Dados';

-- 21 Apresentar a quantidade de alunos que cursou a disciplina de banco de dados em 2019 e 2020. 
SELECT
  COUNT(DISTINCT A.ra) as 'Quantidade de aluno que cursou Banco de Dados'
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
WHERE
  D.disciplina = 'Banco de Dados'
  AND (
    H.ano = 2019
    OR H.ano = 2020
  );

-- 22 Insira todos os alunos da disciplina de BD em 2019 e que tiveram nota > 5, cursando uma disciplina de TBD (Tópicos em Banco de Dados) em 2018 com o mesmo professor mas frequencia e notas desconhecidas. 
INSERT INTO
  disciplina (disciplina, cargahoraria)
VALUES
  ('Tópicos em Banco de Dados', 80);

INSERT INTO
  historico (
    ra,
    codigodisciplina,
    codigoprofessor,
    semestre,
    ano
  )
SELECT
  A.ra,
  D.codigodisciplina,
  P.codigoprofessor,
  1,
  2018
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
  INNER JOIN professor P ON H.codigoprofessor = P.codigoprofessor
WHERE
  D.disciplina = 'Banco de Dados'
  AND H.ano = 2019
  AND H.nota > 5;

-- 23 Altere as notas dos alunos de BD em 2019 com o professor Sandro através da regra: 
-- -- Notas entre 4 e 5, ficam 4. Notas entre 5 e 9.5 terao acrescimo de 0.5, notas acima de 9.5 serao 10.
UPDATE historico H
INNER JOIN professor P ON H.codigoprofessor = P.codigoprofessor
INNER JOIN disciplina D ON H.codigodisciplina = D.codigodisciplina
SET
  nota = CASE
    WHEN nota BETWEEN 4 AND 5  THEN 4
    WHEN nota BETWEEN 5 AND 9.5  THEN nota + 0.5
    WHEN nota > 9.5 THEN 10
  END
WHERE
  D.disciplina = 'Banco de Dados'
  AND P.nome = 'Sandro'
  AND H.ano = 2019;

-- 24 Apresente uma consulta de nome do aluno, nome da disciplina, faltas, notas e uma informação de reprovado caso nota inferior a 7, e aprovado caso superior a 7. 
SELECT
  A.nome as 'Nome do Aluno',
  D.disciplina as 'Disciplina',
  H.faltas as 'Faltas',
  H.nota as 'Nota',
  CASE
    WHEN H.nota >= 7 THEN 'Aprovado'
    ELSE 'Reprovado'
  END AS 'Fechamento'
FROM
  historico H
  INNER JOIN alunos A ON H.ra = A.ra
  INNER JOIN disciplina D on H.codigodisciplina = D.codigodisciplina;

-- 25 Apresente como resultado a média dos alunos REPROVADOS, ou seja média inferior a 5 (???? A Média acima era 7, agora virou 5... Vou fazer dos dois)
SELECT
  D.disciplina,
  AVG(H.nota) AS 'Médias'
FROM
  disciplina D
  JOIN historico H ON D.codigodisciplina = H.codigodisciplina
WHERE
  H.nota < 5
GROUP BY
  D.disciplina;

SELECT
  D.disciplina,
  AVG(H.nota) AS 'Médias'
FROM
  disciplina D
  JOIN historico H ON D.codigodisciplina = H.codigodisciplina
WHERE
  H.nota < 7
GROUP BY
  D.disciplina;

-- 26 comando sql para acrescentar 0.5 pontos na materia de banco de dados para todos os alunos que participam. 
UPDATE historico H
SET
  nota = CASE
    WHEN nota <= 9.5 THEN nota + 0.5
    WHEN nota = 10 THEN 10
  END
WHERE
  codigodisciplina = (
    SELECT
      codigodisciplina
    FROM
      disciplina
    WHERE
      disciplina = 'Banco de Dados'
  );

-- 27 Considere a necessidade de normalizar o DB. Observa-se que o campo CIDADE não atende às normas normais na tabela ALUNOS. Desta forma, apresentar os camondos SQL na sequencia para: 
-- 27a) Criar uma tabela nova chamada CIDADE e transferir o campo cidade  da tabela alunos para esta tabela. 
-- 27b) Criar chave primária e identity (auto_increment)
CREATE TABLE cidade (
  codigocidade INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(40) UNIQUE NOT NULL
);

INSERT INTO
  cidade (nome)
SELECT DISTINCT
  cidade
FROM
  alunos ON DUPLICATE KEY
UPDATE nome =
VALUES
  (nome);

-- 27c) Criar uma chave estrangeira para a tabela alunos e relacionar. 
ALTER TABLE alunos ADD (
  codigocidade INT,
  CONSTRAINT fk_aluno_cidade FOREIGN KEY (codigocidade) REFERENCES cidade (codigocidade)
);

-- 27d) Preencher o campo de chave estrangeira da tabela alunos com o código novo da tabela da cidade levando em consideração o campo cidade da tabela alunos. 
UPDATE alunos A
INNER JOIN cidade C ON A.cidade = C.nome
SET
  A.codigocidade = C.codigocidade;

-- 27e) Excluir o campo cidade na tabela aluno
ALTER TABLE alunos
DROP COLUMN cidade;

-- Opcional: Selecionar para trocar o código da cidade pelo nome: 
SELECT
  A.ra,
  A.nome,
  C.nome
FROM
  alunos A
  JOIN cidade C ON A.codigocidade = C.codigocidade
ORDER BY
  A.ra ASC;

-- Exercicio 1: 
CREATE DATABASE faculdade;

USE faculdade;

CREATE TABLE ALUNOS (
  RA INT IDENTITY PRIMARY KEY,
  NOME VARCHAR(100),
  CIDADE VARCHAR(40)
);

CREATE TABLE DISCIPLINAS (
  codigodisciplina INT IDENTITY NOT NULL PRIMARY KEY,
  nome VARCHAR(100),
  carga_horaria INT
);

CREATE TABLE PROFESSORES (
  codigoprofessor INT IDENTITY PRIMARY KEY,
  nome VARCHAR(100),
  cidade VARCHAR(40)
);

CREATE TABLE HISTORICO (
  codigohistorico INT IDENTITY PRIMARY KEY,
  ra INT,
  codigodisciplina INT,
  codigoprofessor INT,
  semestre INT,
  faltas INT,
  nota FLOAT,
  FOREIGN KEY (ra) REFERENCES ALUNOS (RA),
  FOREIGN KEY (codigodisciplina) REFERENCES DISCIPLINAS (codigodisciplina),
  FOREIGN KEY (codigoprofessor) REFERENCES PROFESSORES (codigoprofessor)
);

-- Exercicio 2: 
INSERT INTO
  ALUNOS (NOME, CIDADE)
VALUES
  ('João Silva', 'São Paulo'),
  ('Maria Oliveira', 'Campinas'),
  ('Pedro Santos', 'Rio de Janeiro'),
  ('Ana Costa', 'Curitiba'),
  ('Lucas Almeida', 'São Paulo'),
  ('José da Silva', 'Campinas'),
  ('Carlos Lima', 'Fortaleza'),
  ('Bianca Pereira', 'Belo Horizonte'),
  ('Pedro Paulo Cunha', 'Salvador'),
  ('Alex', 'Porto Alegre');

SELECT
  *
FROM
  ALUNOS;

INSERT INTO
  PROFESSORES (nome, cidade)
VALUES
  ('Sandro Armelin', 'Mogi Mirim'),
  ('Marcos Nava', 'Mogi Mirim'),
  ('Ricardo Akira', 'Mogi Mirim');

SELECT
  *
FROM
  PROFESSORES;

INSERT INTO
  DISCIPLINAS (nome, carga_horaria)
VALUES
  ('Banco de Dados', 80),
  ('Sistemas Operacionais', 40),
  ('Redes de Computadores', 80),
  ('Estrutura de Dados', 40);

SELECT
  *
FROM
  DISCIPLINAS;

INSERT INTO
  HISTORICO (
    ra,
    codigodisciplina,
    codigoprofessor,
    semestre,
    faltas,
    nota
  )
VALUES
  (1, 1, 1, 1, 2, 7.5),
  (2, 2, 1, 2, 0, 8.0),
  (3, 3, 3, 1, 3, 6.0),
  (4, 4, 2, 2, 1, 5.5),
  (5, 1, 1, 1, 0, 9.0),
  (6, 2, 1, 1, 4, 4.0),
  (7, 3, 3, 2, 2, 7.0),
  (8, 4, 2, 1, 1, 6.5),
  (8, 1, 2, 1, 3, 2.5),
  (9, 1, 1, 2, 3, 1.5),
  (10, 2, 1, 1, 0, 5.0),
  (1, 3, 3, 2, 1, 7.0),
  (2, 4, 2, 1, 2, 6.0),
  (3, 1, 1, 2, 0, 9.5),
  (4, 2, 1, 1, 3, 4.5),
  (5, 3, 3, 2, 1, 5.5);

SELECT
  *
FROM
  HISTORICO;

-- Exercicio 3:
SELECT
  a.NOME,
  a.RA
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  d.nome = 'Banco de Dados'
  AND h.semestre = 2
  AND h.nota < 5;

-- Exercicio 4:
ALTER TABLE HISTORICO ADD ano INT;

-- Exercicio 5:
UPDATE HISTORICO
SET
  ano = 2019
WHERE
  codigohistorico <= 7;

UPDATE HISTORICO
SET
  ano = 2020
WHERE
  codigohistorico > 7;

select
  *
from
  historico;

-- Exercicio 6:
SELECT DISTINCT
  p.nome
FROM
  PROFESSORES p
  JOIN HISTORICO h ON p.codigoprofessor = h.codigoprofessor
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  d.nome = 'Banco de Dados'
  AND h.ano = 2020;

-- Exercicio 7:
SELECT
  p.nome AS nome_professor,
  COUNT(DISTINCT d.nome) AS quantidade_disciplinas
FROM
  PROFESSORES p
  JOIN HISTORICO h ON p.codigoprofessor = h.codigoprofessor
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  h.ano = 2020
GROUP BY
  p.nome;

-- Exercicio 8:
SELECT
  a.NOME AS nome_aluno,
  a.CIDADE AS cidade_aluno,
  d.codigodisciplina,
  d.nome AS nome_disciplina
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  h.nota < 5
  AND h.semestre = 1
  AND h.ano = 2020;

-- Exercicio 9:
SELECT
  a.NOME,
  a.RA
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
  JOIN PROFESSORES p ON h.codigoprofessor = p.codigoprofessor
WHERE
  d.nome = 'Estrutura de Dados'
  AND p.nome = 'Marcos Nava'
  AND h.ano = 2019;

-- Exercicio 10:
SELECT
  a.RA,
  a.NOME AS nome_aluno,
  d.nome AS nome_disciplina,
  h.faltas,
  h.nota,
  h.ano,
  h.semestre
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  a.NOME = 'Alex';

-- Exercicio 11:
SELECT
  nome
FROM
  PROFESSORES
WHERE
  cidade = 'Mogi Mirim';

-- Exercicio 12:
SELECT
  a.nome AS nome_aluno,
  d.nome AS nome_disciplina,
  p.nome AS nome_professor
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
  JOIN PROFESSORES p ON h.codigoprofessor = p.codigoprofessor
WHERE
  d.carga_horaria < 60;

-- Exercicio 13:
SELECT DISTINCT
  p.nome AS nome_professor
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
  JOIN PROFESSORES p ON h.codigoprofessor = p.codigoprofessor
WHERE
  a.NOME = 'Pedro Paulo Cunha'
  AND h.nota < 5;

-- Exercicio 14: 
SELECT
  a.NOME,
  a.RA
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN PROFESSORES p ON h.codigoprofessor = p.codigoprofessor
WHERE
  p.nome = 'Sandro Armelin';

-- Exercicio 15:
SELECT
  A.nome as 'Nome do Aluno',
  A.ra as 'R.A.',
  AVG(H.nota) as 'Media'
FROM
  alunos A
  INNER JOIN historico H ON A.ra = H.ra
  INNER JOIN disciplinas D ON H.codigodisciplina = D.codigodisciplina
  INNER JOIN professores P ON H.codigoprofessor = P.codigoprofessor
WHERE
  P.cidade = 'Mogi Mirim'
GROUP BY
  A.ra,
  A.nome;

-- Exercicio 16:
SELECT
  COUNT(DISTINCT h.ra) AS numero_de_alunos
FROM
  HISTORICO h
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  (
    d.nome = 'Banco de Dados'
    OR d.nome = 'Estrutura de Dados'
  )
  AND h.semestre = 1
  AND h.ano = 2020
GROUP BY
  h.ra
HAVING
  COUNT(DISTINCT d.codigodisciplina) = 2;

-- Exercicio 17:
SELECT
  d.nome AS nome_disciplina,
  AVG(h.nota) AS media_notas
FROM
  HISTORICO h
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
GROUP BY
  d.nome
ORDER BY
  media_notas DESC;

-- Exercicio 18:
SELECT
  a.NOME AS nome_aluno,
  a.CIDADE AS cidade_aluno,
  d.codigodisciplina,
  d.nome AS nome_disciplina
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  h.nota > 5
  AND h.semestre = 1
  AND h.ano = 2020
ORDER BY
  d.nome;

-- Exercicio 19:
SELECT
  a.RA,
  a.NOME AS nome_aluno,
  d.codigodisciplina,
  d.nome AS nome_disciplina,
  h.faltas,
  h.nota,
  h.ano,
  h.semestre
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.ra
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  a.NOME = 'Alex';

-- Exercicio 20: 
SELECT
  COUNT(*) AS 'vezes cursadas'
FROM
  HISTORICO h
  JOIN ALUNOS a ON h.ra = a.RA
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  a.NOME = 'José da Silva'
  AND d.nome = 'Banco de Dados';

-- Exercicio 21: 
SELECT
  h.ano,
  COUNT(DISTINCT h.ra) AS quantidade_alunos
FROM
  HISTORICO h
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina
WHERE
  d.nome = 'Banco de Dados'
  AND h.ano IN (2019, 2020)
GROUP BY
  h.ano
ORDER BY
  h.ano;

-- Exercicio 22:
INSERT INTO
  disciplinas (nome, carga_horaria)
VALUES
  ('Tópicos em Banco de Dados', 40);

INSERT INTO
  Historico (
    RA,
    codigodisciplina,
    codigoprofessor,
    semestre,
    faltas,
    nota,
    ano
  )
SELECT
  h.RA,
  5 AS CodDisciplina,
  h.codigoprofessor,
  1,
  NULL,
  NULL,
  2018
FROM
  Historico h
  JOIN Disciplinas d ON h.codigodisciplina = d.codigodisciplina
  JOIN Professores p ON h.codigoprofessor = p.codigoprofessor
WHERE
  d.Nome = 'Banco de Dados'
  AND h.Ano = 2019
  AND h.Nota > 5
  AND p.Nome = 'Sandro Armelin';

select
  *
from
  historico;

-- Exercicio 23:
UPDATE HISTORICO
SET
  nota = CASE
    WHEN nota BETWEEN 4.0 AND 5.0  THEN 4.0
    WHEN nota BETWEEN 5.0 AND 9.5  THEN nota + 0.5
    WHEN nota > 9.5 THEN 10.0
    ELSE nota
  END
WHERE
  codigoprofessor = (
    SELECT
      codigoprofessor
    FROM
      PROFESSORES
    WHERE
      nome = 'Sandro Armelin'
  )
  AND Ano = 2019
  AND codigodisciplina = (
    SELECT
      codigodisciplina
    FROM
      DISCIPLINAS
    WHERE
      nome = 'Banco de Dados'
  );

SELECT
  *
FROM
  HISTORICO
WHERE
  codigodisciplina = 1;

-- Exercicio 24: 
SELECT
  a.NOME,
  d.nome,
  h.faltas,
  h.nota,
  CASE
    WHEN h.nota >= 7 THEN 'Aprovado'
    ELSE 'Reprovado'
  END AS Situacao
FROM
  ALUNOS a
  JOIN HISTORICO h ON a.RA = h.RA
  JOIN DISCIPLINAS d ON h.codigodisciplina = d.codigodisciplina;

-- Exercicio 25
SELECT
  d.nome,
  AVG(h.nota) AS media_notas
FROM
  DISCIPLINAS d
  JOIN HISTORICO h ON d.codigodisciplina = h.codigodisciplina
WHERE
  h.nota < 5
GROUP BY
  d.nome;

-- Exercicio 26:
UPDATE HISTORICO
SET
  nota = nota + 0.5
WHERE
  codigodisciplina = (
    SELECT
      codigodisciplina
    FROM
      DISCIPLINAS
    WHERE
      nome = 'Banco de Dados'
  );

SELECT
  *
FROM
  HISTORICO
WHERE
  codigodisciplina = 1;

-- Exercicio 27:
CREATE TABLE Cidade (
  CidadeID INT IDENTITY PRIMARY KEY,
  CidadeNome VARCHAR(40) UNIQUE NOT NULL
);

INSERT INTO
  Cidade (CidadeNome)
SELECT DISTINCT
  Cidade
FROM
  Alunos
WHERE
  Cidade NOT IN (
    SELECT
      CidadeNome
    FROM
      Cidade
  );

ALTER TABLE Alunos ADD CidadeID INT;

UPDATE Alunos
SET
  CidadeID = (
    SELECT
      CidadeID
    FROM
      Cidade
    WHERE
      CidadeNome = Alunos.Cidade
  );

ALTER TABLE Alunos
DROP COLUMN Cidade;

select
  *
from
  alunos;