select COUNT(*) as 'Ilosc_zamowien', lastname, firstname
from Orders, Employees
where Orders.EmployeeID=Employees.EmployeeID
group by lastname, firstname
--liczy premie dla pracownikow
select COUNT(*) as 'Ilosc Zamowien', lastname, firstname,
case
when count(*)>50 and COUNT(*)<=100 then 400
when count(*)>100 and COUNT(*)<=150 then 700
when count(*)>150 then 1000
else 0 end as 'Premia w zl'
from Orders, Employees
where Orders.EmployeeID=Employees.EmployeeID
group by lastname, firstname
order by 4
tworzenie procedury create procedure <nazwa_procedury> as <reszta_polecen>
w domu zamina szefa
nazwa produktu i ilość sprzedana
select * into pracownicy1234
from Employees
select productname , SUM(quantity)
from Products p, OrderDetails sz
where p.ProductID=sz.ProductID
group by ProductName
order by 2 desc
Nie można usunac bo sa inne powiazania tego produktu w innych tabelach.
delete from Products where ProductID = 60
TRUNCATE table otherdetails
Lizcy ilosc i pokazuje z product id
select ProductID, sum(Quantity)as suma
from [Order Details]
group by productID
order by SUM(quantity)
Liczy ilsci i pokazuje z nazwa produktu
select productname as "Nazwa produktu", sum(Quantity)as Suma
from [Order Details] a, Products b
where a.ProductID=b.ProductID
group by productname
order by SUM(quantity) desc
pokazuje nazwe produktu, ilość sprzedanych, <- zle
select productname as "Nazwa produktu", sum(Quantity)as Suma, a.UnitPrice as Cena, SUM(Quantity*a.UnitPrice) as Wartosc
from [Order Details] a, Products b
where a.ProductID=b.ProductID
group by productname, a.UnitPrice
order by 2 desc
stowrzenie nowego view
create view sztuki as
select productname as "Nazwa produktu", sum(Quantity)as Suma, SUM(Quantity*a.UnitPrice) as Wartosc
from [Order Details] a, Products b
where a.ProductID=b.ProductID
group by productname
a nastepnie polecenie szukajace najwiekszej sumy
select [Nazwa produktu], Suma, Wartosc, a.unitprice
from sztuki s, Products a
where s.[Nazwa produktu]=a.ProductName and Suma=(select MAX(suma) from sztuki)