wjAFUYJX

1CREATE DATABASE IF NOT EXISTS test_guru;
2
3USE test_guru;
4
5CREATE TABLE categories (
6	id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
7	title varchar(50) NOT NULL,
8	PRIMARY KEY (id)
9);
10
11CREATE TABLE tests (
12	id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
13	title varchar(50) NOT NULL,
14	level smallint(5) unsigned NOT NULL,
15	category_id mediumint(8) unsigned,
16  	CONSTRAINT test_category FOREIGN KEY (category_id) REFERENCES categories(id),
17	PRIMARY KEY (id)	
18);
19
20CREATE TABLE questions (
21	id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
22	body varchar(255) NOT NULL,
23	test_id mediumint(8) unsigned,
24  	CONSTRAINT question_test FOREIGN KEY (test_id) REFERENCES tests(id),
25	PRIMARY KEY (id)	
26);
27
28INSERT INTO categories (id, title) VALUES
29	(1, 'Ruby'),
30	(2, 'SQL'),
31	(3, 'Ruby on Rails');
32
33INSERT INTO tests (id, title, level, category_id) VALUES 
34	(1, 'Advanced Ruby', 3, 1),
35	(2, 'SQL', 2, 2),
36	(3, 'RoR Basics', 0, 3),
37	(4, 'HTML Basics', 0, NULL),
38	(5, 'CSS Basics', 0, NULL);
39	
40INSERT INTO questions (id, body, test_id) VALUES 
41	(1, 'What is the difference between lambda and proc', 1),
42	(2, 'What is DDL?', 2),
43	(3, 'How to set RoR project locale?', 3),
44	(4, 'What is method_missig?', 1),
45	(5, 'What is a tag?', 4);
46
47
48SELECT * FROM tests WHERE id IN (2,3);
49
50SELECT * FROM questions WHERE test_id = 1;
51
52UPDATE tests SET title = 'CSS', level = 1 WHERE id = 5;
53
54DELETE FROM questions WHERE test_id = 4;
55
56SELECT tests.title, categories.title FROM tests
57LEFT JOIN categories ON tests.category_id = categories.id;
58
59SELECT questions.body, tests.title FROM questions
60LEFT JOIN tests ON questions.test_id = tests.id;