Projet BD Oster Luca

an anonymous user · April 08, 2021 · SQL
-- créer une table
CREATE TABLE sites (
  id_site INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  ville TEXT NOT NULL
);
CREATE TABLE vehicules (
  id_vehicule INTEGER PRIMARY KEY,
  couleur TEXT NOT NULL,
  date_mes DATE NOT NULL,
  immatriculation TEXT NOT NULL,
  id_modele INTEGER
);
CREATE TABLE loueurs (
  id_loueur INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  date_naissance DATE NOT NULL,
  num_permis TEXT NOT NULL
);
CREATE TABLE modeles (
  id_modele INTEGER PRIMARY KEY,
  nom_modele TEXT NOT NULL,
  motorisation TEXT NOT NULL,
  poids INTEGER NOT NULL,
  volume INTEGER NOT NULL,
  nb_places INTEGER NOT NULL,
  id_constructeur INTEGER,
  id_type INTEGER
);
CREATE TABLE constructeurs (
  id_constructeur INTEGER PRIMARY KEY,
  nom_marque TEXT NOT NULL
);
CREATE TABLE types (
  id_type INTEGER PRIMARY KEY,
  nom_type TEXT NOT NULL
);
CREATE TABLE heberger (
  id_heberger INTEGER PRIMARY KEY,
  date_debut DATE NOT NULL,
  date_fin DATE NOT NULL,
  id_vehicule INTEGER,
  id_site INTEGER
);
CREATE TABLE reserver (
  id_reserver INTEGER PRIMARY KEY,
  date_debut DATE NOT NULL,
  date_fin DATE NOT NULL,
  id_vehicule INTEGER,
  id_loueur INTEGER
);

-- insert some values
INSERT INTO sites VALUES (1, 'Carrefour Location', 'Villevieille');
INSERT INTO sites VALUES (2, 'Location Auto', 'Sommières');
INSERT INTO vehicules VALUES (1, 'Noir', '2020-04-27', '000xxx', 1);
INSERT INTO vehicules VALUES (2, 'Jaune', '2019-08-12', '001xxx', 2);
INSERT INTO vehicules VALUES (3, 'Blanc', '2019-02-09', '002xxx', 3);
INSERT INTO loueurs VALUES (1, 'Fabrice', '1998-11-03', '123num');
INSERT INTO loueurs VALUES (2, 'Benjamin', '1995-03-07', '124num');
INSERT INTO modeles VALUES (1, 'Clio', 'Essence', 1200, 900, 5, 1, 1);
INSERT INTO modeles VALUES (2, 'Huracan', 'Essence', 1500, 400, 2, 2, 1);
INSERT INTO modeles VALUES (3, 'Trafic', 'Diesel', 2000, 5000, 3, 1, 2);
INSERT INTO constructeurs VALUES (1, 'Renault');
INSERT INTO constructeurs VALUES (2, 'Lamborghini');
INSERT INTO types VALUES (1, 'Particulier');
INSERT INTO types VALUES (2, 'Utilitaire');
INSERT INTO heberger VALUES (1, '2019-08-15', '2021-01-01', 1, 1);
INSERT INTO heberger VALUES (2, '2020-01-07', '2021-04-23', 2, 2);
INSERT INTO heberger VALUES (3, '2018-06-28', '2020-04-03', 3, 1);
INSERT INTO reserver VALUES (1, '2020-04-15', '2020-04-18', 1, 1);
INSERT INTO reserver VALUES (2, '2019-07-08', '2019-07-12', 3, 2);

-- fetch some values
SELECT * FROM students WHERE gender = 'F';

Comments

Please sign up or log in to contribute to the discussion.