Oracle notes three, function, select

Source: Internet
Author: User
Tags dname joins

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.