Common DB2 database statements (5)

Common DB2 database statements:

1. Calculate the total quantity and average unit price of each product ordered by each customer, and sort the number by customer number and product number from small to large.

Select cust_id, prod_id, sum (qty), sum (qty * unit_price)/sum (qty)

From sales a, sale_item B

Where a. order_no = B. order_no

Group by cust_id, prod_id

Order by cust_id, prod_id

2. query the order numbers of more than three products

Select order_no

From sale_item

Group by order_no

Having count (*)> 3

3. The products to be queried include at least the orders for the products ordered in order 3

Select distinct order_no

From sale_item

Where order_no <> '3' and not exists (

Select * from sale_item B where order_no = '3' and not exists

(Select * from sale_item c where c. order_no = a. order_no and c. prod_id = B. prod_id ))

4. In the sales table, find all the orders with the order amount greater than "E0013 clerk's amount for each order received on, and display the salesman who undertakes the orders and the amount of the order

Select sale_id, tot_amt from sales

Where tot_amt> all (select tot_amt

From sales

Where sale_id = 'e0013' and order_date = '2017-11-10 ')

5. query the information of the employees who finally undertake the business

Select *

From employee

Where not exists

(Select * from sales B where a. emp_no = B. sale_id)

6. query the name, phone number, order number, and order amount of a customer from Shanghai.

Select cust_name, tel_no, order_no, tot_amt

From customer a, sales B

Where a. cust_id = B. cust_id and addr = 'shanghai'

7. query the performance of each clerk in each month, and sort by Clerk ID and month in descending order

Select sale_id, month (order_date), sum (tot_amt)

From sales

Group by sale_id, month (order_date)

Order by sale_id, month (order_date) desc

8. Calculate the total sales quantity and total sales amount of each product. The product number, product name, total quantity, and total amount must be displayed, and sorted by product number from small to large.

Select a. prod_id, prod_name, sum (qty), sum (qty * unit_price)

From sale_item a, product B

Where a. prod_id = B. prod_id

Group by a. prod_id, prod_name

Order by a. prod_id

9. query the customer number, customer name, and address of the customer whose total order amount exceeds 'c0002 '.

Select cust_id, cust_name, addr

From customer

Where cust_id in (select cust_id from sales

Group by cust_id

Having sum (tot_amt)>

(Select sum (tot_amt) from sales where cust_id = 'c0002 '))

10. query the best-performing salesman number, salesman name, and total sales amount

Select emp_no, emp_name, sum (tot_amt)

From employee a, sales B

Where a. emp_no = B. sale_id

Group by emp_no, emp_name

Having sum (tot_amt) =

(Select max (totamt)

From (select sale_id, sum (tot_amt) totamt

From sales

Group by sale_id) c)

11. query the detailed list of each product ordered by each customer. The customer number, customer name, product number, product name, quantity, and unit price must be displayed.

Select a. cust_id, cust_name, c. prod_id, prod_name, qty, unit_price

From customer a, sales B, sale_item c, product d

Where a. cust_id = B. cust_id and B. order_no = c. order_no and c. prod_id = d. prod_id

12. Ask for the average salary of each department, and sort by average salary from small to large

Select dept, avg (salary)

From employee

Group by dept

Order by avg (salary)