sql task for lesson_3

gistfile1.txt

PRAGMA foreign_keys=on;

create table categories (
id integer primary key autoincrement not null,
title text
);

create table tests (
id integer primary key autoincrement not null,
title text, 
level integer, 
category_id integer,
foreign key(category_id) references categories(id)
);

create table questions (
id integer primary key autoincrement not null,
body text,
tests_id integer,
foreign key(tests_id) references tests(id)
);

insert into categories (title) values ('first category');
insert into categories (title) values ('second category');
insert into categories (title) values ('third category');

insert into tests (title, level, category_id) values ('first test', 1, 1);
insert into tests (title, level, category_id) values ('second test', 1, 1);
insert into tests (title, level, category_id) values ('third test', 1, 1);
insert into tests (title, level, category_id) values ('four test', 2, 2);
insert into tests (title, level, category_id) values ('five test', 3, 3);

insert into questions (body, tests_id) values ('first question', 1);
insert into questions (body, tests_id) values ('second question', 1);
insert into questions (body, tests_id) values ('third question', 1);
insert into questions (body, tests_id) values ('first question', 2);
insert into questions (body, tests_id) values ('second question', 2);


select * from tests where level in (2,3);
select * from questions where tests_id = 1;

update tests set 
title = 'last test',
level = 4
where title = 'five test';

delete from questions where tests_id = 2;
select tests.title,categories.title from tests left join categories on tests.category_id = categories.id;
select questions.body, tests.title from questions left join tests on questions.tests_id = tests.id;

тут стоит alias для столбцов задать, иначе не понятно где чей title