Oracle BASICS (4): Single Table query

Oracle BASICS (4): Single Table query
1. Basic Query(1) Basic Statement 1. Syntax
Select [distinct] * | {Column name...} from table name [where {condition}] 2. Note 1) distinct: whether to remove duplicate data when displaying results
Indicates that all the queried field data are consistent, so that it is repeated.

SELECT distinct deptno,job,empno FROM EMP order by deptno;

The three fields (deptno, job, And empno) to be queried must be the same to be repeated.

SELECT a.deptno,a.job FROM EMP a; SELECT distinct a.deptno,a.job FROM EMP a order by a.deptno

The two fields (deptno, job) to be queried must be the same
2) deduplication:

SELECT a.deptno,a.job FROM EMP a; SELECT distinct a.deptno,a.job FROM EMP a order by a.deptno

The two fields (deptno, job) to be queried must be the same (ii) Other usage 1. arithmetic expression 1) method:
ABS (x): absolute value
Sign (x): Judge positive and negative.
Return 1 for positive values, return-for negative values, return 0
Ceil (x): rounded up.
Select ceil (3.1), ceil (0), ceil (-3.9) from dual; -- 4, 0,-3
Floor (x): rounded down.
Power (x, y): returns the power y of x.
Mod (x, y): returns the remainder of x divided by y.
Round (x [, y]): returns the rounded value.
Trunc (x [, y]): returns the value after x is truncated by y. *

select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;

Display: 5555.66, 5500, 5555
Sqrt (x): returns the square root of x. 2) instance: displays the annual salary of each employee.
Statement:

SELECT. ename, (. sal * 13 + nvl (. comm, 0) * 13) as "annual salary",. ename | '-' | (. sal * 13 + nvl (. comm, 0) * 13) as "concatenation string" from emp;

Comm: Rewards
Note:
Nvl (field, 0): process null value
As "": Add an alias
|: Concatenation string 2, where Condition 1) to_char () function:

SELECT * FROM EMP a WHERE to_char(a.hiredate,'yyyy-mm-dd') > '1981-11-17';

2) like: '%'-0 ~ Multiple; '_'-1 (3) between: the salary is between 2000 and ~ 2500 of employees

SELECT * from emp a WHERE a. sal between 2000 and 2500; -- contains itself, all in the closed range SELECT * from emp a WHERE a. sal> = 2000 and a. sal <= 2500;

4) in: employees whose number is 72.16,7521, 7654

SELECT * FROM EMP a WHERE a.empno in (7369,7521,7654);

5) is null: employees without superiors

SELECT * FROM EMP a WHERE a.mgr IS NULL;

6) logical operators: NOT, And, Or

SELECT * FROM EMP a WHERE (a.sal >500 or a.job='MANAGER') and a.ename like 'J%';

And AND or Priority: NOT> and> OR
If there is an or condition after where, or automatically separates the Left and Right query conditions.
Use () to differentiate execution sequence 7) order by: sorts the results (with results first, supports sorting aliases)
Asc-ascending (default)/desc-descending
Sort instances

SELECT * from emp a order by a. sal; -- a condition SELECT * from emp a order by a. deptno, a. hiredate desc; -- two conditions

Sort by deptno first
In the first sorting result, press the second condition.
Sort by column alias

SELECT a. ename, (a. sal * 13 + nvl (a. comm, 0) * 13) as "annual salary" from emp a order by annual salary;

This can be sorted by column numbers.

SELECT a. ename, (a. sal * 13 + nvl (a. comm, 0) * 13) as "annual salary" from emp a order by 1;

The number here cannot exceed the number of displayed columns.
Ii. Aggregate functions: max, min, avg, sum, and count
1. maximum and minimum wage: max, min 1) SQL instance

SELECT max(a.sal),min(a.sal) FROM emp a ;SELECT max(a.sal*13) , min(a.sal*13) FROM emp a ;

-- Put them together, if both max and min return a value 2) Note: max and min can only return one value.
2. display average salary and total salary: avg, sum 1) SQL instance

SELECT round(avg(a.sal),2),sum(a.sal) FROM emp a ;

Round: rounded to (2) Note: avg: calculates the average value and does not include the Count of sal as null.

SELECT avg(a.comm) FROM emp a ;SELECT sum(a.comm)/count(*) FROM emp a ;

If this parameter is set to null, you can solve this problem. 3. calculate the total number of employees: count 1) SQL instance.

SELECT count(*) FROM emp a ;SELECT count(a.empno) FROM emp a ;

2) Note:

SELECT count(a.comm) FROM emp a ;

Do not count null columns. 4. Expand Exercise 1) display the name and position of the employee with the highest salary

SELECT a.ename,a.job FROM emp a WHERE a.sal = (SELECT max(a.sal) FROM emp a);

Subquery
You can first find the highest salary
Then find out which employee has so much salary
Analysis execution sequence
SQL Execution is executed from right to left by default.
Run: SELECT max (a. sal) FROM emp a to get 5000
Run: SELECT a. ename, a. job FROM emp a WHERE a. sal = 5000; 2) display the employee information whose salary is higher than the average salary

SELECT a.ename,a.job FROM emp a WHERE a.sal > (SELECT avg(a.sal) FROM emp a);

3. group: group by and having
1. Explanation:
Group by: group statistics on query results
Having: modifies the grouping conditions and limits the Display Results of the grouping.
Aliases are not supported. 2. instances: 1) the average and maximum salaries of each department are displayed.
SQL:

SELECT avg(a.sal), max(a.sal),a.deptno FROM emp a group by a.deptno;

Analysis:
SELECT avg (a. sal), max (a. sal), a. deptno FROM emp Error
Avg (a. sal) and max (a. sal) are one result, while a. deptno has many results. 2) display the average salary and minimum wage for each job in each department
SQL:

SELECT avg(a.sal), max(a.sal),a.deptno,a.job FROM emp a group by a.deptno ,a.job order by a.deptno ；

Analysis:
Group the [Department] And then the [position.
[Sorting] is placed at the end
3) shows the average salary of a department less than 2000 of the Department number and its average salary.
SQL:

SELECT avg (a. sal) as "average wage", a. deptno FROM emp a group by a. deptno having avg (a. sal) <2000

Analysis:
Having: Restrict group display results. Aliases are not supported and can only be recalculated.
Group first to calculate the average salary of each department. Use having to delete and select the group results. 3. Summary of principles: 1) Aggregate functions (avg...) can only appear in the selection list, having, order by clause, and must not appear in the group by function or where clause. 2) If the select statement contains both group by, having, and order by statements, their order is group by, having, and order. 3) If columns, expressions, and aggregate functions exist in the selected column, one of these columns and expressions must appear in the group by clause; otherwise, an error occurs.
SELECT avg (a. sal), max (a. sal), a. deptno, a. job FROM emp a group by a. deptno, a. job;
Both deptno and job must appear in group.
The reason is that the aggregate function returns a result each time.