Oracle common SQL query [turn]

In recent recently, many students have asked questions in the interview. Summary, one is about how to find and remove duplicate data, this is a detailed introduction to another post. The second is to identify the largest or minimum of the first few columns or more than or less than a certain value (maximum or average). In response to this situation, you will make an introduction. 1: Find out the highest three employees in the company's income: SQL> SELECT ROWNUM, LAST_NAME, SALY 2 from (SELECT LAST_NAME, SALARY 3 from S_EMP 4 Order By Salary DESC) 5 Where rownum <= 3; ROWNUM LAST_NAME SALARY - -------- ---------------------------------- 1 Velasquez 4750 2 Ropeburn 2945 3 Nguyen 2897.5 Note: Please analyze the statement why: SQL> SELECT ROWNUM, LAST_NAME, SALARY 2 from S_EMP 3 WHERE ROWNUM <= 3 4 ORDER BY SALY DESC; ROWNUM LAST_NAME SALY ---------- - ------------------------ ---------- 1 Velasquez 4750 3 Nagayama 2660 2 NGAO 2000 Note: Inly in Informix Use: SELECT First 10 * from table where ..... 2: Find the data in a table or a few rows of data: (1): Find the third line of data in the table: Use the following method if it is not Because the ROWNUM can be used to use numbers and other comparison symbols can be used.

SQL> Select * from s_emp 2 WHERE ROWNUM = 3; No Rows S_EMP 2 WHERE ROWNUM BETWEEN 3 and 5; NO ROWS SELECTED The correct way is as follows: SQL> L 1 SELECT LAST_NAME, SALARY 2 from (SELECT ROWNUM A, b. * 3 from s_emp b) 4 * where a = 3 sql> / last_name salary ---------------------------- ------ Nagayama 2660 (2): Find data between the third line to the fifth row: SQL> L 1 SELECT LAST_NAME, SALARY 2 FROM (SALECT ROWNUM A, B. * 3 from S_EMP B) 4 * WHERE A BETWEEN 3 and 5 SQL> / Last_name Salary ---------------------------------- Nagayama 2660 Quick- To-see 2755 Ropeburn 2945 3: Find out those employees who have average salary that are higher than their department.

(1): First method: SQL> SELECT LAST_NAME, DEPT_ID, SALARY 2 from S_Emp A 3 WHERE SALY> (SALECT AVG (SALARY) 4 from S_EMP 5 Where dept_id = a.dept_id); Last_name Dept_ID Salry ---- --------------------------------------- Velasquez 50 4750 Urguhart 41 2280 Menchu ​​42 2375 Biri 43 2090 Catchpole 44 2470 Havel 45 2483.3 Nguyen 34 2897.5 Maduro 41 2660 Nozaki 42 2280 Schwartz 45 2090 10 Rows SELECTED. (2): Second method: SQL> L 1 select a.last_name, a.salary, a.dept_id , B.avgsal 2 from s_emp A, (Select Dept_ID, AVG (SALARY) AVGSAL 3 from S_EMP 4 Group By DePT_ID) B 5 Where a.dept_id = B.DEPT_ID 6 * And A.salary> B.avgsal SQL>

/ Last_name salary dept_id avgsal -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- Velasquez 4750 50 3847.5 Urguhart 2280 41 2181.5 Menchu ​​2375 42 2055.16667 Biri 2090 43 1710 Catchpole 2470 44 1995 Havel 2483.3 45 2069.1 Nguyen 2897.5 34 2204 Maduro 2660 41 2181.5 Nozaki 2280 42 2055.16667 Schwartz 2090 45 2069.1 10 Rows SELECTED. 4: Find out the employees of those salary than the salary of the Manager in their department.