select distinct ProductName

from Products P join [Order Details] OD on P.ProductID=OD.ProductID

where OrderID in (select OD.OrderID

from [Order Details] OD

join Products P on OD.ProductID=P.ProductID

where ProductName = 'Chai')

and ProductName not like 'Chai'

order by 1

--inne rozw. zad 2

select ProductName

from Products

where SupplierID in (select S.SupplierID

from Suppliers S join Products P on S.SupplierID=P.SupplierID

where ProductName like 'Chai') and ProductName not like 'Chai'

--zaetawienie klientow

select CompanyName, Phone

from student.klienciKanada

union

select CompanyName, Phone

from student.klienciPolska

union

select CompanyName, Phone

from student.klienciUSA

--create database my

--

--select * into my.dbo.products

--from northwind.dbo.products

--insert into products(productName,UnitPrice,Discontinued)

--values ('lala', 1000, 0)

--select *

--from products

--order by productName

--delete from Products

--where productname='lala'

--delete from products

--where unitsInStock = 0 and discontinued = 1

--delete from products

--where unitprice > (select unitprice from products where productname='chai')

UPDATE Products SET Discontinued=1,UnitsInStock=0

UPDATE Products SET UnitPrice=UnitPrice*1.05

where CategoryID=1

-----------

create view CenyNajdrozszychProduktow

as

select CategoryID,max(UnitPrice) max_cena

from Products

group by CategoryID

---