Common DB2 database statements (4)

PreviouslyCommon statements that must be mastered by DB2 database 3) common statements that must be mastered by DB2 database 2) common statements that must be mastered by DB2 database 1 ), next we will introduce you to more information about common statements in DB2 databases.

1. In the sales table, find all orders with the order amount greater than "E0013 sales clerk's amount for each order received on"

Select *

From sales

Where tot_amt> all

(Select tot_amt

From sales

Where sale_id = 'e0013 'and order_date = '2017/15 ')

Order by tot_amt

2. Calculate the average unit price of the 'p0001' Product

Select avg (unit_price)

From sale_item

Where prod_id = 'p0001'

3. Find the order received by the female employee of the company

Select sale_id, tot_amt

From sales

Where sale_id in

(Select sale_id from employee

Where sex = 'F ')

4. Find the employees who enter the company's services on the same day

Select a. emp_no, a. emp_name, a. date_hired

From employee

Join employee B

On (a. emp_no! = B. emp_no and a. date_hired = B. date_hired)

Order by a. date_hired

5. Find the employee ID and name with the current performance exceeding RMB 232000

Select emp_no, emp_name

From employee

Where emp_no in

(Select sale_id

From sales

Group by sale_id

Having sum (tot_amt) <1, 232000)

6. query the average salary of all female employees and the average salary of all female employees whose addresses are in Shanghai.

Select avg (salary)

From employee

Where sex like 'F'

Union

Select avg (salary)

From employee

Where sex like 'F' and addr like 'shanghai %'

7. query the employee information whose salary exceeds the average salary in the employee table.

Select *

From employee

Where salary> (select avg (salary)

From employee)

8. Find the number of sales personnel whose current sales performance exceeds 10000 yuan and the sales performance, sorted by sales performance from large to small

Select sale_id, sum (tot_amt)

From sales

Group by sale_id

Having sum (tot_amt)> 10000

Order by sum (tot_amt) desc

9. Find the order number and order amount received by the company's male salesman and the order amount exceeds 2000 yuan

Select order_no, tot_amt

From sales, employee

Where sale_id = emp_no and sex = 'M' and tot_amt> 2000
 

10. query the order number and order amount with the highest order amount in the sales table

Select order_no, tot_amt from sales

Where tot_amt = (select max (tot_amt) from sales)

11. query the customer name and address for which the order amount exceeds 4000 RMB in each order

Select cust_name, addr from customer a, sales B

Where a. cust_id = B. cust_id and tot_amt> 4000

12. Find the total order amount of each customer, display the customer number and total order amount, and sort the order amount in descending order.

Select cust_id, sum (tot_amt) from sales

Group by cust_id

Order by sum (tot_amt) desc