w3resource

SQL Exercises: Salesman details by smallest ID along with order date

SQL Formatting Output: Exercise-7 with Solution

From the following table, write a SQL query that calculates the maximum purchase amount generated by each salesperson for each order date. Sort the result-set by salesperson id and order date in ascending order. Return salesperson id, order date and maximum purchase amount.

Sample table: orders


Sample Solution :

SELECT salesman_id,ord_date,MAX(purch_amt) 
FROM orders 
GROUP BY salesman_id,ord_date 
ORDER BY salesman_id,ord_date;

Output of the Query:

salesman_id	ord_date	max
5001		2012-04-25	3045.60
5001		2012-07-27	2400.60
5001		2012-09-10	5760.00
5001		2012-10-05	65.26
5002		2012-06-27	250.45
5002		2012-09-10	948.50
5002		2012-10-05	150.50
5003		2012-08-17	110.50
5003		2012-10-10	2480.40
5005		2012-09-10	270.65
5006		2012-10-10	1983.43
5007		2012-08-17	75.29

Relational Algebra Expression:

Relational Algebra Expression: Salesman details by smallest ID along with order date.

Relational Algebra Tree:

Relational Algebra Tree: Salesman details by smallest ID along with order date.

Explanation :

Syntax of make a report with salesman ID, order date in such an arrangement smallest order

Pictorial presentation :

Result of Syntax of make a report with salesman ID, order date in such an arrangement smallest order

Practice Online


Query Visualization:

Duration:

Query visualization of Salesman details by smallest ID along with order date - Duration

Rows:

Query visualization of Salesman details by smallest ID along with order date - Rows

Cost:

Query visualization of Salesman details by smallest ID along with order date - Cost

 

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find all the customers. Sort the result-set by customer_id. Return cust_name, city, grade.
Next: From the following table, write a SQL query to find all the customers. Sort the result-set in descending order on 3rd field. Return customer name, city and grade.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Copy data into another table

If both tables are truly the same schema:

INSERT INTO newTable
SELECT * FROM oldTable

Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

Ref: https://bit.ly/3jMQkbA