#1

SELECT 
    AVG(buyPrice) average_buy_price
FROM 
    products;

#2

SELECT 
    productLine, 
    AVG(buyPrice)
FROM
    products
GROUP BY productLine
ORDER BY productLine;

#3

SELECT 
    COUNT(*) AS total
FROM 
    products

#4

SELECT 
    productLine, 
    COUNT(*)
FROM
    products
GROUP BY productLine
ORDER BY productLine;

#5

SELECT 
    productCode, 
    SUM(priceEach * quantityOrdered) total
FROM
    orderDetails
GROUP BY productCode
ORDER BY total DESC;

#6

SELECT 
    productCode,
    productName,
    SUM(priceEach * quantityOrdered) total
FROM
    orderDetails
        INNER JOIN
    products USING (productCode)
GROUP BY productCode
ORDER BY total;

#7

SELECT 
     MAX(buyPrice) highest_price
FROM 
     products;

#8

SELECT 
    productLine, MAX(buyPrice)
FROM
    products
GROUP BY productLine
ORDER BY MAX(buyPrice) DESC;

#9

SELECT 
    MIN(buyPrice) lowest_price
FROM 
    products;

#10

SELECT 
    productLine, 
    MIN(buyPrice)
FROM
    products
GROUP BY productLine
ORDER BY MIN(buyPrice);

#11

SELECT 
    firstName,
    lastName,
    GROUP_CONCAT(
    DISTINCT customername
        ORDER BY customerName) customers
FROM
    employees
INNER JOIN customers 
    ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;