Basic syntax exercises for SQL

Source: Internet
Author: User
Tags logical operators

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

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.