/*** Base de données portables ***/

/** Création des 3 tables **/

CREATE TABLE modele(
  id_modele INT PRIMARY KEY,
  nom TEXT,
  fabricant TEXT,
  annee INT
);

CREATE TABLE systeme(
  id_systeme INT PRIMARY KEY,
  nom TEXT,
  version TEXT
);

CREATE TABLE poste(
  id_poste INT PRIMARY KEY,
  num_serie TEXT UNIQUE NOT NULL,
  nom TEXT,
  id_modele INT,
  id_systeme INT,
  FOREIGN KEY (id_modele) REFERENCES modele(id_modele),
  FOREIGN KEY (id_systeme) REFERENCES systeme(id_systeme)
);

/** Insertion des données **/
INSERT INTO modele (id_modele, nom, fabricant, annee)
  VALUES
    (1, "lenovo x260", "lenovo", 2016),
    (2, "lenovo x270", "lenovo", 2017),
    (3, "latitude e7470", "dell", 2018),
    (4, "hp probook 400", "hp", 2018),
    (5, "hp probook 400", "hp", 2019),
    (6, "hp probook 400", "hp", 2020),
    (7, "hp probook 600", "hp", 2021)
  ;

INSERT INTO systeme (id_systeme, nom, version)
  VALUES
    (1, "debian", "bullseye"),
    (2, "debian", "buster"),
    (3, "fedora linux", "35"),
    (4, "fedora linux", "36"),
    (5, "ubuntu", "18.04"),
    (6, "ubuntu", "20.04")
  ;

INSERT INTO poste (id_poste, num_serie, nom, id_modele, id_systeme)
    VALUES
      (1, "S2069FST", "Portable-01", 5, 4),
      (2, "JQ192AFH", "PORTABLE-02", 3, 1),
      (3, "N1J4HSMY", "PORTABLE-03", 7, 5),
      (4, "GENS27BP", "PORTABLE-04", 6, 2),
      (5, "PW5A4Z5Z", "Portable-05", 2, 2),
      (6, "ZM067CMA", "portable-06", 7, 1),
      (7, "CEQFQIDJ", "PORTABLE-07", 7, 6),
      (8, "Y4HWLYV7", "PORTABLE-08", 1, 4),
      (9, "U8VDRSN0", "Portable-09", 2, 1),
      (10, "6NW82QRC", "PORTABLE-10", 5, 6),
      (11, "O5BHIVUN", "PORTABLE-11", 7, 6),
      (12, "LLJ62S01", "Portable-12", 2, 5),
      (13, "DCY6QKHW", "portable-13", 4, 1),
      (14, "0LUGMXT7", "PORTABLE-14", 5, 6)
    ;

/** Affichage des données **/
SELECT num_serie, poste.nom AS nom_poste, modele.nom AS nom_modele, fabricant, annee, systeme.nom AS nom_systeme, version
FROM poste
JOIN systeme ON poste.id_systeme = systeme.id_systeme
JOIN modele ON poste.id_modele = modele.id_modele;
