· 7 years ago · Nov 22, 2018, 11:56 AM
1use jdoggett;
2
3TEE database.log
4
5SELECT 'STUDENTS' AS "Drop Table";
6DROP TABLE IF EXISTS students;
7
8SELECT 'STUDENTS' AS "Create Table";
9
10CREATE TABLE students
11( student_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
12 , first_name VARCHAR(30)
13 , last_name VARCHAR(30)
14 , level_id INT
15 , type_id INT
16 , num_festivals INT
17 , age INT
18 , instrument VARCHAR(50)
19 , user_id INT
20);
21
22ALTER TABLE students
23ADD FOREIGN KEY (level_id) REFERENCES level (level_id);
24
25ALTER TABLE students
26ADD FOREIGN KEY (type_id) REFERENCES type (type_id);
27
28ALTER TABLE students
29ADD FOREIGN KEY (user_id) REFERENCES user (user_id);
30
31
32INSERT INTO students
33(first_name
34, last_name
35, level_id
36, type_id
37, num_festivals
38, age
39, instrument
40, user_id
41)
42VALUES
43('John'
44, 'Doe'
45, (select level_id
46 from level
47 where level_id = 3)
48,(select type_id
49 from type
50 where type_id = 1)
51, 5
52,13
53, 'piano'
54,(select user_id
55 from user
56 where user_id = 1));
57
58
59INSERT INTO students
60(first_name
61, last_name
62, level_id
63, type_id
64, num_festivals
65, age
66, instrument
67, user_id
68)
69VALUES
70('Jane'
71, 'Doe'
72, (select level_id
73 from level
74 where level_id = 2)
75,(select type_id
76 from type
77 where type_id = 2)
78, 4
79,12
80, 'piano'
81,(select user_id
82 from user
83 where user_id = 1)
84);
85
86INSERT INTO students
87(first_name
88, last_name
89, level_id
90, type_id
91, num_festivals
92, age
93, instrument
94, user_id
95)
96VALUES
97('Aaron'
98, 'Decker'
99, (select level_id
100 from level
101 where level_id = 1)
102,(select type_id
103 from type
104 where type_id = 1)
105, 8
106,10
107, 'piano'
108,(select user_id
109 from user
110 where user_id = 1)
111);
112
113
114
115SELECT 'TYPE' AS "Drop Table";
116DROP TABLE IF EXISTS type;
117
118SELECT 'TYPE' AS "Create Table";
119
120CREATE TABLE type
121(type_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
122, type VARCHAR(30)
123);
124
125INSERT INTO type
126(type
127)
128VALUES
129('concerto');
130
131INSERT INTO type
132(type
133)
134VALUES
135('Two-Piano Duets');
136
137INSERT INTO type
138(type
139)
140VALUES
141('Duet');
142INSERT INTO type
143(type
144)
145VALUES
146('solo');
147
148
149SELECT 'LEVEL' AS "Drop Table";
150DROP TABLE IF EXISTS level;
151
152SELECT 'LEVEL' AS "Create Table";
153
154CREATE TABLE level
155( level_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
156, level VARCHAR(30)
157);
158
159INSERT INTO level
160(level
161)
162VALUES
163('beginning');
164
165INSERT INTO level
166(level
167)
168VALUES
169('intermediate');
170
171INSERT INTO level
172(level
173)
174VALUES
175('Pre-Advanced');
176
177INSERT INTO level
178(level
179)
180VALUES
181('advanced');
182
183INSERT INTO level
184(level
185)
186VALUES
187('Jr. concerto');
188
189INSERT INTO level
190(level
191)
192VALUES
193('Sr. concerto');
194
195
196
197SELECT 'USER' AS "Drop Table";
198DROP TABLE IF EXISTS user;
199
200SELECT 'USER' AS "Create Table";
201
202CREATE TABLE user
203( user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
204 , username VARCHAR(30) NOT NULL
205 , password VARCHAR(30) NOT NULL
206 , first_name VARCHAR(30) NOT NULL
207 , last_name VARCHAR(30) NOT NULL
208 , status INT
209 , teacher_id INT
210 , address VARCHAR(40) NOT NULL
211 , phone VARCHAR(40) NOT NULL
212 , email VARCHAR(50)
213);
214
215INSERT INTO user
216(username
217, password
218, first_name
219, last_name
220, status
221, teacher_id
222, address
223, phone
224, email)
225VALUES
226('teacher', 'teacher', 'Jennifer', 'Kartchner', 1, 1, '1215 NE Pioneer Rd', '543-3453', 'jenniferkartchner@gmail.com');
227
228NOTEE;