fork download
copy 
  1. -- ==============================================================
  2. -- SCRIPT DE CREATION ET PEUPLEMENT : BASE DE DONNEES HOPITAL
  3. -- ==============================================================
  4. -- Note : Les contraintes (PK, FK, UNIQUE, CHECK) ont ete omises
  5. -- pour permettre aux etudiants
  6. -- de les ajouter eux-memes via les requetes ALTER TABLE des exercices.
  7. -- Autheur: Pr. K.BOUKHDIR
  8.  
  9. CREATE DATABASE Hospital
  10.  
  11. DROP TABLE IF EXISTS AFFECTATION;
  12. DROP TABLE IF EXISTS CONSULTATION;
  13. DROP TABLE IF EXISTS SERVICE;
  14. DROP TABLE IF EXISTS MEDECIN;
  15. DROP TABLE IF EXISTS PATIENT;
  16.  
  17. -- 1. CREATION DES TABLES (Sans contraintes)
  18.  
  19. CREATE TABLE PATIENT (
  20.     Id_patient INT,
  21.     Nom VARCHAR(50),
  22.     Prenom VARCHAR(50),
  23.     Ville VARCHAR(50),
  24.     Date_naissance DATE
  25. );
  26.  
  27. CREATE TABLE MEDECIN (
  28.     Id_medecin INT,
  29.     Nom VARCHAR(50),
  30.     Specialite VARCHAR(50),
  31.     Salaire DECIMAL(10,2)
  32. );
  33.  
  34. CREATE TABLE SERVICE (
  35.     Id_service INT,
  36.     Nom_service VARCHAR(50),
  37.     Batiment VARCHAR(50),
  38.     Budget DECIMAL(15,2)
  39. );
  40.  
  41. CREATE TABLE CONSULTATION (
  42.     Id_consult INT,
  43.     Id_patient INT,
  44.     Id_medecin INT,
  45.     Date_consultation DATE,
  46.     Tarif DECIMAL(10,2)
  47. );
  48.  
  49. CREATE TABLE AFFECTATION (
  50.     Id_affect INT,
  51.     Id_medecin INT,
  52.     Id_service INT,
  53.     Annee INT
  54. );
  55.  
  56. -- 2. INSERTION DES DONNEES (au moins 20 lignes par table)
  57.  
  58. -- Table: PATIENT
  59. INSERT INTO PATIENT (Id_patient, Nom, Prenom, Ville, Date_naissance) VALUES
  60. (1, 'Alaoui', 'Sara', 'Casablanca', '1990-05-14'),
  61. (2, 'Bennani', 'Omar', 'Rabat', '1985-08-22'),
  62. (3, 'Tazi', 'Imane', 'Fes', '1992-11-03'),
  63. (4, 'Idrissi', 'Youssef', 'Marrakech', '1978-02-15'),
  64. (5, 'Amrani', 'Salma', 'Casablanca', '1995-07-30'),
  65. (6, 'Chraibi', 'Hassan', 'Tanger', '1980-12-10'),
  66. (7, 'El Fassi', 'Nour', 'Casablanca', '2000-01-25'),
  67. (8, 'Zidane', 'Ali', 'Agadir', '1988-04-18'),
  68. (9, 'Mansouri', 'Kenza', 'Rabat', '1993-09-05'),
  69. (10, 'Radi', 'Hamza', 'Fes', '1982-06-20'),
  70. (11, 'Bourkia', 'Leila', 'Oujda', '1997-03-12'),
  71. (12, 'Tahiri', 'Mehdi', 'Casablanca', '1991-10-08'),
  72. (13, 'Benjelloun', 'Amina', 'Rabat', '1986-05-27'),
  73. (14, 'Filali', 'Rachid', 'Meknes', '1975-08-14'),
  74. (15, 'Jabri', 'Fatima', 'Casablanca', '1999-12-02'),
  75. (16, 'Qadiri', 'Samir', 'Fes', '1989-07-11'),
  76. (17, 'Lahlou', 'Yassine', 'Marrakech', '1994-02-28'),
  77. (18, 'Mernissi', 'Zineb', 'Tanger', '1983-11-19'),
  78. (19, 'Kettani', 'Reda', 'Casablanca', '1996-06-04'),
  79. (20, 'Oufkir', 'Meryem', 'Rabat', '1998-09-21'),
  80. (21, 'PatientFantome', 'SansConsult', 'Casablanca', '2002-01-01');
  81.  
  82. -- Table: MEDECIN
  83. INSERT INTO MEDECIN (Id_medecin, Nom, Specialite, Salaire) VALUES
  84. (1, 'Naciri', 'Cardiologie', 15000.00),
  85. (2, 'Karim', 'Pediatrie', 12000.00),
  86. (3, 'Berrada', 'Cardiologie', 16000.00),
  87. (4, 'El Amrani', 'Neurologie', 18000.00),
  88. (5, 'Daoudi', 'Dermatologie', 11000.00),
  89. (6, 'Slimani', 'Cardiologie', 14500.00),
  90. (7, 'Fahmi', 'Chirurgie', 25000.00),
  91. (8, 'Ghazali', 'Ophtalmologie', 13000.00),
  92. (9, 'Haddad', 'Radiologie', 14000.00),
  93. (10, 'Iraqi', 'Neurologie', 17500.00),
  94. (11, 'Jalil', 'Pediatrie', 11500.00),
  95. (12, 'Kabbaj', 'Pneumologie', 13500.00),
  96. (13, 'Loukili', 'Gastro-enterologie', 14200.00),
  97. (14, 'Moutaouakil', 'Chirurgie', 24000.00),
  98. (15, 'Nabil', 'Dermatologie', 10500.00),
  99. (16, 'Ouazzani', 'Cardiologie', 15500.00),
  100. (17, 'Qabbal', 'Ophtalmologie', 13200.00),
  101. (18, 'Rami', 'Psychiatrie', 12500.00),
  102. (19, 'Sbai', 'Radiologie', 14800.00),
  103. (20, 'MedSansConsult', 'Generaliste', 9000.00);
  104.  
  105. -- Table: SERVICE
  106. INSERT INTO SERVICE (Id_service, Nom_service, Batiment, Budget) VALUES
  107. (1, 'Urgences', 'A', 500000.00),
  108. (2, 'Reanimation', 'A', 800000.00),
  109. (3, 'Consultations Externes', 'B', 40000.00),
  110. (4, 'Imagerie Medicale', 'C', 1200000.00),
  111. (5, 'Laboratoire', 'C', 300000.00),
  112. (6, 'Chirurgie Bloc 1', 'A', 950000.00),
  113. (7, 'Maternite', 'B', 450000.00),
  114. (8, 'Pediatrie A', 'B', 150000.00),
  115. (9, 'Cardiologie Soins Intensifs', 'A', 600000.00),
  116. (10, 'Neurologie Clinique', 'D', 200000.00),
  117. (11, 'Oncologie', 'D', 1500000.00),
  118. (12, 'Orthopedie', 'B', 250000.00),
  119. (13, 'Dermatologie', 'C', 80000.00),
  120. (14, 'Ophtalmologie', 'C', 90000.00),
  121. (15, 'Gastro-enterologie', 'B', 120000.00),
  122. (16, 'Psychiatrie', 'E', 180000.00),
  123. (17, 'Pneumologie', 'D', 210000.00),
  124. (18, 'Pharmacie Centrale', 'E', 750000.00),
  125. (19, 'Dietetique', 'E', 30000.00),
  126. (20, 'ServiceVide', 'F', 20000.00);
  127.  
  128. -- Table: CONSULTATION
  129. INSERT INTO CONSULTATION (Id_consult, Id_patient, Id_medecin, Date_consultation, Tarif) VALUES
  130. (1, 1, 1, '2025-01-10', 250.00),
  131. (2, 2, 1, '2025-01-11', 250.00),
  132. (3, 3, 2, '2025-02-15', 150.00),
  133. (4, 4, 3, '2025-03-20', 300.00),
  134. (5, 5, 4, '2025-04-05', 400.00),
  135. (6, 1, 5, '2025-05-12', 80.00),
  136. (7, 6, 6, '2025-06-18', 200.00),
  137. (8, 7, 7, '2025-07-22', 1000.00),
  138. (9, 8, 8, '2025-08-30', NULL),
  139. (10, 9, 9, '2025-09-14', 250.00),
  140. (11, 1, 10, '2025-10-01', 350.00),
  141. (12, 1, 2, '2025-10-15', 150.00),
  142. (13, 10, 11, '2025-11-05', 150.00),
  143. (14, 11, 12, '2025-12-12', 200.00),
  144. (15, 12, 13, '2026-01-20', 250.00),
  145. (16, 13, 14, '2026-02-14', 90.00),
  146. (17, 14, 15, '2026-03-08', 150.00),
  147. (18, 15, 16, '2026-04-18', 300.00),
  148. (19, 16, 17, '2026-05-22', NULL),
  149. (20, 17, 18, '2026-06-11', 200.00),
  150. (21, 18, 19, '2026-07-09', 180.00),
  151. (22, 19, 1, '2026-08-15', 250.00),
  152. (23, 20, 2, '2026-09-02', 150.00),
  153. (24, 7, 1, '2026-09-10', 250.00),
  154. (25, 2, 1, '2026-09-11', 250.00),
  155. (26, 3, 1, '2026-09-12', 250.00),
  156. (27, 4, 1, '2026-09-13', 250.00),
  157. (28, 5, 1, '2026-09-14', 250.00),
  158. (29, 6, 1, '2026-09-15', 250.00),
  159. (30, 8, 1, '2026-09-16', 250.00),
  160. (31, 9, 1, '2026-09-17', 250.00),
  161. (32, 10, 1, '2026-09-18', 250.00);
  162.  
  163. -- Table: AFFECTATION
  164. INSERT INTO AFFECTATION (Id_affect, Id_medecin, Id_service, Annee) VALUES
  165. (1, 1, 9, 2025),
  166. (2, 1, 9, 2026),
  167. (3, 2, 8, 2026),
  168. (4, 3, 9, 2026),
  169. (5, 4, 10, 2026),
  170. (6, 5, 13, 2026),
  171. (7, 6, 9, 2026),
  172. (8, 7, 6, 2026),
  173. (9, 8, 14, 2026),
  174. (10, 9, 4, 2026),
  175. (11, 10, 10, 2026),
  176. (12, 11, 8, 2026),
  177. (13, 12, 17, 2026),
  178. (14, 13, 15, 2026),
  179. (15, 14, 6, 2026),
  180. (16, 15, 13, 2025),
  181. (17, 16, 9, 2026),
  182. (18, 17, 14, 2026),
  183. (19, 18, 16, 2026),
  184. (20, 19, 4, 2026),
  185. (21, 20, 3, 2026);
  186.  
Success #stdin #stdout #stderr 0.01s 5320KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 9: near "DATABASE": syntax error
https://ideone.com/MPth6j
language:
SQLite (sqlite 3.27.2)
created:
1 day 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!