﻿ SQL - View to show all customers with the highest grade

# 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'

-- 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
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: