w3resource

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:

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