select * into Produkty from Products
select * from produkty
update Produkty
set UnitsOnOrder=0
alter table Produkty
add nazwa_kat varchar(50)
update Produkty
set nazwa_kat=categoryname from Categories
where produkty.CategoryID=Categories.CategoryID
alter table produkty
drop column categoryid
alter table produkty
add wartosc_zap as unitsinstock*unitprice
select unitprice from produkty
update produkty
set unitsinstock=100
where nazwa_kat='seafood'
update produkty
set unitprice=unitprice*1.1
where nazwa_kat='seafood'
update produkty
set nazwa_kat=upper(nazwa_kat)
delete from produkty
where nazwa_kat='Beverages'
select * into klienci
from customers
where customerid in
(select customerid
from orders
group by customerid
having count(orderid)>=1)
alter table klienci
add FirstTransaction date
update klienci
set firsttransaction= dat
from (select customerid,min(orderdate) as 'dat' from orders group by customerid ) as d
where klienci.CustomerID=d.CustomerID
Update klienci
Set contactname = 'Eva Schmitt', ContactTitle = 'Owner'
Where CustomerID = 'alfki'
Create view customer_segment as
Select customerid, sum(quantity*unitprice) as 'suma',
Case
When sum(quantity*unitprice)>50000 then 'v'
When sum(quantity*unitprice)<=50000 and sum(quantity*unitprice)>40000 then 'm'
When sum(quantity*unitprice)<=40000 then 's'
Else 'n' end as 'Segmentname'
From orders o join [order details] d on o.orderid=d.orderid
Group by customerid
alter table klienci
add Segment varchar(1)
update klienci
set segment=segmentname from customer_segment
update produkty
set unitprice=unitprice*case
when supplierid=1 then 1.1
when supplierid=2 then 1.2
when supplierid=3 then 1.3
end
select * into pracownicy from employees
delete from pracownicy
where datediff(year,hiredate,getdate())>21
insert into pracownicy(Lastname,firstname,hiredate) values ('Nowak','Jan',getdate())
--bazy 13
declare @zamowienia_all float
set @zamowienia_all = (select COUNT(*) from Orders)
select shipcountry, COUNT(*)/@zamowienia_all
from Orders
group by shipcountry
select productname from products
where unitprice > (select avg(unitprice) from products where categoryid=6)
select * from products p1
where unitprice > (select avg(unitprice) from products p2 where p2.categoryid=p1.categoryid)
select productname
from products p1
where UnitsInStock = ( select max(unitsinstock)
from products p2 where p1.CategoryID=p2.CategoryID)