Advanced Oracle database query (basic)

Advanced Oracle database query (basic)

Although the title is written in the Oralce database, SQL statements are similar. Therefore, other databases can refer to the following logic. Every programmer knows that in a project, the query function is the most complex in addition, deletion, modification, and query. Various queries, various searches, and project query conditions vary according to customer requirements. Here, I will introduce several common advanced queries. The so-called advanced query is a query other than select * from user.

In the following example, I query an employee table (EMP). The employee salary field is (SAL) and the employee number (EMPNO)

1. query the average employee salary (three methods are introduced here)

 

SELECT AVG(SAL) FROM EMP;

SELECT SUM(SAL)/COUNT(*) FROM EMP;

SELECT SUM(SAL)/COUNT(SAL) FROM EMP;

2. query the total employees' salaries.

 

 

SELECT SUM(SAL) FROM EMP;

3. query the maximum salary of an employee

 

 

SELECT MAX(SAL) FROM EMP;

4. query the minimum salary of an employee

 

 

SELECT MIN(SAL) FROM EMP;

5. query the number of records in the employee table

 

 

SELECT COUNT(*) FROM EMP;

6. query all employee numbers in the employee table (the result is the same as that in the above 5th Cases)

SELECT COUNT(EMPNO) FROM EMP;

7. query all employee numbers in the employee table (remove duplicate employee numbers. Note: because sometimes the data we find is duplicated, and we do not need this duplicate data, you can use the following DISTINCT Statement)

SELECT COUNT(DISTINCT EMPNO) FROM MEP;

8. when the grouping function is not null (NVL), when the first 1st queries average salary, if someone's salary is empty, that is, there is no wage value, there is a problem with the First and Third query methods in the three methods. It is unreasonable for him to remove the employees with null wages because the employees without wages are also employees of the company. Therefore, the null value must be included in the average salary of the company's employees. In my explanation, COUNT () is the number, while NVL (SAL, 0) means that if SAL is empty, we will take the second value, that is, 0. If it is not null, we will take the original SAL value. In this way, an employee with a blank salary will be assigned a 0 value to the employee. The result is select count (*) from emp; is equivalent. Of course, if you want to calculate the number of employees with salaries, you don't have to judge whether it is a non-empty problem.

 

 

SELECT COUNT(NVL(SAL,0)) FROM EMP;

9. the difference between WHERE and HAVING (they are both conditional queries. The only difference is that the condition in WHERE cannot be a function or a grouping function. On the contrary, HAVING can be more practical, next, let's take an example to find out the number of employees whose average salary is greater than 3000). group by is the group deptno and the employee field department number.

 

 

Select count (*) from emp where avg (SAL)> 3000 group by deptno; // This write is incorrect. If the statement fails, an error is reported. A function exists in the WHERE condition,

Select count (*) from emp group by deptno having avg (SAL)> 3000; // use HAVING to correctly execute and obtain the expected result.

9.1 In the following statement, we can get the expected results. Which of the following conditions can we use for queries? From the Perspective of SQL optimization, we should try to use the WHERE statement as much as possible, because their execution order is different, WHERE is the first filter and then grouping, so the query speed is faster, while HAVING is the opposite. It is the first grouping and then filtering, so the grouping is very huge, then filter them one by one. The WHERE statement greatly reduces the number of group records and improves SQL Execution efficiency. This is why we are familiar with WHERE condition queries, but we are not very familiar with HAVING.

 

 

Select count (*) from emp where sal> 3000 group by deptno; and select count (*) from emp group by Deton having sal> 3000; // both queries are normal.

10. sort ORDER

 

 

SELECT * from emp where 1 = 1 order by sal desc // sort BY employee salary in descending ORDER

11. nested query of grouping functions (query the maximum average salary)

 

 

SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;