w3resource
SQL exercises

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

SQL SUBQUERY: Exercise-25 with Solution

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

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');

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

Practice Online


Inventory database model

Query Visualization:

Duration:

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

Rows:

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

Cost:

Query visualization of Display 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 find all orders with an amount smaller than any amount for a customer in London.
Next: Write a query to display only those customers whose grade are, in fact, higher than every customer in New York.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming