What statements do you need to know when learning DB2 databases?

This article mainly tells you about the 10 common statements that we must master when learning the DB2 database. The following is an article about how to learn the DB2 database, A detailed description of the 10 commonly used statements that we all need to understand.

Databases, statements, learning databases, statements, learning

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. Find the sales status of a product named 14-inch display, and display the number, quantity, unit price, and amount of the product.

 
 

  
  
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)='1996'   
  
  
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)='1997'   
  
  
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   
 
 

The above content is an introduction to the Fifty-four common statements that DB2 databases must master. I hope you will gain some benefits.