Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE SEQUENCE user_id_increment;
CREATE TABLE users(
	id smallint NOT NULL DEFAULT nextval('user_id_increment') PRIMARY KEY,
	name varchar(255)
);
ALTER SEQUENCE user_id_increment OWNED BY users.id;
CREATE SEQUENCE skills_id_increment;
CREATE TABLE skills(
	id smallint NOT NULL DEFAULT nextval('skills_id_increment') PRIMARY KEY,
	skill varchar(255)
);
ALTER SEQUENCE skills_id_increment OWNED BY skills.id;
CREATE TABLE users_skills(
	id_user smallint NOT NULL,
	id_skill smallint NOT NULL
);
INSERT INTO skills(skill) VALUES ('HTML'), ('CSS'), ('JavaScript'), ('PHP'), ('LESS'), ('C++'), ('C#'), ('SQL'), ('Python'), ('Java'), ('Ruby'), ('Swift') RETURNING id, skill;


id
skill


1
HTML

2
CSS

3
JavaScript

4
PHP

5
LESS

6
C++

7
C#

8
SQL

9
Python

10
Java

11
Ruby

12
Swift


INSERT INTO users(name) VALUES ('Иван'), ('Надежда'), ('Виктор'), ('Артём'), ('Анна') RETURNING id, name;


id
name


1
Иван

2
Надежда

3
Виктор

4
Артём

5
Анна


INSERT INTO users_skills(id_user, id_skill) VALUES (1, 1), (1, 2), (1, 3), (2, 6), (2, 7), (3, 4), (3, 8), (4, 9), (4, 11), (5, 1), (5, 2), (5, 3), (5, 4), (5, 5), (5, 8) RETURNING id_user, id_skill;


id_user
id_skill


1
1

1
2

1
3

2
6

2
7

3
4

3
8

4
9

4
11

5
1

5
2

5
3

5
4

5
5

5
8


SELECT users.id, users.name, skills.skill
FROM users
INNER JOIN users_skills ON (users.id = users_skills.id_user)
INNER JOIN skills ON (users_skills.id_skill = skills.id);


id
name
skill


1
Иван
HTML

1
Иван
CSS

1
Иван
JavaScript

2
Надежда
C++

2
Надежда
C#

3
Виктор
PHP

3
Виктор
SQL

4
Артём
Python

4
Артём
Ruby

5
Анна
HTML

5
Анна
CSS

5
Анна
JavaScript

5
Анна
PHP

5
Анна
LESS

5
Анна
SQL

id	skill
1	HTML
2	CSS
3	JavaScript
4	PHP
5	LESS
6	C++
7	C#
8	SQL
9	Python
10	Java
11	Ruby
12	Swift

id	name	skill
1	Иван	HTML
1	Иван	CSS
1	Иван	JavaScript
2	Надежда	C++
2	Надежда	C#
3	Виктор	PHP
3	Виктор	SQL
4	Артём	Python
4	Артём	Ruby
5	Анна	HTML
5	Анна	CSS
5	Анна	JavaScript
5	Анна	PHP
5	Анна	LESS
5	Анна	SQL