Odpowiedzi:

1a

SELECT last_name FROM Customers WHERE city like 'P%'

1b

SELECT cust_id FROM Orders WHERE YEAR(o_date) = 2016

2a

SELECT Len(first_name) - LEN(Replace(first_name,'a','')) as Dlugosc FROM Employees

2b

SELECT * FROM Customers WHERE upper(first_name) like '%JAN%';

3a

SELECT top 1 (p.cost/l.quantity) as Cena FROM 
LineItem l
JOIN Products p
on l.prod_id = p.prod_id
ORDER BY Cena desc

3b

SELECT emp.id
FROM Employees a
left join EmployeesCopy b on a.id = b.id
WHERE b.id is null;	 

4a

SELECT MAX(cost) FROM Products p WHERE cost NOT IN (select MAX(cost) FROM Products)

4b

SELECT prod_id, cost FROM Products WHERE cost > (select AVG(cost) FROM Products)


5a

SELECT cust_id, AVG(cost*quantity) as Zamowienie
FROM Products p 
 JOIN LineItem l on p.prod_id = l.prod_id 
 JOIN Orders o on o.order_id = l.order_id
GROUP BY cust_id
HAVING AVG(cost*quantity) > 100

5b

SELECT prod_id, order_id FROM LineItem l
WHERE l.quantity = 
(SELECT max(quantity) from LineItem l2
where prod_id = l.prod_id
GROUP BY prod_id)

6a

SELECT MAX(TotalCost), MIN(Temp.cust_id), Temp.city from (
    SELECT SUM(cost) as TotalCost, c.city, c.cust_id as cust_id
    FROM Products p 
    JOIN LineItem l 
    on p.prod_id = l.prod_id 
    JOIN Orders o
    on o.order_id = l.order_id
    JOIN Customers c
    on o.cust_id = c.cust_id
    GROUP BY c.city, c.cust_id) as Temp
GROUP BY Temp.city

6b

SELECT description, cost, (select COUNT(*) from Products p where p.cost >= p2.cost) row_num
from Products p2 ORDER BY row_num

7a,7b

SELECT * FROM Orders o
        WHERE order_id IN
             (SELECT MIN(order_id) FROM Orders o2
			     GROUP BY cust_id, emp_id, o_date 
			     HAVING count(*) > 1)