This is the SQL basic statement for the Oracle database, SQL Plus executes --------------------------------- -------------------------------
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 For pseudo columns] Select Empno, Ename, Comm arrom Emp where comm = NULL; [Comm is null]; SELECT EMPNO, ENAME, NVL (COMM, "0") from Emp WHERE COMM IS NULL; SELECT Deptno, DName from DePt Where Deptno in (30, 40); SELECT Deptno, DName, Loc from Dept Where Loc Not in ('New York', 'Chicago'); Select Deptno, Ename, Sal from Emp where deptno = 10 or deptno = 20 and SAL> 3000; [Total] Select E.ename Employee, E.SAL * 1.15 New_SAL from Emp E WHERE E.DEPTNO = 10; [Multi-Table Connection] Select D. DName, E.ename, E.SAL, E.COMM from EMP E, Dept D Where D.deptno = E.DEPTNO Order by D.Deptno; [User Query] Select Ename from 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 Deptn o, DNAME from Dept Where Loc = 'New York') dwhere e.deptno = D.deptnoorder by D.deptno; [Union: United] Select Ename, Sal, Comm From EmpunionSelect 'Total', SUM (SAL), SUM Comm) from Emp Order by Sal
ENAME SAL COMM ---------- ----------------- Smith 800james 950adams 1100scott 3000king 5000Total 29025 2200 ----------- ------------------- [INTERSECT: intersection] SELECT ENAME, SAL, Comm From Emp Where Sal> 1300INTERSECTSELECT ENAME, SAL, Comm From Emp Where Comm is not null == = SELECT ENAME, SAL, COMM from Emp Where Sal> 1300 and common is not null
ENAME SAL COMM ---------- ----------------- Allen 1600 300Turner 1500 0 -------------- ---------------- [Minus] Select Ename, Sal Comm from Emp Where Sal> 1300minusselect 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) -------------------- Numeric Function ABS (Number) CEIL (Number) LN (Number) Mod (N, M) Round (Number, Decimal_Digits) SIGN (Number) SIN (Number) ----------------- Character Function ASCII (Character) CHR (Number) CONCAT (STRING1, STRING2) # || INTCAP (String) Length (String) Upper (String) Substr (String, Start [, Length]) Replace (String, Search_String, Replace_String) ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SUM (Expression) Welcome> SELECT Count (*), SUM (SAL), AVG (SAL), Max (SAL), MIN (SAL) from EMP
Count (*) SUM (SAL) AVG (SAL) MAX (SAL) --------------------------- ------ --------- 14 29025 2073.2143 5000 800 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------- [Right connection: as follows Figure, if the condition does not match, the left is main /e.deptno/, the right /d.deptno/ should also fill the left displayed on the left display] 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.Enamewelcome> /
D_DNAME E_ENAME D_DEPTNO E_DEPTNO -------------- ---------- ----------------- Accounting Clark 10 10accounting King 10 10Accounting Miller 10 10RESEARCH ADAMS 20 20RESEARCH FORD 20 20RESECH Jones 20 20RESECH Scott 20 20RESECH SMITH 20Sales Allen 30 30Sales Blake 30 30sales James 30Sales Martin 30 30sales Turner 30 30Sales Ward 30 30 ------------ ----------------------------------
[Left connection: As shown below, if the condition does not match, the left /e.deptno/, the left /e.deptno/ should be fill the right display in the right side] Select D.DName D_DName, E. ENAME E_ENAME, D.DEPTNO D_DEPTNO, E.DEPTNO E_DEPTNO from EMP E, DEPT DWHERE E.DEPTNO ( ) = D.Deptno Order by D. DName, E.ename
D_DNAME E_ENAME D_DEPTNO E_DEPTNO -------------- ---------- ----------------- Accounting Clark 10 10accounting KING 10 10ACCOUNTING MILLER 10 10OPERATIONS 40RESEARCH ADAMS 20 20RESEARCH FORD 20 20RESEARCH JONES 20 20RESEARCH SCOTT 20 20RESEARCH SMITH 20 20SALES ALLEN 30 30SALES BLAKE 30 30SALES JAMES 30 30SALES MARTIN 30 30SALES TURNER 30 30SALES WARD 30 30 ---------- ----------------------------------- [Self-connected: The same table is accessed according to the alias] Select A. ENAME A_ENAME, B.ENAME B_ENAME, A.MGR A_MGR, B.Empno B_empnofrom Emp A, EMP bwhere a.mgr = B.empnoorder by B.ename, a.ename
A_ENAME B_ENAME A_MGR B_EMPNO ---------- -------------------------- Allen Blake 7698 7698Martin Blake 7698 7698martin Blake 7698 7698TURNER BLAKE 7698 7698WARD BLAKE 7698 7698MILLER CLARK 7782 7782SMITH FORD 7902 7902FORD JONES 7566 7566SCOTT JONES 7566 7566BLAKE KING 7839 7839CLARK KING 7839 7839JONES KING 7839 7839ADAMS SCOTT 7788 7788 ------------------ ----------------------- Select E.DEPTNO, E.Ename from Emp E WHERE EXISTS (SELECT '×' from dept d where e.deptno = d .deptnoand d.loc = 'new york') Order by E.empno; DePtno ENAME ------------------ 10 Clark 10 King 10 Miller
