SQL VIEW Exercises: Create a view that shows all of the customers who have 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:

CREATE VIEW highgrade
  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)

Inventory database model:

Contribute your code and comments through Disqus.

Previous: From the following tables, create a view to find the salesperson who handles the customer with the highest order, at least 3 times on a day. Return salesperson ID and name.
Next: From the following table, create a view to count number of the salesperson in each city. Return city, number of salespersons.