Common DB2 database statements (3)

Common DB2 database statementsI have already introduced you a lot before. This article will continue to describe commonly used statements in DB2 databases.

1. Number of employees with salaries in the statistical table between and

Select count (*) as count

From employee

Where salesary between 4000 and 6000

2. query the average salary of employees in the same department in the table, but only query the employees whose "residential address" is "Shanghai ".

Select avg (salary) avg_sal, dept

From employee

Where addr like 'shanghai %'

Group by dept

3. Change the employee's address in the table "Shanghai" to "Beijing"

Update employee

Set addr like 'beijing'

Where addr like 'shanghai'

4. Search for basic information about female employees in the business or accounting department

Select emp_no, emp_name, dept

From employee

Where sex = 'F' and dept in ('business', 'account ')

5. display the total sales amount of each product, and output the total sales amount from large to small.

Select prod_id, sum (qty * unit_price)

From sale_item

Group by prod_id

Order by sum (qty * unit_price) desc
 

6. Select the customer ID, customer name, and customer address of the numbers 'c0001' and 'c0004'

Select CUST_ID, cust_name, addr

From customer

Where cust_id between 'c0001 'AND 'c0004'

7. calculate the total number of products sold

Select count (distinct prod_id) as 'Total number of sold products'

From sale_item

8. Increase the salary of employees in the business department by 3%.

Update employee

Set salary = salary * 1.03

Where dept = 'business'

9. Find the employee information with the lowest salary in the employee table

Select *

From employee

Where salary =

(Select min (salary)

From employee)

10. Use join to query "customer name", "order amount", "Order Date", and "phone number" of the customer whose name is "customer C" purchased goods"

Select a. cust_id, B. tot_amt, B. order_date, a. tel_no

From customer a join sales B

On a. cust_id = B. cust_id and cust_name like 'customer bing'