MySQL Subquery: Examples

#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