The path to oracle for a cainiao -------- II (continued)

A cainiao's oracle path -------- II (continued) case 16 which employees do not receive 400,500,100 yuan in bonuses and are not empty. [SQL] select ename, bonus from emp_xxx where bonus not in () or bonus is null; // when not in is used for comparison, the set cannot contain null values, if it contains //, no rows selected // displays the last two characters of each employee. [SQL] select substr (ename, 2, 2) from emp_xxx; alter session set nls_date_format = 'yyyy _ mm_dd hh24: mi: ss'; Case 17. the case statement calculates the data after the salary increase based on the employee's position. If the job is Analist, the salary increase is 10%. if the job is Programmer, the salary increase is 5%. if the job is clerk, the salary increase is 2%. Other jobs do not change. [SQL] select ename, job, case job when 'analyst' then salary * 1.1 when' Programmer 'then salary * 1.05 when 'cler' then salary * 1.02 else salary end new_salary from emp_xxx; select ename, salary, case salary when <5000 then salary * 1.2 else salary end new_salary from emp_xxx; // There is a problem here. [SQL] select ename, salary, case salary when 10000 then salary * 9 else salary end new_salary from emp_xxx; case 18 decode function decode () A function is a function of oracle statements with a medium price of case when. The decode function syntax is the same as that of case. It must be the same as the case statement [SQL] select ename, decode (job, 'analyst', salary * 1.1, 'programmer ', salary * 1.05, 'cler', salary * 1.02, salary) new_salary from emp_xxx; Case 19: The nested salary of the function is sorted from low to high. [SQL] select ename, salary from emp_xxx order by salary; you can also write this statement: select ename, salary from emp_xxx order by salary asc; case 20 salary sorted from high to low [SQL] select ename, nvl (salary, 100000) from emp_xxx order by salary desc; case 21 is sorted by entry time, And the entry time is earlier than the previous [SQL] Select ename, hiredate from emp_xxx order by hiredate; Case 22 is sorted by department, and the same department is sorted by salary from high to low [SQL] select ename, deptno, salary from emp_xxx order by deptno, salary desc; Case 23: number of records in the employee table [SQL] select count (*) from emp_xxx; data Dictionary user_tables case 24 number of tables under the current user [SQL] select count (*) from user_tables; case 25 how many tables under the openlab account that contain emp [SQL] select count (*) from user_tables where table_name like '% emp %'; Case 26 the total number of data whose entry time is not null [SQL] select count (hiredate) from emp_xxx; sqlplus command to view the current user account show user group function count () arv () sum () max () min () unlike the single-row functions round () and to_date () to_char () coalesce (), the single-row function returns a result for each function, the group function is multi-row data. Return a result. Case 27. Calculate the total salary of an employee. [SQL] select sum (salary) from emp_xxx; case 28 calculate the average salary of an employee [SQL] select avg (salary) from emp_xxx; // note, when the salary is null, the calculation does not take part in the average salary calculation and calculates the total number of employees, total salary, and average salary. [SQL] select count (*) num, sum (salary) sum, avg (sa Lary) avg from emp_xxx; // average salary = total salary/total number of people, avg (salary) = sum (salary)/count (*) // avg (salary) calculate the average value based on the number of employees with salaries. The data obtained is not accurate enough. Case 29 calculate the highest and lowest salaries of employees. [SQL] select max (salary) high salary, min (salary) the minimum salary is from emp_xxx; Case 30 calculates the earliest and latest employee start time [SQL] select max (hiredate) max_hiredate, min (hiredate) min_hiredate from emp_xxx; // there is an error in the courseware. max is the last entry time, min: Calculate the earliest group query group by ** case 31: Calculate the highest and lowest salaries of each department by department. [SQL] select deptno, max (salary ), Min (salary) from emp_xxx group by deptno; Case 32 calculate the total salary and average salary of each Department [SQL] select deptno, sum (salary), avg (salary) from emp_xxx group by deptno; Case 33 statistical information for each Department [SQL] select deptno, max (salary) max_s, min (salary) min_s, sum (salary) sum_s from emp_xxx group by deptno; Case 34 is grouped by position, the highest and lowest salaries and number of people in each position [SQL] select job, max (salary) max_s, min (salary) min_s, count (*) emp_num from emp_xxx group by job order by emp_num; Case 35 If the column after select is not a group function and does not appear in the group by phrase, [SQL] select deptno, max (salary) max_s, min (salary) min_s, sum (salary) sum_s, avg (nvl (salary, 0) avg_s, count (*) emp_num from emp_xxx; // the query statement is incorrect, cause deptno is not a single-component group function case 36 if the column in the group by phrase does not appear in the select phrase, there will be no error and the information is not full [SQL] select max (salary) max_s, min (salary) min_s, sum (salary) sum_s, avg (nvl (salary, 0) avg_s, count (*) emp_num from emp_xxx group by deptno; sele Ct deptno, max (salary) max_s, min (salary) min_s, sum (salary) sum_s, avg (nvl (salary, 0) avg_s, count (*) emp_num from emp_xxx group by deptno; // If the column in the group by phrase does not appear in the select phrase, no error will occur, the having clause is used to filter the grouped data. Note that the difference between where and having is that the result data obtained by the heap Group is further filtered. Case 37: if the average salary of a department is greater than 5000 yuan, isn't the team involved? [SQL] select deptno, avg (nvl (salary, 0) avg_s from emp_xxx where deptno is not null group by deptno having avg (nvl (salary, 0)> 5000; case 38: having further filters out Department data with a total salary of more than 20000 RMB from grouping results. [SQL] select deptno, avg (nvl (salary, 0) avg_s, sum (salary) from emp_xxx where deptno is not null group by deptno having sum (salary)> 20000; Case 39 which positions have more than 2 [SQL] select job, count (*) emp_num from emp_xxx where job is n Ot null group by job having count (*)> 2 order by emp_num; // This case is classic, I don't know yet. // order by must be placed in the last subquery case. 40. query the highest salary. [SQL] select max (salary) max_s from emp_xxx; query the maximum salary of each department: [SQL] select deptno, max (salary) max_s from emp_xxx group by deptno; // query the highest salary in this table. // use the subquery knowledge [SQL] select ename from emp_xxx where salary = (select max (salary) max_s from emp_xxx ); case 41: Who is the lowest salary? // First update the data. The method is as follows: [SQL] update emp_xxx set salary = 100 where ename = 'gojing'; // The Person Who queries the lowest salary is [SQL] select ename ename_min, deptno deptno_min from emp_xxx where salary = (select min (salary) from emp_xxx );