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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/view/sql-view-exercise-9.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics