SQL Exercises: Highest grade for each of the cities of the customers

Last update on February 11 2025 08:01:05 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the highest grade of the customers in each city. Return city, maximum grade.

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        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
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

Sample Solution:

-- This query retrieves the maximum 'grade' value for each 'city' from the 'customer' table.
SELECT city, MAX(grade)
-- Specifies the table from which to retrieve the data (in this case, 'customer').
FROM customer
-- Groups the result set by the 'city' column.
GROUP BY city;

Output of the Query:

city		max
London		300
Paris		300
New York	200
California	200
Berlin		100
Moscow		200

Explanation Code

The said SQL statement retrieves the maximum "grade" value for each "city" in the 'customer' table. A "GROUP BY" clause is used in the query to group the 'customer' table according to a city, and a "MAX" function is used to determine the maximum grade for each group based on the "GROUP BY" clause.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation :

Practice Online

For more Practice: Solve these Related Problems:

• Write a SQL query to find the highest grade of customers in the city 'Berlin'.
• Write a SQL query to find the highest grade of customers who are assigned to salesman ID 5003.
• Write a SQL query to find the highest grade of customers who have made at least one purchase.
• Write a SQL query to find the highest grade of customers in each city, but only for cities with more than one customer.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the minimum purchase amount of all the orders.
Next SQL Exercise: Highest purchase amount ordered by the each customer.