Ft8TsvTr

1I.
2
3# create db test_guru
4
5sqlite3 test_guru.db
6
7# create a table 'categories' with title
8
9CREATE TABLE IF NOT EXISTS categories (
10category_id INTEGER PRIMARY KEY,
11title TEXT NOT NULL
12);
13
14# create a table 'tests' with title, level, and a foreign key to categories
15
16CREATE TABLE IF NOT EXISTS tests (
17test_id INTEGER PRIMARY KEY,
18title TEXT NOT NULL,
19level INTEGER NOT NULL,
20category_id INTEGER,
21FOREIGN KEY (
22category_id
23)
24REFERENCES categories(category_id)
25);
26
27# create a table 'questions' with body and foreign key to tests
28
29CREATE TABLE IF NOT EXISTS questions (
30question_id INTEGER PRIMARY KEY,
31body TEXT NOT NULL,
32test_id INTEGER,
33FOREIGN KEY (
34test_id 
35)
36REFERENCES tests(test_id)
37);
38
39II.
40
41# create three lines in categories
42
43INSERT INTO categories (category_id, title) VALUES
44(1, 'BACKEND'),
45(2, 'FRONTEND'),
46(3, 'MOBILE');
47
48# create five lines in tests
49
50INSERT INTO tests (test_id, title, level, category_id) VALUES 
51(1, 'FIRST BACKEND TEST', 1, (SELECT category_id from categories WHERE category_id=1)),
52(2, 'SECOND BACKEND TEST', 2, (SELECT category_id from categories WHERE category_id=1)),
53(3, 'THIRD BACKEND TEST', 3, (SELECT category_id from categories WHERE category_id=1)),
54(4, 'FIRST FRONTEND TEST', 2, (SELECT category_id from categories WHERE category_id=2)),
55(5, 'FIRST MOBILE TEST', 2, (SELECT category_id from categories WHERE category_id=3));
56
57# create five lines in questions
58
59INSERT INTO questions (question_id, body, test_id) VALUES
60(1, 'How to create an iOS app?', (SELECT test_id from tests WHERE test_id=5)),
61(2, 'How to create a rails app?', (SELECT test_id from tests WHERE test_id=1)),
62(3, 'How to create a migration in rails app?', (SELECT test_id from tests WHERE test_id=1)),
63(4, 'How to create a react app?', (SELECT test_id from tests WHERE test_id=4)),
64(5, 'How to use device gem?', (SELECT test_id from tests WHERE test_id=2));
65
66# select tests with levels 2 and 3
67
68SELECT *
69FROM tests
70WHERE level IN (2, 3);
71
72# select all questions for a single test
73
74SELECT *
75FROM questions
76WHERE test_id = 1;
77
78# update title and level
79
80UPDATE tests
81SET title='SQL test', level=4 WHERE test_id=2;
82
83# delete all questions for a test
84
85DELETE
86FROM questions
87WHERE test_id=1;
88
89# select tests and categories using JOIN
90
91SELECT tests.title, categories.title
92FROM tests
93LEFT OUTER JOIN categories
94ON categories.category_id = tests.category_id;
95
96# select body (of question) and tests using JOIN
97
98SELECT questions.body, tests.title
99FROM questions
100LEFT OUTER JOIN tests
101ON tests.test_id = questions.test_id;