Chapter II: Limitations of the ORACLE_SQL statement (WHERE clause) and permutation data (ORDER BY clause)

Source: Internet
Author: User

Restricting data access:

Use numbers to make conditions

Select Ename,sal,deptno from emp where deptno=10;


Use the character to do the condition, the string to single-citation, case sensitive!

Select Ename,sal,deptno from emp where ename= ' King ';

Select Ename,sal,deptno from emp where ename= ' KING ';


Use date to make conditional, format sensitive!

Select Ename,hiredate from emp where hiredate= ' 23-jan-82 ';


View system date formats with system functions:

Select Sysdate from dual;

SELECT * from Nls_database_parameters;


Using single-line comparison symbols in the Where condition

>, <, =, >=, <=,<>! =


Using logical operations in the Where condition

And, or, not


Retrieve data that meets the following criteria:

Employees with a salary greater than 2000

Employees with wages greater than 2000 and less than 3000

Employees with wages greater than 2000 or less than 1000


Special comparison characters:

Between and equivalent (>= and <=)

Select Ename,sal from emp where Sal between and 3000;


In (set list): Enumerates the data, followed by a collection of lists!

Select Ename,sal from emp where Sal in (1000,2000,3000);


Like: able to do fuzzy matching

Select ename from emp where ename like ' M% ';

Select Ename,hiredate from emp where hiredate like '%81 ';

Select ename from emp where ename like ' _l% ';

Select ename from emp where ename like ' _ _% ' escape ';


Is null (was NOT NULL): Filter NULL value

Select Ename,comm from emp where comm is null;

Select Ename,comm from EMP where comm are NOT null;


Add: Use rownum pseudo-columns in queries, RowNum is the number of result sets!

Select Rownum,ename from EMP;

SELECT * FROM EMP where rownum<6;


Arranging result Sets:

Sort result set in ascending order

Select Ename,sal from emp order by Sal;


Sort result sets in descending order

Select Ename,sal from emp order by Sal Desc;


To sort aliases

Select Ename,sal*12 ann_sal from emp order by Ann_sal;


Multi-column sorting

Select Ename,deptno,sal from emp order by DEPTNO Desc,sal desc;


Select Ename,deptno,sal from emp order by 2,3 Desc;


SELECT * FROM emp order by 5;

Exercise 2

Chapter 2nd restricting and arranging data

1. Show employee name and salary with income greater than 2850

2. View the employee name and department number of employee number 7566

3. View Blake's salary

4. Show people with income between 2000 and 5000

5. Show people with income of 1000,2000,3000,5000

6. The person who shows the bonus is not empty

7. Show the person with the manager number empty

8. Find the employee whose second letter is "L" in the name

9. Find employees with a salary greater than 1000

10. Find people with a salary greater than 1000 or work as staff

11. Find a job for an administrator or an analyst, and a person with a salary greater than 2500

12. View employee names, wages, and wages in descending order

13. View the employee name, department number, salary, the department number in ascending order, the salary in descending order

14. Do an example of sorting by alias (optional)


SELECT * from emp where job in (' MANAGER ', ' analyzest ') and sal>2500;


Chapter II: Limitations of the ORACLE_SQL statement (WHERE clause) and permutation data (ORDER BY clause)

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.