w3resource
SQL exercises

SQL Subquery Exercises: Find all orders with an amount smaller than any amount for a customer in London

SQL SUBQUERY: Exercise-24 with Solution

24. Write a query to find all orders with an amount smaller than any amount for a customer in London.

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:

SQL Subqueries: Find all orders with an amount smaller than any amount for a customer in London.

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Find all orders with an amount smaller than any amount for a customer in London - Duration

Rows:

Query visualization of Find all orders with an amount smaller than any amount for a customer in London - Rows

Cost:

Query visualization of Find all orders with an amount smaller than any amount for a customer in London - Cost

Contribute your code and comments through Disqus.

Previous: Write a query to display all the orders that had amounts that were greater than at least one of the orders from October 9th 2012.
Next: Write a query to display all orders with an amount smaller than any amount for a customer in London.

What is the difficulty level of this exercise?