Shulou(Shulou.com)06/01 Report--

Function: oracle server prior to writing a good section of the program with certain functions, built-in oracle server, for users to call

Single-line function: input a parameter, output a result, for example: upper ('baidu.com')->BAIDU.COM

Multi-line function: input multiple parameters, or internal scan multiple times, output a result, for example: count(*)->14

Count the total number of employees in emp table

select count(*) from emp;

* sign is applicable to the case where there are few fields in the table. If there are many fields, the scanning time is too long, and the efficiency is low. It is recommended to use a non-null unique field in the project, usually the primary key.

How many non-duplicate departments does the statistics company have?

select count(distinct deptno) from emp;

Count the number of employees with commission

select count(comm) from emp;

Note: These multiple row functions mentioned today do not count NULL values.

Total wages of employees, average wages, rounded to 0 decimal places

select sum(sal) "total wage",round(avg(sal),0) "average wage"from emp;

Query the highest and lowest wages in the employee table

select max(sal) "maximum wage",min(sal) "minimum wage"from emp;

Earliest employee, latest employee

select max(hiredate) "latest entry time",min(hiredate) "earliest entry time"from emp;

Multiline function: count/sum/avg/max/min

Find the average wage of the department by department, and the average wage takes an integer, using truncation

select deptno "department number",trunc(avg(sal), 0) "department average wage"from empgroup by deptno;

(Continue) Query departments with average salary greater than 2000 yuan

select deptno "department number",trunc(avg(sal), 0) "department average wage"from empgroup by deptnosaving trunc(avg(sal), 0) > 2000;

(Continued) Ranked in descending order by sector average wage

select deptno "department number",trunc(avg(sal), 0) "department average wage"from empgroup by deptnohaving trunc(avg(sal), 0) > 2000order by 2 desc;

Except for Department No. 10, query departments whose average salary is greater than 2000 yuan. Method 1 [having deptno10]

select deptno,avg(sal)from empgroup by deptnohaving deptno10;

Except for Department No. 10, query departments whose average salary is greater than 2000 yuan. Method 2 [where deptno10][Recommended]

select deptno,avg(sal)from empwhere deptno10group by deptno;

Displays the maximum value of the average departmental wage

select max(avg(sal)) "Maximum value of departmental average wage"from empgroup by deptno;

Think: Show the maximum value of the average salary in the department and the department number.

select max(avg(sal)) "Maximum average wage for department",deptno "Department number"

from emp

group by deptno;

error

Details of the group by clause:

1) All columns of non-multiline functions that appear in the select clause,[must] appear in the group by clause

2) All columns appearing in the group by clause,[may appear, may not appear] in the select clause

Difference between where and having:

where：

1) Line filter

2) For original records

3) Follow from behind

4) Where to Save

5) First implemented

having：

1) Group filters

2) For grouped records

3) Follow the group by

4) Having can save

5) Subsequent execution

Comprehensive syntax in oracle:

1) Select clause----must

I don't know what to write, so I write dual

3) where clause-----optional

4) group by clause---optional

5) having clause----optional

6) order by clause--optional, if column name, alias, expression, field appears

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.