SHARE
TWEET

Untitled

a guest May 24th, 2019 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. sergey@comp:~$ sqlite3 test_guru
  2. sqlite> create table categories(id integer primary key autoincrement, title varchar(30));
  3. sqlite> create table tests(id integer primary key autoincrement, title varchar(30), level integer, categories_id integer not null, foreign key (categories_id) references categories(id));
  4. sqlite> create table questions (
  5.    ...> id integer primary key autoincrement,
  6.    ...> body text,
  7.    ...> tests_id integer not null,
  8.    ...> foreign key (tests_id) references tests(id)
  9.    ...> );
  10. sqlite> insert into categories(title) values
  11.    ...> ('HTML'),
  12.    ...> ('CSS'),
  13.    ...> ('RUBY');
  14. sqlite> insert into tests(title, level, categories_id) values
  15.    ...> ('Basic html', 0, 1),
  16.    ...> ('Basic css', 0, 2),
  17.    ...> ('Basic ruby', 0, 3),
  18.    ...> ('Advanced ruby', 3, 3),
  19.    ...> ('Average ruby', 2, 3);
  20. sqlite> insert into questions(body, tests_id) values
  21.    ...> ('That is html', 1),
  22.    ...> ('That is css?', 2),
  23.    ...> ('That is ruby?', 3),
  24.    ...> ('That is exeption?', 5),
  25.    ...> ('That is metaprogramming?', 4);
  26. sqlite> select * from tests where level = 2 or level = 3;
  27. 4|Advanced ruby|3|3
  28. 5|Average ruby|2|3
  29. sqlite> select * from questions where tests_id = 1;
  30. 1|That is html|1
  31. sqlite> update tests
  32.    ...> set title = 'Intermediate ruby', level = 2
  33.    ...> where id = 4;
  34. sqlite> delete from questions where tests_id = 4;
  35. sqlite> select tests.title, categories.title
  36.    ...> from tests
  37.    ...> join categories
  38.    ...> on tests.categories_id = categories.id;
  39. Basic html|HTML
  40. Basic css|CSS
  41. Basic ruby|RUBY
  42. Intermediate ruby|RUBY
  43. Average ruby|RUBY
  44. sqlite> select questions.body, tests.title
  45.    ...> from questions
  46.    ...> join tests
  47.    ...> on questions.tests_id = tests.id;
  48. That is html|Basic html
  49. That is css?|Basic css
  50. That is ruby?|Basic ruby
  51. That is exeption?|Average ruby
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top