LIKE 08.03.2007
-- Podaj nazwe firmy, która zaczyna się na literę A lub C lub D
use Northwind
select CompanyName
from Customers
where CompanyName like 'A%' or CompanyName like 'C%' or CompanyName like 'D%'
--drugie rozwiązanie
select CompanyName
from Customers
where CompanyName like '[ACD]%'
--Podaj nazwę firmy, której nazwa zaczyna się od liter od przedziału A-D
select CompanyName
from Customers
where CompanyName like '[A-D]%'
--Podaj nazwę firmy, której nazwa zaczyna się od liter od przedziału A-G, a druga litera od a-d
select CompanyName
from Customers
where CompanyName like '[A-G][a-d]%'
--Podaj nazwę firmy
select ProductName
from Products
where ProductName like '%[%]%'
JOIN
--przygotowac liste produktow wraz z kategoriami do ktorych naleza
select ProductName, CategoryName
from Products join Categories on Products.CategoryID = Categories.CategoryID
order by 2,1
--albo z aliasami
select ProductName, CategoryName
from Products P join Categories C on P.CategoryID = C.CategoryID
order by 2,1
--dodajemy kolumne wyznaczajaca wartosc sprzedazy
select ProductName, CategoryName, sum(OD.UnitPrice*OD.Quantity)
from Products P join Categories C on P.CategoryID = C.CategoryID
join [Order Details] OD on P.ProductID = OD.ProductID
group by ProductName, CategoryName
order by 1
HAVING
--szukamy kraje z ktorych pochodzi przynajmniej 10 klientow
select Country, count(CustomerID)ilosc_ludziow
from Customers
group by Country
having count(CustomerID)>10
order by ilosc_ludziow desc
select Country, COUNT(*)[Ilosc] -- wypisuje kraje z ktorych pochodzi conajmniej 10 klientow( having zawsze wystepuje z group by i zawsze poaje sie ja po group by)
from Customers
group by Country
having COUNT(*)>=10 --nie wolno korzystac z aliasu Ilosc!
DISTINCT - wypisuje tylko 1 raz
use Northwind
select distinct Country
from Customers
AGG FUNCTION
use Northwind
select count(*)
from Customers
select count(CustomerID)
from Customers
select count(Fax)
from Customers
select max(UnitPrice), min(UnitPrice), max(UnitPrice)-min(UnitPrice)
from Products
where CategoryID='1'
select sum(UnitPrice*UnitsInStock)
from Products
where Discontinued='1'
select Country, count(CompanyName)
from Customers
group by Country
select top 3 Country, count(CompanyName) ilosc
from Customers
group by Country
order by ilosc desc
ZADANIE DOMOWE
1.
select CompanyName
from Customers
where CompanyName like 'W__s% %z_'
2.
Select DATENAME(month,OrderDate),count(OrderID) from Orders
group by DATEPART(month,OrderDate),DATENAME(month,OrderDate) order by
DATEPART(month,OrderDate)
3.
Select DATENAME(year,OrderDate),DATENAME(quarter,OrderDate),count(OrderID)
from Orders
group by DATENAME(quarter,OrderDate),DATENAME(year,OrderDate)
4.
Select DATENAME(year,OrderDate),count(OrderID) from Orders
where DATENAME(quarter,OrderDate)=1 AND DATENAME(year,OrderDate)
IN (1997,1998)
group by DATENAME(year,OrderDate)
-- drugie rozwiazanie
select count(OrderID)Ilosc, datepart(yyyy,OrderDate)Rok
from Orders
where datepart(qq,OrderDate) = 1 AND (datename(yyyy,OrderDate) = '1997' OR datename(yyyy,OrderDate) = '1998')
group by datepart(yyyy,OrderDate)
5.
select SUM(quantity*unitprice)Suma, ProductID
from [order details]
group by productID
order by 2