MySQL 8.0

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

CREATE TABLE `keywords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `profession` varchar(255) NOT NULL,
  `region` varchar(255) NOT NULL,
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `keywords_rating_index` (`name`,`region`,`profession`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `keywords` (`name`, `profession`, `region`, `rating`) VALUES 
  ("git",  "front-end developer",  "москва",  10),
  ("git",  "дизайнер",  "москва",  3),
  ("git",  "backend developer",  "москва",  12),
  ("git",  "backend developer",  "новосибирск",  7), 
  ("MySql",  "backend developer",  "новосибирск",  7), 
  ("MySql",  "аналитик",  "новосибирск",  5);

SELECT * FROM keywords;

CREATE TABLE `salaries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keywordId` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
   `freq` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_keywordId` (`keywordId`),
  CONSTRAINT `fk_keywordId` FOREIGN KEY (`keywordId`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `salaries` (`keywordId`, `salary`, `freq`) VALUES 
  (1, 100000, 3), (1, 150000, 2),  (1, 200000, 1), 
  (2, 100000, 5), (2, 130000, 3), (2, 150000, 7), 
  (3, 100000, 1), (3, 150000, 7), (3, 170000, 4),
  (4, 100000, 5), (4, 125000, 5), (4, 150000, 2),
  (5, 100000, 8), (5, 150000, 1);

SELECT * FROM salaries;

Records: 6  Duplicates: 0  Warnings: 0




id
name
profession
region
rating


1
git
front-end developer
москва
10

2
git
дизайнер
москва
3

3
git
backend developer
москва
12

4
git
backend developer
новосибирск
7

5
MySql
backend developer
новосибирск
7

6
MySql
аналитик
новосибирск
5



Records: 14  Duplicates: 0  Warnings: 0




id
keywordId
salary
freq


1
1
100000
3

2
1
150000
2

3
1
200000
1

4
2
100000
5

5
2
130000
3

6
2
150000
7

7
3
100000
1

8
3
150000
7

9
3
170000
4

10
4
100000
5

11
4
125000
5

12
4
150000
2

13
5
100000
8

14
5
150000
1


/*
<!-- топ ключевых слов, для профессии "backend developer",  -->
+-------+-------------+
| name  | sum(rating) |
+-------+-------------+
| git   |          19 |
| MySql |           7 |
+-------+-------------+
*/
SELECT name, SUM(rating)
FROM keywords 
WHERE profession = 'backend developer'
GROUP BY name
ORDER BY 2 DESC, 1 LIMIT 10;


name
SUM(rating)


git
19

MySql
7


/*
<!-- зарплаты для ключевого слова git, которые участвовали в группировке -->
+----+-----------+--------+-----------+
| id | keywordId | salary | sum(freq) |
+----+-----------+--------+-----------+
|  7 |         3 | 100000 |         6 |
|  8 |         3 | 150000 |         9 |
|  9 |         3 | 170000 |         4 |
| 11 |         4 | 125000 |         5 |
+----+-----------+--------+-----------+
*/

-- без учёта лимитирования по задаче 1
SELECT MIN(salaries.id), MIN(salaries.keywordId), salaries.salary, SUM(salaries.freq)
FROM keywords
JOIN salaries ON salaries.keywordId = keywords.id
WHERE (keywords.name, keywords.profession) = ('git', 'backend developer')
GROUP BY salaries.salary


MIN(salaries.id)
MIN(salaries.keywordId)
salary
SUM(salaries.freq)


7
3
100000
6

8
3
150000
9

9
3
170000
4

11
4
125000
5


SELECT *
FROM keywords
JOIN salaries ON salaries.keywordId = keywords.id
WHERE (keywords.name, keywords.profession) = ('git', 'backend developer')
ORDER BY salary


id
name
profession
region
rating
id
keywordId
salary
freq


3
git
backend developer
москва
12
7
3
100000
1

4
git
backend developer
новосибирск
7
10
4
100000
5

4
git
backend developer
новосибирск
7
11
4
125000
5

3
git
backend developer
москва
12
8
3
150000
7

4
git
backend developer
новосибирск
7
12
4
150000
2

3
git
backend developer
москва
12
9
3
170000
4

id	name	profession	region	rating
1	git	front-end developer	москва	10
2	git	дизайнер	москва	3
3	git	backend developer	москва	12
4	git	backend developer	новосибирск	7
5	MySql	backend developer	новосибирск	7
6	MySql	аналитик	новосибирск	5

id	keywordId	salary	freq
1	1	100000	3
2	1	150000	2
3	1	200000	1
4	2	100000	5
5	2	130000	3
6	2	150000	7
7	3	100000	1
8	3	150000	7
9	3	170000	4
10	4	100000	5
11	4	125000	5
12	4	150000	2
13	5	100000	8
14	5	150000	1