SQL VIEW Exercises: Create a view to keeping track the number of customers ordering number of salesmen attached, average amount of orders and the total amount of orders in a day
SQL VIEW : Exercise-5 with Solution
5. From the following table, create a view to count the number of unique customer, compute average and total purchase amount of customer orders by each date.
Sample table: orders
Sample table: customer
Sample Solution:
CREATE VIEW totalforday
AS SELECT ord_date, COUNT(DISTINCT customer_id),
AVG(purch_amt), SUM(purch_amt)
FROM orders
GROUP BY ord_date;
output:
sqlpractice=# SELECT * sqlpractice-# FROM totalforday; ord_date | count | avg | sum ------------+-------+-----------------------+--------- 2012-04-25 | 1 | 3045.6000000000000000 | 3045.60 2012-06-27 | 1 | 250.4500000000000000 | 250.45 2012-07-27 | 1 | 2400.6000000000000000 | 2400.60 2012-08-17 | 3 | 95.2633333333333333 | 285.79 2012-09-10 | 3 | 2326.3833333333333333 | 6979.15 2012-09-22 | 1 | 322.0000000000000000 | 322.00 2012-10-05 | 2 | 132.6300000000000000 | 265.26 2012-10-10 | 2 | 2231.9150000000000000 | 4463.83 (8 rows)
Inventory database model:

Contribute your code and comments through Disqus.
Previous: From the following table, create a view to count the number of customers in each grade.
Next: From the following tables, create a view to get the salesperson and customer by name. Return order name, purchase amount, salesperson ID, name, customer name.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework