MySQL Subquery: Examples

Examples
Sample Database

#1

SELECT customerNumber,
       checkNumber,
       amount
FROM payments
WHERE amount = (
	SELECT MAX(amount) 
        FROM payments
);

#2

SELECT customerNumber,
       checkNumber,
	   amount
FROM payments
WHERE amount > (
	SELECT AVG(amount) 
    FROM payments
);

#3

SELECT customername
FROM customers
WHERE customerNumber NOT IN(
	SELECT DISTINCT customernumber
	FROM orders
);

#4

SELECT priceEach * quantityOrdered
FROM orderdetails
WHERE priceEach * quantityOrdered > 10000
GROUP BY orderNumber;

#5

SELECT customerName
FROM customers
WHERE EXISTS (
    SELECT priceEach * quantityOrdered
    FROM orderdetails
    WHERE priceEach * quantityOrdered > 10000
    GROUP BY orderNumber
)

#6

SELECT max(items),
       min(items),
       floor(avg(items))
FROM
(SELECT orderNumber, 
	count(orderNumber) AS items
FROM orderdetails
GROUP BY orderNumber) AS lineitems;

#7

SELECT productname,
       buyprice
FROM products AS p1
WHERE buyprice > ( 
	SELECT AVG(buyprice)
	FROM products
        WHERE productline = p1.productline)

Table List

customers

SELECT * FROM customers;

employees

SELECT * FROM employees;

offices

SELECT * FROM offices;

orderdetails

SELECT * FROM orderdetails;

orders

SELECT * FROM orders;

payments

SELECT * FROM payments;

productlines

SELECT * FROM productlines;

products

SELECT * FROM products;

Result