USE tempdb;
GO

CREATE TABLE Stadio
(
      Nome varchar(30) NOT NULL
    , Citta varchar(30) NOT NULL
    , Capienza int NOT NULL
    , CONSTRAINT PK_Stadio PRIMARY KEY CLUSTERED
    (
        Nome
    )
)
GO

INSERT INTO Stadio (Nome, Citta, Capienza)
VALUES ('San Siro', 'Milano', 80000)
INSERT INTO Stadio (Nome, Citta, Capienza)
VALUES ('Delle Alpi', 'Torino', 70000)
INSERT INTO Stadio (Nome, Citta, Capienza)
VALUES ('Tardini', 'Parm', 30000)
INSERT INTO Stadio (Nome, Citta, Capienza)
VALUES ('San Paolo', 'Napoli', 70000)
INSERT INTO Stadio (Nome, Citta, Capienza)
VALUES ('Olimpico', 'Roma', 90000)
GO


CREATE TABLE Nazionale
(
      Paese varchar(30) NOT NULL
    , Continente varchar(20) NOT NULL
    , Categoria int NOT NULL
    , CONSTRAINT PK_Nazionale PRIMARY KEY CLUSTERED
    (
          Paese
    )
)
GO

INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Italia','Europa',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Francia','Europa',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Germania','Europa',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Spagna','Europa',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Portogallo','Europa',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Brasile','America',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Argentina','America',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Giappone','Asia',1)
INSERT INTO Nazionale (Paese, Continente, Categoria)
VALUES ('Russia','Asiz',1)
GO

CREATE TABLE Incontro
(
      NomeStadio varchar(30) NOT NULL
    , DataOra datetime NOT NULL
    , Squadra1 varchar(30) NOT NULL
    , Squadra2 varchar(30) NOT NULL
    , CONSTRAINT PK_Incontro PRIMARY KEY CLUSTERED
    (
          NomeStadio
        , DataOra
    )
)
GO

INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('San Siro', '20990701', 'Italia', 'Francia')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Olimpico', '20990710', 'Italia', 'Spagna')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Tardini', '20990720', 'Portogallo', 'Italia')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('San Paolo', '20050701', 'Italia', 'Germania')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('San Siro', '20080330', 'Italia', 'Brasile')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('San Paolo', '20080330', 'Spagna', 'Argentina')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Delle Alpi', '20080330', 'Francia', 'Portogallo')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Tardini', '20080410', 'Francia', 'Brasile')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Olimpico', '20080420', 'Argentina', 'Brasile')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Delle Alpi', '20990701', 'Spagna', 'Argentina')
INSERT INTO Incontro (NomeStadio, DataOra, Squadra1, Squadra2)
VALUES ('Delle Alpi', '20990730', 'Spagna', 'Italia')
GO

--SELECT * FROM Stadio
/*
Nome                           Citta                          Capienza
------------------------------ ------------------------------ -----------
Delle Alpi                     Torino                         70000
Olimpico                       Roma                           90000
San Paolo                      Napoli                         70000
San Siro                       Milano                         80000
Tardini                        Parm                           30000
*/

--SELECT * FROM Nazionale
/*
Paese                          Continente           Categoria
------------------------------ -------------------- -----------
Argentina                      America              1
Brasile                        America              1
Francia                        Europa               1
Germania                       Europa               1
Giappone                       Asia                 1
Italia                         Europa               1
Portogallo                     Europa               1
Russia                         Asiz                 1
Spagna                         Europa               1
*/

--SELECT * FROM Incontro ORDER BY DataOra
/*
NomeStadio                     DataOra                 Squadra1                       Squadra2
------------------------------ ----------------------- ------------------------------ ------------------------------
San Paolo                      2005-07-01 00:00:00.000 Italia                         Germania
San Paolo                      2008-03-30 00:00:00.000 Spagna                         Argentina
San Siro                       2008-03-30 00:00:00.000 Italia                         Brasile
Delle Alpi                     2008-03-30 00:00:00.000 Francia                        Portogallo
Tardini                        2008-04-10 00:00:00.000 Francia                        Brasile
Olimpico                       2008-04-20 00:00:00.000 Argentina                      Brasile
Delle Alpi                     2099-07-01 00:00:00.000 Spagna                         Argentina
San Siro                       2099-07-01 00:00:00.000 Italia                         Francia
Olimpico                       2099-07-10 00:00:00.000 Italia                         Spagna
Tardini                        2099-07-20 00:00:00.000 Portogallo                     Italia
Delle Alpi                     2099-07-30 00:00:00.000 Spagna                         Italia
*/

-- RICHIESTA 1
-- trovare le squadre che devono ancora affrontare l'Italia in uno stadio diverso da quello di Roma 
-- supponiamo che la data di oggi sia 30/03/2008 (20080330)
SELECT
  CASE WHEN Squadra1 = 'Italia' THEN Squadra2 ELSE Squadra1 END AS Squadra
FROM
  Incontro
WHERE
  (Squadra1 = 'Italia' OR Squadra2 = 'Italia') AND
  NomeStadio <> 'Olimpico' AND
  DataOra >= '20080330'

-- RICHIESTA 2
-- selezionare le partite di oggi in cui giocano squadre di continenti diversi
SELECT
    I.*
  , N1.Continente
  , N2.Continente
FROM 
  Incontro    AS I
  JOIN Nazionale  AS N1  ON N1.Paese = I.Squadra1
  JOIN Nazionale  AS N2  ON N2.Paese = I.Squadra2
WHERE
  I.DataOra >= '20080330' AND I.DataOra < '20080331' AND
  N1.Continente <> N2.Continente

-- pulizia
DROP TABLE Incontro
DROP TABLE Stadio
DROP TABLE Nazionale
GO