Fifty-fourteen referral of common DB2 statements

This is a great question from Baidu Library. We recommend that you use SQL beginners to do this. It will improve a lot. Don't look at the answer first.

This database is from DB2. The answer may be different from mssql in some details. I don't know, but I have never run it.

 

Is the E-R of the Enterprise Order Management System, provides the employee information, customer information, supplier information, product information, order information, Order Details Management. <Pk> description is a primary key, <fk> description is a foreign key, and the foreign key constraint is marked by the arrow lines below the graph. In the employee information table, the sex value is m or f, indicating male and female.

The creation script is as follows:

/* ===================================================== ======================================= */

/* Table: Supplier information Table Supplier */

/* ===================================================== ======================================= */

Create table Supplier (

SupplierID char (5) not null,

SupplierName varchar (50) not null,

Phone varchar (20) not null,

Address varchar (50) null,

PostalCode varchar (15) null,

City varchar (20) null,

Constraint PK _ Supplier primary key (SupplierID)

)

Go

 

/* ===================================================== ======================================= */

/* Table: Products */

/* ===================================================== ======================================= */

Create table Products (

ProductID char (5) not null,

ProductName varchar (50) not null,

SupplierID char (5) not null,

Constraint PK _ Products primary key (ProductID ),

Constraint FK_PRODUCTS_SUPPLIER foreign key (SupplierID)

References Supplier (SupplierID)

)

Go

 

 

 

/* ===================================================== ======================================= */

/* Table: customer information Table Customers */

/* ===================================================== ======================================= */

Create table Customers (

CustomerID char (5) not null,

CustomerName varchar (50) not null,

Phone varchar (20) not null,

Address varchar (50) null,

PostalCode varchar (15) null,

City varchar (20) null,

Constraint PK _ MERs primary key (CustomerID)

)

Go

 

/* ===================================================== ======================================= */

/* Table: Department */

/* ===================================================== ======================================= */

Create table Department (

Repeated mentid char (5) not null,

DepartmentName char (50) not null,

Constraint pk_Department primary key (inclumentid)

)

Go

 

/* ===================================================== ======================================= */

/* Table: employee information Table Employees */

/* ===================================================== ======================================= */

Create table Employees (

EmployeeID char (5) not null,

EmployeeName varchar (30) not null,

Sex char (1) not null,

BirthDate smalldatetime null,

HireDate smalldatetime null,

Repeated mentid char (5) not null,

Title varchar (20) null,

Address varchar (50) null,

LinkPhone varchar (20) null,

Salary smallmoney not null,

Constraint PK _ Employees primary key (EmployeeID ),

Constraint FK_EMPLOYEE_DEPTMENT foreign key (DepartmentID)

References Department (dimension mentid ),

Constraint CK_Sex check (sex in ('M', 'F '))

)

Go

 

/* ===================================================== ======================================= */

/* Table: Order Table Orders */

/* ===================================================== ======================================= */

Create table Orders (

OrderID char (5) not null,

CustomerID char (5) not null,

EmployeeID char (5) not null,

OrderDate smalldatetime not null,

RequiredDate smalldatetime null,

Amount numeric (12, 2) not null,

Constraint PK _ Sales primary key (OrderID ),

Constraint FK_ORDERS_CUSTOMER foreign key (CustomerID)

References MERs (CustomerID ),

Constraint FK_ORDERS_EMPLOYEE foreign key (EmployeeID)

References Employees (EmployeeID)

)

Go

 

/* ===================================================== ======================================= */

/* Table: Order List OrderDetails */

/* ===================================================== ======================================= */

Create table OrderDetails (

OrderID char (5) not null,

ProductID char (5) not null,

UnitPrice numeric (12, 2) not null,

Quantity smallint not null,

Constraint pk_saledetails primary key (OrderID, ProductID ),

Constraint FK_ORDERDETAILS_ORDERS foreign key (OrderID)

References Orders (OrderID ),

Constraint FK_ORDERDETAILS_PRODUCTS foreign key (ProductID)

References Products (ProductID)

)

Go

 

Follow these steps:

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.

2. Search for the employee ID, employee name, gender, department, and title of the same organization as Yu Ziqiang.

3. Summarize by department, and output the Department name and the total salary of the Department.

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

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

6. Customer numbers: the total order amount of each customer in 1996 exceeds 0.5 million customer numbers and total orders

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

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

9. Search for the largest sales record at a time

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

11. Use the Exists keyword to find the customer name without the order record.

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

13. Search for the Sales Status of the product "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.

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.

15. Search for the customer name and total payment with the largest sales amount

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

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

18. Search for customer numbers, names and product numbers, product names, quantities, and amounts that are at least the same as those sold by the customer "World Technology Development Corporation"

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

20. Search for all information (including customer ID and name) with an order amount greater than 20000)

21. Number of employees with salaries between-in the statistical table

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

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

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

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

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

27. computing has sold several products in total.

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

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

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

31. The Orders table finds all Orders whose order amount is greater than the amount of each order received by the E0013 salesman on.

32. Calculate the average unit price of the 'p0001' Product

33. Find the order received by the female employee of the company

34. Find the employees who enter the company's services on the same day

35. Find the employee ID and name with the current performance exceeding 232000 RMB.

36. query the average salary of all female employees and the average salary of all female employees whose addresses are in Shanghai.

37. query the employee information whose salary exceeds the average salary in the employee table.

38. Identify the number of sales personnel whose current sales performance exceeds 40000 yuan and their sales performance, and sort them by sales performance from large to small.

39. Find the order number and order amount received by the company's male salesman and the order amount exceeds 2000 yuan.

40. query the order number and order amount with the highest order amount in the Orders table.

41. query the customer name and address for which the order amount exceeds 24000 RMB in each order.

42. Find the total order amount of each customer, display the customer number and total order amount, and sort them in descending order according to the total order amount.

43. Calculate the total quantity and average unit price of each product ordered by each customer, and sort the number by customer number and product number from small to large.

44. query the order numbers of more than three products.

45. The products to be queried include at least the orders for the products ordered in order 10003.

46. In the Orders table, find all the Orders whose order amount is greater than the amount of each order received by the E0013 salesman on December 10, and display the salesman who undertakes the Orders and the amount of the order.

47. query the information of the employees who undertake the business at the end.

48. query the name, phone number, order number, and order amount of a customer from Shanghai.

49. query the performance of each clerk in each month, and sort by Clerk ID and month in descending order.

50. Calculate the total sales quantity and total sales amount of each product. The product number, product name, total quantity, and total amount must be displayed and arranged in ascending order by product number.

51. query the customer number, customer name, and address of the customer whose total order amount exceeds 'c0002.

52. query the best-performing salesman's number, salesman's name, and total sales amount.

53. query the detailed list of each product ordered by each customer. The customer number, customer name, product number, product name, quantity, and unit price are displayed.

54. Calculate the average salary of each department, and sort the average salary from small to large.

55. Update the total amount of each order according to the order list.

 

Answer download