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

SQL VIEW: Exercise-9 with Solution

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

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.

Inventory database model:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to find the salesman with the highest order.
Next SQL Exercise: View that shows the number of salesman in each city.