Subquery
1. From payments table, return customerNumber and the amount whose payments are greater than average payment.
SELECT
customerNumber,
amount
FROM
payments
WHERE
amount > (SELECT AVG(amount) FROM payments);
2. From payments table, return customerNumber and the amount of the customer who has the highest payment amount.
SELECT
customerNumber,
amount
FROM
payments
WHERE
amount = (SELECT MAX(amount) FROM payments);
3. From customers table, return the customer_id and their total sum of the order_amount from the orders table using a subquery. Do note that customers can have multiple orders.
SELECT
customer_id,
(
SELECT SUM(order_amount)
FROM orders
WHERE orders.customer_id = customers.customer_id
) AS total_spent
FROM customers;
4. From customers table, return the customer_id are in the orders table using a subquery.
SELECT customer_id
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);