SQL Exercises: Customers with higher grades than everyone in New York

SQL SUBQUERY : Exercise-26 with Solution

26. From the following tables write a SQL query to find those customers whose grades are higher than those living in New York City. Return customer_id, cust_name, city, grade and 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 all values in the set of 'grade' returned by a subquery
WHERE grade > ALL
   -- Subquery: Selecting 'grade' values from the 'customer' table where 'city' is 'New York'
   (SELECT grade
	FROM customer
	WHERE city='New York');

Output of the Query:

customer_id	cust_name		city	grade	salesman_id
3008		Julian Green	London	300		5002
3004		Fabian Johnson	Paris	300		5006

Explanation:

The said SQL query is selecting all columns (*) from the table 'customer' where the "grade" value of the row is greater than all 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 equal to 'New York'. The ALL keyword is used to check if the outer query's condition is true for all the values in the subquery.
The outer query will only return the rows from the 'customer' table whose grade is greater than all the grades of customers who are from New York.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Orders higher than any amount for a London customer.
Next SQL Exercise: Salesperson live in the same city as the customers.