Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the solutions to SQL query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Query 1

The following table consists of two columns: name and occupation. All names need to be queried and followed by a parenthesis that encloses the corresponding initials in the occupation column.

The solution of this article

SELECT CONCAT (Name,'(', SUBSTR (Profession, 1,1),') FROM table

Because you need to combine a name with a profession, you can use CONCAT. And because there is only one letter in parentheses, you can use SUBSTR to pass the column name, the start index, and the end index. Since only the initials are needed, we will pass 1Go 1 (including the start index, but not the end index).

Query 2

Tina needs to calculate the average salary of all employees from the EMPLOYEES table she created, but the results show that the average is very low, which may be because the return zero button on the keyboard has failed. She wants us to help find out the difference between the miscalculated average and the actual average. We need to write a query to find errors (actual average-calculate average).

The solution of this article

SELECT AVG (Salary)-AVG (REPLACE (Salary, 0,') FROM table

It is important to note that only one table contains actual wage values. To create an error scene, replace 0 with REPLACE. Then pass the column name, the replacement value, and the value used to replace the REPLACE method. Then, use the aggregate function AVG to find the difference of the average.

Query 3

Given a table, it is a binary search tree composed of two columns of node and parent node. You need to write a query to return the node types sorted in ascending order by node value. There are three types:

Root (Root)-if the node is root

Leaves (Leaf)-if the node is a leaf

Inner-if the node is neither a root nor a leaf

The solution of this article

After preliminary analysis, it can be concluded that if the corresponding P value of a given node N is NULL (null), then it is the root. If a given node N exists in the P column, it is not an internal node. Write a query based on this idea.

SELECT CASE WHEN P IS NULL THENCONCAT (N, 'Root') WHEN N IN (SELECTDISTINCT P from BST) THENCONCAT (N,' Inner') ELSE CONCAT (N, 'Leaf') ENDFROM BSTORDER BY N asc

CASE can be used as a switch function. As mentioned earlier, if N _ P is null for a given node, then N is the root. Therefore, we use CONCAT to combine node values and labels.

Similarly, if a given node N exists in the P column, it is an internal node. To get all the nodes in the P column, we write a subquery that returns all the different nodes in the P column. Because output is required to be sorted in ascending order of node values, the ORDER BY clause is used.

Query 4

The transaction table consists of transaction_id, user_id, transaction_date,product_id, and quantity (transaction ID, user ID, transaction date, product ID and quantity). You need to query the number of users who have purchased products over multiple days (note that a given user can purchase multiple products in one day).

The solution of this article

To solve this query, you cannot directly calculate the number of user_id occurrences, and since a given user can buy multiple times a day, user_id may return multiple times. Therefore, only when there are multiple different dates associated with a given user_id does it mean that the user has purchased the product for multiple days. Write the query in the same way. (internal query)

SELECT COUNT (user_id) FROM (SELECT user_id FROM orders GROUP BY user_id HAVING COUNT (DISTINCT DATE (date)) > 1) T1

Because the question asks about the number of user_id, not the user_id itself, COUNT is used in external queries.

Query 5

Give a subscription table that contains the start and end dates for each user subscription. You need to write a query that returns true/false for each user based on the overlap of dates with other users. For example, if the subscription cycle of user1 overlaps with any other user, the query must return true for user1.

The solution of this article

After a preliminary analysis, we know that each subscription must be compared with other subscriptions. Treat the start and end dates of userA as startA and endA, and similarly, userB is set to startB and endB accordingly. If startA ≤ endB and endA ≥ startB, you can say that the two date ranges overlap. Let's give two examples, first compare U1 and U3:

StartA = 2020Mel 01 endA = 2020Mel 01 MUE 31 startB = 2020Mel 01Mui 16 endB = 2020Mel 01Mui 26

It can be seen here that startA (2020mur01) is less than endB (2020mur01), so similarly, endA (2020mur01) is greater than startB (2020mur01), so it can be concluded that dates overlap. Similarly, if you compare U1 and U4, the above condition is not true, so FALSE is returned.

It is also important to ensure that users are not compared to their own subscriptions. At the same time, you want to run a left connection that can match the user with other users who meet the criteria. Now we will create two copies of the same table, S1 and S2.

SELECT * FROM subscriptions AS S1 LEFT JOIN subscriptions AS S2 ON s1.user_id! = s2.user_id AND s1.start_date = s2.start_date

Given a conditional join, there should be a user_id from S2 for each user_id in S1 if there is an overlap between dates.

Output

As you can see, in case the dates overlap, each user has a corresponding user. For user1, there are 2 lines that show that it matches 2 users. For user 4, the corresponding ID is empty, indicating that he does not match any other user. Now, group them all together, group them by the s1.user_ID field, and check to see if the value of the user whose s2.user_ID is not empty is true.

Final query

SELECT s1.user_id, (CASE WHEN s2.user_idIS NOT NULL THEN 1 ELSE 0 END) AS overlap FROM subscriptions AS S1 LEFT JOIN subscriptions AS S2 ON s1.user_id! = s2.user_id AND s1.start_date = s2.start_date GROUP BY s1.user_id

Use the CASE clause to mark 1 and 0 based on the s2.userid value of a given user. The final output is as follows:

This is the end of the content of "what are the solutions for SQL query". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.