SQLtrying

postgres=# CREATE DATABASE chapter_four;
CREATE DATABASE
postgres=# CREATE TABLE new_tests (
postgres=#  id serial PRIMARY KEY,
postgres=#  title varchar(40),
postgres=#  level int
postgres=# );
CREATE TABLE
postgres=# CREATE TABLE users (
postgres(#  id serial PRIMARY KEY,
postgres(#  name varchar(30),
postgres(#  stage int
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE user_tests (
postgres(#  id serial PRIMARY KEY,
postgres(#  user_id int,
postgres(#  test_id int
postgres(# );
CREATE TABLE
postgres=# INSERT INTO new_tests(title, level) VALUES
postgres=# ('Ruby base', 0),
postgres=# ('Ruby advanced', 1),
postgres=# ('RoR base', 1),
postgres=# ('RoR advanced', 2),
postgres=# ('HTML base', 0),
postgres=# ('HTML advanced', 2),
postgres=# ('CSS base', 1),
postgres=# ('CSS advanced', 3);
INSERT 0 8
postgres=# INSERT INTO users(name, stage) VALUES
postgres-# ('Harry', 1),
postgres-# ('Germiona', 2),
postgres-# ('Ron', 0),
postgres-# ('Navil', 0);
INSERT 0 4
postgres=# SELECT*
postgres-# FROM new_tests;
 id |     title     | level
----+---------------+-------
  1 | Ruby base     |     0
  2 | Ruby advanced |     1
  3 | RoR base      |     1
  4 | RoR advanced  |     2
  5 | HTML base     |     0
  6 | HTML advanced |     2
  7 | CSS base      |     1
  8 | CSS advanced  |     3
(8 строк)

postgres=# SELECT* FROM users;
 id |   name   | stage
----+----------+-------
  1 | Harry    |     1
  2 | Germiona |     2
  3 | Ron      |     0
  4 | Navil    |     0
(4 строки)

postgres=# INSERT INTO user_tests(user_id, test_id) VALUES
postgres-# (1, 1),
postgres-# (1, 2),
postgres-# (1, 3),
postgres-# (2, 3),
postgres-# (2, 4),
postgres-# (3, 5),
postgres-# (3, 6),
postgres-# (4, 5),
postgres-# (4, 6),
postgres-# (4, 7);
INSERT 0 10
postgres=# SELECT* FROM user_tests;
 id | user_id | test_id
----+---------+---------
  1 |       1 |       1
  2 |       1 |       2
  3 |       1 |       3
  4 |       2 |       3
  5 |       2 |       4
  6 |       3 |       5
  7 |       3 |       6
  8 |       4 |       5
  9 |       4 |       6
 10 |       4 |       7
(10 строк)

postgres=# SELECT users.name, new_tests.title, new_tests.level
postgres=# FROM users, new_tests
postgres=# JOIN user_tests
postgres=# ON new_tests.id = user_tests.test_id
postgres=# WHERE user_tests.user_id = users.id;
   name   |     title     | level
----------+---------------+-------
 Harry    | Ruby base     |     0
 Harry    | Ruby advanced |     1
 Harry    | RoR base      |     1
 Germiona | RoR base      |     1
 Germiona | RoR advanced  |     2
 Ron      | HTML base     |     0
 Ron      | HTML advanced |     2
 Navil    | HTML base     |     0
 Navil    | HTML advanced |     2
 Navil    | CSS base      |     1
(10 строк)