SQL Subquery Exercises: Write a query to find all orders with an amount smaller than any amount for a customer in London. Use ANY keyword.

SQL SUBQUERY: Exercise-24 with Solution

24. From the following tables write a SQL query to find orders where the order amount is less than the order amount of a customer residing in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

Sample table: customer

Sample table: orders

Sample Solution:

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

Sample Output:

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:

Note : For some necessery regions data might have changed. You can get different result.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find all those orders whose order amount greater than at least one of the orders of September 10th 2012. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Next: From the following tables, write a SQL query to find those orders where every order amount less than the maximum order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.