SelectEmpno,sal fromEmpwhereSAL between +and ---get data between 1000 and 2000 in Sal from ENPSelectEname,sal fromEmpwhereSAL >= +and SAL <= ---get data between 1000 and 2000 in Sal from ENPSelectEname,sal fromEmpwhereSAl in ( +,2300);SelectEMPNO fromEMP ORDER by empno;--default SortSelectEMPNO fromEMP ORDER by EMPNO asc--AscendingSelectEMPNO fromEMP ORDER by EMPNO desc--DescendingSelectLOWER (ename) fromEMP--uppercase and lowercase conversionsSelect* fromEMP; Select* fromEMP;SelectTrim (ename) fromemp--Remove the trailing spacesSelect* fromEMP;Select* fromEmpwhereename ='KING'--from which to find a specifiedSelect* fromEMP;Selectsal,sal+ - fromemp--using the math operator "+"Selectsal,sal*Ten fromemp--using mathematical Operators "*"SelectSalTen*sal+ - fromemp--Precedence of OperatorsSelectSalTen* (sal+ -) fromemp--with well-enclosed operationsSelect* fromEMP;Selectsal+ -As Sal_o fromEMPSelect* fromEMP;Selectename | | JOB fromemp--the operation of the connector------Numeric Function-----round--RoundingSelectRound412,-1) fromdual--410SelectRound412.777,2) fromdual--412.78trunc--truncateSelectTrunc412.13,-1) fromdual--410MOD--Seeking RedundancySelectMOd ( $, One) fromEMPSelectMod (135.23, the) fromDual--Date function Months_between ()---two months of difference between datesSelectMonths_between (Sysdate,hiredate) fromEmplast_day---last day of the monthSelectLast_day (Sysdate) fromDualnext_day ()--the next date for the specified dateSelectNext_day (Sysdate,'Monday') fromDual-------------------------------------------------Select* fromemp ORDER by Sal DESCSelectSal fromemp ORDER by Sal DESC--------------------------------------------------add_months ()--add a number of months to a specified dateSelectAdd_months (Sysdate,0) fromdual;-----conversion Function----To_char--The conversion of numbersSelectTo_char (Sysdate,'yyyy') fromdual;SelectTo_char (Sysdate,'FMYYYY-MM-DD') fromdual;SelectTo_char (Sal,'l999,999,999') fromEMP;SelectTo_char (Sysdate,'D') fromdual--return to week To_number--convert the spoken character into a numberSelectTo_number (' -') +to_number (' -') fromDual--- -To_dateselect to_date ('20090210','YYYYMMDD') fromDual------General Function--NVL () functionSelectNVL (Comm,1) fromEMP-------------------------------------------------------------------Fuzzy query likeSelect* fromEMPSelect* fromEMPwhereJob like'%*_%'-----logical Operators--Select all information for employees in department 30Select* fromEMPwheredeptno= -;--The number of the employee who listed the position as (MANAGER), nameSelectEmpno,ename fromEMPwherejob='MANAGER'--find employees with bonuses above their salariesSelect* fromEMPwhereComm>Sal--find out the sum of bonuses and wages for each employeeSelectComm+sal,ename fromEMP--identify the manager in department 10 and General Staff in department 20 (clerk)Select* fromEMPwhere(deptno=Tenand job='MANAGER') or (deptno= -and job='Clerk')--Find out that department 10 is neither a manager nor a regular employee, and the employee with a salary greater than or equal to 2000Select* fromEMPwheredeptno=Tenand Job notinch('MANAGER','Clerk') and sal>= -;--identify the different jobs of employees with bonusesSelectDistinct job fromemp--distinct differentwhereComm isNotNULLand comm>0; --find employees with no bonuses or bonuses below 500Select* fromEMPwhereComm < -or comm is NULL;--Display employee information names, according to their service life, the oldest employees in the frontSelectEname fromEmporder by HireDate;
Basic syntax exercises for SQL