-- Quais os jogadores de um determinado grupo

SELECT g.nome_grupo, j.nome_jogador
FROM grupo as g, jogador as j
WHERE g.cod_grupo = j.cod_grupo

SELECT g.nome_grupo, j.nome_jogador
FROM grupo as g INNER JOIN jogador as j ON (g.cod_grupo = j.cod_grupo)

-- Quais grupos não possuem quaisquer jogadores cadastrados

SELECT g.nome_grupo, j.nome_jogador
FROM grupo as g LEFT JOIN jogador as j ON (g.cod_grupo = j.cod_grupo)
WHERE j.nome_jogador is null

-- Quantas rodadas teve ate o presente momento

SELECT count(*) as 
FROM
	(SELECT DISTINCT num_rodada
	FROM jogo as j) as r

--- ou

SELECT count (DISTINCT num_rodada)
	FROM jogo as j

-- Quais peças foram sorteadas até o presente momento

SELECT peca_esquerda as peca
FROM jogo as j
UNION 
SELECT peca_direita
FROM jogo as j
WHERE peca_direita is not null
order by peca


SELECT j.peca_esquerda as peca, d.face_direita, d.face_esquerda 
FROM jogo as j inner join domino as d on (j.peca_esquerda = d.cod_peca)
UNION 
SELECT j.peca_direita as peca, d.face_direita, d.face_esquerda 
FROM jogo as j inner join domino as d on (j.peca_direita = d.cod_peca)
order by peca

SELECT DISTINCT d.face_direita, d.face_esquerda 
FROM jogo as j inner join domino as d 
    on (j.peca_esquerda = d.cod_peca or j.peca_direita = d.cod_peca)

-- Quais peças NÃO FORAM sorteadas até o presente momento


SELECT cod_peca, face_direita, face_esquerda
FROM domino as d 
WHERE d.cod_peca not IN 
	(SELECT peca_esquerda as peca
	FROM jogo as j
	UNION 
	SELECT peca_direita
	FROM jogo as j
	WHERE peca_direita is not null
	order by peca) 



SELECT d.cod_peca, d.face_direita, d.face_esquerda
FROM domino as d 
WHERE NOT EXISTS (
    SELECT *
    FROM (SELECT peca_esquerda as cod_peca
	FROM jogo as j
	UNION 
	SELECT peca_direita
	FROM jogo as j
	WHERE peca_direita is not null
	) as s
     WHERE s.cod_peca = d.cod_peca
)


SELECT d.cod_peca, d.face_direita, d.face_esquerda
FROM domino as d LEFT JOIN  
   (SELECT peca_esquerda as cod_peca, ordem 
    	 FROM jogo as j
	 UNION 
    SELECT peca_direita, ordem
	 FROM jogo as j
    WHERE peca_direita is not null) as S 
 ON (d.cod_peca = S.cod_peca)
WHERE ordem is null

-- Quais peças foram colocadas em uma determinada rodada 
-- de um grupo específico

SELECT d.face_esquerda, d.face_direita,  
       d2.face_esquerda, d2.face_direita  
FROM jogo as j  
     INNER JOIN domino as d ON (j.peca_esquerda = d.cod_peca)
     LEFT JOIN domino as d2 ON (j.peca_direita = d2.cod_peca)
WHERE num_rodada = 1 and cod_grupo = 12

-- Quais peças mais saíram por grupo

SELECT r.cod_grupo, r.peca, count (*) as vezes
FROM (SELECT j.cod_grupo, j.peca_esquerda as peca
	FROM jogo as j
	UNION ALL
	SELECT j.cod_grupo, j.peca_direita
	FROM jogo as j
	WHERE j.peca_direita is not null
	ORDER BY cod_grupo, peca) as r
GROUP BY r.cod_grupo, r.peca

-- Quantas vezes uma determinada peca saiu nas rodadas

SELECT r.num_rodada, r.peca, count (*) as vezes
FROM (SELECT j.num_rodada, j.peca_esquerda as peca
	FROM jogo as j
	UNION ALL
	SELECT j.num_rodada, j.peca_direita
	FROM jogo as j
	WHERE j.peca_direita is not null
	ORDER BY num_rodada, peca) as r
GROUP BY r.num_rodada, r.peca

-- Quantas vezes uma determinada peca saiu nas rodadas para um 
-- grupo especifico

SELECT r.cod_grupo, r.num_rodada, r.peca, count (*) as vezes
FROM (SELECT j.cod_grupo, j.num_rodada, j.peca_esquerda as peca
	FROM jogo as j
	UNION ALL
	SELECT j.cod_grupo, j.num_rodada, j.peca_direita
	FROM jogo as j
	WHERE j.peca_direita is not null
	ORDER BY cod_grupo, num_rodada, peca) as r
GROUP BY cod_grupo,r.num_rodada, r.peca

-- Qual peca mais saiu e qual menos saiu nas partidas?

SELECT r.peca, count (*) as vezes
FROM (SELECT j.peca_esquerda as peca
	FROM jogo as j
	UNION ALL
	SELECT j.peca_direita
	FROM jogo as j
	WHERE j.peca_direita is not null) as r
GROUP BY r.peca
ORDER BY vezes

-- Qual numero de face saiu mais vezes por grupo

SELECT fc.cod_grupo, fc.face, count (*) as vezes
FROM 
	(SELECT r.cod_grupo, d.face_esquerda as face
	FROM 
	(SELECT j.cod_grupo, j.peca_esquerda as peca
		FROM jogo as j
		UNION ALL
		SELECT j.cod_grupo, j.peca_direita
		FROM jogo as j
		WHERE j.peca_direita is not null
		ORDER BY cod_grupo, peca) as r
	 INNER JOIN domino as d on (r.peca = d.cod_peca)
	UNION ALL
	SELECT r.cod_grupo, d.face_direita
	FROM 
	(SELECT j.cod_grupo, j.peca_esquerda as peca
		FROM jogo as j
		UNION ALL
		SELECT j.cod_grupo, j.peca_direita
		FROM jogo as j
		WHERE j.peca_direita is not null
		ORDER BY cod_grupo, peca) as r
	 INNER JOIN domino as d on (r.peca = d.cod_peca)) as fc
GROUP BY fc.cod_grupo, fc.face
ORDER BY fc.cod_grupo, fc.face

-- Qual o total de casadinhas por grupo

SELECT g.nome_grupo, count (*) as vezes
FROM jogo as j INNER JOIN grupo as g 
   ON (j.cod_grupo = g.cod_grupo)
WHERE j.peca_direita is not null
GROUP by g.nome_grupo

-- Qual o total de casadinhas por rodada e grupo

SELECT g.nome_grupo, j.num_rodada, count (*) as vezes
FROM jogo as j INNER JOIN grupo as g 
   ON (j.cod_grupo = g.cod_grupo)
WHERE j.peca_direita is not null
GROUP by g.nome_grupo, j.num_rodada

-- Quem venceu o jogo 
-- Quem foi o lanterninha

SELECT g.nome_grupo, count (*) as vezes
FROM jogo as j INNER JOIN grupo as g 
   ON (j.cod_grupo = g.cod_grupo)
WHERE j.peca_direita is not null
GROUP by g.nome_grupo
ORDER BY vezes DESC