Common DB2 database statements (2)

Common commands for DB2 DatabasesI have already introduced the common statements that DB2 databases must master). Next I will introduce more common statements for DB2 databases.

1. Use an existing quantizer to find the customer name without an order record

Select cust_name

From customer

Where not exists

(Select *

From sales B

Where a. cust_id = B. cust_id)

2. Use the left outer link to find the customer ID, name, order date, and order amount of each customer. Do not display the order date. The date format is yyyy-mm-dd, Which is sorted by customer number, output by order in descending order for the same customer

Select a. cust_id, cust_name, convert (char (10), order_date, 120), tot_amt

From customer a left outer join sales B on a. cust_id = B. cust_id

Order by a. cust_id, tot_amt desc
 

3. Find the sales status of 16 m dram, and display the name, gender, sales date, sales quantity, and amount of the corresponding salesperson. The gender is represented by male and female.

Select emp_name, Gender = case a. sex when 'M' then 'male'

When 'F' then 'female'

Else 'UN'

End,

Sales date = isnull (convert (char (10), c. order_date, 120), 'date unknown '),

Qty quantity, qty * unit_price as amount

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

Where d. prod_name = '16m DRAM 'and d. prod_id = c. prod_id and

A. emp_no = B. sale_id and B. order_no = c. order_no

4. Search for the sales records of each person. The salesperson ID, name, gender, product name, quantity, unit price, amount, and sales date must be displayed.

Select emp_no, emp_name, Gender = case a. sex when 'M' then 'male'

When 'F' then 'female'

Else 'UN'

End,

Prod_name product name, sales date = isnull (convert (char (10), c. order_date, 120), 'date unknown '),

Qty quantity, qty * unit_price as amount

From employee a left outer join sales B on a. emp_no = B. sale_id, sale_item c, product d

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

5. Find the customer name and total payment with the largest sales amount

Select cust_name, d. cust_sum

From customer,

(Select cust_id, cust_sum

From (select cust_id, sum (tot_amt) as cust_sum

From sales

Group by cust_id) B

Where B. cust_sum =

(Select max (cust_sum)

From (select cust_id, sum (tot_amt) as cust_sum

From sales

Group by cust_id) c)

) D

Where a. cust_id = d. cust_id

6. Search for salesperson numbers, names, and sales with a total sales volume less than 1000 yuan

Select emp_no, emp_name, d. sale_sum

From employee,

(Select sale_id, sale_sum

From (select sale_id, sum (tot_amt) as sale_sum

From sales

Group by sale_id) B

Where B. sale_sum <1000

) D

Where a. emp_no = d. sale_id

7. Search for customer numbers, Customer names, product numbers, product names, quantities, and amounts of at least three types of products sold

Select a. cust_id, cust_name, B. prod_id, prod_name, d. qty, d. qty * d. unit_price

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

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

C. order_no = d. order_no and a. cust_id in (

Select cust_id

From (select cust_id, count (distinct prod_id) prodid

From (select cust_id, prod_id

From sales e, sale_item f

Where e. order_no = f. order_no) g

Group by cust_id

Having count (distinct prod_id)> = 3) h)

8. Search for customer numbers, names and product numbers, product names, quantities, and amounts that are at least the same as those sold by World Technology development companies.

Select a. cust_id, cust_name, d. prod_id, prod_name, qty, qty * unit_price

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

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

C. order_no = d. order_no and not exists

(Select f .*

From customer x, sales e, sale_item f

Where cust_name = 'World technology developers' and x. cust_id = e. cust_id and

E. order_no = f. order_no and not exists

(Select g .*

From sale_item g, sales h

Where g. prod_id = f. prod_id and g. order_no = h. order_no and

H. cust_id = a. cust_id)

)

9. Find the employee ID, department, and salary of all employees surnamed Liu in the table.

Select emp_no, emp_name, dept, salary

From employee

Where emp_name like 'Liu %'

10. Search for all customer numbers with an order amount greater than 2000

Select cust_id

From sales

Where tot_amt> 2000

I am very happy to share with you the frequently used statements of DB2 databases, hoping to help you.