SQL statement Exercises

1.sql writing: (1) from table (2) Where Condition (3) Select column

2.rownum line number (pseudo column)

1.1rownum is always generated in the default order and does not change after sorting

1.2rownum can only use <,<=, cannot use >,>=

Explanation: Oracle is a row database, always the first row begins, row by line increment; >,>= is against the principle.

3. Temporary tables

2.1 Manual creation: Create global temporary TABLE * * * * *

2.2 Automatic creation: ORDER BY

Features: When the transaction or session ends, the data in the table is automatically deleted

4. Correlated subqueries: Pass values from the main query to the subquery as parameters

3. Use RowNum to find the top three of the highest wages in the employee table

-- Thinking of solving problems -- The results of the query are sorted in descending order from the result set after sorting, and the resulting rownum are ordered Select Rownum,empno,ename,sal  from (Select*fromorderbydesc) where rownum<=3;

ROWNUM EMPNO ename SAL
------- ---------- ---------- ----------
1 7839 KING 5000
2 7788 SCOTT 3000
3 7902 FORD 3000

4. Paging by using rownum

 --  How to solve the problem: R is the E1 line number, for the E2 column, because it is not the E2 line number, so you can >= 5  select  E2. *  from  (select  rownum r,e1.         *  from  (select  *   From  emp order  by   Sal) E1  where  rownum<=8  ) E2  where  r>=  5 ; 

5. Find employees who pay more than the department's average salary in the employee table

-- Sub-query Select E.empno,e.ename,e.sal,d.avgsal  from emp E, (select deptno,avgfromgroup by Deptno) d where e.deptno= and e.sal>d.avgsal;

-- Problem Solving Ideas: Related sub-query Select empno,ename,sal, (selectavgfromwhere deptno=   E.deptno) avgsal from emp ewhere sal>(Select avg  from where deptno=e.deptno);

5. Statistics of the number of employees entering the year

--using case and thenSelect Count(*) Total,sum( CaseTo_char (HireDate,'yyyy') when '1981'  Then 1                                    Else 0End) fromEMP;--using decodeSelect Count(*) Total,sum(Decode (To_char (HireDate,'yyyy'),'1981',1,0)) "1981" fromEmp

SQL statement Exercises