SQL Exercises: Highest grade for each of the cities of the customers
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:
![Syntax of find the highest grade for each of the cities of the customers](https://www.w3resource.com/w3r_images/sql-exercise-aggregate-function-code-8.gif)
Visual presentation :
![Find the highest grade for each of the cities of the customers](https://www.w3resource.com/w3r_images/sql-exercise-aggregate-funtion-result-8.gif)
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics