SQL Subquery Exercises: Display all orders with an amount smaller than max amount for a customer in London. Use MAX keyword.

SQL SUBQUERY: Exercise-25 with Solution

25. From the following tables write a SQL query to find those orders where every order amount is less than the maximum order amount of a customer who lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

Sample table: orders

Sample table: customer

Sample Solution:

SELECT *
FROM orders
WHERE purch_amt < 
   (SELECT MAX (purch_amt)
	FROM orders a, customer b
	WHERE  a.customer_id=b.customer_id
	AND b.city='London');

Output of the Query:

ord_no	purch_amt	ord_date	customer_id	salesman_id
70002	65.26		2012-10-05	3002		5001
70004	110.50		2012-08-17	3009		5003
70011	75.29		2012-08-17	3003		5007
70001	150.50		2012-10-05	3005		5002
70012	250.45		2012-06-27	3008		5002

Explanation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those orders where an order amount less than any order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Next: From the following tables, write a SQL query to find those customers whose grade are higher than customers living in New York City. Return customer_id, cust_name, city, grade and salesman_id.