libname n 'C:\Users\student\Desktop\NorthWind';

data daty;
    a="10.01.2019";
run;

proc sql;
create table daty_1 as
    select *,
        input(substrn(a,1,2),4.0) as dzien format=4. label="to jest dzieñ",
        input(substrn(a,4,2),4.0) as miesiac,
        input(substrn(a,7,4),4.0) as rok,
        mdy(calculated dzien, calculated miesiac, calculated rok) as data format mmddyy10.
    from daty;

*podzapytania nieskorelowane;
create table z1 as
    select idzamowienia, krajodbiorcy,
        fracht,datawysylki
    from n.zamowienia
    where fracht > (select avg(fracht) from n.zamowienia)
    and datawysylki is not null;

*podzapytania skorelowane;
create table z2 as
select distinct a.idproduktu, a.cenajednostkowa as max

from n.opisy_zamowien as a
where a.cenajednostkowa = (
    select max(cenajednostkowa)
    from n.opisy_zamowien as b
    where a.idproduktu = b.idproduktu
    )
order by a.idproduktu;

*inner join;
create table z3 as
select distinct
    a.idproduktu
    ,a.cenajednostkowa as max
from n.opisy_zamowien as a
inner join (
    select idproduktu, max(cenajednostkowa) as max_cena
    from n.opisy_zamowien
    group by idproduktu
) as b
on a.idproduktu = b.idproduktu and a.cenajednostkowa = b.max_cena
order by a.idproduktu;

create table z4 as
select idklienta, nazwafirmy
from n.klienci
where idklienta in (
    select idklienta
    from n.zamowienia
    where datazamowienia > '01MAY1988'd
);

create table z5 as
select
    y.idkategorii,
    y.nazwakategorii,
    round(x.actual, 2) as actual,
    round(y.planned, 2) as planned
from (
    select
        avg(a.cenajednostkowa) as actual,
        c.idkategorii
    from n.opisy_zamowien as a
    inner join n.produkty as b on b.idproduktu = a.idproduktu
    inner join n.kategorie as c on b.idkategorii = c.idkategorii
    group by c.idkategorii
) as x
inner join (
    select
        a.idkategorii,
        b.nazwakategorii,
        avg(cenajednostkowa) as planned
    from
        n.produkty as a
        inner join n.kategorie as b on b.idkategorii = a.idkategorii
    group by a.idkategorii, b.nazwakategorii
)as y
on x.idkategorii = y.idkategorii;
quit;