USE BD1d_cz_1B
GO

-- 1. Które miasto występuje najczęściej w danych adresowych?
SELECT TOP 1  miasto From Klienci GROUP BY Miasto ORDER BY COUNT(idklienta) DESC

-- 2. Jak nazywają się: najstarszy i najmłodszy pracownik w firmie (imię i nazwisko, wiek w latach)
SELECT t1.Nazwisko, t1.dataUR FROM
(SELECT TOP 1 Idpracownika, Nazwisko, DATEDIFF(yy,DataUrodzenia, GETDATE()) dataUR FROM Pracownicy WHERE DataUrodzenia IS NOT NULL ORDER BY    DATEDIFF(yy,DataUrodzenia, GETDATE())DESC ) t1
UNION ALL
SELECT t2.Nazwisko, t2.dataUR FROM
(SELECT TOP 1 Idpracownika, Nazwisko, DATEDIFF(yy,DataUrodzenia, GETDATE()) dataUR FROM Pracownicy WHERE DataUrodzenia IS NOT NULL ORDER BY   DATEDIFF(yy,DataUrodzenia, GETDATE()) ) t2

/* 3. Podać imię, nazwisko i wiek w latach wszystkich pracowników,
      którzy są starsi o co najmniej 10 lat od najmłodszego pracownika.*/
	  SELECT t1.idpracownika,t1.lat FROM(
	  SELECT p.idpracownika, DATEDIFF(yy,p.dataurodzenia,getdate()) lat FROM Pracownicy p where p.DataUrodzenia is not null
	  ) t1 inner join (
	  SELECT TOP 1 DATEDIFF(yy,p.dataurodzenia,getdate())+10 dataUrodzenia FROM Pracownicy p where p.DataUrodzenia is not null ORDER BY DATEDIFF(yy,p.dataurodzenia,getdate())+10
	  ) t2 on t1.lat>=t2.dataUrodzenia


/* 4. Podać ID zamówienia, ID klienta i datę zamówienia dla wszystkich zamówień złożonych później,
      niż data ostatniego zamówienia produktu o nazwie "Queso Cabrales".*/

	  SELECT k.idklienta, z.IDzamówienia, z.DataZamówienia, DATEDIFF(HH,z.DataZamówienia,GETDATE()) x FROM Zamówienia z INNER JOIN Klienci k on k.IDklienta=z.IDklienta
	  INNER JOIN
	  (
	  SELECT TOP 1 p.NazwaProduktu, z.DataZamówienia,  DATEDIFF(HH,z.DataZamówienia,GETDATE()) x from Produkty p INNER JOIN pozycjeZamówienia pz on pz.IDproduktu=p.IDproduktu
	  INNER JOIN Zamówienia z on z.IDzamówienia=pz.IDzamówienia
	   where p.NazwaProduktu='Queso Cabrales'
	   ORDER BY DATEDIFF(HH,z.DataZamówienia,GETDATE())
	   ) t1 on t1.DataZamówienia<z.DataZamówienia

	   /*
	   	  SELECT t2.IDklienta,t2.IDzamówienia,t2.DataZamówienia FROM(
	  SELECT k.idklienta, z.IDzamówienia, z.DataZamówienia, DATEDIFF(HH,z.DataZamówienia,GETDATE()) x FROM Zamówienia z INNER JOIN Klienci k on k.IDklienta=z.IDklienta
	  )t2
	  INNER JOIN
	  (
	  SELECT TOP 1 p.NazwaProduktu, z.DataZamówienia,  DATEDIFF(HH,z.DataZamówienia,GETDATE()) x from Produkty p INNER JOIN pozycjeZamówienia pz on pz.IDproduktu=p.IDproduktu
	  INNER JOIN Zamówienia z on z.IDzamówienia=pz.IDzamówienia
	   where p.NazwaProduktu='Queso Cabrales'
	   ORDER BY DATEDIFF(HH,z.DataZamówienia,GETDATE())
	   ) t1 on t1.x>t2.x
	   */
/* 5. Podać nazwę kategorii, OD produktu, nazwę produktu i jego cenę, dla wszystkich produktów,
      których cena jednostkowa jest większa od średniej ceny produktów kategorii, do której produkt należy.*/
	  SELECT t1.IDkategorii,t1.IDproduktu, t1.NazwaProduktu FROM
	  (
	  Select k.IDkategorii,k.NazwaKategorii, p.IDproduktu, p.NazwaProduktu, pz.CenaJednostkowa CenaWszystkichProduktów
	  From Kategorie k inner join Produkty p on p.IDkategorii=k.IDkategorii
	  INNER JOIN PozycjeZamówienia pz on pz.IDproduktu=p.IDproduktu
	  GROUP BY p.NazwaProduktu, p.IDproduktu, k.NazwaKategorii, k.IDkategorii
	  )t1 inner join
	  (
	  SELECT k.IDkategorii,k.NazwaKategorii, AVG(pz.CenaJednostkowa) srednia FROM Kategorie k INNER JOIN Produkty p on p.IDkategorii=k.IDkategorii
INNER JOIN PozycjeZamówienia pz on pz.IDproduktu=p.IDproduktu
GROUP BY k.IDkategorii, k.NazwaKategorii
) t2 on  t1.CenaWszystkichProduktów>t2.srednia

-- 6. Wyświetlić nazwy produktów, które były częściej sprzedawane niż wynosi średnia sprzedaż w kategorii.
/* 7. Dla każdej firmy wygenerować tyle kolumn, ile lat była prowadzona sprzedaż i
      dokonać zestawienia wartości zamówień w tych kolumnach.*/