SQL Exercises: View to show all customers with the highest grade

Last update on January 12 2026 12:48:15 (UTC/GMT +8 hours)

9. From the following table, create a view to find all the customers who have the highest grade. Return all the fields of customer.

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:

-- Creating a VIEW named 'highgrade'
CREATE VIEW highgrade

-- Selecting all columns from the 'customer' table
-- Filtering records where the 'grade' is equal to the maximum 'grade' in the 'customer' table
-- Using a subquery to find the maximum 'grade' in the 'customer' table
AS SELECT *
FROM customer
WHERE grade =
    (SELECT MAX (grade)
     FROM customer);

output:

sqlex=# select * from highgrade;
 customer_id |   cust_name    |  city  | grade | salesman_id 
-------------+----------------+--------+-------+-------------
        3008 | Julian Green   | London |   300 |        5002
        3004 | Fabian Johnson | Paris  |   300 |        5006
(2 rows)

Code Explanation:

The said query in SQL creates a view named 'highgrade' that selects all columns from a table named 'customer', but only includes records for customers with the highest grade.
The WHERE clause that filters only that records for customers with the highest grade will be included in the view.
The uses of subquery find the maximum grade value in the 'customer' table, and then checking that the grade value of each record in the main query matches the maximum grade value found in the subquery.

Go to:

PREV : View to find the salesman with the highest order.
NEXT : View that shows the number of salesman in each city.

Inventory database model:

Contribute your code and comments through Disqus.