KEMBAR78
Practical Assignment - 9 | PDF | Chess | Chess Theory
Scribd
Upload
4 views1 page

Practical Assignment - 9

The document outlines a practical assignment involving SQL queries based on a sample 'orders' table. It includes tasks such as calculating total and average purchase amounts, finding the number of salespeople, and determining maximum and minimum purchase amounts. Additionally, it requires queries to analyze purchases by customer and date, with specific filters and conditions.

Uploaded by

kumarvineet4893
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views1 page

Practical Assignment - 9

The document outlines a practical assignment involving SQL queries based on a sample 'orders' table. It includes tasks such as calculating total and average purchase amounts, finding the number of salespeople, and determining maximum and minimum purchase amounts. Additionally, it requires queries to analyze purchases by customer and date, with specific filters and conditions.

Uploaded by

kumarvineet4893
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

PRACTICAL ASSIGNMENT – 9

Consider the following table to solve the queries.

Sample table: orders


ord_no purch_amt ord_date customer_id salesman_id
---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001

1. write a SQL query to calculate total purchase amount of all orders. Return total purchase
amount.
2. write a SQL query to calculate average purchase amount of all orders. Return average
purchase amount.
3. write a SQL query to find the number of salespeople.
4. write a SQL query to find the maximum purchase amount.
5. write a SQL query to find the minimum purchase amount.
6. write a SQL query to find the highest purchase amount ordered by each customer. Return
customer ID, maximum purchase amount.
7. write a SQL query to find the highest purchase amount ordered by each customer on a
particular date. Return, order date and highest purchase amount.
8. write a SQL query to find the highest purchase amount on '2012-08-17' by each salesperson.
Return salesperson ID, purchase amount.
9. write a SQL query to find highest order (purchase) amount by each customer in a particular
order date. Filter the result by highest order (purchase) amount above 2000.00. Return
customer id, order date and maximum purchase amount.
10. write a SQL query to find the maximum order (purchase) amount in the range 2000, 6000
(Begin and end values are included.) by combination of each customer and order date.
Return customer id, order date and maximum purchase amount.
11. write a SQL query to find the maximum order (purchase) amount by each customer. The
customer ID should be in the range 3002 and 3007(Begin and end values are included.).
Return customer id and maximum purchase amount.
12. write a SQL query to count all the orders generated on '2012-08-17'. Return number of
orders

You might also like