Common DB2 database statements (1)

Common statements for DB2 databases:

1. Search for the employee's number, name, department, and date of birth. If the date of birth is null, the date is displayed as unknown and is sorted by department. The date format is yyyy-mm-dd.

Select emp_no, emp_name, dept, isnull (convert (char (10), birthday, 120), 'date unknown ') birthday

From employee

Order by dept

2. Search for the names, gender, department, and title of employees in the same unit as Yu Ziqiang.

Select emp_no, emp_name, dept, title

From employee

Where emp_name <> 'yu Ziqiang 'and dept in

(Select dept from employee

Where emp_name = 'yu Ziqiang ')

3. Collect the total salary of each department by department

Select dept, sum (salary)

From employee

Group by dept

4. Search for the sales status of a product named "14-inch display", and display the product number, sales quantity, unit price, and amount.

Select a. prod_id, qty, unit_price, unit_price * qty totprice

From sale_item a, product B

Where a. prod_id = B. prod_id and prod_name = '14-inch dashboard'

5. In the sales list, summarize the sales quantity and amount of each product by product number.

Select prod_id, sum (qty) totqty, sum (qty * unit_price) totprice

From sale_item

Group by prod_id

6. Use the convert function to calculate the total order amount for each customer in 1996 by customer number

Select cust_id, sum (tot_amt) totprice

From sales

Where convert (char (4), order_date, 120) = '20140901'

Group by cust_id

7. Search for customer numbers, names, and total orders with sales records

Select a. cust_id, cust_name, sum (tot_amt) totprice

From customer a, sales B

Where a. cust_id = B. cust_id

Group by a. cust_id, cust_name

8. Search for customer numbers, names, and total orders with sales records in June 1997

Select a. cust_id, cust_name, sum (tot_amt) totprice

From customer a, sales B

Where a. cust_id = B. cust_id and convert (char (4), order_date, 120) = '123'

Group by a. cust_id, cust_name

9. Search for the largest sales record at a time

Select order_no, cust_id, sale_id, tot_amt

From sales

Where tot_amt =

(Select max (tot_amt)

From sales)

10. Search for the salesperson list and sales date with at least three sales attempts

Select emp_name, order_date

From employee a, sales B

Where emp_no = sale_id and a. emp_no in

(Select sale_id

From sales

Group by sale_id

Having count (*)> = 3)

Order by emp_name

This is the ten frequently-used statements that DB2 databases must master. There are also many frequently-used statements that have not been introduced here, in future articles, we will introduce more knowledge to you and hope you will continue to pay attention to it.