База данных "Фирма вторсырья".
| Название таблицы | Поля таблицы |
| Income_o | (point, date, inc) |
| Outcome_o | (point, date, out) |
| Income | (code, point, date, inc) |
| Outcome | (code, point, date, out) |
Фирма имеет несколько пунктов приема вторсырья. Каждый пункт получает деньги для их выдачи сдатчикам вторсырья. Сведения о получении денег на пунктах приема записываются в таблицу:
Income_o(point, date, inc)
Первичным ключом является (point, date). При этом в столбец date записывается только дата (без времени), т.е. прием денег (inc) на каждом пункте производится не чаще одного раза в день. Сведения о выдаче денег сдатчикам вторсырья записываются в таблицу:
Outcome_o(point, date, out)
В этой таблице также первичный ключ (point, date) гарантирует отчетность каждого пункта о выданных деньгах (out) не чаще одного раза в день.
В случае, когда приход и расход денег может фиксироваться несколько раз в день, используется другая схема с таблицами, имеющими первичный ключ code:
Income(code, point, date, inc)
Outcome(code, point, date, out)
Здесь также значения столбца date не содержат времени.
Разминка:
SELECT i.*, o.out # из таблицы income выведутся все поля, а из outcome - только поле out FROM income i LEFT JOIN outcome o ON i.point=o.point AND i.date=o.date
Задание 29:
В предположении, что приход и расход денег на каждом пункте приема фиксируется не чаще одного раза в день [т.е. первичный ключ (пункт, дата)], написать запрос с выходными данными (пункт, дата, приход, расход). Использовать таблицы Income_o и Outcome_o.
SQL-запрос 29:
SELECT t1.point, t1.date, inc, out FROM income_o t1 LEFT JOIN outcome_o t2 ON t1.point = t2.point AND t1.date = t2.date UNION SELECT t2.point, t2.date, inc, out FROM income_o t1 RIGHT JOIN outcome_o t2 ON t1.point = t2.point AND t1.date = t2.date
Задание 30:
В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (первичным ключом в таблицах является столбец code), требуется получить таблицу, в которой каждому пункту за каждую дату выполнения операций будет соответствовать одна строка.
Вывод: point, date, суммарный расход пункта за день (out), суммарный приход пункта за день (inc).
Отсутствующие значения считать неопределенными (NULL).
SQL-запрос 30:
SELECT point, date, SUM(sum_out), SUM(sum_inc)
FROM (
SELECT point, date, SUM(inc) AS sum_inc, null AS sum_out
FROM Income
GROUP BY point, date
UNION
SELECT point, date, null AS sum_inc, SUM(out) AS sum_out
FROM Outcome
GROUP BY point, date) AS T
GROUP BY point, date
ORDER BY point
Задание 59:
Посчитать остаток денежных средств на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день.
Вывод: пункт, остаток.
SQL-запрос 59:
WITH T AS (
SELECT t1.point, t1.date, inc, out
FROM income_o t1
LEFT JOIN outcome_o t2
ON t1.point = t2.point
AND t1.date = t2.date
UNION
SELECT t2.point, t2.date, inc, out
FROM income_o t1
RIGHT JOIN outcome_o t2
ON t1.point = t2.point
AND t1.date = t2.date
)
SELECT point, SUM(
CASE WHEN inc IS NULL
THEN
CASE WHEN out IS NULL
THEN 0
ELSE 0 - out
END
ELSE
CASE WHEN out IS NULL
THEN inc
ELSE inc - out
END
END
)
FROM T
GROUP BY point
Задание 60:
Посчитать остаток денежных средств на начало дня 15/04/01 на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день.
Вывод: пункт, остаток.
Замечание. Не учитывать пункты, информации о которых нет до указанной даты.
SQL-запрос 60:
WITH T AS (
SELECT t1.point, t1.date, inc, out
FROM income_o t1
LEFT JOIN outcome_o t2
ON t1.point = t2.point
AND t1.date = t2.date
UNION
SELECT t2.point, t2.date, inc, out
FROM income_o t1
RIGHT JOIN outcome_o t2
ON t1.point = t2.point
AND t1.date = t2.date
)
SELECT point, SUM(
CASE WHEN inc IS NULL
THEN
CASE WHEN out IS NULL
THEN 0
ELSE 0 - out
END
ELSE
CASE WHEN out IS NULL
THEN inc
ELSE inc - out
END
END
)
FROM T
WHERE date < CONVERT(date, '15.04.2001', 104)
GROUP BY point
Задание 61:
Посчитать остаток денежных средств на всех пунктах приема для базы данных с отчетностью не чаще одного раза в день.
SQL-запрос 61:
SELECT SINC - SOUT FROM (
SELECT SUM(inc) AS SINC FROM income_o
) AS T1, (
SELECT SUM(out) AS SOUT FROM outcome_o
) AS T2
SELECT SINC - SOUT FROM (
SELECT SUM(
CASE WHEN inc IS NULL
THEN 0
ELSE inc
END
) AS SINC FROM income_o
) AS T1, (
SELECT SUM(
CASE WHEN out IS NULL
THEN 0
ELSE out
END
) AS SOUT FROM outcome_o
) AS T2
Задание 62:
Посчитать остаток денежных средств на всех пунктах приема на начало дня 15/04/01 для базы данных с отчетностью не чаще одного раза в день.
SQL-запрос 62:
SELECT SINC - SOUT FROM (
SELECT SUM(inc) AS SINC FROM income_o
WHERE date < CONVERT(date, '15.04.2001', 104)
) AS T1, (
SELECT SUM(out) AS SOUT FROM outcome_o
WHERE date < CONVERT(date, '15.04.2001', 104)
) AS T2
Задание 64:
Используя таблицы Income и Outcome, для каждого пункта приема определить дни, когда был приход, но не было расхода и наоборот.
Вывод: пункт, дата, тип операции (inc/out), денежная сумма за день.
SQL-запрос 64:
WITH T AS (
SELECT t1.point, t1.date, inc, out
FROM income t1
LEFT JOIN outcome t2
ON t1.point = t2.point
AND t1.date = t2.date
UNION
SELECT t2.point, t2.date, inc, out
FROM income t1
RIGHT JOIN outcome t2
ON t1.point = t2.point
AND t1.date = t2.date
)
SELECT point, date, (
CASE WHEN (inc IS NULL AND out IS NOT NULL)
THEN 'out'
ELSE CASE WHEN (inc IS NOT NULL AND out IS NULL)
THEN 'inc'
ELSE 'bad' END
END
) AS TYPE, (
CASE WHEN (inc IS NULL AND out IS NOT NULL)
THEN out
ELSE CASE WHEN (inc IS NOT NULL AND out IS NULL)
THEN inc
ELSE 0 END
END
) AS MONEY FROM T
WHERE (inc IS NULL AND out IS NOT NULL)
OR (inc IS NOT NULL AND out IS NULL)