Oracle study Note 5 --- Table query

Oracle study Note 5 --- Table query
The scott user is used below. The tables used are shown at the bottom.
1. view the table structureDesc [Table name] desc emp
2. Run the distinct command to remove duplicate lines.
3. ExerciseSelect deptno, job, sal from emp where ename = 'Smith ';
4. Function nvlIf the value is null, it is replaced by 0.

Select sal * 13 + comm * 13 "annual salary" from emp;

If comm has no value, the result will have no value. Therefore, use the nvl select sal * 13 + nvl (comm, 0) * 13 "annual salary" from emp;
5. Parallel conditions: andSelect sal, empno, ename from emp where sal> = 2000 and sal <2500;
6. like Operator% Indicates multiple arbitrary characters _ indicates the employee name and wage select ename, sal where ename like's % 'for a single arbitrary character first '; the third character is the name and salary of all employees in uppercase O select ename, sal from emp where ename like '_ O % ';
7. Use the in clause in the where clauseSelect * from emp where empno in (7844, 7998, 3222 );
8. logical operatorsThe salary is higher than 500 or the position is MANAGER, and the first letter of the name is uppercase J select * from emp where (sal> 500 or job = 'manager') and ename like 'J % ';
9. order by [field] [Rule: asc, desc]Select * from emp order by sal; // asc default select * from emp order by sal desc; // select * from emp order by deptno, sal desc in descending order of employee salaries from high to low according to department numbers;10. sort as by column alias Select ename, (sal + nvl (comm, 0) * 13 as "annual salary" from emp order by "annual salary"; select ename, (sal + nvl (comm, 0 )) * 13 as nianxin from emp order by nianxin; select ename, (sal + nvl (comm, 0) * 13 "annual salary" from emp order by "annual salary"; select ename, (sal + nvl (comm, 0) * 13 nianxin from emp order by nianxin; as can be omitted. Double quotation marks are required for Chinese aliases.
Note:: The alias cannot be added to the table as. For details, see the following 19.
------ 8 <strong 8 <strong 8 <--------------- 8 <--------------- 8 <--------------- 8 <--------- complex query ------ 8 <strong 8 <strong 8 <--------------- 8 <--------------- 8 <strong 8 <---------
11. Data groupingMax min sum count avg shows the highest wage and minimum wage of all employees select max (sal), min (sal) from emp; displays the name of the highest wage employee and its wage select ename, sal from emp where sal = (select max (sal) from emp );Note:This write is wrong: select ename, sal from emp where sal = max (sal); ORA-00934: the grouping function is not allowed to show select * from emp where sal> (select avg (sal) from emp) for employees with higher average salaries ); show average and total salaries of all employees Calculate total number of employees
12. groupSelect avg (sal), max (sal), deptno from emp group by deptno; select avg (sal), max (sal ), deptno from emp group by deptno, job;
13. Screening havingGroup select avg (sal), max (sal), deptno from emp where group by deptno

Select avg (sal), max (sal), deptno from emp where group by deptno having avg (sal)> 2000 After grouping;
14. Data grouping Summary14.1 The grouping function can only appear in the selection list, having, and order by clauses. 14.2 If the select statement contains group by, having, order by then their order is from left to right group by, having, order
-------- 8 <strong 8 <strong 8 <------------------- 8 <--------------- 8 <--------- multi-Table query -------- 8 <strong 8 <strong 8 <------------------- 8 <--------------- 8 <---------
15. Cartesian set multi-Table queryThe multi-Table query condition is at least not less than the number of tables-1 displays the employee name, salary, and department name select. ename,. sal, B. dname from emp a, dept B where. deptno = B. deptno; here two tables dept and emp, one condition emp. deptno = dept. deptno; display employee name, salary, Department name, sort by department select. deptno,. ename,. sal, B. dname from emp a, dept B where. deptno = B. deptno order by. deptno16. Self-connectionSelect a. ename "", B. ename BOSS from emp a, emp B where a. mgr = B. empno and a. ename = 'Ford'


17. subquery 17.1 single row subquery '=' equal to No. Question: Search for employees in the same department as SMITH
Find the Department Number of SMITH (only one row of data is returned) select deptno from emp where ename = 'Smith '; display select * from emp where deptno=(Select deptno from emp where ename = 'Smith ');
17.2 Single Column multi-row subquery in, all, anyQuestion: Find the names of employees who have the same job as department 10 and find all positions in department 10 (multiple rows of data) select distinct job from emp where deptno = 10; show select * from emp where jobIn(Select distinct job from emp where deptno = 10)
Display the name of the employee whose salary is higher than the salary of all employees in the department 30 and the Department number select ename, sal, deptno from emp where sal>All(Select sal from emp where deptno = 30 );
According to the execution sequence of oracle, the following single-row subquery is more efficient: select ename, sal, deptno from emp where sal> (Select max (sal) from emp where deptno = 30 );
Display the name of the employee whose salary is higher than the salary of any employee in the department 30 and the Department number select ename, sal, deptno from emp where sal>Any(Select sal from emp where deptno = 30 );
The following single-row subquery is more efficient: select ename, sal, deptno from emp where sal> (Select min (sal) from emp where deptno = 30 );
18. Multiple rows and multiple columnsSelect * from emp where (job, deptno) = (select job, deptno from emp where ename = 'Smith ');
19. ExerciseDisplays the names, salaries, and department numbers of employees whose salaries are higher than the average salaries of each department. select a2.ename, a2.sal, a2.deptno, a1.mysal from emp a2, (select deptno, avg (sal) mysal from emp group by deptno) a1 where a2.deptno = a1.deptno and a2.sal> a1.mysal;
The table a1 obtained from the face query is calledEmbedded View Aliases must be specified in the from subquery.This parameter is specified as a1. In addition, the as cannot be added when an alias is used for the table.
20. querying by PAGEA total of three types of data are divided by rownum, which is efficient and readable.
Select a1. *, rownum rn from (select * from emp) a1; all columns in a1. * a1 table
Select a1. *, rownum rn from (select * from emp) a1 where rownum <8 select * from (select a1. *, rownum rn from (select * from emp) a1 where rownum <8) a2 where rn> 5; here, rn cannot be changed to rownum, nor rownum of the subquery can be changed to rn 20.2. ROWID-based score is the most efficient, however, the readability is less than 20.3, and the analysis function is used as the score, resulting in poor efficiency,
21. Create a new table using the query resultsCreate table mytable (id, name, sal, job, deptno) as select empno, ename, sal, job, deptno from emp; the table will be created and the data can be copied.
22. Merge QueryTo merge the results of multiple select statements, you can use the set operators: union, union all, intersect, minus 22.1, and union [union set] to remove the repeated records select ename, sal, job from emp where job = 'manager' union select ename, sal, job from emp where sal> 2500; 22.2 union all [union set] will not remove duplicate records 22.3, intersect [intersection] 22.4, minus [difference set]
23. Create a new Database 23.1. Use the wizard tool dbca provided by oracle: Database Configuration Assistant.

Emp table
Dept table