t2pVa1S1

1DROP DATABASE IF EXISTS Exercice_Aeroport;
2
3CREATE DATABASE Exercice_Aeroport;
4
5USE Exercice_Aeroport;
6
7CREATE TABLE Passager
8	(
9		pas_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
10		pas_name VARCHAR(60),
11		PRIMARY KEY (pas_id)
12	);
13
14	INSERT INTO Passager VALUES
15		(NULL,"passagerAlpha"),
16		(NULL,"passagerBeta"),
17		(NULL,"passagerCharlie"),
18		(NULL,"passagerDelta"),
19		(NULL,"passagerEcho"),
20		(NULL,"passagerFoxtrot"),
21		(NULL,"passagerGolf"),
22		(NULL,"passagerHotel"),
23		(NULL,"passagerIndia"),
24		(NULL,"passagerJuliet"),
25		(NULL,"passagerKilo"),
26		(NULL,"passagerLima"),
27		(NULL,"passagerMike"),
28		(NULL,"passagerNovember"),
29		(NULL,"passagerOscar"),
30		(NULL,"passagerPapa"),
31		(NULL,"passagerQuebec"),
32		(NULL,"passagerRomeo"),
33		(NULL,"passagerSierra"),
34		(NULL,"passagerTango"),
35		(NULL,"passagerUniform"),
36		(NULL,"passagerVictor"),
37		(NULL,"passagerWhiskey"),
38		(NULL,"passagerXray");
39		(NULL,"passagerYankee");
40		(NULL,"passagerZulu");
41#///////////////////////////////////////////////////////////////////////////
42
43CREATE TABLE Piste
44	(
45		pis_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
46		pis_name VARCHAR(60),
47		PRIMARY KEY (pis_id)
48	);
49
50	INSERT INTO Piste VALUES
51		(1,"piste_Nord_Big_Plane",1),
52		(2,"piste_Nord_Meduim_Plane",2),
53		(3,"piste_Nord_Small_Plane",3),
54		(4,"piste_Ouest_Big_Plane",4),
55		(5,"piste_Ouest_Meduim_Plane",5),
56		(6,"piste_Ouest_Small_Plane",6),
57		(7,"piste_Est_Big_Plane",7),
58		(8,"piste_Est_Meduim_Plane",8),
59		(9,"piste_Est_Small_Plane",9),
60		(10,"piste_Sud_Big_Plane",10),
61		(11,"piste_Sud_Meduim_Plane",11),
62		(12,"piste_Sud_Small_Plane",12);
63#///////////////////////////////////////////////////////////////////////////
64
65CREATE TABLE Billet
66	(
67		bil_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
68		bil_name VARCHAR(60),
69		PRIMARY KEY (bil_id)
70	);
71
72	INSERT INTO Billet VALUES
73		(1,"BilletLongCourrier", 1000€),
74		(1,"BilletMoyenCourrier", 700€),
75		(1,"BilletCourtCourrier", 400€);
76#///////////////////////////////////////////////////////////////////////////
77
78CREATE TABLE Aeroport
79	(
80		aero_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
81		aero_name VARCHAR(60),
82		PRIMARY KEY (aero_id)
83	);
84
85	INSERT INTO Aeroport VALUES
86		(1,"CrashPlaneAirport");
87#///////////////////////////////////////////////////////////////////////////
88
89CREATE TABLE Avion
90	(
91		avi_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
92		avi_name VARCHAR(60),
93		PRIMARY KEY (avi_id)
94	);
95
96	INSERT INTO Avion VALUES
97		(1,"A380-800"), #BigPlane
98		(2,"A340-300"), #MédiumPlane
99		(3,"A340-200"), #MédiumPlane
100		(4,"A318"), #SmallPlane
101		(5,"747-8"), #BigPlane
102		(6,"777-300"), #MédiumPlane
103		(7,"747-200"), #MédiumPlane
104		(8,"737-900ER"); #SmallPlane
105#///////////////////////////////////////////////////////////////////////////
106
107CREATE TABLE Employer
108	(
109		emp_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
110		emp_name VARCHAR(60),
111		PRIMARY KEY (emp_id)
112	);
113
114	INSERT INTO Hotesses VALUES
115
116		(2,"Marecher","Elvir"),
117		(2,"Belluci","Nikita"),
118		(2,"Polina","Anna"),
119		(2,"Pantera","Lexi"),
120		(2,"Volt","Jessie"),
121		(2,"Kate","Anissa"),
122
123#///////////////////////////////////////////////////////////////////////////