w3resource

SQL Exercises: View to count many customers at each lavel of grade

SQL VIEW: Exercise-4 with Solution

4. From the following table, create a view that counts the number of customers in each grade.

Sample table: customer


Sample Solution:

-- Creating a VIEW named 'gradecount' with columns 'grade' and 'number'
CREATE VIEW gradecount (grade, number)

-- Selecting columns 'grade' and the count of rows as 'number' from the 'customer' table
-- Grouping the result by the 'grade' column
AS SELECT grade, COUNT(*)
FROM customer
GROUP BY grade;

output:

sqlpractice=# SELECT *
sqlpractice-# FROM gradecount
sqlpractice-# WHERE number = 2;
 grade | number
-------+--------
       |      2
   200 |      2
   300 |      2
(3 rows)

Code Explanation:

The SQL statement creates a view called "gradecount" that shows the count of customers for each grade.
The specified two columns in the view are "grade" and "number".
It selects the "grade" column and the count of rows for each grade from the "customer" table and groups the results by grade.

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to find the salesmen of the city New York.
Next SQL Exercise: View to keep track the number of customers ordering.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.