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

Source: Internet
Author: User
Tags dname

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

Related Article

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.