Advanced oracle query and advanced Query

Advanced oracle query and advanced Query

1. eliminate duplicate rows
If several rows are the same in the Oracle query results, you can use DISTINCT to remove duplicate rows.

Code Demonstration: Remove duplicate rows in DISTINCT

SQL> SELECT DISTINCT DEPTNO FROM EMP;

2. NULL operation
If a record contains a missing data value, it is a NULL value ). A null value is not equal to 0 or a space. A null value is a value that is not assigned a value, unknown, or unavailable.
Any data type column can contain NULL values, unless the column is defined as non-empty or primary key.
In the query condition, the NULL value is null as the condition, and the non-NULL value is not null as the condition.

Code Demo: Query employees who do not receive bonuses in the EMP table

SQL> SELECT ENAME, JOB, SAL, COMM FROM EMP 2 WHERE SAL <2000 AND COMM IS NULL;

3. IN operation
IN the Where clause, you can use the IN operator to query the rows IN the specified list of column values. There is not in corresponding to the IN operation. The usage is the same, and the result is the opposite.

Code Demonstration: IN Operation

SQL> SELECT ENAME, JOB, SAL FROM EMP 2 WHERE job IN ('salesman', 'President ', 'analyst ');

4.... AND...

In the WHERE clause, you can use the BETWEEN operator to query rows whose column values are included in the specified range. The range specified by the BWTWEEN operation also includes the boundary.

Code Demonstration: BETWEEN Operation

SQL> SELECT ename, job, sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;