﻿ SQL VIEW: Create a view to keep track the number of customers ordering, number of salesmen attached, average amount of orders and total amount of orders in a day - 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:

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