· 8 years ago · Aug 29, 2018, 11:58 AM
1drop table if exists Mark;
2drop table if exists Answer;
3drop table if exists Question;
4drop table if exists Test;
5drop table if exists [User];
6
7-- Create the user table
8create table [User] (
9 [User_ID] int not null identity (100, 11),
10 [Name] varchar(30) not null,
11 [Surname] varchar(30) not null,
12 [Password] varchar(30) not null,
13 [Type] varchar(10) not null,
14 [Style] int default 4 not null,
15 [Theme] int default 1 not null,
16
17 primary key([User_ID])
18)
19
20-- Create the test table
21create table [Test] (
22 [Test_ID] int not null identity(12, 3),
23 [Name] varchar(30) not null,
24
25 primary key([Test_ID])
26)
27
28-- Create the question table
29create table [Question] (
30 [Question_ID] int not null identity(13, 6),
31 [Test_ID] int not null,
32 [Question_Text] varchar(MAX) not null,
33 [ChoiceA] varchar(MAX) not null,
34 [ChoiceB] varchar(MAX) not null,
35 [ChoiceC] varchar(MAX) not null,
36 [ChoiceD] varchar(MAX) not null,
37 [Answer] int not null,
38 [Points] int not null,
39
40 primary key (Question_ID),
41 foreign key (Test_ID) references Test(Test_ID)
42)
43
44-- Create the answer table
45create table [Answer] (
46 [Question_ID] int not null,
47 [User_ID] int not null,
48 [User_Answer] int not null,
49
50 primary key (Question_ID, [User_ID]),
51 foreign key (Question_ID) references Question(Question_ID),
52 foreign key (User_ID) references [User]([User_ID])
53)
54
55create table Mark (
56 [User_ID] int not null,
57 [Test_ID] int not null,
58 [User_Mark] int not null,
59
60 foreign key (User_ID) references [User]([User_ID]),
61 foreign key (Test_ID) references [Test]([Test_ID]),
62 primary key (User_ID, Test_ID)
63)
64
65-- Insert data into test
66insert into Test
67values
68('Basic Computing'),
69('Cat Trivia')
70
71-- Inssert data into question
72insert into Question
73values
74(12, 'The Operating System is a...' , 'System Software' , 'Application Software' , 'Utility Software' , 'Malware' , 0, 1),
75(12, 'Files are organized in...' , 'RAM' , 'Cache' , 'Directories' , 'None of the above' , 2, 2),
76(12, 'A graphic artist would use...' , 'Accounting software' , 'Word processing application', 'Graphics presentation software', 'Antivirus software', 2, 5),
77(12, 'What type of software manages an inventory system?', 'Communications software', 'System software' , 'Accounting software' , 'Database software' , 3, 4),
78(12, 'What does the "R" in RAM stand for?' , 'Rewrite' , 'Read' , 'Readable' , 'Random' , 3, 2),
79(12, 'The brain of any computer system is...' , 'ALU' , 'Memory' , 'CPU' , 'Control Unit' , 2, 10),
80(12, 'The binary system uses powers of...' , '2' , '10' , '16' , '0' , 2, 7),
81(12, 'A single packet on a data link is known as...' , 'Path' , 'Frame' , 'Block' , 'Group' , 1, 9),
82(15, 'A cat has how many whiskers, on average?' , '12' , '24' , '8' , '16' , 1, 8),
83(15, 'Which brain is the cat’s brain most similar to?' , 'Dogs' , 'Humans' , 'Lions' , 'Birds' , 1, 2),
84(15, 'Outdoor-only cats live, on average, about...' , '7 to 10 years' , '3 to 5 years' , '12 to 15 years' , '15 to 18 years' , 1, 4),
85(15, 'A term for a group of cats is...' , 'Caggle' , 'Covey' , 'Clowder' , 'Clutch' , 2, 1),
86(15, 'A term for a group of kittens is...' , 'Kindle' , 'Kaboodle' , 'Kaggle' , 'Nook' , 0, 5)
87
88-- Inssert data into user
89insert into [User]
90values
91('Steve' , 'Jobs' , 'Apple' , 'Student', 4, 1),
92('Keegan', 'Fargher' , 'pen' , 'Student', 4, 1),
93('Susan' , 'November', 'Password123', 'Student', 4, 1),
94('Edward', 'Snowden' , 'nsa2' , 'Student', 4, 1),
95('John' , 'Snow' , 'wint3' , 'Student', 4, 1),
96('Ethel' , 'Kearns' , 'sob342' , 'Student', 4, 1),
97('Connah', 'Mcclain' , 'pineapple5' , 'Student', 4, 1),
98('Rhian' , 'Levine' , 'summer5' , 'Student', 4, 1),
99('Shreya', 'Mccoy' , 'roses53' , 'Student', 4, 1),
100('Sam' , 'Sung' , 'rock2' , 'Lecture', 4, 1),
101('Steve' , 'Ben' , 'cat2' , 'Lecture', 4, 1)