Oracle BASICS (4): Single Table query and oracle basic Single Table query

Oracle BASICS (4): Single Table query and oracle basic Single Table query

1. Basic Query(1) Basic Statement 1. Syntax

• Select [distinct] * | {Column name...} from table name [where {condition }]

2. Note 1) distinct: Indicates whether duplicate data is excluded when the result is displayed.

• 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

(2) 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
  • |: Concatenated 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 (results are obtained first, and aliases can be sorted)

• 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, min1) 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: Only one value can be returned for max and min.
2. display average salary and total salary: avg, sum1) SQL instance

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

• Round: Rounding

2) Note: avg: calculate the average value and does not include the statistics of sal being null.

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

• If it is null, this can be solved.

3. calculate the total number of employees: count1) 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 information of employees whose salaries are higher than average

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.
  • Alias not supported

2. Example: 1) display the average and maximum salaries of each department

• 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.