MySQL Subquery: Examples

Back Home

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