Skip to content

Instantly share code, notes, and snippets.

@matisiekpl

matisiekpl/bazy_danych.sql

Last active January 23, 2023 22:39
Show Gist options
  • Save matisiekpl/857c3093300e0f65f262b7f6f1b88a0a to your computer and use it in GitHub Desktop.
Save matisiekpl/857c3093300e0f65f262b7f6f1b88a0a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
bazy_danych.sql
-- 1. Napisz funkcję uzupełnijWybory, która przyjmuje idZamówienia. Funkcja ma uzupełnić te dni (w tabeli wybory), dla których nie
-- zostało wybrane jeszcze danie. Ma do niego wpisać danie o największej kaloryczności, które jest dostępne w tym dniu i w tej
-- diecie.
create or replace function uzupelnijWybory(in idZamowienia integer)
returns void
as
$$
declare
przetwarzanyDzien date;
declare wybraneDanie integer;
BEGIN
przetwarzanyDzien := (select dostawy_od from zamowienia where zamowienia.id_zamowienia = idZamowienia);
while przetwarzanyDzien <= (select dostawy_do from zamowienia where id_zamowienia = idZamowienia)
loop
if not exists(select *
from wybory
where id_zamowienia = idZamowienia
and data_dostawy = przetwarzanyDzien) then
wybraneDanie := (select d.id_dania
from dania d
join dostepnosc d on d.id_dania = d.id_dania
join zamowienia z on d.id_diety = z.id_diety
where z.id_zamowienia = idZamowienia
order by d.kalorycznosc desc
limit 1);
insert into wybory values (idZamowienia, wybraneDanie, przetwarzanyDzien);
end if;
przetwarzanyDzien = przetwarzanyDzien + interval '1 day';
end loop;
END;
$$
language plpgsql;
-- 2. Dzień 25 luty to dzień kuchnii włoskiej (dotyczy dań obiadowych). Dodaj do dostępności tego dnia każdej diety każde włoskie danie, o ile w tym dniu
-- nie występuje ono jeszcze w menu (uwaga na duplikaty).
create or replace function dzienWloski(in dataDniaSpecjalnego date)
returns void
as
$$
declare
c1 record;
declare c2 record;
BEGIN
for c1 in (select * from diety)
loop
for c2 in (select * from dania where dania.kuchnia = 'Włoska')
loop
if not exists(select *
from dostepnosc
where id_dania = c2.id_dania
and id_diety = c1.id_diety
and data_dostawy = dataDniaSpecjalnego
and pora_dnia = 'obiad') then
insert into dostepnosc (id_diety, id_dania, data_dostawy, pora_dnia)
values (c1.id_diety, c2.id_dania, dataDniaSpecjalnego, 'obiad');
end if;
end loop;
end loop;
END;
$$ language plpgsql;
-- 3. Napisz funkcję utarg, przyjmującą argument data_od oraz data_do (typu date), która zwróci całkowity utarg firmy w tych
-- dniach.
create or replace function utarg(in data_od date, in data_do date)
returns integer
as
$$
declare
przetwarzanyDzien date;
declare wynik integer;
BEGIN
przetwarzanyDzien := data_od;
while przetwarzanyDzien <= data_do
loop
wynik := wynik + (select sum(cena_dzien)
from diety
join zamowienia z on diety.id_diety = z.id_diety
where z.dostawy_od >= przetwarzanyDzien
and z.dostawy_do <= przetwarzanyDzien);
przetwarzanyDzien = przetwarzanyDzien + interval '1 day';
end loop;
return wynik;
END;
$$
language plpgsql;
-- 4. Dostawca składników bez laktozy podwyższył ceny. Napisz funkcję wiekszeKoszty, która zaktualizuje koszt produkcji o 10% dla
-- każdego dania, które występuje w conajmniej jednej diecie bez laktozy.
create or replace function wiekszeKoszty()
returns void
as
$$
declare
c1 record;
BEGIN
for c1 in (select distinct dania.id_dania
from dania
join dostepnosc d on dania.id_dania = d.id_dania
join diety d2 on d.id_diety = d2.id_diety
where d2.laktoza = false)
loop
update dania set koszt_produkcji=koszt_produkcji * 1.1 where id_dania = c1.id_dania;
end loop;
END;
$$ language plpgsql;
--
-- 5. W Kalwarii nie ma prądu. Dla każdego klienta mieszkającego w 'Kalwaria' zaktualizuj wybory 25 lutego dla jego zamówień tak,
-- aby zawierały jakiekolwiek dania, które nie wymagają podgrzania.
create or replace function prad()
returns void
as
$$
declare
c1 record;
declare c2 record;
declare c3 record;
declare cel integer;
BEGIN
for c1 in (select * from klienci where miejscowosc = 'Kalwaria')
loop
for c2 in (select * from zamowienia where id_klienta = c1.id_klienta)
loop
for c3 in (select *
from wybory
where data_dostawy = '25-02-2023'::date
and id_zamowienia = c2.id_zamowienia)
loop
if exists(select *
from dania
where id_dania = c3.id_dania
and wymaga_podgrzania = True) then
cel := (select id_dania
from dania
join dostepnosc d on dania.id_dania = d.id_dania
where d.id_diety = c2.id_diety
and wymaga_podgrzania = false
limit 1);
update wybory
set id_dania=cel
where id_dania = c3.id_dania
and data_dostawy = '25-02-2023'::date;
end if;
end loop;
end loop;
end loop;
END;
$$ language plpgsql;
-- Rozwiązaie Maćka
CREATE OR REPLACE FUNCTION kalwariaAwaria()
RETURNS Void AS
$$
DECLARE
currWybor RECORD;
DECLARE newDanieID INTEGER;
BEGIN
FOR currWybor IN SELECT *
FROM wybory w
INNER JOIN zamowienia z on w.id_zamowienia = z.id_zamowienia
INNER JOIN klienci k on z.id_klienta = k.id_klienta
INNER JOIN dania d on w.id_dania = d.id_dania
WHERE d.wymaga_podgrzania = TRUE
AND k.miejscowosc LIKE 'Kalwaria'
LOOP
newDanieID := (SELECT da.id_dania
FROM dania da
WHERE da.wymaga_podgrzania = FALSE
AND EXISTS(
SELECT 1
FROM dostepnosc dos
WHERE dos.id_dania = da.id_dania
AND dos.id_diety = currWybor.id_diety
AND dos.data_dostawy = currWybor.data_dostawy
));
UPDATE wybory
SET id_dania = newDanieID
WHERE data_dostawy = currWybor.data_dostawy
AND id_zamowienia = currWybor.id_zamowienia;
end loop;
END;
$$
LANGUAGE plpgsql;
-- Any/All
-- 6. Wypisz klientów, których WSZYSTKIE zamówienia obejmują conajmniej 12 dni, oraz mają CONAJMNIEJ jedno zamówienie z dietą
-- wege.
select *
from klienci k
where 12 <= all (select z.dostawy_do - z.dostawy_od from zamowienia z where z.id_klienta = k.id_klienta)
and exists(select *
from diety di
join zamowienia z2 on di.id_diety = z2.id_diety
where z2.id_klienta = k.id_klienta
and di.wege = true);
-- 7. Wypisz klientów, których WSZYSTKIE diety były wege i wybrał conajmniej raz danie o id=18
select *
from klienci k
where true = all (select di.wege
from diety di
join zamowienia z on di.id_diety = z.id_diety
where z.id_klienta = k.id_klienta)
and 18 = any (select da.id_dania
from dania da
join wybory w on da.id_dania = w.id_dania
join zamowienia z2 on z2.id_zamowienia = w.id_zamowienia
where z2.id_klienta = k.id_klienta);
-- 8. Wypisz ile jest diet, które mogą jeść mieszkańcy kalwarii (tzn wszystkie dania nie wymagają podgrzania)
with dania_na_zimno as (select *
from diety di
where true = all (select da.wymaga_podgrzania
from dania da
join dostepnosc d on da.id_dania = d.id_dania
where d.id_diety = di.id_diety))
select count(*)
from dania_na_zimno;
--
-- 9. Wypisz diety, w których wszystkie dania obiadowe mają powyżej 1000 kcal (masa mięsniowa au)
select *
from diety di
where 1000 < all (select da.kalorycznosc
from dania da
join dostepnosc d on da.id_dania = d.id_dania
where pora_dnia = 'obiad'
and d.id_diety = di.id_diety);
--
-- 10. Wypisz diety, w których conajmniej jedno danie na każdy dzień ma 1000 kcal
--
-- Finalne rozwiązanie
select distinct x.id_diety
from diety x
join dostepnosc d on x.id_diety = d.id_diety
where true = all (select q1.czy_dzien_mocy
from (with dni_mocy as (select distinct data_dostawy,
d2.id_diety,
(1000 < any (select kalorycznosc
from dania da
join dostepnosc d on da.id_dania = d.id_dania
where d.data_dostawy = d2.data_dostawy)) as czy_dzien_mocy
from dostepnosc d2)
select distinct xdi.id_diety, d3.data_dostawy, dni_mocy.czy_dzien_mocy
from diety xdi
join dostepnosc d3 on xdi.id_diety = d3.id_diety
join dni_mocy on dni_mocy.data_dostawy = d3.data_dostawy and
dni_mocy.id_diety = xdi.id_diety) q1
where q1.id_diety = x.id_diety);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment