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)