DDL - DML Queries sample

gistfile1.txt

dmitry@linuxmint in ~
$ sqlite3 test_guru

sqlite> CREATE TABLE categories (
   ...> id INT PRIMARY KEY,
   ...> title VARCHAR(40)
   ...> );
sqlite> CREATE TABLE tests (
   ...> id INT PRIMARY KEY,
   ...> title VARCHAR(40),
   ...> level INT NOT NULL,
   ...> category_id INT NOT NULL,
   ...> FOREIGN KEY (category_id) REFERENCES categories(id)
   ...> );
sqlite> CREATE TABLE questions (
   ...> id INT PRIMARY KEY,
   ...> body TEXT,
   ...> test_id INT NOT NULL,
   ...> FOREIGN KEY (test_id) REFERENCES tests(id)
   ...> );
sqlite> INSERT INTO categories (id, title)
   ...> VALUES (1, 'FRONTEND'),
   ...> (2, 'BACKEND'),
   ...> (3, 'UX/UI');
sqlite> INSERT INTO tests (id, title, level, category_id)
   ...> VALUES (1, 'Ruby', 2, 2),
   ...> (2, 'JavaScript', 1, 1),
   ...> (3, 'Python', 3, 2),
   ...> (4, 'HTML', 0, 1),
   ...> (5, 'PHP', 2, 2);
sqlite> SELECT * FROM tests;
id          title       level       category_id
----------  ----------  ----------  -----------
1           Ruby        2           2          
2           JavaScript  1           1          
3           Python      3           2          
4           HTML        0           1          
5           PHP         2           2          

sqlite> INSERT INTO questions (id, body, test_id)
   ...> VALUES (1, 'Ruby most famous framework?', 1),
   ...> (2, 'JS framework designed by Facebook', 2),
   ...> (3, 'Python most known framework', 3),
   ...> (4, 'What does HTML stand for', 4),
   ...> (5, 'What is the latest version of PHP', 5);
sqlite> SELECT * FROM questions;
id          body                         test_id   
----------  ---------------------------  ----------
1           Ruby most famous framework?  1         
2           JS framework designed by Fa  2         
3           Python most known framework  3         
4           What does HTML stand for     4         
5           What is the latest version   5       

sqlite> SELECT * FROM tests WHERE level in (2,3);
id          title       level       category_id
----------  ----------  ----------  -----------
1           Ruby        2           2          
3           Python      3           2          
5           PHP         2           2          

sqlite> SELECT * FROM questions WHERE test_id=3;
id          body                         test_id   
----------  ---------------------------  ----------
3           Python most known framework  3         

sqlite> UPDATE tests SET title = "Ruby on Rails", level = 3 WHERE id = 1;

sqlite> SELECT tests.title, categories.title FROM tests INNER JOIN categories ON category_id = categories.id;
title          title     
-------------  ----------
Ruby on Rails  BACKEND   
JavaScript     FRONTEND  
Python         BACKEND   
HTML           FRONTEND  
PHP            BACKEND

sqlite> SELECT questions.body, tests.title FROM questions INNER JOIN tests ON test_id = tests.id;
body                         title        
---------------------------  -------------
Ruby most famous framework?  Ruby on Rails
JS framework designed by Fa  JavaScript   
Python most known framework  Python       
What does HTML stand for     HTML         
What is the latest version   PHP