w3resource

SQL VIEW Exercises: Define a view that shows the number of orders in each day

SQL VIEW: Exercise-14 with Solution

14. From the following table, create a view to get number of orders in each day. Return order date and number of orders.

Sample table: orders


Sample Solution:

CREATE VIEW dateord(ord_date, odcount)
AS SELECT ord_date, COUNT (*)
FROM orders 
GROUP BY ord_date;

output:

sqlpractice=# SELECT *
sqlpractice-# FROM dateord;
  ord_date  | odcount
------------+---------
 2012-10-05 |       2
 2012-08-17 |       3
 2012-07-27 |       1
 2012-09-22 |       1
 2012-09-10 |       3
 2012-10-10 |       2
 2012-06-27 |       1
 2012-04-25 |       1
(8 rows)

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous: From the following tables, create a view that shows all matches of customers with salesperson such that at least one customer in the city of customer served by a salesperson in the city of the salesperson.
Next: From the following tables, create a view to find the salespersons who issued orders on October 10th, 2012. Return all the fields of salesperson.

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