select *
from Employees
order by ReportsTo
select 'dykrektor',lastname as 'nazwisko', firstname as 'imie'
from Employees
where ReportsTo is null
select YEAR(HireDate)
from Employees
where ReportsTo is null
select lastname, firstname, YEAR(HireDate)
from Employees
where YEAR(hiredate)=(select YEAR(hiredate)from Employees where reportsto is null)
and ReportsTo is not null
select lastname, firstname, country
from Employees
select country, COUNT(*) as 'ilosc_prac'
from Employees
group by country
select COUNT(*) as 'ilosc_prac'
from employees
select categoryid, COUNT(*) as 'ilosc_produkow'
from Products
group by categoryid
order by 2 desc
having count(*) >=10
order by 2 desc
select country , COUNT(*) as 'ilosc_klientow_z_kraju'
from Customers
group by Country
order by 2 desc
select country , COUNT(*) as 'ilosc_kraju'
from Customers
where country='Polska' or country='poland'
select companyname,country
from Customers
where Fax is null
order by 2
http://technet.microsoft.com/pl-pl/library/ms130214%28v=sql.105%29.aspx
http://forum.php.pl/lofiversion/index.php/t35277.html
http://www.sql-kursy.pl/ms-sql-kurs-funkcje-agregujace-group-by-5.html
http://technet.microsoft.com/pl-pl/library/ms187810.aspx
http://zasoby.open.agh.edu.pl/~11smdrobniak/beginner
--komentarz wyciąga imie i nazwsko
select 'dyrektor', LastName as 'nazwisko', FirstName as 'imie'
from Employees
where ReportsTo is null
-- dodatkowo w jakim roku został zatrudniony
select 'dyrektor', LastName as 'nazwisko', FirstName as 'imie', YEAR(hiredate) as 'rok zatrudnienia'
from Employees
where ReportsTo is null
--pokazuje kraj z jakiego pochodzi i grupuje według 3 kolumny
select LastName as 'nazwisko', FirstName as 'imie', Country as 'kraj'
from Employees
order by 3
-- pokazuje ilość pracowników
select Country as 'kraj', COUNT(*) as 'ilosc pracownikow'
from Employees
group by Country
--ilość klientów w kraju
select Country as 'kraj', COUNT(*) as 'ilosc klientow'
from customers
group by Country
order by 1
--wszyscy pracownicy oprócz dyrektora
select LastName as 'nazwisko', FirstName as 'imie', YEAR(HireDate)
from Employees
where YEAR(HireDate)=(select YEAR(hiredate) from Employees where ReportsTo is null)
and ReportsTo is not null
-- ile zamówień złożyły firmy sumarycznie z danego kraju
select Country, COUNT(*)
from Customers k, Orders s
where k.CustomerID=s.CustomerID
group by Country
order by 2 desc
--nazwisko i imię pracownika który obsługiwał zamówienie z polski
select orderid,lastname as 'nazwisko', firstname as 'imie', c.Country
from Orders o, Employees e, Customers c
where e.EmployeeID=o.EmployeeID
and c.CustomerID=o.CustomerID
and (c.Country= 'polska' or c.Country= 'poland')
order by 1
-- sumowanie produktów względem id produktu i posortowanie
select sum (Quantity), Productid from [Order Details] -- sumowanie wzg. quantity
group by ProductID – grupowanie
order by ProductID –sortowanie
-- stoworzenie tabeli która sumuje ilość, podaje nazwę.
create view halo as
select sum (Quantity) as 'ilosc', Productname as 'nazwa produktu'
from [Order Details] o, Products p
where o.ProductID=p.ProductID
group by ProductName
--wyświatla max ilość
select * from halo
where ilosc=(select max(ilosc)from halo)