SQL Exercises: Customers with the highest grade in alphabetical order

SQL SUBQUERY: Exercise-22 with Solution

22. From the following table write a SQL query to find all those customers with a higher grade than any customer who belongs to the alphabetically lower than the city New York. Return customer_id, cust_name, city, grade, salesman_id.

customer_id  cust_name     city        grade       salesman_id
-----------  ------------  ----------  ----------  -----------
3002         Nick Rimando  New York    100         5001
3005         Graham Zusi   California  200         5002
3001         Brad Guzan    London      100         5005
3004         Fabian Johns  Paris       300         5006
3007         Brad Davis    New York    200         5001
3009         Geoff Camero  Berlin      100         5003
3008         Julian Green  London      300         5002
3003         Jozy Altidor  Moncow      200         5007

Sample Solution:

-- Selecting all columns from the 'customer' table
SELECT *
-- Specifying the table to retrieve data from ('customer')
FROM customer
-- Filtering the results based on the condition that 'grade' is greater than any value in the set of grades returned by a subquery
WHERE grade > ANY
   -- Subquery: Selecting 'grade' values from the 'customer' table where 'city' is less than 'New York'
   (SELECT grade
	FROM CUSTOMER
	WHERE  city < 'New York');

Output of the Query:

customer_id	cust_name		city		grade	salesman_id
3007		Brad Davis		New York	200	5001
3005		Graham Zusi		California	200	5002
3008		Julian Green		London		300	5002
3004		Fabian Johnson		Paris		300	5006
3003		Jozy Altidor		Moscow		200	5007

Explanation:

The above SQL query is selecting all columns (*) from the table 'customer' where the "grade" value of the row is greater than any of the "grade" values in the subquery.
The subquery is also selecting the "grade" column from the 'customer' table, but only for rows where the "city" value is less than 'New York'.
The ANY keyword is used to check if there is any value in the subquery that the outer query's condition is true for.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Alphabetically list the salesmen below their customers.
Next SQL Exercise: All orders with amounts greater than one on given date.