-
Notifications
You must be signed in to change notification settings - Fork 2
/
init_db.sql
89 lines (75 loc) · 12.4 KB
/
init_db.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
create database stu_info;
use stu_info;
create table polls_studentinfo(stu_id varchar(20),
stu_name varchar(20),
stu_pwd varchar(20),
stu_email varchar(50),
stu_course varchar(100),
stu_role varchar(1));
use stu_info;
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('001', 'AndyXia', 'csc3170', 'andy@xmail.com', 'csc3170,csc4150,csc4008,mat3007', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('002', 'DodoXia', 'csc3170', 'dodo@xmail.com', 'csc3170,csc4150,eco2011', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('003', 'AlexLi', 'csc3170', 'alex@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('004', 'ArthurLi', 'csc3170', 'arthur@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('005', 'zndyXia', 'csc3170', 'zndy@xmail.com', 'csc3170,csc4150,csc4008,mat3007', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('006', 'BndyXia', 'csc3170', 'bndy@xmail.com', 'csc3170,csc4150,eco2011', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('007', 'CndyLi', 'csc3170', 'cndy@xmailcom', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('008', 'DndyLi', 'csc3170', 'dndy@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('009', 'EndyXia', 'csc3170', 'endy@xmail.com', 'csc3170,csc4150,csc4008,mat3007', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('010', 'FndyXia', 'csc3170', 'fndy@xmail.com', 'csc3170,csc4150,eco2011', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('011', 'GndyLi', 'csc3170', 'gndy@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('012', 'HndyLi', 'csc3170', 'hndy@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('013', 'IndyXia', 'csc3170', 'indy@xmail.com', 'csc3170,csc4150,csc4008,mat3007', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('014', 'JndyXia', 'csc3170', 'jndy@xmail.com', 'csc3170,csc4150,eco2011', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('015', 'KndyLi', 'csc3170', 'kndy@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('016', 'LndyLi', 'csc3170', 'lndy@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('017', 'MndyXia', 'csc3170', 'mndy@xmail.com', 'csc3170,csc4150,csc4008,mat3007', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('018', 'NndyXia', 'csc3170', 'nndy@xmail.com', 'csc3170,csc4150,eco2011', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('019', 'OndyLi', 'csc3170', 'ondy@xmail.com', 'csc3170,csc4001', '0');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('020', 'PndyLi', 'csc3170', 'pndy@xmail.com', 'csc3170,csc4001', '1');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('021', 'LudwigBeethoven', 'csc3170', 'ludwig@xmail.com', 'csc4001', '1');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('022', 'RichardFeynman', 'csc3170', 'richard@xmail.com', 'csc4008', '1');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('023', 'AlanTuring', 'csc3170', 'alan@xmail.com', 'csc3170', '1');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('024', 'EdsgerDijkstra', 'csc3170', 'edsger@xmail.com', 'csc4150', '1');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('025', 'ClaudeShannon', 'csc3170', 'claude@xmail.com', 'mat3007', '1');
INSERT INTO `stu_info`.`polls_studentinfo` (`stu_id`, `stu_name`, `stu_pwd`, `stu_email`, `stu_course`, `stu_role`) VALUES ('026', 'FriedrichHayek', 'csc3170', 'friedrich@xmail.com', 'eco2011', '1');
select * from polls_studentinfo;
use stu_info;
create table polls_courseinfo(
course_id varchar(20), #id csc3170
course_name varchar(50), #name database design
course_instructor varchar(20),
course_description varchar(1000)
);
INSERT INTO `stu_info`.`polls_courseinfo` (`course_id`, `course_name`, `course_instructor`, `course_description`) VALUES ('eco2011', 'Basic Microeconomics', 'FriedrichHayek','This course covers the basic concepts of microeconomics. Major topics include: consumer preference and decision making, demand theory and applications, theory of the firm, perfect competition in partial equilibrium, monopoly, oligopoly, basic game theory, general competitive equilibrium, welfare economics, and market failure.');
INSERT INTO `stu_info`.`polls_courseinfo` (`course_id`, `course_name`, `course_instructor`, `course_description`) VALUES ('mat3007', 'Optimization', 'ClaudeShannon','This course will focus on the basics of optimization theories and applications. The first part of the course will be mainly about linear programming, including how to formulate a linear program, the simplex method and the duality theory. Then we will study nonlinear optimization, focusing on the optimality conditions and some useful algorithms. Integer programming and dynamic programming will also be briefly discussed in this course.');
INSERT INTO `stu_info`.`polls_courseinfo` (`course_id`, `course_name`, `course_instructor`, `course_description`) VALUES ('csc3170', 'Database System', 'AlanTuring','This is an introductory course in database systems. Topics discussed include database schemas and architectures, relational database, SQL, E-R-model, database design and normalization, indexing and hashing, transaction management, analytic processing, data warehouse and data mining.');
INSERT INTO `stu_info`.`polls_courseinfo` (`course_id`, `course_name`, `course_instructor`, `course_description`) VALUES ('csc4001', 'Software Engineering', 'LudwigBeethoven','The main goal of this course is to illustrate the basic concepts, knowledge framework and life cycle model of software engineering from an engineering practitioner’s point of view. The topics include: software processes, agile software development, requirement engineering, system modelling, architectural design, design and implementation, software testing, software evolution, software measurement, software reuse, etc. The course also illustrates and practices with common CASE tools used in software engineering and software management.');
INSERT INTO `stu_info`.`polls_courseinfo` (`course_id`, `course_name`, `course_instructor`, `course_description`) VALUES ('csc4008', 'Techniques for Data Mining', 'RichardFeynman','This course intends to build a strong theoretical understanding of data mining for the students. Meanwhile, the course also intends to provide you the opportunity to learn skills and content to practice and engage in scalable pattern discovery methods on massive data.');
INSERT INTO `stu_info`.`polls_courseinfo` (`course_id`, `course_name`, `course_instructor`, `course_description`) VALUES ('csc4150', 'Mobile Computing with Internet of Things', 'EdsgerDijkstra','This course covers wireless Internet technologies, mobile devices, sensing technologies, satellite technology, Internet of Things (IoT), big data analytics and mobile cloud applications. Important topics include smart phones, tablet computers, RFID, sensors, and GPS/BDS technologies, 4G/5G cellular networks, WiFi meshes, Zigbee, mobile OS, mobile, AI/machine learning, edge and cognitive computing for smart cities, healthcare systems, vehicle networks, etc. Undergraduate students take the course under CSC 4150. Postgraduates take it under the new number CIE 6130.');
select * from polls_courseinfo;
use stu_info;
create table polls_studentmark(
mark_id varchar(20),
stu_id varchar(20),
stu_name varchar(20),
course_id varchar(50),
assignment_1 varchar(20),
assignment_2 varchar(20),
assignment_3 varchar(20),
assignment_4 varchar(20),
assignment_5 varchar(20),
assignment_6 varchar(20));
use stu_info;
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('AndyXiacsc3170','001', 'AndyXia', 'csc3170', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('AndyXiacsc4150','001', 'AndyXia', 'csc4150', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('AndyXiacsc4008','001', 'AndyXia', 'csc4008', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('AndyXiamat3007','001', 'AndyXia', 'mat3007', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('DodoXiacsc3170','002', 'DodoXia', 'csc3170', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('DodoXiacsc4150','002', 'DodoXia', 'csc4150', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('DodoXiaeco2011','002', 'DodoXia', 'eco2011', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('AlexLicsc3170','003', 'AlexLi', 'csc3170', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('AlexLicsc4001','003', 'AlexLi', 'csc4001', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('ArthurLicsc3170','004', 'ArthurLi', 'csc3170', '100','100', '100','100', '100','100');
INSERT INTO `stu_info`.`polls_studentmark` (`mark_id`,`stu_id`, `stu_name`, `course_id`, `assignment_1`, `assignment_2`, `assignment_3`, `assignment_4`, `assignment_5`, `assignment_6`) VALUES ('ArthurLicsc4001','004', 'ArthurLi', 'csc4001', '100','100', '100','100', '100','100');
select * from polls_studentmark;