--   JULIEN BERNARDO   --
-- TELECOM 1A APPRENTIS --

-- A - Création de la base de données
----------------------------------------------------------
-- 1 - Table SERVEUR

CREATE TABLE Serveur
  (numserv  INTEGER NOT NULL,
   nom      VARCHAR(50),
   prenom   VARCHAR(50),
   grade    VARCHAR(50),
   PRIMARY KEY (numserv)
  );

INSERT INTO Serveur VALUES (1,'Debois','Paul','barman');
INSERT INTO Serveur VALUES (2,'Labout', 'Victor','serveur g1');
INSERT INTO Serveur VALUES (3,'Riplat','Claude','serveur g2');
INSERT INTO Serveur VALUES (4,'Lacour','Jean','serveur g1');
INSERT INTO Serveur VALUES (5,'Duteil','Gilles','chef serveur');

-- 2 - Table TABLES

CREATE TABLE Tables
  (numtab INTEGER NOT NULL,
   NbPlace INTEGER,
   PRIMARY KEY (numtab)
  );

INSERT INTO Tables VALUES (10,4);
INSERT INTO Tables VALUES (11,6);
INSERT INTO Tables VALUES (12,8);
INSERT INTO Tables VALUES (13,4);
INSERT INTO Tables VALUES (14,6);
INSERT INTO Tables VALUES (15,4);
INSERT INTO Tables VALUES (16,4);
INSERT INTO Tables VALUES (17,6);
INSERT INTO Tables VALUES (18,2);
INSERT INTO Tables VALUES (19,4);

-- 3 - Création des autres tables

CREATE TABLE Plat
  (codePlat VARCHAR(15) NOT NULL,
   libelle  VARCHAR(150),
   typeplat VARCHAR(150),
   prix     FLOAT,
   PRIMARY KEY (codePlat)
  );

CREATE TABLE Commande
  (numcom INTEGER NOT NULL,
   numtab INTEGER,
   datecom DATE,
   NbPers INTEGER,
   HeurePay DATE,
   ModePay VARCHAR(50),
   PRIMARY KEY (numcom)
  );

CREATE TABLE Affecter
  (numtab INTEGER,
   dateAffect DATE,
   numserv INTEGER,
   PRIMARY KEY (numtab, dateAffect)
  );

CREATE TABLE Contient
  (numcom INTEGER,
   codeplat VARCHAR(50),
   qte INTEGER,
   PRIMARY KEY (numcom, codeplat)
  );

-- 4 - Clés étrangères

-- Commande
ALTER TABLE Commande ADD FOREIGN KEY (numtab) REFERENCES Tables(numtab);

-- Affecter
ALTER TABLE Affecter ADD FOREIGN KEY (numtab) REFERENCES Tables(numtab);
ALTER TABLE Affecter ADD FOREIGN KEY (numserv) REFERENCES Serveur(numserv);

-- Contient
ALTER TABLE Contient ADD FOREIGN KEY (numcom) REFERENCES Commande(numcom);
ALTER TABLE Contient ADD FOREIGN KEY (codeplat) REFERENCES Plat(codeplat);

-- 5 - Insertion des données

-- B - Requêtes SQL
-------------------------------------------------------------------------------

-- 1.  Liste de tous les serveurs avec toutes leurs informations
SELECT * FROM Serveur;

NUMSERV NOM    PRENOM GRADE       
      1 Debois Paul   barman      
      2 Labout Victor serveur g1  
      3 Riplat Claude serveur g2  
      4 Lacour Jean   serveur g1  
      5 Duteil Gilles chef serveur

-- 2.  Liste de toutes les commandes (n° et date) triées par date
SELECT numcom, datecom FROM Commande ORDER BY datecom DESC;

NUMCOM DATECOM            
    24 20.03.2014 00:00:00
    23 20.03.2014 00:00:00
    21 21.02.2014 00:00:00
    25 21.02.2014 00:00:00
    20 20.02.2014 00:00:00
    22 21.01.2014 00:00:00
    12 11.01.2014 00:00:00
    11 11.01.2014 00:00:00
    15 11.01.2014 00:00:00
    13 10.01.2014 00:00:00
    10 10.01.2014 00:00:00
    14 10.01.2014 00:00:00

-- 3.  Liste des serveurs (nom et grade) ayant servi la table 18