--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;