Oracle Table query

1. process null values

To process the null value, you can use the nvl function. If comm (Comm, 0) is null, use 0 instead. Otherwise, it is Comm.

Example: displays the annual salary of each employee

Select Sal * 12 + nvl (Comm, 0) * 12 "annual salary", ename, comm from EMP;

 

2. connection string |

Example: Select ename | 'is a' | job from EMP;

 

3. Use the like Operator

%: 0 to multiple characters. _: Represents any single character.

For example, the employee name and salary with the first character s are displayed.

Select ename, Sal from EMP where ename like's % ';

For example, the name and salary of all employees whose third character is uppercase o are displayed.

Select ename, Sal from EMP where ename like '_ o ';

 

4. Use in the WHERE clause

For example, how to display 7788 of employees who have empno.

Select * from EMP where empno in (7844,783 9, 7788 );

 

5. sort by order

For example, information about an employee is displayed in ascending order of salary.

Select * from EMP order by Sal;

For example, employees are output in descending order of their salaries based on their department numbers.

Select * from EMP order by deptno, Sal DESC;

 

6. Data grouping: Max, Min, AVG, sum, and count

For example, the maximum wage and minimum wage of all employees are displayed.

Select max (SAL), min (SAL) from EMP;

Example: displays the employees with the highest salary.

Incorrect syntax: Select ename, Sal from EMP where sal = max (SAL );

Correct syntax: Select ename, Sal from EMP where sal = (select max (SAL) from EMP );

Easy to error: Select ename, max (SAL) from EMP; an error is reported when this statement is executed, indicating that the function is not a single component group function. Because Max is a grouping function, while ename is not a grouping function, select Min (SAL), max (SAL) from EMP; this statement is correct, because Min and Max are both grouping functions.

Therefore, if the column contains a grouping function, each column must be a grouping function; otherwise, an error occurs.

 

7. Group by and having clauses

(1). Group by: used for grouping statistics on query results.

(2). Having clause: used to restrict group display results.

For example, how do I display the average and maximum salaries of each department?

Select AVG (SAL), max (SAL), deptno from EMP group by deptno;

For example, the average salary and minimum wage for each job in each department are displayed.

Select AVG (SAL), max (SAL), deptno from EMP group by deptno, job;

For example, the average salary is less than 2000 of the Department number and its average salary.

Select deptno, AVG (SAL) from EMP group by deptno having AVG (SAL) <2000;

(3) Summary of data groups:

A. The grouping function can only appear in the selection column, having, and order by clauses.

B. If select statements contain group by, having, and order by clauses, the order is group by, having, and order.

C. If there are columns, expressions, and grouping functions in the selection column, one of these columns and expressions must appear in the group by clause; otherwise, an error occurs.

 

8. Multi-Table query

(1) multi-row subquery: A subquery that returns multiple rows of data.

Use the all operator in multi-row subqueries

For example, how do I display the name, salary, and department number of an employee whose salary is higher than that of all employees in the department 30?

Select ename, Sal, deptno from EMP where SAL> All (select Sal from EMP where deptno = 30 );

Use the any operator in multi-row subqueries

For example, how do I display the name, salary, and department number of an employee whose salary is higher than that of any employee of Department 30?

Select ename, Sal, deptno from EMP where SAL> Any (select Sal from EMP where deptno = 30 );
(2) Multi-column subquery: A subquery statement that returns multiple columns.

For example, how to query all employees who have the same department and position as Smith.

Select * from EMP where (job, deptno) = (select job, deptno from EMP where ename = 'Smith ');