Oracle SQL Usage

Oracle
This is the SQL BASIC statement for the Oracle database,
SQL Plus execution passes the
------------------------------------------------------------------

Select Empno, To_char (Sal, ' 999,999.99 ') Sal from EMP;
SELECT DISTINCT deptno from EMP;
Select empno,ename,sal*0.5 from emp where deptno=10;
Select Empno| | ' | | ENAME,NVL (sal,0) +NVL (comm,0) from EMP;
Select Empno,ename,job,sal from emp where empno=&empno;
Select Sysdate,user,uid,rowid,rownum from EMP;
[Sysdate,user,uid,rowid,rownum to pseudo columns]
Select Empno,ename,comm from emp where comm=null;
[comm is null];
Select EMPNO,ENAME,NVL (Comm, "0") from the EMP where comm is null;
Select Deptno,dname from dept where Deptno in (30,40);
Select Deptno,dname,loc from dept where Loc isn't in (' NEW YORK ', ' CHICAGO ');
Select Deptno,ename,sal from emp where deptno=10 or deptno=20 and sal>3000;
[Column Alias]
Select E.ename employee,e.sal*1.15 new_sal from emp e where e.deptno=10;
[Multiple table joins]
Select D.dname,e.ename,e.sal,e.comm from emp e,dept D where d.deptno=e.deptno order by D.deptno;
[Using subqueries]
Select ename from the EMP where deptno= (select Deptno from dept where Dname= ' SALES ');
[Query alias]
Select E.ename,d.dname,e.deptno| | ' =='|| D.deptno from EMP E,
(select Deptno,dname from dept where loc= ' NEW YORK ') d
where E.deptno=d.deptno
Order BY D.deptno;
[Union: Union]
Select Ename,sal,comm from emp
Union
Select ' Total ', sum (SAL), SUM (comm) from the EMP order by Sal

Ename SAL COMM
---------- --------- ---------
SMITH 800
JAMES 950
ADAMS 1100
SCOTT 3000
KING 5000
Total 29025 2200
------------------------------
[Intersect: Intersecting]
Select Ename,sal,comm from emp where sal>1300
INTERSECT
Select Ename,sal,comm from EMP where comm are NOT null
===select Ename,sal,comm from EMP where sal>1300 and comm are NOT NULL

Ename SAL COMM
---------- --------- ---------
ALLEN 1600 300
TURNER 1500 0
------------------------------
[Minus]
Select Ename,sal Comm from EMP where sal>1300
Minus
Select Ename,sal Comm from EMP where sal>1500;
===select Ename,sal,comm from EMP where sal>1300 and not (sal>1500)
Ename COMM
---------- ---------
TURNER 1500
--------------------
Select To_char (sysdate, ' yyyy/mm/dd hh24:mi ') sys_date from dual;
Select To_date (' 2002/08/13 ', ' YYYY/MM/DD ') from dual;
Select To_number (' 12345 ', 99999) from dual;
Select Empno,ename from emp where Months_between (sysdate,hiredate) >=12;
Add_months (Date,number)
Last_day (date)
Months_between (DATE1,DATE2)
Next_dat (Date,day)
Round (Date,format)
Trunc (Date,format)
---------------------
numeric function
ABS (number)
Ceil (number)
COS (number)
ln (number)
MoD (n,m)
Round (Number,decimal_digits)
Sign (number)
sqrt (number)
Sin (number)
-------------------
Character function
ASCII (character)
Chr (number)
Concat (string1,string2) #| |
Initcap (String)
Length (String)
Lower (string) upper (String)
SUBSTR (String,start[,length])
Replace (string,search_string,replace_string)
-------------------
Other
Greatest (List of values)
Least (List of values)
NVL (Expression,replacement_value)
AVG (expression)
COUNT (expression)
MAX (expression)
MIN (expression)
SUM (expression)
Welcome>select Count (*), sum (SAL), avg (SAL), Max (Sal), Min (sal) from EMP;

COUNT (*) SUM (SAL) AVG (SAL) MAX (SAL) MIN (SAL)
--------- --------- --------- --------- ---------
14 29025 2073.2143 5000 800
---------------------------------------------------------------------------
[Right connection: The following figure, if the condition does not conform to, to the left main/e.deptno/, the right/d.deptno/should be empty lines also fill the left side of the content displayed]
Select D.dname,e.ename from emp e,dept D where E.DEPTNO=D.DEPTNO (+) Order by D.dname,e.ename;

1 Select d.dname d_dname,e.ename e_ename,d.deptno d_deptno,e.deptno e_deptno from emp e,dept D
2* where E.deptno=d.deptno (+) Order by D.dname,e.ename
welcome>/

D_dname e_ename D_deptno E_deptno
-------------- ---------- --------- ---------
ACCOUNTING CLARK 10 10
ACCOUNTING KING 10 10
ACCOUNTING MILLER 10 10
ADAMS 20 20
The FORD 20 20
The JONES 20 20
SCOTT 20 20
The SMITH 20 20
SALES ALLEN 30 30
SALES BLAKE 30 30
SALES JAMES 30 30
SALES MARTIN 30 30
SALES TURNER 30 30
SALES WARD 30 30
---------------------------------------------



[Left connection: The following figure, if the condition does not match, to the right of the main/d.deptno/, the left side of the/e.deptno/should be empty lines also fill the right display content]
Select D.dname d_dname,e.ename e_ename,d.deptno d_deptno,e.deptno e_deptno from emp e,dept D
where E.deptno (+) =d.deptno ORDER by D.dname,e.ename

D_dname e_ename D_deptno E_deptno
-------------- ---------- --------- ---------
ACCOUNTING CLARK 10 10
ACCOUNTING KING 10 10
ACCOUNTING MILLER 10 10
OPERATIONS 40
ADAMS 20 20
The FORD 20 20
The JONES 20 20
SCOTT 20 20
The SMITH 20 20
SALES ALLEN 30 30
SALES BLAKE 30 30
SALES JAMES 30 30
SALES MARTIN 30 30
SALES TURNER 30 30
SALES WARD 30 30
---------------------------------------------
[Self-connection: Same table table accessed by alias]
Select A.ename a_ename,b.ename b_ename,a.mgr a_mgr,b.empno b_empno
From EMP A,emp b
where A.mgr=b.empno
ORDER BY B.ename,a.ename

A_ename b_ename a_mgr B_empno
---------- ---------- --------- ---------
ALLEN BLAKE 7698 7698
JAMES BLAKE 7698 7698
MARTIN BLAKE 7698 7698
TURNER BLAKE 7698 7698
WARD BLAKE 7698 7698
MILLER CLARK 7782 7782
SMITH FORD 7902 7902
FORD JONES 7566 7566
SCOTT JONES 7566 7566
BLAKE KING 7839 7839
CLARK KING 7839 7839
JONES KING 7839 7839
ADAMS SCOTT 7788 7788
-----------------------------------------
Select E.deptno,e.ename from emp E
where exists
(SELECT ' x ' from Dept D where E.deptno=d.deptno
and d.loc= ' NEW YORK ')
Order BY E.empno;

DEPTNO ename
--------- ----------
Ten CLARK
Ten KING
Ten MILLER