fork download
copy 
  1. -- =====================================================
  2. -- BASE DE DONNEES : PHARMACIE (VERSION MAROCAINE)
  3. -- FICHIER COMPLET PRET A IMPORTER
  4. -- =====================================================
  5.  
  6. DROP DATABASE IF EXISTS pharmacie;
  7. CREATE DATABASE pharmacie;
  8. USE pharmacie;
  9.  
  10. -- =====================================================
  11. -- TABLES
  12. -- =====================================================
  13.  
  14. CREATE TABLE Client (
  15.     id_client INT AUTO_INCREMENT PRIMARY KEY,
  16.     nom VARCHAR(50),
  17.     prenom VARCHAR(50),
  18.     ville VARCHAR(50)
  19. );
  20.  
  21. CREATE TABLE Fournisseur (
  22.     id_fourn INT AUTO_INCREMENT PRIMARY KEY,
  23.     nom VARCHAR(50),
  24.     ville VARCHAR(50)
  25. );
  26.  
  27. CREATE TABLE Medicament (
  28.     id_med INT AUTO_INCREMENT PRIMARY KEY,
  29.     nom VARCHAR(100),
  30.     prix DECIMAL(8,2),
  31.     stock INT,
  32.     famille VARCHAR(50),
  33.     id_fourn INT,
  34.     FOREIGN KEY (id_fourn) REFERENCES Fournisseur(id_fourn)
  35. );
  36.  
  37. CREATE TABLE Archive_Medicament (
  38.     id_med INT PRIMARY KEY,
  39.     nom VARCHAR(100),
  40.     prix DECIMAL(8,2),
  41.     stock INT,
  42.     famille VARCHAR(50),
  43.     id_fourn INT
  44. );
  45.  
  46. CREATE TABLE Commande (
  47.     id_cmd INT AUTO_INCREMENT PRIMARY KEY,
  48.     date_cmd DATE,
  49.     id_client INT,
  50.     FOREIGN KEY (id_client) REFERENCES Client(id_client)
  51. );
  52.  
  53. CREATE TABLE LigneCommande (
  54.     id_cmd INT,
  55.     id_med INT,
  56.     quantite INT,
  57.     PRIMARY KEY(id_cmd, id_med),
  58.     FOREIGN KEY (id_cmd) REFERENCES Commande(id_cmd),
  59.     FOREIGN KEY (id_med) REFERENCES Medicament(id_med)
  60. );
  61.  
  62. -- =====================================================
  63. -- CLIENTS
  64. -- =====================================================
  65.  
  66. INSERT INTO Client(nom, prenom, ville) VALUES
  67. ('El Amrani','Yassine','Tioumliline'),
  68. ('Alaoui','Salma','Imlil'),
  69. ('Bennani','Hamza','Tabant'),
  70. ('Tazi','Imane','Souk Larbaa'),
  71. ('Lahlou','Omar','Midelt'),
  72. ('Chraibi','Aya','Ain Taoujtat'),
  73. ('Idrissi','Mehdi','Imsfrane'),
  74. ('Berrada','Sara','Oued Kennar'),
  75. ('Fassi','Anas','Khmis Louta'),
  76. ('Mekouar','Lina','Badis'),
  77. ('El Idrissi','Youssef','Louizia'),
  78. ('Tahiri','Khadija','Tioumliline'),
  79. ('Amrani','Zakaria','Imlil'),
  80. ('Skalli','Nadia','Tabant'),
  81. ('Benjelloun','Rachid','Souk Larbaa'),
  82. ('Kadiri','Meryem','Midelt'),
  83. ('Cherkaoui','Soufiane','Ain Taoujtat'),
  84. ('Hassani','Othmane','Imsfrane'),
  85. ('Boussaid','Asmae','Oued Kennar'),
  86. ('Jabri','Bilal','Khmis Louta'),
  87. ('Lmrabet','Nour','Badis'),
  88. ('Zerouali','Achraf','Louizia'),
  89. ('El Fihri','Sanaa','Tioumliline'),
  90. ('Naciri','Younes','Imlil'),
  91. ('Ouazzani','Ilham','Tabant'),
  92. ('Benkirane','Karim','Souk Larbaa'),
  93. ('Rhazali','Hajar','Midelt'),
  94. ('Moutaouakil','Walid','Ain Taoujtat'),
  95. ('Bakkali','Chaimae','Imsfrane'),
  96. ('Ait Lahcen','Ayoub','Oued Kennar');
  97.  
  98. -- =====================================================
  99. -- FOURNISSEURS
  100. -- =====================================================
  101.  
  102. INSERT INTO Fournisseur(nom, ville) VALUES
  103. ('Atlas Pharma','Tioumliline'),
  104. ('Sahara Med','Imlil'),
  105. ('BioCare Maroc','Tabant'),
  106. ('Rif Medical','Souk Larbaa'),
  107. ('HealthPlus','Midelt'),
  108. ('Pharma Sud','Ain Taoujtat'),
  109. ('Med Atlas','Imsfrane'),
  110. ('NovaPharm','Oued Kennar'),
  111. ('ClinicaMed','Khmis Louta'),
  112. ('Vitalis Pharma','Badis');
  113.  
  114. -- =====================================================
  115. -- MEDICAMENTS
  116. -- =====================================================
  117.  
  118. INSERT INTO Medicament(nom, prix, stock, famille, id_fourn) VALUES
  119. ('Amoxidem 200mg',20.19,320,'Gastro-entérologie',1),
  120. ('Dolicetamol 400mg',20.01,43,'Ophtalmologie',2),
  121. ('Doliine 500mg',31.16,191,'Gastro-entérologie',3),
  122. ('Aspirmac 300mg',30.25,301,'Antibiotique',4),
  123. ('Clamofenadine 500mg',54.25,239,'Antalgique',5),
  124. ('Loxacetamol 500mg',10.89,216,'Antalgique',6),
  125. ('Loxamac 500mg',34.82,328,'Anti-inflammatoire',7),
  126. ('Doliprolol 400mg',68.78,169,'Pneumologie',8),
  127. ('Paraprofen 400mg',83.29,111,'Antidépresseur',9),
  128. ('Macaxine 100mg',81.45,482,'Anti-inflammatoire',10),
  129. ('Doliprofen 100mg',84.05,80,'Ophtalmologie',1),
  130. ('Omecetamol 400mg',27.13,308,'Antalgique',2),
  131. ('Amoxiprolol 200mg',37.56,151,'Cardiologie',3),
  132. ('Macaprolol 300mg',55.17,74,'Antihistaminique',4),
  133. ('Omeprazole 500mg',66.85,374,'Gastro-entérologie',5);
  134.  
  135. -- =====================================================
  136. -- ARCHIVE MEDICAMENTS
  137. -- =====================================================
  138.  
  139. INSERT INTO Archive_Medicament VALUES
  140. (1001,'OldZolpicetamol 200mg',21.89,0,'Pneumologie',1),
  141. (1002,'OldOmedem 100mg',10.05,0,'Antalgique',2),
  142. (1003,'OldMetoprane 500mg',11.18,0,'Antihistaminique',3),
  143. (1004,'OldMacaine 200mg',21.07,0,'Antidépresseur',4),
  144. (1005,'OldAmoxiprolol 500mg',37.33,0,'Cardiologie',5);
  145.  
  146. -- =====================================================
  147. -- COMMANDES
  148. -- =====================================================
  149.  
  150. INSERT INTO Commande(date_cmd, id_client) VALUES
  151. ('2024-01-08',1),
  152. ('2024-01-15',2),
  153. ('2024-02-03',3),
  154. ('2024-02-11',4),
  155. ('2024-03-07',5),
  156. ('2024-03-14',6),
  157. ('2024-03-20',7),
  158. ('2024-04-01',8),
  159. ('2024-04-09',9),
  160. ('2024-04-18',10);
  161.  
  162. -- =====================================================
  163. -- LIGNES DE COMMANDE
  164. -- =====================================================
  165.  
  166. INSERT INTO LigneCommande VALUES
  167. (1,1,2),
  168. (1,2,1),
  169. (2,3,5),
  170. (2,5,2),
  171. (3,4,3),
  172. (4,6,4),
  173. (5,7,1),
  174. (6,8,6),
  175. (7,9,2),
  176. (8,10,3),
  177. (9,11,5),
  178. (10,12,2);
  179.  
  180. -- =====================================================
  181. -- FIN
  182. -- =====================================================-- your code goes here
Success #stdin #stdout #stderr 0.01s 5304KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 6: near "DATABASE": syntax error
Error: near line 7: near "DATABASE": syntax error
Error: near line 8: near "USE": syntax error
https://ideone.com/UVw5xl
language:
SQLite (sqlite 3.27.2)
created:
5 hours ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!