Oracle SQL Training Tutorial: Learn Oracle SQL and Web Rapid Application development in practice-book notes

Different character sets cause problem resolution of date type display

Alter session set nls_date_language= ' AMERICAN ';

Unlock Scott Account

Alter user Scott identified by Tiger account unlock;

Distinct to remove duplicate rows

The format of the basic query statement

SELECT *| {[DISTINCT] list, ...}

from table name;

List: Column name | expression [Alias]

SELECT * from Tab;
SELECT * from EMP;
SELECT empno,ename,sal
from emp
WHERE sal >=;
SELECT empno,ename,sal
from emp
WHERE Sal between and 2900;
SELECT hiredate from EMP;
SELECT empno,ename,sal, HireDate
from emp
WHERE hiredate between ' January-January -81 ' and ' 3 January-May -82 ';
SELECT empno,ename,sal, HireDate
from emp
WHERE hiredate not between ' January-January -81 ' and ' 3 January-May -82 ';
SELECT empno,ename,job,sal
from emp
WHERE job = ' salesman ';
SELECT empno,ename,sal,job
from emp
WHERE job in (' salesman ', ' Clerk ', ' MANAGER ');
SELECT empno,ename,sal,job
from emp
WHERE job isn't in (' ANALYST ', ' president ');
The LIKE operator can use the following two wildcard characters "%" and "_" Where:
"%" for 0 or more characters
"_" represents one and can only be one character
SELECT empno,ename,sal,job
from EMP
WHERE job like ' sal% ';
SELECT empno,ename,sal,job
from the EMP
WHERE job like ' s_l_s% ';
SELECT empno,ename,sal,hiredate
from emp
WHERE hiredate like '%81 ';

SELECT * from Tab;
SELECT * from Dept_temp;
CREATE TABLE dept_temp as SELECT * from dept;
INSERT into Dept_temp VALUES (n, ' It_presearch ', ' Beijing ');
SELECT * from Dept_temp WHERE dname like ' it\_% ' escape ' \ ';
SELECT * from Dept_temp WHERE dname like ' it~_% ' escape ' ~ ';
The ORDER by clause must be the last clause of the SQL statement
SELECT empno,ename,sal from emp WHERE sal >= the ORDER by Sal; [ASC] Ascending default
SELECT empno,ename,sal from emp WHERE sal >= the ORDER by Sal DESC;
Select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"
From EMP ORDER by "annual Salary" DESC;
Select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"
From EMP ORDER by (500+sal) *12 DESC;
Select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"
From EMP ORDER by 3 DESC;
SELECT ename,job,sal from emp ORDER by Job,sal DESC;
SELECT ename,job,sal from emp ORDER by Empno;

Modify Default display width

SET line[size] {80|n}

SET Line 100

L display the SQL statement in the buffer you just entered

N Text modified section of error

'/' (run) command

SAVE

GET

@ or Start command when the specified script file contents are loaded into the SQL buffer to run

Spool command

SELECT * from EMP;
SELECT * from Tab;
DESC EMP;
DESC Dept;

This article from "ZQSLZR" blog, declined reprint!

Oracle SQL Training Tutorial: Learn Oracle SQL and Web Rapid Application development in practice-book notes