Oracle's SQL query

------------------------1. Simple SQL query--------------------------
SELECT * from EMP;
Select Empno,ename,job from EMP;
Select ' number is: ' | | Empno | | ' Name is: ' | | Ename from EMP;
Select distinct empno, ename from EMP;
Select empno,ename,job,sal*100 Income from EMP;

------------------------2. Limit query --------------------------
SELECT * FROM EMP where sal>2000;
SELECT * from emp where not (sal>2000);
SELECT * from EMP where Mgr are NOT null;
SELECT * from emp where Sal between 0 and 1000;
SELECT * from emp where Sal in (800,900);
SELECT * from emp where Sal isn't in (800,900);
SELECT * from emp where Sal >1000;
SELECT * from emp where Sal <>100;
SELECT * from emp where ename like ' sco% ';

------------------------3. Sort ------------------------------
SELECT * from emp order by Sal asc;--Ascending
SELECT * from emp order by Sal Desc,hiredate asc;--in descending order by Sal, if Sal is the same, schedule hiredate for the morning and evening sort

------------------------4. Single-line function ------------------------------
--1.> character functions: special handling of characters

--Uppercase and lowercase conversions
SELECT * from emp where Ename=upper (' Allen ');
Select Lower (ename) from EMP;
--Capitalize the first letter.
Select Empno,initcap (ename) from EMP;
Select Initcap (' Hello,world ') from dual;
--string interception
Select substr (ename,1,2) from emp;--intercept the first two bits
Select substr (ename,0,2) from EMP--0/1 start all the same
Select substr (Ename,length (ename)-2) from emp;--character last two bits
--Character length
Select Length (ename) from EMP;
--Character substitution
Select Replace (ename, ' S ', ' 5 ') from EMP;

--2.> Numeric functions

--Rounding
Select Round (9.536) from dual;--10
Select Round (9.536,2) from dual;--9.54

--Truncation of the decimal point, direct interception, not rounding
Select Trunc (9.536) from dual;--9
Select Trunc (9.536,2) from dual;--9.53

--Take surplus
Select mod (10,3) from dual;--1

---3> Date function
Select Sysdate from dual;
Select sysdate-1 from dual;
SELECT * from EMP;
--Inquiry into the number of weeks of 10 jobs
Select Empno,ename,round ((sysdate-hiredate)/7) week from EMP;
---3.1 months_between () to find a number of months between two dates.
Select Round (Months_between (sysdate,hiredate)) from EMP;

---3.2 add_months () on the specified date, plus the number of months
Select Add_months (sysdate,1) from dual;

---3.3 next_day () What's the date of the next day?
Select Next_day (sysdate, ' Saturday ') from dual;--start today, find a Saturday

---3.4 last_day () to find the last day of a given date
Select Last_day (sysdate) from dual;--the last day of the current month

---4>. Conversion functions

--4.1 To_char Conversion characters
Select Empno,ename,to_char (hiredate, ' yyyy-mm-dd ') d from emp;--2016-06-18
Select Empno,ename,to_char (hiredate, ' fmyyyy-mm-dd ') d from Emp;--2016-6-18,fm minus 0 days before date;
Select Empno,ename,to_char (Sal, ' 999,90.00 ') from emp;--to numeric formatting
Select Empno,ename,to_char (Sal, ' $999,90.00 ') from EMP;
Select Empno,ename,to_char (Sal, ' l999,90.00 ') from emp;--to numeric formatting, l, local currency
--4.2 To_number Conversion Value
Select To_number (' 123 ') + to_number (' 123 ') from dual;--246
--4.3 to_date Conversion Date
Select To_date (' 2016-06-18 ', ' yyyy-mm-dd ') d from dual;

---5>. General functions
Select ENAME,NVL (comm*2,0) from emp;--changes the null value to the specified content (0).
Select Decode (3,1, ' AA ', 2, ' BB ', 3, ' cc ') from DUAL;--CC similar if: ElseIf. Else
Select empno number, ename name, decode (Job, ' Clerk, ', ' salesman ', ' salesman ', ' Sales person ', ' analyst ', ' analysts ', ' manager ', ' manager ', ' president ', ' President ') position from EMP;

Oracle's SQL query