Помогите найти решение двух задач связанных с запросами в которых используются подзапросы

Question

Помогите, пожалуйста, разобраться как решить мои две задачи - столкнулся с ними во время обучения, потратил изрядно времени, но так и не смог понять как их решить.

Есть такая БД

Возможно, связи таблиц interns, interns_specialty и practice_result изображены не совсем верно, но по другому используемый редактор отказался из изображать. Суть - они связанны по полю intern_id, соотв. на диаграмме должна присутствовать связь interns.intern_id -> practice_result.intern_id

Запросы создания и наполнения таблиц

CREATE TABLE `interns` (
  `intern_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `f_name` varchar(20) NOT NULL,
  `l_name` varchar(20) NOT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `pr_st_date` date DEFAULT NULL,
  PRIMARY KEY (`intern_id`),
  KEY `ind` (`f_name`,`l_name`)
)

INSERT INTO `interns` VALUES 
(1,'Ivan','Ivanov','M','2020-09-22'),
(2,'Natalia','Kurtz','F','2021-01-12'),
(3,'Kristian','Tompson','M','2021-10-20'),
(4,'Libi','Uolsh','F','2021-12-30'),
(5,'Garry','Cutcher','M','2021-01-30'),
(6,'Steven','Larson','M','2021-02-25'),
(7,'Hugh','Laurie','M','2021-03-16'),
(8,'Jonny','Walker','M','2021-01-31'),
(9,'Ketty','Perri','F','2021-05-11'),
(10,'Liza','Torn','F','2020-11-28'),
(11,'Ely','Buggle','F','2021-01-16');




CREATE TABLE `interns_specialty` (
  `intern_id` smallint(10) NOT NULL,
  `specialty_id` varchar(10) NOT NULL,
  `mentor_id` int(4) NOT NULL,
  PRIMARY KEY (`intern_id`,`specialty_id`)
)

INSERT INTO `interns_specialty` VALUES 
(1,'1',2),
(2,'1',1),
(3,'4',2),
(4,'2',3),
(5,'3',1),
(6,'3',3),
(7,'4',4),
(8,'4',4),
(9,'5',1),
(10,'6',3);



CREATE TABLE `mentors` (
  `mentor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `f_name` varchar(20) NOT NULL,
  `l_name` varchar(20) NOT NULL,
  `pr_st_date` date DEFAULT NULL,
  `salary` int(11) NOT NULL,
  PRIMARY KEY (`mentor_id`)
)

INSERT INTO `mentors` VALUES (1,'Vladimir','Zhukov','2010-01-16',1400),
(2,'Nick','Natarov','2005-05-11',1600),
(3,'Stas','Stupin','2014-12-30',1200),
(4,'Lera','Jonson','2014-01-13',1100);



CREATE TABLE `practice_result` (
  `intern_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `mark` smallint(5) DEFAULT NULL,
  PRIMARY KEY (`intern_id`)
)

INSERT INTO `practice_result` VALUES 
(1,5),
(2,4),
(3,5),
(5,3),
(6,5),
(7,4),
(8,5),
(9,4),
(10,4);



CREATE TABLE `specialty` (
  `specialty_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `s_name` varchar(30) NOT NULL,
  PRIMARY KEY (`specialty_id`)
)

INSERT INTO `specialty` VALUES 
(1,'QA'),
(2,'Automation QA'),
(3,'PM'),
(4,'.Net Developer'),
(5,'C++ Developer'),
(6,'IOS Developer');

mysql version - Ver 15.1 Distrib 10.4.20-MariaDB, for osx10.10 (x86_64) using EditLine wrapper

Сами задачи:

Display the full name of the mentor whose all interns have passed the practice excellently. (оценка "excellently" в БД выглядит как "5")
Select a specialty with the highest number of excellent marks.

Моей последней попыткой было использовать запрос ниже:

SELECT mentors.f_name, mentors.l_name, COUNT(interns_specialty.mentor_id) from mentors
INNER JOIN interns_specialty ON 
mentors.mentor_id = interns_specialty.mentor_id 
INNER JOIN interns ON 
interns.intern_id = interns_specialty.intern_id
GROUP BY mentors.f_name, mentors.l_name
HAVING COUNT(interns_specialty.mentor_id) = (
    SELECT COUNT(interns_specialty.mentor_id) FROM interns_specialty
    INNER JOIN mentors ON
    mentors.mentor_id = interns_specialty.intern_id
    INNER JOIN interns ON
    interns_specialty.intern_id = interns.intern_id
    INNER JOIN practice_result ON
    practice_result.intern_id = interns.intern_id
    WHERE practice_result.mark = 5
);

Помогите, пожалуйста, с запросами для этих задач - очень хочу разобраться как именно можно правильно их решить. Заранее большое спасибо!

Answer 1

в первой задаче вам нужно соединить нужные таблицы, далее сделать группировку по ФИО наставника, а условием фильтрации после группировки будет, что число студентов равно числу пятерок. Последнее можно посчитать с помощью суммы

SELECT concat(m.f_name, ' ', m.l_name) AS mname
FROM mentors AS m
INNER JOIN interns_specialty AS sp ON sp.mentor_id = m.mentor_id
INNER JOIN practice_result AS pr ON pr.intern_id = sp.intern_id
GROUP BY  mname
HAVING sum(case when mark = 5 then 1 else 0 end) = count(pr.intern_id)
ORDER BY mname

Во втором случае опять таки вы соединяете все таблицы, однако, таблицу оценок вы джойните с учетом интереса к отметке "5". Далее подсчитаете число оценок и возьмете первую строку.
Такое решение будет иметь один недостаток, если по нескольким дисциплинам имеется одинаковое число пятерок, то будет выведена только первая

SELECT s.s_name, count(pr.intern_id) as cnt
FROM  specialty AS s
INNER JOIN interns_specialty AS sp ON sp.specialty_id = s.specialty_id
INNER JOIN practice_result AS pr ON (
     pr.intern_id = sp.intern_id
     AND pr.mark = 5
  )
GROUP BY s.s_name
ORDER BY cnt DESC
LIMIT 1

Поэтому лучше сначала подсчитать, каково это максимальное число пятерок, так что концовка приведенного выше запросы изменится на having с подзапросом

GROUP BY s.s_name
HAVING cnt = (
    SELECT count(pr.intern_id) as cnt
    FROM practice_result AS pr    
    INNER JOIN interns_specialty AS sp ON sp.intern_id = pr.intern_id AND mark = 5    
    GROUP BY specialty_id
    ORDER BY cnt DESC
    LIMIT 1
)

БЛОГ НА HUSL

Помогите найти решение двух задач связанных с запросами в которых используются подзапросы

Ответы (1 шт):