use HM
go
--01
SELECT TOP 10 wykonawca
, SUM(Ilosc * CenaSprz) as CenaRazyIlosc
FROM tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
group by wykonawca
order by CenaRazyIlosc desc
--02
SELECT TOP 10 nazwa
, SUM(Ilosc * CenaSprz) as CenaRazyIlosc
FROM tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
group by nazwa
order by CenaRazyIlosc desc
--03 Klienci nieaktywni - LEFT JOIN
SELECT tbKlienci.nazwa, tbKlienci.miasto, tbFaktury.NrFaktury
FROM tbKlienci LEFT OUTER JOIN
tbFaktury ON tbKlienci.IDKlienta = tbFaktury.KlientID
WHERE (tbFaktury.NrFaktury IS NULL)
--04 nieaktywne towary
SELECT tbTowary.IDTowaru, tytul, wykonawca
FROM tbPozycjeFaktur RIGHT OUTER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
where tbPozycjeFaktur.TowarID is null
--05 zaytanie do innej bazy (można teżzlinkować dane z innych serwerów)
--słownik miast z 2 baz z 3 tabel
SELECT upper(left(Miasto,1)) + lower(SUBSTRING(Miasto,2,LEN(Miasto)-1)) as Miasto
FROM Sezam.dbo.tblKlienci
UNION
SELECT Miasto
FROM Sezam.dbo.tblPracownicy
UNION
SELECT miasto
FROM tbKlienci
--06 pokaz działania DRI
SELECT wykonawca, tytul, nosnik, IDTowaru
FROM tbTowary
WHERE wykonawca = 'U2'
DELETE
FROM tbTowary
WHERE IDTowaru = 33
--07 Raport dłużników
SELECT NrFaktury, TerminPlat
FROM tbFaktury
WHERE Uregulowana = 'FALSE'
UPDATE
tbFaktury set TerminPlat = GETDATE()+1
where NrFaktury = 92
SELECT NrFaktury, nazwa, miasto, ulica, sum(Ilosc* CenaSprz) as Wartosc, TerminPlat
FROM tbFaktury
INNER JOIN tbKlienci
ON tbKlienci.IDKlienta = tbFaktury.KlientID
INNER JOIN tbPozycjeFaktur
ON tbPozycjeFaktur.FakturaID = tbFaktury.IDFaktury
WHERE TerminPlat < GETDATE() AND Uregulowana = 'FALSE'
group by NrFaktury, nazwa, miasto, ulica, TerminPlat
--08 nowy towar
-- Dystrybutor Rolling Stones
SELECT dystrybutor, nosnik, tytul, IDTowaru FROM tbTowary WHERE wykonawca = 'Rolling Stones'
INSERT INTO [dbo].[tbTowary]
([nosnik]
,[wykonawca]
,[tytul]
,[rok_wydania]
,[dystrybutor]
,[cena])
VALUES
('BD'
,'Rolling Stones'
,'Greatest Hits'
,2008
,'EMI'
,69.00)
ALTER TABLE tbTowary ALTER COLUMN nosnik nvarchar(6)
--09 Rankingi po ilości
-- MiastoKlienta (ile szt. towaru)
SELECT miasto
, SUM(Ilosc) AS SztukTowaru
FROM tbKlienci INNER JOIN
tbFaktury ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID
GROUP BY miasto
ORDER BY SztukTowaru DESC
--10 Rok (ile szt. towaru)
SELECT
DATEPART(yyyy, DataSprzed) as Rok
, SUM(Ilosc) AS SztukTowaru
FROM tbFaktury INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID
GROUP BY DATEPART(yyyy, DataSprzed)
ORDER BY SztukTowaru DESC
--11 Dystrybutor (ile szt. towaru)
SELECT
dystrybutor
, SUM(Ilosc) AS SztukTowaru
FROM tbTowary INNER JOIN
tbPozycjeFaktur ON tbTowary.IDTowaru = tbPozycjeFaktur.TowarID
GROUP BY dystrybutor
ORDER BY SztukTowaru DESC
--12 Nosnik (ile szt. towaru)
SELECT
nosnik
, ISNULL(SUM(Ilosc),0) AS SztukTowaru
FROM tbTowary INNER JOIN
tbPozycjeFaktur ON tbTowary.IDTowaru = tbPozycjeFaktur.TowarID
GROUP BY nosnik
ORDER BY SztukTowaru DESC
--13
WITH CTE1 AS
(
SELECT nazwa
, SUM(Ilosc * CenaSprz) as CenaRazyIlosc
FROM tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
group by nazwa
),
CTE2 AS
(SELECT SUM(Ilosc * CenaSprz) as SUMA FROM tbFaktury
tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID)
SELECT nazwa, CenaRazyIlosc/SUMA * 100 AS PROCENT FROM CTE2, CTE1
ORDER BY PROCENT DESC
--14 funkcje szeregujące z funkcją okna
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Ilosc) DESC) AS Pozycja
, wykonawca
, SUM(Ilosc) AS SztukTowaru
FROM tbTowary INNER JOIN
tbPozycjeFaktur ON tbTowary.IDTowaru = tbPozycjeFaktur.TowarID
INNER JOIN tbFaktury ON IDFaktury = FakturaID
GROUP BY wykonawca
ORDER BY SztukTowaru DESC
--15 funkcje szeregujące z funkcją okna
SELECT RANK() OVER(ORDER BY SUM(Ilosc) DESC) AS Pozycja
, wykonawca
, SUM(Ilosc) AS SztukTowaru
FROM tbTowary INNER JOIN
tbPozycjeFaktur ON tbTowary.IDTowaru = tbPozycjeFaktur.TowarID
INNER JOIN tbFaktury ON IDFaktury = FakturaID
GROUP BY wykonawca
ORDER BY SztukTowaru DESC
--16 funkcje szeregujące z funkcją okna
SELECT DENSE_RANK() OVER(ORDER BY SUM(Ilosc) DESC) AS Pozycja
, wykonawca
, SUM(Ilosc) AS SztukTowaru
FROM tbTowary INNER JOIN
tbPozycjeFaktur ON tbTowary.IDTowaru = tbPozycjeFaktur.TowarID
INNER JOIN tbFaktury ON IDFaktury = FakturaID
GROUP BY wykonawca
ORDER BY SztukTowaru DESC
--17 funkcje szeregujące z funkcją okna - podział na grupy (NTILE(4) na - na cztery grupy)
SELECT NTILE(4) OVER(ORDER BY SUM(Ilosc) DESC) AS Grupa
, wykonawca
, SUM(Ilosc) AS SztukTowaru
FROM tbTowary INNER JOIN
tbPozycjeFaktur ON tbTowary.IDTowaru = tbPozycjeFaktur.TowarID
INNER JOIN tbFaktury ON IDFaktury = FakturaID
GROUP BY wykonawca
ORDER BY SztukTowaru DESC
--18 W oknie oprócz ORDER BY można dopisać grupowanieza pomocą Partition
WITH CTE AS
(
SELECT RANK() OVER(PARTITION BY dystrybutor ORDER BY SUM(Ilosc * CenaSprz) DESC) AS Pozycja
, dystrybutor
, wykonawca
, SUM(Ilosc * CenaSprz) as CenaRazyIlosc
FROM tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
group by dystrybutor, wykonawca
--order by dystrybutor, CenaRazyIlosc desc
)
SELECT * FROM CTE
WHERE Pozycja = 1
ORDER BY CenaRazyIlosc DESC
--19 W oknie oprócz ORDER BY można dopisać grupowanieza pomocą Partition
WITH CTE AS
(
SELECT RANK() OVER(PARTITION BY dystrybutor ORDER BY SUM(Ilosc * CenaSprz) ASC) AS Pozycja
, dystrybutor
, wykonawca
, SUM(Ilosc * CenaSprz) as CenaRazyIlosc
FROM tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
group by dystrybutor, wykonawca
--order by dystrybutor, CenaRazyIlosc desc
)
SELECT * FROM CTE
WHERE Pozycja = 1
ORDER BY CenaRazyIlosc DESC
--20 Rok, kwartał, wykonawca, suma z ilość
SELECT DATEPART(yyyy, DataSprzed) AS Rok
, DATEPART(q, DataSprzed) AS Kwartal
, wykonawca
, sum(Ilosc) AS IloscRazem
FROM tbFaktury INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
GROUP BY DATEPART(yyyy, DataSprzed)
, DATEPART(q, DataSprzed)
, wykonawca
ORDER BY Rok, Kwartal, IloscRazem DESC
--21 (Nietypowe grupowania ROLLUP: Rok, kwartał, wykonawca, suma z ilość
--grupowanie hierarchiczne
SELECT DATEPART(yyyy, DataSprzed) AS Rok
, DATEPART(q, DataSprzed) AS Kwartal
, wykonawca
, sum(Ilosc) AS IloscRazem
FROM tbFaktury INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
GROUP BY ROLLUP (DATEPART(yyyy, DataSprzed)
, DATEPART(q, DataSprzed)
, wykonawca)
ORDER BY Rok, Kwartal, IloscRazem DESC
--22 (Nietypowe grupowania CUBE: grupowanie wszystkiego co możliwe
SELECT DATEPART(yyyy, DataSprzed) AS Rok
, DATEPART(q, DataSprzed) AS Kwartal
, wykonawca
, sum(Ilosc) AS IloscRazem
FROM tbFaktury INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
GROUP BY CUBE (DATEPART(yyyy, DataSprzed)
, DATEPART(q, DataSprzed)
, wykonawca)
ORDER BY Rok, Kwartal, IloscRazem DESC
--23 (Nietypowe grupowania GROUPING SETS: podobne do ROLLUP z możliwością wyłączeniu dowolnego poziomu hierarchii
SELECT DATEPART(yyyy, DataSprzed) AS Rok
, DATEPART(q, DataSprzed) AS Kwartal
, wykonawca
, sum(Ilosc) AS IloscRazem
FROM tbFaktury INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
GROUP BY GROUPING SETS (
(),
(DATEPART(yyyy, DataSprzed)),
(DATEPART(yyyy, DataSprzed) , DATEPART(q, DataSprzed)),
(DATEPART(yyyy, DataSprzed) , DATEPART(q, DataSprzed), wykonawca)
)
ORDER BY Rok, Kwartal, IloscRazem DESC
--24 ranking wykonawców podziałem na 4 Grupy z podsumowaniem każdej grupy
WITH CTE AS
(
SELECT wykonawca
, SUM(Ilosc * CenaSprz) as CenaRazyIlosc, NTILE(4) OVER(ORDER BY SUM(Ilosc * CenaSprz) DESC) AS Grupa
FROM tbFaktury INNER JOIN
tbKlienci ON tbFaktury.KlientID = tbKlienci.IDKlienta INNER JOIN
tbPozycjeFaktur ON tbFaktury.IDFaktury = tbPozycjeFaktur.FakturaID INNER JOIN
tbTowary ON tbPozycjeFaktur.TowarID = tbTowary.IDTowaru
group by wykonawca
--order by CenaRazyIlosc desc
)
SELECT Grupa
, ISNULL(wykonawca, '--SUMA--' + CAST(grupa as char(1))) as Wykonawca
, sum(CenaRazyIlosc) as WartoscRazem
FROM CTE
GROUP BY GROUPING SETS (
(Grupa),
(Grupa, wykonawca)
)
ORDER BY Grupa, WartoscRazem DESC