【 Interview questions 】

There is a “phone bill table” with three fields: phone number (8 digits), month, and month spent.

Where, the monthly consumption is 0, indicating that no expenses are incurred in that month. The meaning of the first line of data: the monthly consumption (30.6 yuan) generated by the phone number (64262631) in the month (November 2017).

【 Question 1 】 Find all phone numbers with four mantras matching AABB, ABAB or AAAA since 2017 (as of October 31) (A and B represent any number from 1 to 9 respectively)

[Question 2] Delete the repeated data in October in the “Telephone fee table”.

【 答 案 】

Problem 1: Complex query

Using logical tree analysis, problem 1 is broken down into the following sub-problems, that is, we need to find a phone number that meets the following conditions:

1) Condition 1: telephone numbers from 201701 to 201710 in the telephone fee table;

2) Condition 2: The four mantissa of the phone number should be any digit from 1 to 9;

3) Condition 3: The four mantissa of the phone number conforms to AABB, ABAB or AAAA formats.

Therefore, the phone number that meets condition 1 is obtained first, and the four mantissa digits of the phone number are taken out respectively for the next judgment.

A string interception function is used here: substr()

Run the SQL statement to obtain the query result (” intermediate result 1 “) :

On the basis of “intermediate result 1” (that is, the above query result is used as a sub-query), condition 2 (all the mantras of the phone number are any digit from 1 to 9) is carried out.

AABB = 5th digit and 7th digit = 8th digit, ABAB = 5th digit = 7th digit and 6th digit = 8th digit, The AAAA format is the 5th, 6th, 7th, and 8th digits, which are included in the first two formats.

Add the judgment of condition 3 to the SQL above

The final SQL is as follows:

Run the SQL statement to get the “final result” :

Problem 2: Delete duplicate data

The deletion of duplicate values is a common skill in data cleaning.

1. Duplicate data is displayed

You can look at the previous article how do I Find Duplicate Data? In this case, the SQL for querying duplicate data is as follows

2. Delete the duplicate data

Delete data using the DELETE statement.

【 答 案 】

1. Check your mastery of subqueries

2. How do you master grouping and summarizing

3. Test how well you can delete tables, create tables, and delete data from tables

Recommended: How to learn SQL from zero?