Find the largest or minimum of the first few columns, or data greater than or less than a value (maximum or average) 1: Find the top three employees in the company's income:
SQL> SELECT ROWNUM, LAST_NAME, SALARY 2 from (SALECT 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 the statement is wrong:
SQL> SELECT ROWNUM, LAST_NAME, SALARY 2 from S_EMP 3 WHERE ROWNUM <= 3 4 Order by Salary DESC;
Rownum last_name salary ---------- ---------------------------------- 1 Velasquez 4750 3 Nagayama 2660 2 NGAO 2000
2: Find the data of a certain line or a few lines in the table:
(1): Find the third row of data in the table: It is not possible to use the following method because ROWNUM can be used to use
SQL> Select * from s_emp 2 where rownum = 3;
No rows selected
SQL> SELECT * from 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 the data between the third line to the fifth line: 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 29453: Find Those employees who have average salary is higher than their department.
(1): First method: SQL> SELECT LAST_NAME, DEPT_ID, SALARY 2 from S_EMP A 3 WHERE SALE> (SALECT AVG (SALARY) 4 from S_EMP 5 where dept_id = a.dept_id);
Last_name dept_id salary --------------------------------- 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.110 rows SELECTED.
4: Find out the employees of those salary of the salary greater than the Manager of their department.
SQL> L 1 SELECT ID, LAST_NAME, SALARY, Manager_ID 2 from S_Emp A 3 Where Salary> (SELECT SALY 4 from S_EMP 5 * WHERE ID = a.manager_id) SQL> /
ID last_name salary manager_id ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------- 6 Urguhart 2280 2 7 Menchu 2375 2 8 Biri 2090 2 9 CatchPole 2470 2 10 Havel 2483.3 2 12 Giljum 2831 3 13 Sedeghi 2878.5 3 14 nguyen 2897.5 3 15 Dumas 2755 3 16 Maduro 2660 6
10 rows selected.
The first question: There are two tables as follows: Table A (VARCHAR (32) Name, Int grade data: zhangshan 80 lisi 60 WANGWU 84 table B (varchar (32) Name, int Age data: zhangshan 26 lisi 24 WANGWU 26 Wutian 26
(1) Write the SQL statement to get the following query results: Name Grade age zhangshan 80 26 lisi 60 24 WANGWU 84 26 Wutian Null 26 Question: How to get the record of the person who has no results here?
(2) Write the SQL statement to get the average score of different ages according to the age (Age) group group, and write the result. Doubt: According to the name, Wutian does not have a grade, shouldn't you count?
(3) There is a database table DEPT as follows: ID_NO ID_NAME 1000 S1 1001 S2 1002 S3 1003 S4 1000 S5 1000 S6 1001 S7 table has ID_NO repetition, such as 3 records of ID_no 1000, such as ID_NO 1001 There are 2 records. Now, according to ID_NO, you need to delete those records that Id_no repeated, but you cannot delete all records with the ID_no, you must keep a record with this ID_NO (such as only 1000 left) Next record)
(1) Please write the SQL statement (or SQL statement group) to query all ID_no repetitive records.
(2) Please write the result of the requirements of the SQL statement.
1: SQL> CREATE TABLE A 2 (Name Varchar2 (32), 3 grade Int; Table Created. SQL> INSERT INTO A_T 2 VALUES ('& A', & B); Enter Value for A: Zhangshan Enter Value for B: 80 OLD 2: Values ('& a', & b) New 2: Values ('Zhangshan', 80)
1 row created.
SQL> / Enter Value for A: Lisi Enter Value for B: 60 OLD 2: Values ('& A', & B) New 2: Values ('Lisi', 60)
1 row created.
SQL> / Enter Value for A: WANGWU Enter Value for B: 84 OLD 2: Values ('& a' & b) New 2: Values ('WANGWU', 84)
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> CREATE TABLE B_T 2 (Name Varchar2 (32), 3 AGE INT);
Table created.
SQL> INSERT INTO B_T 2 VALUES ('& A', & B); Enter Value for A: Zhangshan Enter Value for B: 26 OLD 2: Values ('& A', & B) New 2: Values ('Zhangshan', 26) 1 Row created.
SQL> / Enter Value for A: Lisi Enter Value for B: 24 OLD 2: Values ('& A', & B) New 2: Values ('Lisi', 24)
1 row created.
SQL> / Enter Value for A: WANGWU ENTER VALUE for B: 26 OLD 2: Values ('& a' & b) New 2: Values ('Wangwu', 26)
1 row created.
SQL> / Enter Value for A: Wutian Enter Value for B: 26 OLD 2: Values ('& A', & B) New 2: Values ('Wutian', 26)
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> SQL> Col grade Null 'Null' SQL> L 1 Select B.Name, A.Grade, B.AGE 2 from A_T A, B_T B 3 * Where A.Name ( ) = B.Name SQL> /
Name grade agn --------------------------------- ----- Lisi 60 24 WANGWU 84 26 Wutian Null 26 Zhangshan 80 26
SQL>
2: SQL> L 1 SELECT NAME, AVG (Grade), AGE 2 from (SELECT B.NAME NAME, A.GRADE Grade, B.AGE 3 from A_T A, B_T B 4 Where A.Name ( ) = B .name) 5 * Group by agn, name sql> /
Name AVG (Grade) agn -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- Lisi 60 24 WANGWU 84 26 Wutian 26 zhangshan 80 26
Swutian does not have a score without a score, 3: 1, when we want to create a unique index for a table, if the table has a repetitive record, it cannot be created. Method Principle: 1, Oracle, each record has a RowID, RowID is unique throughout the database, RowID determines which data file, block, line on each record is in Oracle. 2. In the repeated record, the contents of all columns may be the same, but the ROWID will not be the same, so as long as it is determined that the maximum ROWID has the maximum ROWID, the rest is removed.
3, the following statement is used to three skills: RowID, child query, alias.
Implementation: SQL> Create Table A (2 BM Char (4), - Code 3 MC VARCHAR2 (20) - Name 4) 5 /
The table has been established.
SQL> INSERT INTO a VALUES ('1111', '1111'); SQL> INSERT INTO A VALUES ('1112', '1111'); SQL> INSERT INTO A VALUES ('1113', '1111'); SQL> INSERT INTO A VALUES ('1114', '1111');
SQL> INSERT INTO A SELECT * FROM A;
Insert 4 records.
SQL> Commit; SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC ------------------ ---- ------- 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 0000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 000000D5.0004.0002 1111 1111 0000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111
Query 8 records.
Isolated Record SQL> SELECT ROWID, BM, MC from a where a.rowid! = (SELECT MAX (RowID) from a b where a.bm = b.bm and a.mc = b.mc);
Rowid BM MC ---------------------------------------- 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111
Delete Record SQL> Delete from a a where a.rowid! = (SELECT MAX (RowID) from A b where A.Bm = B.Bm and A.MC = B.MC);
Delete 4 records.
SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC ---------------------------------------- 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111
In fact, there are a lot of ways, using IN or use group by: 1: Using IN:
A) Find out duplicate data: SQL> L 1 SELECT ROWID, E. * From ae 2 * where e.rowid> (Select Min (x.rowid) from ax where x.bm = E.BM and X.MC = E . mc); Rowid BM MC ---------------------------- Aaabdcaagaaaayyaae 1111 1111 AAABdcAAGAAAAYyAAF 1112 1111 AAABdcAAGAAAAYyAAG 1113 1111 AAABdcAAGAAAAYyAAH 1114 1111B) deduplication: SQL> l 1 delete from a 2 where rowid in (select rowid from ae 3 * where e.rowid> (select min (x.rowid) from ax where x.bm = e.bm and x.mc = e.mc)) SQL> /
4 rows deleded.
SQL> SELECT * FROM A;
BM MC ---- -------------------- 1111 1111 1112 1111 1113 1111 1114 1111
SQL>
2: Use group by:
A): Find Dress Data: SQL> L 1 SELECT BM, MC 2 from a 3 Group BM, MC 4 * Having Count (*)> 1 SQL> /
BM MC ---- -------------------- 1111 1111 1112 1111 1113 1111 1114 1111
SQL>
B) Delete Data: SQL> Delete from A 2 Where (BM, MC) in (SELECT BM, MC 3 from A 4 Group by BM, MC 5 Having Count (*)> 1) 6 And RowId Not in (SELECT MIN (RowID) 7 from a 8 Group by BM, MC 9 Having Count (*)> 1);
4 rows deleded.
SQL> SELECT * FROM A;
BM MC ---- -------------------- 1111 1111 1112 1111 1113 1111 1114 1111
SQL>
