· 8 years ago · Mar 08, 2018, 11:02 PM
1--
2-- Current Database standup
3--
4
5DROP DATABASE IF EXISTS standup;
6CREATE DATABASE standup;
7USE standup;
8
9
10DROP TABLE IF EXISTS STUDENT;
11CREATE TABLE STUDENT(
12 STUDENT_NAME VARCHAR(2) NOT NULL,
13 STUDENT_ID int NOT NULL,
14 SECTION_ID INT,
15 PRIMARY KEY(STUDENT_ID)
16);
17
18
19DROP TABLE IF EXISTS LANGUAGE;
20CREATE TABLE LANGUAGE(
21 LANGUAGE VARCHAR(50) NOT NULL,
22 LANGUAGE_ID INT NOT NULL,
23 PRIMARY KEY(LANGUAGE_ID)
24);
25
26DROP TABLE IF EXISTS SKILL;
27CREATE TABLE SKILL(
28 SKILL_ID INT,
29 VALUE VARCHAR(50),
30 PRIMARY KEY (SKILL_ID)
31);
32
33DROP TABLE IF EXISTS USERLANG;
34CREATE TABLE USERLANG(
35 USERLANG_ID INT NOT NULL AUTO_INCREMENT,
36 STUDENT_ID INT NOT NULL,
37 LANGUAGE_ID INT NOT NULL,
38 SKILL_LEVEL INT,
39 NOTES VARCHAR(255),
40 PRIMARY KEY(USERLANG_ID),
41 FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT (STUDENT_ID),
42 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE (LANGUAGE_ID),
43 FOREIGN KEY (SKILL_LEVEL) REFERENCES SKILL (SKILL_ID)
44);
45
46
47
48INSERT INTO STUDENT VALUES
49('AA', 1,1),('AB', 2,1),('AC', 3,1),('AD', 4,1),('AE', 5,1),
50('AF', 6,1),('AG', 7,1),('AH', 8,1),('AI', 9,1),('AJ', 10,1),
51('AK', 11,1),('AL', 12,1),('AM', 13,1),('AN', 14,1),('AO', 15,1),('AP', 16,1),
52('AQ', 17,2),('AR', 18,2),('AS', 19,2),('AT', 20,2),('AU', 21,2),('AV', 22,2),
53('AW', 23,2),('AX', 24,2),('AY', 25,2),('AZ', 26,2),('BA', 27,2),('BB', 28,2),
54('BC', 29,2),('BD', 30,2),('BE', 31,2),('BF', 32,2),('BG', 33,2),('BH', 34,2),
55('BI', 35,2),('BJ', 36,2),('BK', 37,2),('BL', 38,2),('BM', 39,2),('BN', 40,2);
56
57
58INSERT INTO SKILL VALUES(1,"HIGH"), (2, "MEDIUM"), (3, "LOW"), (4,"ULTRA LOW");
59
60INSERT INTO LANGUAGE VALUES
61('JAVA', 1),('JAVASCRIPT', 2),('C++', 3),('C', 4),('C#', 5),('PYTHON', 6),('PHP', 7),('COBOL', 8),('OTHER', 9);
62
63INSERT INTO USERLANG (STUDENT_ID, LANGUAGE_ID,SKILL_LEVEL,NOTES) VALUES
64(1,1,1,NULL),(1,3,2,NULL),
65(2,1,1,NULL),(2,3,2,NULL),(2,5,3,NULL),
66(3,1,1,NULL),(3,3,2,NULL),
67(4,1,1,NULL),(4,3,2,NULL),
68(5,1,1,NULL),(5,3,2,NULL),
69(6,1,1,NULL),(6,3,2,NULL),
70(7,1,1,NULL),(7,3,2,NULL),
71(8,1,1,NULL),(8,3,2,NULL),
72(9,1,1,NULL),(9,3,2,NULL),
73(10,1,1,NULL),(10,3,2,NULL),(10,6,3,NULL),
74(11,1,1,NULL),(11,6,2,NULL),
75(12,1,1,NULL),(12,3,2,NULL),
76(13,1,1,NULL),(13,3,2,NULL),
77(14,1,1,NULL),(14,3,2,NULL),
78(15,1,1,NULL),(15,5,2,NULL),
79(16,9,1,'NO PREFERENCE'),
80(17,1,1,NULL),(17,2,2,NULL),(17,7,3,NULL),
81(18,7,1,NULL),(18,9,2,'MYSQL'),(18,9,3,'HTML'),
82(19,2,1,NULL),(19,7,2,NULL),(19,6,3,NULL),
83(20,4,1,NULL),(20,3,2,NULL),(20,5,3,NULL),(20,9,4,'.NET'),
84(21,6,1,NULL),(21,1,2,NULL),(21,7,3,NULL),
85(22,1,1,NULL),(22,2,2,NULL),(22,3,3,NULL),
86(23,1,1,NULL),(23,3,3,NULL),(23,9,2,'ANDROID'),
87(24,1,1,NULL),(24,7,2,NULL),(24,3,3,NULL),
88(25,1,1,NULL),(25,4,2,NULL),(25,3,3,NULL),(25,9,4,'I HATE COMPUTING LANG BCOZ, MY MOM ONCE TOLD ME: GO TO THE GROCERY STORE, BRING 3 BOTTLES MILK, IF THEY HAVE EGGS BRING 6. I BROUGHT 6 BOTTLES MILK BECOZ THEY HAD EGGS!!!'),
89(26,1,1,NULL),(26,7,2,NULL),(26,9,3,'.NET'),
90(27,1,1,NULL),(27,5,2,NULL),(25,7,3,NULL),
91(28,1,1,NULL),(28,8,2,NULL),(28,9,3,'.NET'),
92(29,1,1,NULL),(29,3,2,NULL),(29,9,3,'.NET'),
93(30,1,1,NULL),(30,3,2,NULL),(30,4,3,NULL),
94(31,1,1,NULL),(31,3,2,NULL),(31,4,3,NULL),
95(32,1,1,NULL),(32,3,2,NULL),(32,4,3,NULL),
96(33,1,1,NULL),(33,3,2,NULL),(33,4,3,NULL),
97(34,1,1,NULL),(34,2,2,NULL),
98(35,5,1,NULL),(35,3,2,NULL),(35,4,3,NULL),
99(36,1,1,NULL),(36,2,2,NULL),(36,9,3,'HTML'),
100(37,1,1,NULL),(37,3,2,NULL),(37,9,3,'SQL'),
101(38,1,1,NULL),(38,4,2,NULL),(38,9,3,'SQL'),
102(39,1,1,NULL),(39,3,2,NULL),(39,4,3,NULL),
103(40,1,1,NULL),(40,9,2,'.NET'),(40,4,3,NULL);