Expert Query - JOIN's

--1.  Liste todos os jogadores de uma determinada equipa e a respectiva

--          posição onde jogam habitualmente, que estão inscritos na época 09/10 da 1a Liga portuguesa.

   

SELECT     dbo.Empregado.idEmpregado, dbo.Empregado.nome, dbo.Posicao.descricao

FROM         dbo.Inscrito INNER JOIN

                      dbo.Empregado ON dbo.Inscrito.idEmpregado = dbo.Empregado.idEmpregado INNER JOIN

                      dbo.Posicao ON dbo.Empregado.idPosicao = dbo.Posicao.idPosicao

WHERE     (dbo.Inscrito.fifaTeamCode = 'pt-benfica') AND (YEAR(dbo.Inscrito.dataInicio) = 2009)

--2.  Qual o número de golos que o jogador Liedson, do Sporting, marcou até à data,

--          em cada edição da ia Liga portuguesa.

SELECT     YEAR(dbo.Jogou.dataInicio) AS Edicao, COUNT(*) AS Golos

FROM         dbo.Estatistica INNER JOIN

                      dbo.Jogou ON dbo.Estatistica.idEmpregado = dbo.Jogou.idEmpregado AND dbo.Estatistica.dataJogo = dbo.Jogou.dataJogo AND

                      dbo.Estatistica.equipa1 = dbo.Jogou.equipa1

GROUP BY dbo.Estatistica.idEmpregado, dbo.Estatistica.idFacto, YEAR(dbo.Jogou.dataInicio)

HAVING      (dbo.Estatistica.idEmpregado = 'SPO04')-- Liedson

                   AND (dbo.Estatistica.idFacto = 8) -- Golos

--3.  De que países são as Selecções com a média de idade de jogadores mais baixa,

--          na Edição 2006 do Campeonato do Mundo. Devem ser seleccionadas todas as selecções

--          que se situarem no 1° e 2° lugar da média mais baixa.

SELECT     TOP (2) WITH TIES dbo.Edicao.fifaTeamCode, AVG(YEAR(GETDATE()) - YEAR(dbo.Empregado.dataNascimento)) AS MédiaIdades

FROM         dbo.Empregado INNER JOIN

                      dbo.Inscrito ON dbo.Empregado.idEmpregado = dbo.Inscrito.idEmpregado INNER JOIN

                      dbo.Edicao ON dbo.Inscrito.fifaTeamCode = dbo.Edicao.fifaTeamCode AND dbo.Inscrito.idCompeticao = dbo.Edicao.idCompeticao AND

                      dbo.Inscrito.dataInicio = dbo.Edicao.dataInicio

WHERE     (YEAR(dbo.Edicao.dataInicio) = 2010)-- Ano

GROUP BY dbo.Edicao.fifaTeamCode, dbo.Edicao.idCompeticao

HAVING      (dbo.Edicao.idCompeticao = 'MUNDL')

ORDER BY MédiaIdades

--4.  Quem foi/foram o(s) jogadore(s), e de que país(es), que ganharam maior número de

--          um determinado tipo de prémio em edições do Campeonato do Mundo, até à data.

SELECT     TOP (1) WITH TIES dbo.Premio.idEmpregado, dbo.Empregado.nome, dbo.Premio.fifaTeamCode, dbo.TipoPremio.descricao AS Premio, COUNT(dbo.Premio.id)

                      AS NumPremios

FROM         dbo.Premio LEFT OUTER JOIN

                      dbo.TipoPremio ON dbo.Premio.idTipoPremio = dbo.TipoPremio.idTipoPremio LEFT OUTER JOIN

                      dbo.Empregado ON dbo.Premio.idEmpregado = dbo.Empregado.idEmpregado

GROUP BY dbo.Premio.idTipoPremio, dbo.Premio.fifaTeamCode, dbo.Premio.idEmpregado, dbo.TipoPremio.descricao, dbo.Premio.idCompeticao, dbo.Empregado.nome

HAVING      (dbo.Premio.idCompeticao = 'MUNDL') AND (dbo.Premio.idTipoPremio = 2)

ORDER BY NumPremios DESC

--5.  Qual o país (ou países), que tem mais jogadores dessa nacionalidade, inscritos

--          na época 09/10 da 1a Liga portuguesa.

SELECT     TOP (1) WITH TIES dbo.Pais.nome, COUNT(*) AS Count

FROM         dbo.Inscrito INNER JOIN

                      dbo.Empregado ON dbo.Inscrito.idEmpregado = dbo.Empregado.idEmpregado INNER JOIN

                      dbo.Localidade ON dbo.Localidade.idLocalidade = dbo.Empregado.idLocalidade INNER JOIN

                      dbo.Pais ON dbo.Localidade.ISOalpha2Id = dbo.Pais.ISOalpha2Id

WHERE     (YEAR(dbo.Inscrito.dataInicio) = 2009) AND (dbo.Inscrito.idCompeticao = '1LGPT')

GROUP BY dbo.Pais.nome

ORDER BY dbo.Pais.nome DESC

--6.  Para cada uma das classificações, 1° lugar, 2° lugar e 3° lugar, mostre o número

--          de vezes que um determinado país se classificou nesse lugar, na competição do Campeonato do Mundo.

SELECT     fifaTeamCode, classificacao, COUNT(*) AS Vezes

FROM         dbo.Classificacao

WHERE     (idCompeticao = 'MUNDL') AND (ultimaEtapa = 's')

GROUP BY   fifaTeamCode, classificacao

HAVING      (classificacao IN ('1', '2', '3')) AND (fifaTeamCode = 'espanha')

--7.  Liste todos os jogos da época 09/10 da 1a Liga portuguesa em que o Benfica joga em casa.

SELECT     dataJogo, equipa1, equipa2

FROM         dbo.Jogo

WHERE     (equipa1 = 'PT-BENFICA') AND (equipaCasa = '1') AND (idCompeticao = '1LGPT') AND (YEAR(dataInicio) = 2009)

--8.  Qual o número de internacionalizações (jogos em que jogou), do jogador Ricardo Sá Pinto.

SELECT     COUNT(*) AS Internacionalizações

FROM         dbo.Jogou

GROUP BY  idEmpregado, idCompeticao

HAVING      (idCompeticao = 'MUNDL') AND (idEmpregado = 'SPO10') --Jogador Sá Pinto

--9.  Qual a equipa (jogadores e treinador), mais cara e o respectivo valor, do

--          campeonato da Premiership (1a Liga Inglesa), na época de 09/10.

--          Note que pode acontecer ser mais de uma.

SELECT     TOP (1) WITH TIES dbo.Equipa.fifaTeamCode, Equipa_1.descricao, SUM(dbo.Empregado.salario) AS Valor

FROM         dbo.Edicao INNER JOIN

                      dbo.Equipa ON dbo.Edicao.fifaTeamCode = dbo.Equipa.fifaTeamCode INNER JOIN

                      dbo.Contrato ON dbo.Equipa.fifaTeamCode = dbo.Contrato.fifaTeamCode INNER JOIN

                      dbo.Empregado ON dbo.Contrato.idEmpregado = dbo.Empregado.idEmpregado INNER JOIN

                      dbo.Equipa AS Equipa_1 ON dbo.Edicao.fifaTeamCode = Equipa_1.fifaTeamCode

GROUP BY dbo.Edicao.idCompeticao, dbo.Equipa.fifaTeamCode, YEAR(dbo.Edicao.dataInicio), Equipa_1.descricao

HAVING      (dbo.Edicao.idCompeticao = '1LGUK') AND (YEAR(dbo.Edicao.dataInicio) = 2009)

ORDER BY Valor DESC

--10. Liste todos os jogadores do Sporting de Braga que foram inscritos na época 09/10 da 1a Liga portuguesa

--          e nunca jogaram nenhum jogo.

                       

SELECT     dbo.Inscrito.idEmpregado, dbo.Empregado.nome

FROM         dbo.Inscrito INNER JOIN

                      dbo.Empregado ON dbo.Inscrito.idEmpregado = dbo.Empregado.idEmpregado

WHERE     (dbo.Inscrito.idEmpregado NOT IN

                          (SELECT     idEmpregado

                            FROM          dbo.Jogou

                            WHERE      (fifaTeamCode = 'PT-BRAGA') AND (YEAR(dataInicio) = 2009) AND (idCompeticao = '1LGPT')))

                           

           AND (dbo.Inscrito.fifaTeamCode = 'PT-BRAGA') AND (YEAR(dbo.Inscrito.dataInicio) = 2009) AND (dbo.Inscrito.idCompeticao = '1LGPT')

                       

                                           

--11. Qual o jogo e respectiva competição (note que pode acontecer ser mais de um),

--          onde ocorreram mais cartões vermelhos nos primeiros 90 minutos de jogo.

SELECT     TOP (1) WITH TIES dbo.Jogo.idCompeticao, dbo.Competicao.descricao, dbo.Estatistica.dataJogo, dbo.Estatistica.equipa1, dbo.Jogo.equipa2,

           COUNT(dbo.Estatistica.equipa1) AS CartoesVermelhos

FROM         dbo.Estatistica INNER JOIN

                      dbo.Jogou ON dbo.Estatistica.idEmpregado = dbo.Jogou.idEmpregado

                                                     AND dbo.Estatistica.dataJogo = dbo.Jogou.dataJogo

                                                     AND dbo.Estatistica.equipa1 = dbo.Jogou.equipa1 INNER JOIN

                      dbo.Jogo ON dbo.Jogou.dataJogo = dbo.Jogo.dataJogo

                                                     AND dbo.Jogou.equipa1 = dbo.Jogo.equipa1 INNER JOIN

                      dbo.Competicao ON dbo.Jogou.idCompeticao = dbo.Competicao.idCompeticao

GROUP BY dbo.Estatistica.idFacto, dbo.Estatistica.equipa1, dbo.Estatistica.valor, dbo.Estatistica.dataJogo, dbo.Jogo.equipa2, dbo.Jogo.idCompeticao, dbo.Competicao.descricao

HAVING  (dbo.Estatistica.idFacto = 6) AND (dbo.Estatistica.valor < 90)

ORDER BY CartoesVermelhos DESC

--12. Liste os nomes dos guarda-redes e respectivo país pelo qual jogam na edição de 2010

--          do campeonato do mundo

SELECT DISTINCT dbo.Jogou.idEmpregado, dbo.Empregado.nome, dbo.Pais.nome AS Pais

FROM         dbo.Edicao INNER JOIN

                      dbo.Jogou ON dbo.Edicao.dataInicio = dbo.Jogou.dataInicio AND dbo.Edicao.idCompeticao = dbo.Jogou.idCompeticao AND

                      dbo.Edicao.fifaTeamCode = dbo.Jogou.fifaTeamCode INNER JOIN

                      dbo.Empregado ON dbo.Jogou.idEmpregado = dbo.Empregado.idEmpregado INNER JOIN

                      dbo.Equipa ON dbo.Edicao.fifaTeamCode = dbo.Equipa.fifaTeamCode INNER JOIN

                      dbo.Pais ON dbo.Equipa.ISOalpha2Id = dbo.Pais.ISOalpha2Id

WHERE     (YEAR(dbo.Edicao.dataInicio) = 2010) AND (dbo.Edicao.idCompeticao = 'MUNDL') AND (dbo.Jogou.idPosicao = 'GD')

--13. Que equipas já foram treinadas pelo treinador José Mourinho até à data.

SELECT DISTINCT dbo.Equipa.fifaTeamCode, dbo.Equipa.descricao

FROM         dbo.Inscrito LEFT OUTER JOIN

                      dbo.Equipa ON dbo.Inscrito.fifaTeamCode = dbo.Equipa.fifaTeamCode

WHERE     (dbo.Inscrito.idEmpregado = 'BEN12')

--14. Quantas vezes o jogador Fábio Coentrão foi suplente na época 09/10 da 1a Liga portuguesa.

SELECT     COUNT(*) AS Suplente

FROM         dbo.Jogo INNER JOIN

                      dbo.Inscrito ON dbo.Jogo.equipa1 = dbo.Inscrito.fifaTeamCode AND dbo.Jogo.idCompeticao = dbo.Inscrito.idCompeticao AND

                      dbo.Jogo.dataInicio = dbo.Inscrito.dataInicio

WHERE     (dbo.Jogo.dataJogo NOT IN

                          (SELECT     Jogo_1.dataJogo

                            FROM          dbo.Jogo AS Jogo_1 INNER JOIN

                                                   dbo.Jogou ON Jogo_1.dataInicio = dbo.Jogou.dataInicio AND Jogo_1.idCompeticao = dbo.Jogou.idCompeticao AND

                                                   Jogo_1.dataJogo = dbo.Jogou.dataJogo AND Jogo_1.equipa1 = dbo.Jogou.equipa1 INNER JOIN

                                                   dbo.Competicao ON Jogo_1.idCompeticao = dbo.Competicao.idCompeticao

                            WHERE      (Jogo_1.idCompeticao = '1LGPT') AND (dbo.Jogou.idEmpregado = 'BEN06')))

GROUP BY YEAR(dbo.Inscrito.dataInicio), dbo.Inscrito.idEmpregado

HAVING      (YEAR(dbo.Inscrito.dataInicio) = 2009) AND (dbo.Inscrito.idEmpregado = 'BEN06')

--15. Qual o melhor marcador na época 09/10 da 1a Liga portuguesa, até à data.

--          Note que pode acontecer ser mais de um.

SELECT     TOP (1) WITH TIES COUNT(*) AS Golos, dbo.Estatistica.idEmpregado, dbo.Empregado.nome

FROM         dbo.Estatistica INNER JOIN

                      dbo.Jogou ON dbo.Estatistica.idEmpregado = dbo.Jogou.idEmpregado AND dbo.Estatistica.dataJogo = dbo.Jogou.dataJogo AND

                      dbo.Estatistica.equipa1 = dbo.Jogou.equipa1 INNER JOIN

                      dbo.Empregado ON dbo.Estatistica.idEmpregado = dbo.Empregado.idEmpregado

WHERE     (dbo.Estatistica.idFacto = 8)

GROUP BY dbo.Estatistica.idEmpregado, dbo.Jogou.dataInicio, dbo.Empregado.nome, dbo.Jogou.idCompeticao

HAVING      (YEAR(dbo.Jogou.dataInicio) = 2009) AND (dbo.Jogou.idCompeticao = '1LGPT')

ORDER BY Golos DESC

--16. Qual o jogador do Sporting de Braga que jogou mais minutos de jogo na época 09/10 da 1a Liga portuguesa,

--          até à data. Note que pode acontecer ser mais de um.

SELECT     TOP (1) WITH TIES dbo.Estatistica.idEmpregado, dbo.Empregado.nome,

--SOMA((Saida/Fim do jogo)-(Entrada/inicio de Jogo))

SUM((CASE WHEN dbo.Estatistica.idFacto = 10  THEN dbo.Estatistica.valor ELSE 0 END)

    - (CASE WHEN dbo.Estatistica.idFacto = 9 THEN dbo.Estatistica.valor ELSE 0 END)) AS Tempo

FROM         dbo.Equipa INNER JOIN

                      dbo.Jogou ON dbo.Equipa.fifaTeamCode = dbo.Jogou.fifaTeamCode INNER JOIN

                      dbo.Empregado ON dbo.Jogou.idEmpregado = dbo.Empregado.idEmpregado RIGHT OUTER JOIN

                      dbo.Estatistica ON dbo.Jogou.idEmpregado = dbo.Estatistica.idEmpregado AND dbo.Jogou.dataJogo = dbo.Estatistica.dataJogo AND

                      dbo.Jogou.equipa1 = dbo.Estatistica.equipa1

WHERE     (dbo.Equipa.fifaTeamCode = 'PT-BRAGA') AND (YEAR(dbo.Jogou.dataInicio) = 2009) AND (dbo.Jogou.idCompeticao = '1LGPT')

GROUP BY dbo.Estatistica.idEmpregado, dbo.Empregado.nome

ORDER BY Tempo DESC

--17. Quais os países que nunca participaram em nenhuma edição do campeonato do mundo.

SELECT     dbo.Pais.ISOalpha2Id, dbo.Pais.nome

FROM       dbo.Equipa INNER JOIN

                      dbo.Edicao ON dbo.Equipa.fifaTeamCode = dbo.Edicao.fifaTeamCode RIGHT OUTER JOIN

                      dbo.Pais ON dbo.Equipa.ISOalpha2Id = dbo.Pais.ISOalpha2Id

WHERE     (dbo.Edicao.fifaTeamCode IS NULL)

--18. Em que edições (ano) do Campeonato do Mundo participou uma dada equipa.

SELECT     YEAR(dbo.Edicao.dataInicio) AS Edição

FROM         dbo.Equipa INNER JOIN

                      dbo.Edicao ON dbo.Equipa.fifaTeamCode = dbo.Edicao.fifaTeamCode INNER JOIN

                      dbo.Pais ON dbo.Equipa.ISOalpha2Id = dbo.Pais.ISOalpha2Id INNER JOIN

                      dbo.Competicao ON dbo.Edicao.idCompeticao = dbo.Competicao.idCompeticao

WHERE     (dbo.Edicao.idCompeticao = 'MUNDL') AND (dbo.Equipa.ISOalpha2Id = 'IT')

--19. Quem foi o melhor marcador de todos os tempos em edições do Campeonato do Mundo.

SELECT     TOP (1) WITH TIES YEAR(dbo.Edicao.dataInicio) AS Ediçao, COUNT(*) AS Golos, dbo.Empregado.idEmpregado, dbo.Empregado.nome

FROM         dbo.Empregado INNER JOIN

                      dbo.Estatistica ON dbo.Empregado.idEmpregado = dbo.Estatistica.idEmpregado LEFT OUTER JOIN

                      dbo.Edicao INNER JOIN

                      dbo.Jogou ON dbo.Edicao.fifaTeamCode = dbo.Jogou.fifaTeamCode AND dbo.Edicao.idCompeticao = dbo.Jogou.idCompeticao AND

                      dbo.Edicao.dataInicio = dbo.Jogou.dataInicio INNER JOIN

                      dbo.Competicao ON dbo.Edicao.idCompeticao = dbo.Competicao.idCompeticao ON dbo.Estatistica.idEmpregado = dbo.Jogou.idEmpregado AND

                      dbo.Estatistica.dataJogo = dbo.Jogou.dataJogo AND dbo.Estatistica.equipa1 = dbo.Jogou.equipa1

WHERE     (dbo.Edicao.idCompeticao = 'MUNDL')

GROUP BY dbo.Estatistica.idFacto, dbo.Empregado.idEmpregado, YEAR(dbo.Edicao.dataInicio), dbo.Empregado.nome

HAVING      (dbo.Estatistica.idFacto = 8)

ORDER BY Golos DESC

--20. Quem são os jogadores que jogam (estão inscritos) no campeonato português de 09/10 e

--estão também inscritos para integrar a equipa representará Portugal no Campeonato do Mundo de 2010.

SELECT     dbo.Empregado.idEmpregado, dbo.Empregado.nome

FROM         dbo.Inscrito INNER JOIN

                      dbo.Competicao ON dbo.Inscrito.idCompeticao = dbo.Competicao.idCompeticao INNER JOIN

                      dbo.Empregado ON dbo.Inscrito.idEmpregado = dbo.Empregado.idEmpregado

WHERE     (YEAR(dbo.Inscrito.dataInicio) = 2010) AND (dbo.Empregado.idEmpregado IN

                          (SELECT     Inscrito_1.idEmpregado

                            FROM          dbo.Inscrito AS Inscrito_1 INNER JOIN

                                                   dbo.Competicao AS Competicao_1 ON Inscrito_1.idCompeticao = Competicao_1.idCompeticao

                            WHERE      (Competicao_1.descricao = N'1ª Liga Portuguesa') AND (YEAR(Inscrito_1.dataInicio) = 2009))) AND (dbo.Inscrito.idCompeticao = 'MUNDL')