bd lab5

LAB 5

:)))))))))))))))))))))

WITH max AS (
SELECT masa from czekoladki
ORDER BY masa DESC
LIMIT 1
)

SELECT * FROM (
SELECT idczekoladki, nazwa, masa FROM czekoladki
) as tabela, max
WHERE tabela.masa = max.masa






!!! W where nie można używać aliasów !!!

1.1
SELECT COUNT(*) as liczba_czekoladek FROM czekoladki cz

1.2
SELECT COUNT(cz.orzechy) as liczba_czekoladek_z_orzechami FROM czekoladki cz

1.3
SELECT sumy.idpudelka, sumy.suma FROM (
SELECT p.idpudelka, SUM(z.sztuk) as suma FROM pudelka p JOIN zawartosc z ON p.idpudelka = z.idpudelka
GROUP BY p.idpudelka
) sumy
ORDER BY sumy.suma DESC
LIMIT 1

1.4
SELECT p.nazwa, SUM(z.sztuk) as suma FROM pudelka p JOIN zawartosc z ON p.idpudelka = z.idpudelka
GROUP BY p.idpudelka
ORDER BY suma DESC

1.5
SELECT p.nazwa, SUM(z.sztuk) as suma FROM pudelka p
JOIN zawartosc z ON p.idpudelka = z.idpudelka
JOIN czekoladki c ON z.idczekoladki = c.idczekoladki
WHERE c.orzechy IS NULL
GROUP BY p.idpudelka
ORDER BY suma DESC

1.6
SELECT p.nazwa, SUM(z.sztuk) as suma FROM pudelka p
JOIN zawartosc z ON p.idpudelka = z.idpudelka
JOIN czekoladki c ON z.idczekoladki = c.idczekoladki
WHERE c.czekolada = 'mleczna'
GROUP BY p.idpudelka
ORDER BY suma DESC


2.1
SELECT p.nazwa, SUM(c.masa*z.sztuk) as masa FROM pudelka p
JOIN zawartosc z ON p.idpudelka = z.idpudelka
JOIN czekoladki c ON z.idczekoladki = c.idczekoladki
GROUP BY p.idpudelka
ORDER BY masa DESC

2.2
SELECT p.idpudelka, SUM(cz.masa*z.sztuk) as masa
FROM
    czekoladki cz
    INNER JOIN zawartosc z ON cz.idczekoladki = z.idczekoladki
    INNER JOIN pudelka p ON z.idpudelka = p.idpudelka
GROUP BY p.idpudelka
ORDER BY masa DESC
LIMIT 1

2.3
SELECT AVG(masy.masa) FROM (SELECT p.idpudelka, SUM(cz.masa*z.sztuk) as masa
FROM
    czekoladki cz
    INNER JOIN zawartosc z ON cz.idczekoladki = z.idczekoladki
    INNER JOIN pudelka p ON z.idpudelka = p.idpudelka
GROUP BY p.idpudelka )masy

2.4
SELECT p.nazwa, (SUM(c.masa*z.sztuk) / SUM(z.sztuk)) as srednia FROM pudelka p
JOIN zawartosc z ON p.idpudelka = z.idpudelka
JOIN czekoladki c ON z.idczekoladki = c.idczekoladki
GROUP BY p.idpudelka
ORDER BY srednia DESC

3.1
SELECT z.datarealizacji, COUNT(z.idzamowienia) FROM zamowienia z
GROUP BY z.datarealizacji

3.2
SELECT COUNT(z.idzamowienia) as liczba_zamowien FROM zamowienia z

3.3
SELECT SUM(a.sztuk*p.cena) as suma FROM public.artykuly a
JOIN pudelka p USING (idpudelka)

3.4
select k.nazwa, count(*) as liczba_zamówień, sum(a.sztuk*p.cena) as wartość from klienci k
join zamowienia z using(idklienta)
join artykuly a using(idzamowienia)
join pudelka p using(idpudelka)
group by k.nazwa


4.1
SELECT idczekoladki, COUNT(idpudelka) as suma FROM zawartosc
GROUP BY idczekoladki
ORDER BY suma DESC

4.2
SELECT idpudelka, sum(z.sztuk) as suma from zawartosc z
JOIN czekoladki c USING(idczekoladki)
WHERE c.orzechy IS NULL
group by idpudelka

4.4
select idpudelka, sum(sztuk) as suma from artykuly
group by idpudelka
order by suma desc
limit 1

5.1
SELECT EXTRACT(quarter from datarealizacji) as kwartał, COUNT(idzamowienia) FROM zamowienia
GROUP BY EXTRACT(quarter from datarealizacji)

5.2
SELECT EXTRACT(month from datarealizacji) as miesiąc,
EXTRACT(year from datarealizacji) as rok, COUNT(idzamowienia) FROM zamowienia
GROUP BY EXTRACT(month from datarealizacji),  EXTRACT(year from datarealizacji)

5.4
select miejscowosc, count(*) from klienci k join zamowienia z using(idklienta)
group by miejscowosc

6.1
select sum(sub.masa) from (
select z.idpudelka, sum(cz.masa*z.sztuk) as masa from zawartosc z join czekoladki cz using(idczekoladki)
group by idpudelka
) as sub

6.2
select sum(sub.masa) from (
select z.idpudelka, sum(cz.koszt*z.sztuk) as masa from zawartosc z join czekoladki cz using(idczekoladki)
group by idpudelka
) as sub


///
select sum(sub.masa*a.sztuk) from (
select z.idpudelka, sum(cz.masa*z.sztuk) as masa from zawartosc z join czekoladki cz using(idczekoladki)
group by idpudelka
) as sub join artykuly a using(idpudelka)
///


7.1
with koszty as (
select z.idpudelka, sum(cz.koszt*z.sztuk) as koszt from zawartosc z join czekoladki cz using(idczekoladki)
group by idpudelka )

select p.idpudelka, p.cena-k.koszt as zysk from pudelka p join koszty k using(idpudelka)


7.2
...