Summary of commonly used and practical SQL statements in DB2

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.
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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


11. Use an existing quantizer to find the customer name without an order record
Copy codeThe Code is as follows:
Select cust_name
From customer
Where not exists
(Select *
From sales B
Where a. cust_id = B. cust_id)


12. Use the left outer link to find the customer ID, name, order date, and order amount of each customer. Do not display the time. The date format is yyyy-mm-dd, Which is sorted by customer number, output by order in descending order for the same customer
Copy codeThe Code is as follows:
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


13. 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.
Copy codeThe Code is as follows:
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


14. query the sales records of each person. The salesperson ID, name, gender, product name, quantity, unit price, amount, and sales date must be displayed.
Copy codeThe Code is as follows:
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



15. Search for the customer name and total payment with the largest sales amount
Copy codeThe Code is as follows:
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


16. Search for salesperson numbers, names, and sales with a total sales volume less than 1000 yuan
Copy codeThe Code is as follows:
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


17. Search for customer numbers, Customer names, product numbers, product names, quantities, and amounts of at least three types of products sold
Copy codeThe Code is as follows:
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)


18. 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.
Copy codeThe Code is as follows:
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)
)

19. Find the employee ID, department, and salary of all employees surnamed Liu in the table.
Copy codeThe Code is as follows:
Select emp_no, emp_name, dept, salary
From employee
Where emp_name like 'Liu %'


20. Search for all customer numbers with an order amount greater than 2000
Copy codeThe Code is as follows:
Select cust_id
From sales
Where tot_amt> 2000


21. Number of employees with salaries in the statistical table between and
Copy codeThe Code is as follows:
Select count (*) as count
From employee
Where salesary between 4000 and 6000


22. query the average salary of employees in the same department in the table, but only query the employees whose "residential address" is "Shanghai ".
Copy codeThe Code is as follows:
Select avg (salary) avg_sal, dept
From employee
Where addr like 'shanghai %'
Group by dept


23. Change the employee's address in the table "Shanghai" to "Beijing"
Copy codeThe Code is as follows:
Update employee
Set addr like 'beijing'
Where addr like 'shanghai'


24. Search for basic information about female employees in the business or accounting department.
Copy codeThe Code is as follows:
Select emp_no, emp_name, dept
From employee
Where sex = 'F' and dept in ('business', 'account ')


25. display the total sales amount of each product, and output the total sales amount from large to small.
Copy codeThe Code is as follows:
Select prod_id, sum (qty * unit_price)
From sale_item
Group by prod_id
Order by sum (qty * unit_price) desc


26. Select the customer ID, customer name, and customer address of the numbers 'c0001' and 'c0004.
Copy codeThe Code is as follows:
Select CUST_ID, cust_name, addr
From customer
Where cust_id between 'c0001 'AND 'c0004'

27. computing has sold several products in total.
Copy codeThe Code is as follows:
Select count (distinct prod_id) as 'Total number of sold products'
From sale_item

28. Increase the salary of employees in the business department by 3%.
Copy codeThe Code is as follows:
Update employee
Set salary = salary * 1.03
Where dept = 'business'

29. Find the employee information with the lowest salary in the employee table.
Copy codeThe Code is as follows:
Select *
From employee
Where salary =
(Select min (salary)
From employee)

30. Use join to query "customer name", "order amount", "Order Date", and "phone number" of the customer whose name is "customer C" purchased goods"
Copy codeThe Code is as follows:
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'


31. In the sales table, find all orders with the order amount greater than "E0013 clerk's amount for each order received on.
Copy codeThe Code is as follows:
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


32. Calculate the average unit price of the 'p0001' Product
Copy codeThe Code is as follows:
Select avg (unit_price)
From sale_item
Where prod_id = 'p0001'


33. Find the order received by the female employee of the company
Copy codeThe Code is as follows:
Select sale_id, tot_amt
From sales
Where sale_id in
(Select sale_id from employee
Where sex = 'F ')



34. Find the employees who enter the company's services on the same day
Copy codeThe Code is as follows:
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


35. Find the employee ID and name with the current performance exceeding 232000 RMB.
Copy codeThe Code is as follows:
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)