Oracle 051 for several topics (Oracle 11g)

Oracle 051 for several topics (Oracle 11g)

1. Usage of interval

SELECT INTERVAL ' month,interval ' 54-2 ' year to Month,interval ' 11:12:10.1234567 ' HOUR-SECOND from dual;

The query results are:

INTERVAL ' ' MONTH --------------------------------------------------------------------------- INTERVAL ' 54-2 ' yeartomonth --------------------------------------------------------------------------- INTERVAL ' 11:12:10.1234567 ' Hourtosecond --------------------------------------------------------------------------- +25-00 +54-02 +00 11:12:10.123457

2.

Select DISTINCT Deptno, Sal from emp Order by 1;

Equivalent to

Select DISTINCT Deptno, Sal from emp order by Deptno;

Select DISTINCT Deptno, Sal from emp order by 2;

Equivalent to

Select DISTINCT Deptno, Sal from emp order by Sal;

3. Using Data merge in the SELECT clause

The EMP table is as follows

EMPNO	Ename	JOB	MGR	HireDate	SAL	COMM
7369	SMITH	Clerk	7902	17-dec-80	800
7499	ALLEN	Salesman	7698	20-feb-81	1600	300
7521	WARD	Salesman	7698	22-feb-81	1250	500
7566	JONES	MANAGER	7839	02-apr-81	2975
7654	MARTIN	Salesman	7698	28-sep-81	1250	1400
7698	BLAKE	MANAGER	7839	01-may-81	2850
7782	CLARK	MANAGER	7839	09-jun-81	2450
7788	SCOTT	ANALYST	7566	19-apr-87	3000
7839	KING	President		17-nov-81	5000

The query statements are as follows:

Select Empno | | "| | To_char (ROUND (ROUND (sysdate-hiredate)/365) * sal + comm) from EMP;

The query results are:

empno| | ' | | To_char (ROUND (ROUND (sysdate-hiredate)/365) *sal+comm) -------------------------------------------------------------------------------- 7369 7499 56300 7521 44250 7566 7654 43900 7698 7782 7788 7839 7844 51000 7876 7900 7902 7934

The query results have only one column, which is the concatenation of the value of empno with the subsequent arithmetic value.

Description

a) using "| |" for data merging in select

b) Query result error. Rows with an empty value of COMM no calculation results

4. Q-quote delimiter

Query statements

Select Q ' [I am ' OK ' ey '] ' from dual;

Output

I am ' OK ' ey

The following symbols are possible

Select Q ' <i am ' OK ' ey > ' from dual; Select Q ' \i am ' OK ' ey ' from dual; Select Q ' {I am ' OK ' ey '} ' from dual; Select Q ' #i am ' OK "ey" # ' from dual; Select Q ' *i am ' OK ' ey "* ' from dual; Select Q ' (I am ' OK ' "ey") ' from dual; Select Q '%i am ' OK ' ey "% ' from dual;

5. USING

Select * from EMP Join dept using (DEPTNO);

The query results are:

DEPTNO EMPNO ename JOB MGR hiredate SAL COMM dname LOC ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- -------------- ------------- 7782 CLARK MANAGER 7839 09-jun-81 2450 ACCOUNTING NEW YORK Ten 7839 KING President 17-nov-81 ACCOUNTING NEW YORK 7934 MILLER Clerk 7782 23-jan-82 1300 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 02-apr-81 2975 the DALLAS 7902 FORD ANALYST 7566 03-dec-81-DALLAS 7876 ADAMS Clerk 7788 23-may-87 1100-DALLAS 7369 SMITH Clerk 7902 17-dec-80-DALLAS 7788 SCOTT ANALYST 7566 19-apr-87-DALLAS 7521 WARD salesman 7698 22-feb-81 1250 SALES CHICAGO 7844 TURNER salesman 7698 08-sep-81 0 SALES CHICAGO 7499 ALLEN salesman 7698 20-feb-81, SALES CHICAGO 7900 JAMES Clerk 7698 03-dec-81 950 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-may-81 2850 SALES CHICAGO 7654 MARTIN salesman 7698 28-sep-81 1250 1400 SALES CHICAGO

Causes Oracle to use the field specified by the using as a connection instead of the default of two in the natural join connection.

Reference http://blog.163.com/wkyuyang_001/blog/static/108021228200917598857/

Sugeei

2015/8/28

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle 051 for several topics (Oracle 11g)