Problem

This is a database problem that occurs in a department store. A database of the department store contains two tables, namely, the Promotion table and the SalesRecord table.

The Promotion table is a calendar for promotion:

The SalesRecord table is used to list the sales for each salesperson in a year:

Please select the salesperson who achieved the highest sales value in each promotion, so as to reward him/her the performance bonus.

Tip

General steps: Loop through the Promotion table. In each loop, firstly select all the sales records about the current promotion from the SalesRecord table; secondly, group these records by salesperson name and calculate the total sales value for each group so as to search for the salesperson achieving the highest sales value; thirdly, record the corresponding salesperson name in the new table sequence, and then this is the result.

1. Create a resulting table sequence before loops begin;

2. Loop through the records in Promotion table;

3. In each loop body, select all sales records about the current promotion from the SalesRecord table;

4. Group the records by salesperson name, count the total sales value for each salesperson, and create a new table sequence;

5. Select out the sales record in which the salesperson has achieved the highest sales value;

6. Write the salesperson’s name and the current promotion name to the resulting table sequence outside the loop body;

7. After the loops are finished, the resulting table sequence with detail data is what you want.

Code

Result