Thinknetica SQL queries task

gistfile1.txt

# SQL DDL queries
# Create test_guru database
CREATE DATABASE test_guru;

# Create categories table
CREATE TABLE categories (id serial PRIMARY KEY, title varchar);

# Create tests table
CREATE TABLE tests (id serial PRIMARY KEY, title varchar, level varchar, category_id int);

# Create questions table
CREATE TABLE questions (id serial PRIMARY KEY, body varchar, test_id int);

#SQL DML queries
# Create 3 rows in categories table
INSERT INTO categories (title) VALUES ('Frontend'), ('Backend'), ('Game Development');

# Create 5 rows in tests table
INSERT INTO tests (title, level, category_id) VALUES
('HTML & CSS', 'beginner', 1),
('Rails', 'advanced', 2),
('NodeJS', 'advanced', 2),
('Unity Engine', 'advanced', 3),
('Elixir', 'master', 2);

# Create 5 rows in questions table
INSERT INTO questions (body, test_id) VALUES
('Float grid', 1),
('Thin controllers', 2),
('Background jobs', 2),
('NPM', 3),
('Terrain', 4);

# Select all tests with 'advanced' and 'master' level
SELECT * FROM tests WHERE level = 'advanced' OR level = 'master';

# Select all questions for specific test
SELECT * FROM questions WHERE test_id = 2;
# Alternative
SELECT * FROM questions JOIN tests ON tests.id = test_id WHERE title = 'Rails';

# Update title and level values for one row from tests table using one query
UPDATE tests SET title = 'HTML & CSS3', level = 'advanced' WHERE title = 'HTML & CSS';

# Delete all questions for one tests using one query
DELETE FROM questions WHERE test_id = 3;

# Select all tests titles and their categories names with one JOIN query
SELECT tests.title, categories.title FROM tests JOIN categories ON categories.id = category_id;

# Select all questions body attributes with title of corresponding tests with one JOIN query
SELECT body, title FROM questions JOIN tests ON tests.id = test_id;

https://gist.github.com/Lokideos/2d2422ea77827e08a0192bf549c71306#file-gistfile1-txt-L9 уровень лучше сделать числом.

https://gist.github.com/Lokideos/2d2422ea77827e08a0192bf549c71306#file-gistfile1-txt-L49 здесь удобно использовать алиасы атрибутов, чтобы в итоговом отношении было ясно где чей заголовок.