Skip to content

Instantly share code, notes, and snippets.

@Sandstorm750

Sandstorm750/SQLtables

Created November 8, 2019 17:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Sandstorm750/cfb52f8febfb4d28c25f5660ed642d3f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Sandstorm750/cfb52f8febfb4d28c25f5660ed642d3f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SQLtables
postgres=# CREATE DATABASE chapter_four;
CREATE DATABASE
postgres=# CREATE TABLE tests (
postgres(# id serial PRIMARY KEY,
postgres(# title varchar(40),
postgres(# level int
postgres(# );
ОШИБКА: отношение "tests" уже существует
postgres=# SELECT*
postgres-# FROM tests;
id | title | level | category_id
----+-------+-------+-------------
1 | Ruby | 2 | 2
3 | CSS | 2 | 1
4 | SQL | 3 | 2
5 | RoR | 3 | 3
2 | HAML | 3 | 1
(5 строк)
postgres=# CREATE TABLE new_tests (
id serial PRIMARY KEY,
title varchar(40),
level int
);
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);
ОШИБКА: ошибка синтаксиса (примерное положение: "(")
СТРОКА 9: ('CSS advanced', 3);
^
postgres=# INSERT INTO new_tests(title, level) VALUES
('Ruby base', 0),
('Ruby advanced', 1),
('RoR base', 1),
('RoR advanced', 2),
('HTML base', 0),
('HTML advanced', 2),
('CSS base', 1),
('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 new_tests.level = user_tests.user_id;
name | title | level
----------+---------------+-------
Harry | Ruby advanced | 1
Harry | RoR base | 1
Harry | RoR advanced | 2
Germiona | Ruby advanced | 1
Germiona | RoR base | 1
Germiona | RoR advanced | 2
Ron | Ruby advanced | 1
Ron | RoR base | 1
Ron | RoR advanced | 2
Navil | Ruby advanced | 1
Navil | RoR base | 1
Navil | RoR advanced | 2
(12 строк)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment