Oracle notes three, function, select

There are a few commonly used tables under the Scott table, and there are data. are EMP, dept, Salgrade, respectively;

1. View table structure with DESC

    DESC EMP;

2, empty table dual, the most commonly used empty table, such as:

    Select 2 * 4 from dual;

    Select Sysdate from dual;

3, double quotation marks can maintain the format

    such as: Select Sysdate "ToDay date" from dual;

4, | | String connection

    Example: Select 2*3 | | 8 from dual;

    Select Ename | | Sal from Scott.emp;

    Select Ename | | ' ORACLE ' from scott.emp;

5, single quotes, such as: Select 2 * 2 | | ' abc ' EFG ' from dual;

    Use two single quotes to denote a single quotation mark

6. Remove duplicate data distinct

    Select distinct deptno from Scott.emp;

    Remove repeated combinations: SELECT distinct deptno,job from Scott.emp;

7. Where query

    A, = query, select * from scott.emp where sal = 1500;

    B, compare <, >, >=, <=

        SELECT * from scott.emp where sal > 1500;

    C, and OR

        SELECT * from Scott.emp where Sal > and sal <= or deptno = 10;

    D, in, not in

        SELECT * from Scott.emp where Sal in (Deptno, +) and not in (10, 20)

    E, like Fuzzy escape escape

        Select * from scott.emp where ename like '%in% ';

        Select * from scott.emp where ename like '%in\%k% ';

        Select * from scott.emp where ename like '%in#%k% ' escape ' # ';

        Indicates that the # number in like is an escape character, equivalent to \

    F, is null, is not NULL

    K,    ORDER BY

        Select Sal, ename from Scott.emp order by Sal;

        Select Sal, ename from Scott.emp order by Sal ASC;

        Select Sal, ename from Scott.emp order by Sal Desc;

        Select Sal, ename from scott.emp where Sal > The order by sal Desc;

        Select Sal, Deptno, ename from Scott.emp order by sal,deptno Desc;

8. function

    A, Lower, upper, substr

        Select lower (' ABCABC ') from dual;

        Select Upper (' Abcabc ') from dual;

        SUBSTR (target, startIndex, length)

        Select substr (' Abcabc ', 1, 3) from dual;

    B, CHR, ASCII

        Converts the digital installation ASCII value to a character: select char from dual;

        Converts a character to an ASCII value: Select ASCII (' Z ') from dual;

    C, round, To_char

        Exact decimals

        Select Round (22.456) from dual;

        2 decimal places reserved: Select Round (22.456, 2) from dual;

        Precision to bits: Select Round (22.456,-1) from dual;

        Currency

        Set currency format, 0 instead of 000 front

        Select To_char (Sal, ' $000,000.00 ') from Scott.emp;

        999 will not replace the insufficient place, will only install the format output

        Select To_char (Sal, ' $999,999.99 ') from Scott.emp;

        Local currency format

        Select To_char (Sal, ' l999,999.99 ') from Scott.emp;

        Date

        HH24:MI:SS AM 15:43:20 PM

        Select To_char (sysdate, ' Yyyy-mm-dd HH:MI:SS ') from dual;

        Select To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from dual;

     D, To_date, To_number, NVL

        To_date (target, Current_format)

        Select To_date (' 2011-4-2 17:55:55 ', ' yyyy-mm-dd HH:MI:SS ') from dual;

        Select To_number (' $12,322.56 ', ' $999,999.99 ') + from dual;

        Select To_number (' $12,322.56 ', ' $00,000.00 ') + from dual;

        Select To_number (' 22.56 ') + from dual;

        NVL can convert a field's null value to a specified value

        Select Ename, Sal, NVL (comm, 1.00) from scott.emp;

9. Group function: Min, max, AVG, SUM, Count

        Select Max (SAL) from Scott.emp;

        Select min (sal) from Scott.emp;

        Select AVG (SAL) from EMP;

        Select round (AVG (SAL), 2) from EMP;

        Select To_char (avg (SAL), ' l999,999.99 ') from EMP;

        Select sum (SAL) from EMP;

        Select COUNT (comm) from EMP;

        Select COUNT (Distinct deptno) from EMP;

10. GROUP BY group

    Select Deptno, Avg (SAL) from the EMP group by DEPTNO;

    Select Deptno, Job, avg (SAL) from the EMP group by DEPTNO, job;

    Employee information in the department where the department's highest wage is obtained:

    Select Deptno, ename, Sal from EMP where Sal in (select Max (SAL) from EMP Group by DEPTNO);

11, having to filter the packet data

    The department evaluates the salary:

    SELECT * FROM (select AVG (sal) Sal, deptno from EMP Group by Deptno) where Sal > 2000;

    Select AVG (SAL) Sal, deptno from EMP Group BY DEPTNO have avg (SAL) > 2000;

12, sub-query

    Employee information with the highest salary after division

    Select Emp.ename, Emp.sal, Emp.deptno from EMP, (select Max (SAL) max_sal, deptno from EMP Group by Deptno) T where emp.sal = T.max_sal and Emp.deptno = T.deptno;

    Department average salary level

    Select S.grade, T.deptno, t.avg_sal from Scott.salgrade S, (select Deptno, avg (SAL) avg_sal from EMP Group by DEPTNO) T WH ere t.avg_sal > S.losal and T.avg_sal < s.hisal; (between)

13. Self-connected

    Select A.ename, B.ename mgr_name from EMP A, emp b where a.empno = B.mgr;

14. Connection Query

    Select Dname, ename from dept, emp where dept.deptno = Emp.deptno;

    Select Dname, ename from dept join emp on dept.deptno = Emp.deptno;

    Select Dname, ename from dept join EMP using (DEPTNO);

    Select Dname, ename from dept left join EMP on dept.deptno = Emp.deptno;

    Select Dname, ename from dept right join EMP on dept.deptno = Emp.deptno;

    Select Dname, ename from dept full join EMP on dept.deptno = Emp.deptno;

    Select A.ename, B.ename mgr_name from emp a joins EMP b on a.mgr = B.empno;

    Select A.ename, B.ename mgr_name from EMP a LEFT join EMP b on a.mgr = B.empno;

15, Rownum

    Select Rounum, Deptno, dname from dept;

    SELECT * FROM (

         Select RowNum r, dept.* from dept

    ) t where  T.R > 2;

16, tree-like structure query

    Select level, empno, ename, Mgr from EMP

    Connect by prior mgr = Empno;

17. Sorting function

    --Group by department, give the number after grouping

    Select Row_number () over (partition by Deptno order by Sal), emp.* from EMP;

    --rank sort, empty out the same part

    Select Rank () over (partition by Deptno order by Sal), emp.* from EMP;

    Select Rank () over (order by Deptno), emp.* from EMP;

    Select Rank () over (order by Sal), emp.* from EMP;

    --dense_rank sort given the same sequence number, not empty left serial number

    Select Rank () over (order by Sal), emp.* from EMP;

    Select Dense_rank () over (order by Sal), emp.* from EMP;

18, intersection, set, cut set query

    --and set: without duplicate data

    SELECT * FROM emp

    Union

    SELECT * from EMP2;

    --and set: with duplicate data

    SELECT * FROM emp

    UNION ALL

    SELECT * from EMP2;        

    -cut set, show different parts

    SELECT * FROM emp

    Minus

    SELECT * from EMP2;

19. Querying system tables, views

    Select owner, object_name, object_type, status, dba_objects.* from dba_objects where object_type = ' view ' and status = ' in Valid ';

    SELECT * from user_objects where object_type like ' PROCEDURE ';

20. Exercises

    --The department's most well-paid staff information

    Where Sal in (the Select Max (SAL) from the EMP Group by DEPTNO);

    --The department's most well-paid staff information

    On emp.deptno = T.deptno and emp.sal = t.max_sal;

    --Department average salary level

    Join (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO) T

    On t.avg_sal between Losal and Hisal;

    --Manager

    Select ename, job from EMP where empno in (select Mgr from EMP);

    --Do not use the GROUPING function to query the highest salary value

    SELECT * FROM (select Sal, ename from emp order BY sal desc) where rownum = 1;

    SELECT DISTINCT a.sal from emp a joins EMP B on A.sal > b.sal where rownum = 1;

    Select Sal from emp where Sal isn't in (select distinct a.sal from emp a joins EMP B on a.sal < b.sal);

    -department number with the highest average salary

    Select Deptno, T.avg_sal from (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO) T

    where avg_sal = (

        Select Max (avg_sal) max_sal from (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO)

    );

    Select Deptno, T.avg_sal from (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO) T

    where avg_sal = (

        Select Max (SAL) max_sal from EMP Group by Deptno

    );

    -department name with the highest average salary in the department

    Select Dname from dept where deptno = (

     Select Deptno from (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO) T

     where avg_sal = (

            Select Max (avg_sal) max_sal from (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO)

     )

    );

    Select Dname from dept where deptno = (

        Select Deptno from (select AVG (SAL) avg_sal, deptno from EMP Group by DEPTNO) T

        where avg_sal = (

               Select Max (SAL) from the EMP Group by Deptno

        )

    );

    -the department name of the department with the lowest average salary

    Select Dname from dept where deptno = (

      where avg_sal = (

        Select min (avg_sal) Min_sal from (

               Select AVG (SAL) avg_sal from EMP Group by Deptno

        )

      )

    );

    Select Dname from dept where deptno = (

        where avg_sal = (    

          Select min (avg (SAL)) Avg_sal from EMP Group by Deptno

        )

    );

    -the department name of the department with the lowest average salary level

    Select Dname from dept where deptno = (

    Select Deptno from (

         Select grade, T.deptno from Salgrade s join (

            Select AVG (SAL) avg_sal, deptno from EMP Group by Deptno

         ) T

         On t.avg_sal between S.losal and S.hisal

      )

      where grade = (

        Select min (grade) from Salgrade s join (

            Select AVG (SAL) avg_sal, deptno from EMP Group by Deptno

        ) T

        On t.avg_sal between S.losal and S.hisal

      )

    );

    --The department name of the department manager with the lowest average salary

    Select T.deptno, Dname from (

        Select Sal, Deptno from EMP where empno in (select distinct MGR from EMP)

    On t.deptno = Dept.deptno

    where Sal = (

        Select min (sal) from EMP where empno in (select distinct MGR from EMP)

    );

    --a manager's name that is higher than the average employee's salary

    SELECT * FROM (

        Select Empno, ename, Sal from EMP where empno in (select distinct MGR from EMP where Mgr are NOT NULL)

    ) T

    where T.sal > (

        Select Max (SAL) max_sal from EMP where empno not in (

         SELECT distinct MGR from EMP where Mgr are NOT NULL

        )

    );

Oracle notes three, function, select