Tips for Oracle Database Query

Tips for Oracle database query I. Cancel duplicate rows. For example, in the personnel management system, there is a basic employee information table. This table may contain fields such as department, position, employee name, and ID card number. If the content is queried, duplicate rows may not exist. However, if I want to know which departments and positions have been set up in the company, and relevant personnel have been configured for these departments and positions. In this case, how can I query it? If I query the Department table directly, the oracle training institution can know which departments and positions are set in the system. However, it is very likely that these departments or positions have no one due to personnel changes. Therefore, the information of all departments and positions found here cannot be ensured that there must be employees in this department or position. That is to say, this does not meet our above requirements. If I want to query employee information directly from the employee information table, although the Department and position information can be queried, there must be employee information. However, at this time, the department and position information are displayed in duplicate rows. If the procurement department has a division of labor and cooperation, there may be a procurement team lead. At this time, there are three duplicate records in the queried department and position information. Therefore, neither of the above two processing methods can fully meet the needs of enterprise users. In this case, we can actually use a DISTINCT function to eliminate the repeated rows queried. For example, we can use select distinct department information, job information FROM employee basic information table. This query statement with the DISTINCT constraint can not only query information about all positions and departments with employees, but also filter out duplicate records to improve readability. Therefore, this function is particularly useful in the database design process, especially in the use of query statements. Tip 2: Use a connector to connect multiple fields, such as the employee name, employee position, and date of birth, in the employee basic information table. If the three fields in the current view are displayed in the same field, there is a delimiter in the middle. For example, I want to display the result as "Manager Victor was born in May 3, 1976 ". What should we do? In fact, this is relatively simple. We can use the connector to connect these fields in the Select query statement. If you can write a query statement like this: SELECT employee position | ''| employee name | 'derived FROM '| date of birth as employee birth information FROM employee basic information table; you can use this statement to meet the preceding requirements. That is to say, in normal queries, we can use the | connector to connect some related fields. This is useful in the report view. For example, when I used to design a library management system, there were publishing houses and serial numbers in the basic information of books. However, when printing a report, you need to combine these fields into one field for printing. Therefore, you need to use this connector to connect these fields. Additionally, you can add descriptive text in the middle of the field to facilitate reading. For example, I added a space between the employee's position and employee's name, and added several annotated texts between the employee's name and date of birth. These functions seem relatively small, but can greatly improve the readability of the content. This is what we need to pay attention to in the database design process. In short, the later connector can improve the readability and flexibility of our reports.