--a) Criação das tabelas Candidato, Votacao e Zona_Se
--cao, especificando chave primária e
--chaves estrangeiras;

-- Já respondido

--b) Alteração da estrutura da tabela Votacao, adicio
--nando um atributo data_votacao;

alter table votacao
add column data_votacao date;


--c) Alteração da estrutura da tabela Candidato, remo
--vendo a chave estrangeira com a
--tabela Partido;

alter table candidato
drop constraint fk_candidato_partido

--d) Criar um índice “não-clustered” na tabela Candid
--ato, com os atributos cod_cargo e
--cod_partido;

create index on candidato (cod_cargo, cod_partido);

/*e) Inserir um registro na tabela Votacao. 
(qtd_votos = 1000, num_secao = 10, num_zona = 5, 
 num_candidato = 555);*/

insert into votacao 
   (qtd_votos, num_secao, num_zona, num_candidato)
values (1000,10,5,555);

/*f) Inserir um registro na tabela Zona_Secao (qtd_el
eitores = 300, nome_zona_secao =
“Zona Teste”, num_zona = 7, num_secao = 99).*/

insert into zona_secao
   (qtd_eleitores, nome_zona_secao, num_zona, num_secao)
values 
    (300, 'zona_teste', 7,99);

/*g) Alterar a quantidade de votos, multiplicando por
2, para os candidatos a governador
(código do cargo = 1) e do Partido Democrático (cód
igo de partido = 5);*/

select *
from votacao as v, candidato as c
where v.num_candidato = c.num_candidato
and cod_cargo = 1 and cod_partido = 5

update votacao as v
set qtd_votos = qtd_votos * 2
from candidato as c
where v.num_candidato = c.num_candidato
and cod_cargo = 1 and cod_partido = 5

/* h) Alterar a quantidade de eleitores, somando 100,
para as zonas/seções onde ocorreu
votação para candidatos ao Senado (código do cargo
= 2);
*/

select *
from zona_secao as z, votacao as v, 
     candidato as c, cargo as g
where z.num_zona = v.num_zona and
      z.num_secao = v.num_secao and
      v.num_candidato = c.num_candidato and
      c.cod_cargo = g.cod_cargo and
      g.cod_cargo = 2


update zona_secao as z
set qtd_eleitores = qtd_eleitores + 100
from votacao as v, 
     candidato as c, cargo as g
where z.num_zona = v.num_zona and
      z.num_secao = v.num_secao and
      v.num_candidato = c.num_candidato and
      c.cod_cargo = g.cod_cargo and
      g.cod_cargo = 2


--i) Apagar todos os cargos que não possuem candidatos;

select *
from cargo as c
where not exists 
  (select * from candidato as cn
     where c.cod_cargo = cn.cod_cargo)

begin transaction

delete 
from cargo as c
where not exists 
  (select * from candidato as cn
     where c.cod_cargo = cn.cod_cargo)

rollback

/*j) Apagar as zonas/seções que possuam menos de 1.00
0 eleitores e que o nome comecem com a letra “A”;
*/

select *
from zona_secao as z
where z.qtd_eleitores < 1000 and 
      nome_zona_secao like 'A%'

delete 
from zona_secao as z
where z.qtd_eleitores < 1000 and 
      nome_zona_secao like 'A%'

/*k) Selecionar nome do partido, nome do candidato e
nome do cargo, em ordem alfabética
do nome do partido; */

select nome_partido, nome_candidato, nome_cargo
from partido as p inner join candidato as c
     on (p.cod_partido = c.cod_partido)
     inner join cargo as g
     on (c.cod_cargo = g.cod_cargo)
order by nome_partido

--l) Selecionar código e nome do partido, 
--nome do cargo e a quantidade de candidatos por 
--partido, para cada cargo; 

SELECT P.COD_PARTIDO, P.NOME_PARTIDO, C.NOME_CARGO,
       COUNT (*) AS QTDE_CANDIDATOS
FROM PARTIDO AS P INNER JOIN CANDIDATO AS CA
     ON (P.COD_PARTIDO = CA.COD_PARTIDO)
	 INNER JOIN CARGO AS C
	 ON (CA.COD_CARGO = C.COD_CARGO)
GROUP BY P.COD_PARTIDO, C.COD_CARGO	 

--m) Selecionar as zonas/seções onde 
--não ocorreu votação; 

SELECT Z.NUM_ZONA, Z.NUM_SECAO
FROM ZONA_SECAO AS Z
WHERE (Z.NUM_ZONA, Z.NUM_SECAO) NOT IN
    (SELECT V.NUM_ZONA, V.NUM_SECAO
	 FROM VOTACAO AS V)

--n) Selecionar número do candidato, 
--nome do candidato, nome do cargo, nome do partido 
--e quantidade total de votos de cada candidato, 
--apenas para os candidatos que tiveram 
--uma votação superior 100.000 votos; 

SELECT CA.NUM_CANDIDATO, CA.NOME_CANDIDATO, 
	   C.NOME_CARGO, P.NOME_PARTIDO, SUM (V.QTD_VOTOS) AS QTDE_VOTOS
FROM PARTIDO AS P INNER JOIN CANDIDATO AS CA
     ON (P.COD_PARTIDO = CA.COD_PARTIDO)
	 INNER JOIN CARGO AS C
	 ON (CA.COD_CARGO = C.COD_CARGO)
	 INNER JOIN VOTACAO AS V
	 ON (CA.NUM_CANDIDATO = V.NUM_CANDIDATO)
GROUP BY CA.NUM_CANDIDATO, C.COD_CARGO, P.COD_PARTIDO
HAVING  SUM (V.QTD_VOTOS) > 1000

--o) Selecionar o número do candidato, 
--nome do candidato e a sua média de votos obtidos 
--nas zonas/seções; 
SELECT CA.NUM_CANDIDATO, CA.NOME_CANDIDATO, 
       Z.NUM_ZONA, Z.NUM_SECAO, AVG (V.QTD_VOTOS) AS MEDIA
FROM CANDIDATO AS CA INNER JOIN VOTACAO AS V
	 ON (CA.NUM_CANDIDATO = V.NUM_CANDIDATO)
	 INNER JOIN ZONA_SECAO AS Z
	 ON ((Z.NUM_ZONA, Z.NUM_SECAO) = (V.NUM_ZONA, V.NUM_SECAO))
	 GROUP BY CA.NUM_CANDIDATO, CA.NOME_CANDIDATO, 
       Z.NUM_ZONA, Z.NUM_SECAO

--p) Selecionar o código do partido, nome do partido, 
--nome do candidato e a média de 
--votos obtidos, apenas para os candidatos 
--que tiveram média de votos superior à média 
--de votos do seu partido.
SELECT * 
FROM
(SELECT P.COD_PARTIDO, CA.NOME_CANDIDATO, 
 AVG (QTD_VOTOS) AS MEDIA_CANDIDATO
FROM PARTIDO AS P INNER JOIN CANDIDATO AS CA
     ON (P.COD_PARTIDO = CA.COD_PARTIDO)
	 INNER JOIN VOTACAO AS V
	 ON (CA.NUM_CANDIDATO = V.NUM_CANDIDATO)
GROUP BY P.COD_PARTIDO, CA.NOME_CANDIDATO) AS T1
INNER JOIN 
(SELECT P.COD_PARTIDO, AVG (QTD_VOTOS) AS MEDIA_PARTIDO
FROM PARTIDO AS P INNER JOIN CANDIDATO AS CA
     ON (P.COD_PARTIDO = CA.COD_PARTIDO)
	 INNER JOIN VOTACAO AS V
	 ON (CA.NUM_CANDIDATO = V.NUM_CANDIDATO)
GROUP BY P.COD_PARTIDO) AS T2
ON (T1.COD_PARTIDO = T2.COD_PARTIDO)
WHERE T1.MEDIA_CANDIDATO > T2.MEDIA_PARTIDO
ORDER BY T1.COD_PARTIDO, MEDIA_CANDIDATO DESC

----
SELECT
    p.cod_partido,
    p.nome_partido,
    c.nome_candidato,
    AVG(v.qtd_votos) AS media_votos_candidato
FROM
    partido AS p
JOIN
    candidato AS c ON p.cod_partido = c.cod_partido
JOIN
    votacao AS v ON c.num_candidato = v.num_candidato
GROUP BY
    p.cod_partido,
    p.nome_partido,
    c.nome_candidato
HAVING
    AVG(v.qtd_votos) > (
        SELECT
            AVG(v2.qtd_votos)
        FROM
            partido AS p2
        JOIN
            candidato AS c2 ON p2.cod_partido = c2.cod_partido
        JOIN
            votacao AS v2 ON c2.num_candidato = v2.num_candidato
        WHERE
            p2.cod_partido = p.cod_partido
    )
ORDER BY
    p.COD_partido, media_votos_candidato DESC;