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

This article introduces the relevant knowledge of "what are the skills and traps of SQL interview". 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!

Ask a question

The most important thing to win an SQL interview is to ask as many questions as possible to make sure you have all the details of a given task and data sample. Understanding these requirements can help save time iterating over problems and better deal with marginal situations.

Many candidates will begin to solve the problem directly before they have a deep understanding of the SQL problem or dataset. After the author pointed out the problems in the solution, they had to modify the query repeatedly, wasted a lot of time on iteration, and even did not find the correct solution in the end.

The author's advice is to think of the SQL interview as working with a business partner, and maintain this mindset so that the interviewer will try to collect all the requirements of the data request before providing a solution.

Example

Find out the three highest-paid employees from the table below.

Sample: staff salary scale

The interviewer should ask the interviewer to elaborate on the concept of "top three"-must there be only three employees in the result? What are the requirements for juxtaposition processing? In addition, the interviewer should take a closer look at the sample employee's data-what is the data type of the salary field? Do you need to clear the data before calculating?

What kind of connection

In SQL, joins are often used to combine information from multiple tables. There are four different types of connections, but in most cases we only use natural, left, and full connections because the right connection is not intuitive and the left connection is easy to override. In a SQL interview, the interviewer needs to choose the correct connection according to the specific requirements of a given question.

Example

Find out the total number of periods in each student's class. (student ID card, name and number of classes are known.)

Sample: student list and course data sheet

It can be noted that not all students who appear in the course data sheet are on the student list, which may be because these students have graduated (which is typical in the transaction database and is deleted when the data is inactive). After knowing whether the interviewer wants to include inactive students, you can use left join and natural join to merge forms as appropriate.

WITHclass_count AS (SELECT student_id, COUNT (*) ASnum_of_class FROM class_history GROUP BY student_id) SELECT c.student_id, s.student_name, c.num_of_class FROM class_count c-- CASE 1: include only active students JOIN students ON c.student_id = s.student students CASE-CASE 2: include all students-- LEFT JOIN students ON c.student_id = s.student_id

GROUP BY

GROUP BY is the most basic function in SQL, which is widely used in data aggregation. If keywords such as sum, average, minimum, or maximum appear in a SQL problem, it is highly likely that GROUP BY should be used in the query. A common trap is to confuse WHERE with HAVING when filtering data with GROUP BY-a mistake many people have made.

Example

Calculate each student's required grade point average for each academic year, and find out the students with a ≥ 3.5 grade point in each semester.

Sample: GPA data sheet

Only required courses are considered when calculating GPA, so you need to use WHERE is_required = TRUE to exclude electives. You need to calculate each student's grade point average for each school year, so you need to use the GROUP BY command to group by student_id and school_year, and take the average of gpa. Finally, only rows with an average GPA higher than 3. 5 are retained, which can be achieved through HAVING. Then combine the above results:

SELECT student_id, school_year, AVG (gpa) AS avg_gpa FROM gpa_history WHERE is_required = TRUE GROUP BY student_id, school_year HAVING AVG (gpa) > = 3.5

Remember that whenever you use GROUP BY in a query, you can only select the columns to be grouped and aggregate because the row-level information in the other columns has been discarded.

Someone may be wondering what's the difference between WHERE and HAVING, or why you don't just use avg_gpa > = 3.5, but instead specify a function. The next section will give a detailed explanation.

Execution order of SQL query statements

Most people write SQL queries in top-down order, but they may not know that SELECT is one of the last functions executed by the SQL engine. The following is the order in which the SQL query is executed:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

FROM, JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT, OFFSET

Go back to the previous example. Because you need to filter out electives before calculating your grade point average, you can use WHERE is_required = TRUE instead of HAVING, because WHERE is executed before GROUP BY and HAVING. The reason for not using HAVINGavg_gpa > = 3.5 is that avg_gpa is defined as part of SELECT, so it cannot be referenced in the steps performed before SELECT.

Image source: unsplash

The author recommends that queries be written in the order in which they are executed, which is very useful when writing complex queries.

Window function

Window functions also often appear in SQL interviews. Five common window functions are as follows:

RANK / DENSE_RANK / ROW_NUMBER: assigns a rank to each row by sorting specific columns. If any partition column is given, the rows are arranged in the partition group to which they belong.

LAG / LEAD: retrieves column values from the previous or subsequent row based on the specified order and partition group.

In a SQL interview, the interviewer must know the differences between the ranking functions and when to use LAG/LEAD.

Example

Identify the three highest-paid employees in each department.

Sample: staff salary scale 2

When a SQL question asks for a "top N name", you can use the ORDER BY or ranking function to answer it. However, the above example requires that "the first N Xs in each Y" be calculated, which means that the interviewer should use the row ranking function because the rows in each partition group need to be arranged.

The following query can accurately find the three highest-paid employees, regardless of juxtaposition:

WITH TAS (SELECT *, ROW_NUMBER () OVER (PARTITION BYdepartment_id ORDER BY employee_salary DESC) AS rank_in_dep FROM employee_salary) SELECT * FROM T WHERE rank_in_dep

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.