Created
October 30, 2017 08:36
-
-
Save Dehax/b0643b5423e44f0bd314147ec9d9d8e2 to your computer and use it in GitHub Desktop.
Kidlespay database (Delphi/Firebird)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT d.id_district, SUM(kmv.cost) FROM kindergartens k | |
INNER JOIN districts d ON d.id_district = k.district_id | |
INNER JOIN kindergartens_money_view kmv ON kmv.id_kindergarten = k.id_kindergarten | |
GROUP BY d.id_district; | |
/* итоговый запрос с условием на данные */ | |
/* Посчитать по каждому детскому саду количество детей в выбранной группе. */ | |
SELECT kindergartens.id_kindergarten, kindergartens.kindergarten_name, COUNT(*) FROM kindergartens | |
INNER JOIN children ON kindergartens.id_kindergarten = children.kindergarten_id | |
WHERE children.group_id = 1 | |
GROUP BY kindergartens.id_kindergarten, kindergartens.kindergarten_name; | |
/* итоговый запрос с условием на группы */ | |
/* Найти детские сады, количество детей в которых превышает заданное значение. */ | |
SELECT kindergartens.id_kindergarten, kindergartens.kindergarten_name, COUNT(*) AS num_children FROM kindergartens | |
INNER JOIN children ON kindergartens.id_kindergarten = children.kindergarten_id | |
GROUP BY kindergartens.id_kindergarten, kindergartens.kindergarten_name | |
HAVING COUNT(*) > 100; | |
/* итоговый запрос с условием на данные и на группы */ | |
/* Найти детские сады с младшими группами, количество детей в которых не превышает заданное значение. */ | |
SELECT k.id_kindergarten, k.kindergarten_name, COUNT(*) AS num_children FROM kindergartens k | |
INNER JOIN children c ON k.id_kindergarten = c.kindergarten_id | |
WHERE c.group_id = 1 | |
GROUP BY k.id_kindergarten, k.kindergarten_name | |
HAVING COUNT(*) <= 10; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment