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

SQL Aggregate Functions: Exercise-8 with Solution

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

Query Visualization:

Duration:

Rows:

Cost:

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.