SELECT D_num, D_sname, COUNT(*)
FROM `kurs`.`separation` JOIN `kurs`.`doctors` USING(s_num) LEFT JOIN `kurs`.`patients` USING(D_num)
Where S_name = 'XXX' AND YEAR(D_receipt)<2018 AND ((D_dismissal IS NULL)OR(YEAR(D_dismissal)>2016))
Group by D_sname


SELECT S_num, S_name, S_head, SUM(Number_of_patients)
FROM `kurs`.`separation` JOIN `kurs`.`wards` USING(S_num)
GROUP BY S_num

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

Select D_num, D_sname, D_passport_id, D_birthday, D_address, Spetiality, MIN(D_receipt), S_num
FROM `kurs`.`doctors`
Where D_dismissal is NULL


SELECT `kurs`.`patients`.*
FROM `kurs`.`desease_histories` join `kurs`.`patients` using(P_number)
WHERE Dh_receipt=(SELECT MIN(Dh_receipt)
                  FROM `kurs`.`desease_histories`)


SELECT `kurs`.`doctors`.*
FROM `kurs`.`doctors` LEFT JOIN `kurs`.`patients` USING(D_num)
Where P_number is NULL


SELECT D_sname
FROM `kurs`.`doctors` O LEFT JOIN
(SELECT *
FROM `kurs`.`patients` LEFT JOIN`kurs`.`desease_histories` USING(P_number)
WHERE Dh_receipt LIKE '2017-03-%') D
ON O.D_num=D.D_num
WHERE P_number IS NULL