Kolos1

-- https://pastebin.com/5hPyjaAG

SELECT FirstName  + ' ' + LastName as dane -- konkatenacja
FROM Employees
order by dane

SELECT ContactName,Country,
    case
        when Country = 'Germany'  then 'Niemiec'
        when Country = 'Sweden' then 'Szwed'
        when Country = 'France' then 'Francuz'
        else 'Nieznany'
    end as 'Narodowość'
FROM Customers

SELECT * FROM  Customers WHERE lower(ContactName) like 'm%' -- na konkretna litere

-- Najwieksza cena w kategorii

SELECT  CategoryName, ProductName, UnitPrice
FROM
(
    SELECT CategoryName, ProductName, k.CategoryID, UnitPrice
    FROM Categories as k
    join Products as p
    on k.CategoryID = p.CategoryID
) as t1
join
(
    SELECT CategoryID, MAX(UnitPrice) as 'maks'
    FROM Products
    GROUP BY CategoryID
) as t2
on t1.CategoryID = t2.CategoryID
WHERE UnitPrice = maks

SELECT Country, COUNT(OrderID) as 'ile'
FROM Orders as z
left join Customers as k
on z.CustomerID = k.CustomerID
GROUP BY Country
having COUNT(OrderID) =
(
    SELECT MIN(ile) as 'Min'
    FROM
    (
        SELECT Country, COUNT(OrderID) as 'ile'
        FROM Orders as z
        left join Customers as k
        on z.CustomerID = k.CustomerID
        GROUP BY Country
    ) as t1
)


SELECT City, COUNT(CustomerID) as CustQty
FROM dbo.Customers
WHERE Country = 'Brazil'
GROUP BY City
having COUNT(CustomerID)>2

SELECT City, COUNT(CustomerID) as CustQty
FROM dbo.Customers
GROUP BY City, Country
having Country = 'Brazil' AND COUNT(CustomerID)>2


SELECT FirstName, LastName, SUM(UnitPrice * Quantity) as 'Kwota'
FROM Orders as z
left join [Order Details] as op
    on z.OrderID = op.OrderID
left join Employees as p
    on p.EmployeeID = z.EmployeeID
        WHERE OrderDate >= '1997-04-01' AND OrderDate <= '1997-06-30'
            GROUP BY FirstName, LastName --, p.IDpracownika, z.IDzamówienia
                Order By Kwota asc



--Zapytanie zwracaj¹ce 3 kolumny : imiê, nazwisko pracownika oraz wartoœci
--zamówieñ przez niego zrealizowanych.Jedynie zamówienia z drugiego kwarta³u 1997 roku. (93)


select p.Imię, p.Nazwisko, Podsumowanie from Zamówienia z
    join Pracownicy p
        on (p.IDpracownika = z.IDpracownika)
    join [Podsumowania zamówień] o
        on (o.IDzamówienia = z.IDzamówienia)
            where DataZamówienia between '1997-04-01' and '1997-06-30'


-- ______________________________________________________________________

--Zapytanie zwraca 2 kolumny : imiê, nazwisko (pracownik). Jedynie osoby, które zrealizowa³y najwiêcej zamówieñ w kraju swojego pochodzenia. (1) ------------

SELECT Imię, Nazwisko FROM pracownicy
WHERE idpracownika in
(
     SELECT idpracownika FROM
    (SELECT idpracownika, count(idzamówienia) as ile FROM Zamówienia -- ile każdy ma ogólnie zamówień
            GROUP BY idpracownika
    ) as t2 -- wylistowane ID
    WHERE ile in -- wiersz musi zawierac się w ile czyt. max
        (SELECT max(ile) FROM
        (SELECT idpracownika, count(idzamówienia) as ile FROM Zamówienia -- ile każdy ma ogólnie zamówień
            GROUP BY idpracownika
        ) as t1) -- MAX ZAMÓWIEŃ
)


-- Zapytanie zwracajace 3 kolumny : NazwaFirmy, idzamówienia, kwota. Jedynie najdroższe zamówienia dla każdego z klientów. (89)        ------------


SELECT NazwaFirmy, Faktury.IDzamówienia, Faktury.CenaKońcowa as kwota FROM Klienci
    join Faktury
        on Klienci.IDklienta = Faktury.IDklienta
join
(
    SELECT  max(Faktury.CenaKońcowa) as kwota , Faktury.IDklienta  FROM Faktury
        GROUP BY Faktury.IDklienta
) as t1
on Klienci.IDklienta = t1.IDklienta
    WHERE Faktury.CenaKońcowa in (t1.kwota) -- albo = t1.kwota
        order by NazwaFirmy asc


-- Zapytanie zwracające 2 kolumny : Kraj (klient), NazwaFirmy (spedytor). Jedynie najczęściej wykorzystywani spedytorzy na terenie danego kraju. (23)    ------------

SELECT tg1.KrajOdbiorcy, tg2.NazwaFirmy FROM
(
    SELECT  MAX(t1.maks) as ilosc, t1.KrajOdbiorcy FROM
    (
        SELECT count(KrajOdbiorcy) as maks, NazwaFirmy, KrajOdbiorcy FROM Spedytorzy as s
        join Zamówienia as z
            on s.IDspedytora=z.IDspedytora
                GROUP BY NazwaFirmy,KrajOdbiorcy
    ) as t1 -- max dla kazdego spedytora dla kazdego kraju (3 te same Argentyny)
        GROUP BY t1.KrajOdbiorcy
) as tg1    -- wybor maxa z tych 3-ech
join
(
    SELECT count(KrajOdbiorcy) as maks, NazwaFirmy, KrajOdbiorcy FROM Spedytorzy as s
    join Zamówienia as z
        on s.IDspedytora=z.IDspedytora
            GROUP BY NazwaFirmy,KrajOdbiorcy
) as tg2    -- max dla kazdego spedytora dla kazdego kraju (3 te same Argentyny)
on tg1.KrajOdbiorcy=tg2.KrajOdbiorcy
    WHERE tg1.ilosc=tg2.maks -- gdzie ilosc z 3 dostepnych jest rowna maxowi


--zadanie 4                                                             ---- mniej wiecej
--znajdz najtanszy i najdrozszy produkt dostarczony przez
--dostawce ktorego nazwa zaczyna sie na litere od c-p
--podaj z jakiej kategorii on pochodzi oraz w dodatkowej
--kolumnie czy jest to najdrozszy czy najtanszy produkt
--nazwa kategorii
--najdrozszy produkt ma dostawce na litere A


SELECT 'najdrozszy' as 'drogi_tani', d.NazwaFirmy , p.NazwaProduktu , p.CenaJednostkowa , k.NazwaKategorii
    FROM Dostawcy as d
    inner join produkty as p
        on d.IDdostawcy=p.IDdostawcy
    inner join kategorie as k
        on k.IDkategorii=p.IDkategorii
            WHERE (lower(substring(d.NazwaFirmy,1,1))) between 'a' and 'p'
            and p.CenaJednostkowa in (SELECT max(p.cenajednostkowa)
                FROM produkty as p
                inner join Dostawcy as d
                    on d.IDdostawcy=p.IDdostawcy)
union
SELECT 'najtanszy' as 'drogi_tani', d.NazwaFirmy , p.NazwaProduktu , p.CenaJednostkowa , k.NazwaKategorii
    FROM Dostawcy as d
    inner join produkty as p
        on d.IDdostawcy=p.IDdostawcy
    inner join kategorie as k
        on k.IDkategorii=p.IDkategorii
            WHERE (lower(substring(d.NazwaFirmy,1,1))) between 'a' and 'p'
            and p.CenaJednostkowa in
            (SELECT min(p.cenajednostkowa)
                FROM produkty as p
                inner join Dostawcy as d
                    on d.IDdostawcy=p.IDdostawcy)




 -- Zapytanie zwracające 2 kolumny : Kraj, Nazwaproduktu. Jedynie produkty najczęściej zamawiane w danym kraju. (46) -------------

SELECT tg1.KrajOdbiorcy,tg2.NazwaProduktu FROM
(
SELECT t1.KrajOdbiorcy,MAX(t1.ilosc) as maks FROM
(
SELECT z.KrajOdbiorcy, p.NazwaProduktu, COUNT(oz.IDzamówienia) as ilosc FROM Zamówienia as z
    join [Opisy zamówień]     as oz
        on  z.IDzamówienia=oz.IDzamówienia
    join Produkty as p
        on p.IDproduktu=oz.IDproduktu
            GROUP BY z.KrajOdbiorcy, p.NazwaProduktu
) as t1
    GROUP BY t1.KrajOdbiorcy
) as tg1
join
(SELECT z.KrajOdbiorcy, p.NazwaProduktu, COUNT(oz.IDzamówienia) as iloscc FROM     Zamówienia as z
    join [Opisy zamówień] as oz
        on  z.IDzamówienia=oz.IDzamówienia
    join Produkty as p
        on p.IDproduktu=oz.IDproduktu
            GROUP BY z.KrajOdbiorcy, p.NazwaProduktu
) as tg2 -- zliczana ilosc dla kazdego kraju i kazdego produktu w
on tg1.KrajOdbiorcy=tg2.KrajOdbiorcy
    WHERE tg1.maks=tg2.iloscc
        order by KrajOdbiorcy asc



-- Zapytanie zwracające 1 kolumnę : idzamówienia. Jedynie zamówienia, na których ilość produktów równa jest średniej ilości produktów na zamówieniu. (283) -----------


select IDzamówienia from (
    select IDzamówienia, count(IDproduktu) as ileProduktow from [Opisy zamówień]
    group by IDzamówienia
    having count(IDproduktu) = (
        select avg(ile) from (
            select IDzamówienia, count(idproduktu) as ile from [Opisy zamówień]
        group by IDzamówienia) as t1)
    ) as t2



 --Zapytanie zwracające 3 kolumny : kraj (klient), imię, nazwisko.
--Jedynie pracowicy realizujący najwięcej zamówień z danego kraju. (28) ---------------

SELECT  t2.Nazwisko, tg1.KrajOdbiorcy FROM
(
    SELECT max(t1.ilosc) as maks, t1.KrajOdbiorcy FROM
    (
        SELECT count(p.IDpracownika) as ilosc, p.Nazwisko, KrajOdbiorcy FROM Zamówienia as z
        join Pracownicy as p
            on z.IDpracownika=p.IDpracownika
                GROUP BY  p.IDpracownika, z.KrajOdbiorcy, p.Nazwisko
    ) as t1 -- każdy ziomek w każdym kraju
GROUP BY t1.KrajOdbiorcy
) as tg1 -- max w kazdym kraju
join
    (
        SELECT count(p.IDpracownika) as ilosc, p.Nazwisko, KrajOdbiorcy FROM Zamówienia as z
        join Pracownicy as p
            on z.IDpracownika=p.IDpracownika
                GROUP BY  p.IDpracownika, z.KrajOdbiorcy, p.Nazwisko
    ) as t2 -- każdy ziomek w każdym kraju
        on tg1.KrajOdbiorcy=t2.KrajOdbiorcy
            WHERE t2.ilosc=tg1.maks


 -- Zapytanie zwracające 2 kolumny : Orderid, ilość różnych produktów na zamówieniu. Jedynie zamówienia z największą ilością różnych produktów. (1) ------

select IDzamówienia  from
(select IDzamówienia , count(idproduktu) as 'ile' from [Opisy zamówień]
group by IDzamówienia) as t1
where ile=(select max(ile) from (select IDzamówienia , count(idproduktu) as 'ile' from [Opisy zamówień]
group by IDzamówienia) as t2 );


 -- Zapytanie zwracające 3 kolumny : imię, nazwisko, ilość zrealizowanych zamówień w 1997. Jedynie pracownicy, -------------
 -- którzy w roku 1997 wykonali więcej niż wynosiła średnia ilość zrealizowanych zamówień na pracownika. (4)


SELECT p.Imię,p.Nazwisko, COUNT(p.IDpracownika) as 'ilosc zamowien'  FROM Pracownicy as p
JOIN Zamówienia as z
    ON p.IDpracownika = z.IDpracownika
        WHERE DataZamówienia between '1997/01/01' and '1997/12/31'
            GROUP BY p.Imię, p.Nazwisko
                HAVING COUNT(p.IDpracownika)
                    >
                    (
                    SELECT AVG(t1.suma) AS srednia FROM
                    (
                    SELECT p.IDpracownika, COUNT(p.IDpracownika) AS suma FROM Pracownicy as p
                    JOIN Zamówienia as z
                        ON p.IDpracownika = z.IDpracownika
                            WHERE DataZamówienia between '1997/01/01' and '1997/12/31'
                                GROUP BY p.IDpracownika
                    ) AS t1
                    )



--zadanie 3
--Zapytanie zwracaj¹ce 2 kolumny : nazwê kategorii oraz nazwê produktu.
-- Jedynie najdro¿sze produkty dla ka¿dej z kategorii. (8)


SELECT  NazwaKategorii, NazwaProduktu from Produkty
    JOIN Kategorie
        ON Produkty.IDkategorii = Kategorie.IDkategorii
            where (CenaJednostkowa * StanMagazynu) in(
                SELECT  MAX(CenaJednostkowa * StanMagazynu) from Produkty
                    Group by IDkategorii)



--Zapytanie zwracające 2 kolumny : kraj (klient), wartość zamówienia.
--Jedynie zamówienia realizowane w roku 1998 (21)

select * from Zamówienia
select * from [Opisy zamówień]

select z.KrajOdbiorcy,  sum(t2.suma) as wartość_zamówienia from Zamówienia as z
join
(select z.IDzamówienia, sum(t1.wartosc) as suma from Zamówienia as z
join
 (select IDzamówienia, (oz.ilość * oz.CenaJednostkowa) as wartosc from [Opisy zamówień] as oz
 group by IDzamówienia, oz.ilość, oz.CenaJednostkowa) as t1
 on z.IDzamówienia = t1.IDzamówienia
 group by z.IDzamówienia) as t2

 on z.IDzamówienia = t2.IDZamówienia
 where z.DataWysyłki between '1998-01-01' and '1999-01-01'
 group by z.KrajOdbiorcy


 --Zapytanie zwracające 2 kolumny : nazwę kategorii oraz nazwę firmy.
 Jedynie firmy, które dostarczaja najwięcej produktów w danej kategorii. (14)

select * from Dostawcy
select * from  Kategorie
select * from Produkty


 select tg1.NazwaKategorii,tg2.NazwaFirmy from (
select t1.NazwaKategorii,MAX(t1.ilosc) as maks from (
    select k.NazwaKategorii, d.NazwaFirmy ,COUNT(p.IDdostawcy) as ilosc from Dostawcy as d
join Produkty as p on d.IDdostawcy=p.IDdostawcy
join Kategorie as k on k.IDkategorii=p.IDkategorii
group by k.NazwaKategorii, d.NazwaFirmy) as t1 group by t1.NazwaKategorii) as tg1
    join
    (select k.NazwaKategorii, d.NazwaFirmy ,COUNT(p.IDdostawcy) as ilosc from Dostawcy as d
join Produkty as p on d.IDdostawcy=p.IDdostawcy
join Kategorie as k on k.IDkategorii=p.IDkategorii
group by k.NazwaKategorii, d.NazwaFirmy) as tg2
    on tg1.NazwaKategorii=tg2.NazwaKategorii
    where tg1.maks=tg2.ilosc
 order by tg1.NazwaKategorii asc

-----------------------------------------------------------------WORD------------------------------------------------------------------



Średni wiek w dziale
select d.nazwa,avg(p.wiek) as srednia,MIN(p.wiek) as minimum,MAX(p.wiek) as maximum from pracownicy as p join dzialy as d
on p.dzialID=d.dzialID
join zarobki on zarobki.pracID=p.pracID
group by d.nazwa


1.  Maksymalna i średnia zarobków w dziale
select top 1 nazwa,srednia from(
select nazwa,max(brutto) as max,min(brutto) as min,avg(brutto) as srednia from pracownicy
 join dzialy on dzialy.dzialid=pracownicy.dzialid
 join zarobki on zarobki.pracid=pracownicy.pracid
 group by nazwa
 ) as a order by srednia desc

2.  Osoby które cokolwiek zarobiły
select nazwisko,imie from pracownicy as p
 where exists (select * from zarobki as z
 where z.pracid = p.pracid)


3.  Nieprzypisani do żadnego działu
select * from dzialy as d
 where not exists (select * from pracownicy as p
 where p.dzialid= d.dzialid)

4.  Osoby których zarobki są większe od 3000( klauzula in – można select w selecie)
select imie,nazwisko from pracownicy
where pracid in
(
select distinct pracid from zarobki
where brutto > 3000
)


5.  Zapytanie zwracające 2 kolumny: 1 nazwę produktu i kategorii do której należy
SELECT Kraj,Count(Idzamówienia) as 'ile' FROM Zamówienia
as z LEFT JOIN Klienci as k
ON z.IDklienta=k.IDklienta
GROUP BY Kraj
HAVING COUNT(Idzamówienia) =
(SELECT MAX(ile) as 'maks' FROM
(SELECT Kraj , COUNT(Idzamówienia) as 'ile' FROM Zamówienia
as z LEFT JOIN Klienci as k
ON z.IDklienta = k.IDklienta
GROUP BY Kraj) as t1);



6.  Zapytanie zwracające kategorie i nazwę produktu(jedynie najdroższe)
SELECT NazwaKategorii, NazwaProduktu, CenaJednostkowa FROM
(SELECT NazwaKategorii, NazwaProduktu, k.Idkategorii,
CenaJednostkowa FROM Kategorie as k LEFT JOIN Produkty as p
ON k.IDkategorii=p.IDkategorii) as t1
LEFT JOIN
(SELECT idkategorii, MAX(CenaJednostkowa) as 'maks' FROM
Produkty
GROUP BY IDkategorii) as t2
ON t1.IDkategorii=t2.IDkategorii
WHERE CenaJednostkowa=maks;




7.  Najtańszy i najdroższy produkt, kto dostarczył i do jakiej kategorii należy
use Northwind

select 'najtanszy' ogolne, p.productname,p.unitprice,
s.COMPANYNAME from products p
inner join categories c on
c.categoryid = p.categoryid
inner join suppliers s on
s.supplierid = p.supplierid
where unitprice in
(select min(unitprice) from products where unitprice
is not null and unitprice<>0)
union
select 'najdrozszy' ogolne,p.productname,p.unitprice,
s.COMPANYNAME from products p
inner join categories c on
c.categoryid = p.categoryid
inner join suppliers s on
s.supplierid = p.supplierid
where unitprice in
(select max(unitprice) from products);


8.  Zamówienie na najwyższą wartość wraz z datą
select od.orderid,
sum(od.unitprice*od.quantity),
o.orderdate
from [order details] od
    join orders o
on o.orderid = od.orderid
group by od.orderid, o.orderdate
having sum(od.unitprice*od.quantity) in
(select(sum(unitprice*quantity)) from
[order details]
group by orderid);




9.  Zapytanie zwracające orderID oraz łączną wartość każdego z zamówień, posortowane
select od.orderid as zamowienie,
sum(quantity*unitprice) as wartosc from
"order details" as ord left join orders as
od
on ord.orderid = od.orderid
group by od.orderid order by od.orderid desc






10. Zapytanie zwracające nazwę kategorii zawierające najwięcej produktów
Select NazwaKategorii from
produkty as p left join Kategorie as c
on p.IDkategorii=c.IDkategorii group by
c.IDKategorii,NazwaKategorii
having count(IDproduktu) =
(select max(ile) from
(select count(IDproduktu)
as ile from Produkty group by IDkategorii) as temp)


11. Zapytanie zwracające kategorie i ilość produktów do niej przypisaną
select NazwaKategorii, count(IDproduktu) from
Produkty as p left join Kategorie as c
on p.IDKategorii=c.IDkategorii
group by NazwaKategorii

12. Cena jednostkowa produktu w zakresie
select NazwaProduktu, CenaJednostkowa from Produkty
where CenaJednostkowa BETWEEN 20 and 80


13. Zapytanie zwracające 2 kolumny : Kraj (klient), idzamówienia. (830)
select c.Kraj, o.IDzamówienia from Klienci c
join Zamówienia o on c.IDklienta = o.IDklienta

14. Zapytanie zwracajace 3 kolumny : imię, nazwisko, id zamóeinia. Jedynie zamówienia z 3 kwartału 1996 zrealizowane po terminie. (5)
select p.imię, p.nazwisko,  f.IDzamówienia   from Pracownicy as p
join Zamówienia as z on(p.IDpracownika=z.IDpracownika)
join Faktury as f on (z.IDzamówienia=f.IDzamówienia)
where f.DataZamówienia>= '1996-07-01' and f.DataZamówienia <= '1996-09-30'
and f.DataWymagana<f.DataWysyłki
group by p.Imię, p.Nazwisko,   f.IDzamówienia


15. Zapytanie zwracające 2 kolumny : idzamówienia, wartość zamówienia.Jedynie najdroższe zamówienie w USA. (1)
select top 1 o.IDzamówienia, od.CenaJednostkowa from Zamówienia as o
 join [Opisy zamówień] as od on od.IDzamówienia=o.IDzamówienia
 join Klienci as c on o.IDKlienta=c.IDklienta
 where c.Kraj like('USA')
 order by CenaJednostkowa desc


16. --Zapytanie zwracające 2 kolumny : nazwaFirmy (dostawca), ilość produktów. Jedynie dostawca dostarczający najwięcej produktów w kategorii napoje. (3)
select s.NazwaFirmy,p.IlośćZamówiona as maks, c.NazwaKategorii from
produkty p join Kategorie c on
c.IDKAtegorii = p.IDKAtegorii
join Dostawcy s on
s.IDDostawcy = p.IDDostawcy
where p.IlośćZamówiona =
(select max(wynik.maks) from (
select s.NazwaFirmy,p.IlośćZamówiona maks, c.NazwaKategorii from produkty p
join Kategorie c on
c.IDKAtegorii = p.IDKAtegorii
join Dostawcy s on
s.IDDostawcy = p.IDDostawcy
where c.NazwaKategorii like 'napoje'
group by NazwaKategorii,NazwaFirmy,IlośćZamówiona)wynik)

-------------------------------------------------------------Katarzyna-----------------------------------------------------------------


--Zapytanie zwracające 2 kolumny : NazwaFirmy (klient), NazwaFirmy (dostawca).
--Dostawca, którego produkty są najczęściej zamawiane przez danego Klienta. (161)

select t1.NazwaOdbiorcy, t1.NazwaFirmy from(
    select NazwaOdbiorcy, NazwaFirmy, count(NazwaFirmy) as ile from Zamówienia as z
    join [Opisy zamówień] as o on o.IDzamówienia=z.IDzamówienia
    join Produkty as p on p.IDproduktu=o.IDproduktu
    join Dostawcy as d on d.IDdostawcy=p.IDdostawcy
    group by NazwaOdbiorcy, NazwaFirmy
) as t1
join (
    select MAX(ile) as maks, NazwaOdbiorcy from (
        select NazwaOdbiorcy, NazwaFirmy, count(NazwaFirmy) as ile from Zamówienia as z
        join [Opisy zamówień] as o on o.IDzamówienia=z.IDzamówienia
        join Produkty as p on p.IDproduktu=o.IDproduktu
        join Dostawcy as d on d.IDdostawcy=p.IDdostawcy
        group by NazwaOdbiorcy, NazwaFirmy
    ) as a
    group by NazwaOdbiorcy
) as t2
on t1.NazwaOdbiorcy=t2.NazwaOdbiorcy
where t1.ile=t2.maks