Oracle Study Notes (7) -- Advanced query (1)
Before learning advanced queries, let's take a look at how to view all the tables in the Oracle database. This is because we need to use several tables under the SCOTT user in the Oracle database (these tables are included in the Oracle database ).
Grouping functions: grouping Functions Act on a group of data and return a value for a group of data. Common grouping functions: AVG, SUM, MIN, MAX, COUNT, WM_CONCAT (Row-to-column conversion) Syntax: grouping functions use AVG (average) and SUM (total) function 1. Calculate the average salary and total salary of an employee select avg (sal), sum (sal) from emp; MIN (minimum) and MAX (maximum) function 2: Find the maximum and minimum values of employee salaries select max (sal), min (sal) from emp; COUNT (count) function 3. Find the total number of employees select COUNT (*) from emp; select count (empno) from emp; DISTINCE (distinct) KEYWORDS (DISTINCT is used to remove duplicate records) 4. Find the department count select count (deptno) from emp; select deptno from emp; select count (distinct deptno) from emp;
WM_CONCAT: Example of Row-to-column conversion: select deptno, wm_concat (ename) from emp group by deptno;
The style shown above is not very nice. We first use the host cls command to clear the screen, and then execute the following command to format the employee's name in the displayed format set linesize 200 col for a60 select deptno Department number, name of an employee in the wm_concat (ename) department from emp group by deptno; Grouping function and null value 1. count the average salary of an employee select sum (sal)/count (*) 1, sum (sal)/count (sal) Two, avg (sal) three from emp;
2. Calculate the average employee bonus select sum (comm)/count (*) 1, sum (comm)/count (comm) 2, avg (comm) 3 from emp; select count (*), count (comm) from emp;
Summary: The grouping function automatically ignores NULL values and only counts the number of non-null values. Use the NVL function in the grouping function. Note: The NVL function cannot ignore null values using the grouping function select count (*), count (nvl (comm, 0) from emp; use the group by clause to GROUP data (group by can be applied to a column or multiple columns) the group by clause uses a single column grouping example: calculate the average salary of each department, the requirement is: department number, the average salary of the department select deptno, avg (sal) from emp group by deptno;
Abstract: Oracle syntax requirements select a, group function (x) from table group by a; select a, B, c, group function (x) from table group by, b, c;
Note: All columns not included in GROUP functions in the SELECT list should be included in the group by clause. The columns contained in the group by clause do not need to be included in the SELECT list. For example: calculate the average salary of each department. The requirement is as follows: select avg (sal) from emp group by deptno; example of grouping multiple columns: calculate the total salary of employees by department or position. select deptno, job, sum (sal) from emp group by deptno, job; select deptno, job, sum (sal) from emp group by deptno, job order by deptno; Invalid group function: Modify: Use the HAVING clause to filter out the departments whose average salary is greater than 2000 in the grouping result set, requirements: department no., average salary select deptno, avg (sal) from emp group by deptno having avg (sal)> 2000
The difference between where and having is the same: Both filter result sets are different: you cannot use group functions in the WHERE clause (note ). You can use group functions in the HAVING clause. Where and having: select deptno, avg (sal) from emp group by deptno having deptno = 10; select deptno, avg (sal) from emp where deptno = 10 group by deptno; having grouping first and then filtering where first and then grouping where greatly reduce the number of group records, thus improving efficiency Note: The where clause cannot use group functions to use order by clauses in grouping queries. Example: calculate the average salary of each department. The requirement is shown as follows: department number, average salary of the Department, and select deptno, avg (sal) in ascending order of salary) from emp group by deptno order by avg (sal); select deptno, avg (sal) average salary from emp group by deptno order by average salary; select deptno, avg (sal) average salary from emp group by deptno order by 2; can be sorted by column, alias, expression, serial number
Error Demo:
Nested examples of grouping functions: calculate the maximum average salary of a department 1. Use the AVG function to find the average salary of each department 2. Use the nested MAX function to find the maximum average salary of a department select max (avg (sal )) from emp group by deptno; the enhanced break on deptno 2 of the group by statement is displayed only once, skip two rows of set pagesize 30 between different department numbers and 30 records are displayed on each page.
SQL * Plus reports include the title, page number, and alias.
