SQL_26Nov.sql

--cw koncowe podzapytania


--2.2
select UnitPrice, ProductName
from Products
where UnitPrice < (
	select avg(UnitPrice) 
	from Products)

--2.4
select UnitPrice, ProductName
from Products as p1
where UnitPrice < (
	select avg(UnitPrice) 
	from Products as p2
	where p1.CategoryID = p2.CategoryID)

--3.1
select ProductName, UnitPrice, (select avg(unitprice) from products) as average, 
	unitprice - (select avg(unitprice) from products) as difference
from products

--3.2
select ProductName, UnitPrice, (select avg(unitprice) from products) as average, 
	unitprice - (select avg(unitprice) from products) as difference, (select categoryname from Categories  as c where c.CategoryID=p.CategoryID) as category
from products as P

--4.1 bad
select UnitPrice * Quantity * (1 - Discount) + (
	select freight
	from Orders
	where [Order Details].OrderID = Orders.OrderID) as value
from [Order Details]
where OrderId = 10250

--4.1
select sum(UnitPrice * Quantity * (1 - Discount)) + (
	select freight
	from Orders
	where orderid = 10250) as value
from [Order Details]
where OrderId = 10250

--4.2 ?
select sum(UnitPrice * Quantity * (1 - Discount)) + (
	select freight
	from Orders
	where [Order Details].OrderID = Orders.OrderID) as value
from [Order Details]
group by OrderID

--4.3
select CompanyName
from Customers 
where Customers.CustomerID not in (select CustomerID from Orders where year(OrderDate) = 1997)

--4.4 weird
select distinct ProductName from Products as p
inner join [Order Details] as OD on p.ProductID = OD.ProductID
inner join Orders as O on O.OrderId = OD.OrderID
where p.ProductName in (
	select ProductName from Products as p2
	inner join [Order Details] as OD2 on p2.ProductID = OD2.ProductID
	inner join Orders as O2 on O2.OrderId = OD2.OrderID
	where o.CustomerID <> o2.CustomerID)