The path to oracle for a cainiao ---------- 3

A cainiao's oracle road -------- 3. knowledge points learned today include 1. Non-correlated subqueries learn the data environment and syntax of non-correlated subqueries 2. correlated subqueries Compare non-correlated subqueries, GRASP syntax 3, set operations between multiple result sets 4, table operations between multiple tables association relationship 5, inner join and outer join multi-Table association query 6, join query between multiple tables in the left/right/all Outer Join mode. 1. knowledge point summary. 1.1 Basic Format of the query statement. [SQL] select Field 1, Field 2, Field 3, expression, function ,... from table name where condition group by column name having condition with group function order by column name 1.2 function 1.21 group function, count avg sum max min // note: group functions ignore null values of 1.22 single-line function character functions, upper/lower/initcap/length/lpad/rpad/replace/trim upper convert to uppercase lower convert to lowercase initcap convert first letter capital length take length lpad left patch rpad right patch replace character convert trim removes the leading space. Case 1 sets the ename field to 10 characters in length, if not, fill in [SQL] select lpad (ename, 10, '*') from emp_xxx with '*' on the left. Case 2 [SQL] select Rpad (ename, 10, '#') from emp_xxx; Case 3 numeric function, round/trunc/mod evaluate salary to 5000 modulo [SQL] select salary, mod (salary, 5000) from emp_xxx; Case 4: The date functions months_between, add_months, and last_day months_between are given a time, add the specified month last_day for this date to find out the parameter. The last day of the month in which the time point is located advances amy's entry time two months in advance [SQL] select ename, hiredate from emp_xxx where ename = 'guojing '; select ename, add_months (hiredate,-2) from emp_xxx where ename = 'guojing'; // use updat E Statement to modify [SQL] update emp_xxx set hiredate = add_months (hiredate,-2) where ename = 'guojing'; // query again [SQL] select ename, hiredate from emp_xxx where ename = 'guojing '; Case 5: what is the last day of this month [SQL] select last_day (sysdate) from dual; conversion functions to_char/to_date and to_number to_char to_number date ----------> character ------------> Number <---------- <------------ to_date to_char case 6 multiply "$7912345.67" by 10, output result [SQL] select to_number ('$7912345.67', '$ 9999999.99 ') * 10 from dual; Case 7 will output 79123456.79 in the specified format $9999999.99 [SQL] select to_char ('2017. 67 ',' $9999999.99 ') from dual; general functions nvl/coalesce/decode single-line functions four types of character functions, numeric functions, date functions, conversion functions, there are also some other functions nvl/coalesce/decode which use a single-row function with a high frequency. The upper round to_char/to_date/nvl group function count/avg/sum/max/min2 sub-queries the comparison of 2.1 single-row Functions operation ><>=<> case 8, who is higher than Huang Rong's salary [SQL] select ename, salary from emp_xxx where salary> (select salary from Emp_xxx where ename = 'huangrong '); // keep this problem, what should I do if there are two Huang Rong? Case 9 What are the positions in the R & D department? [SQL] select distinct job from emp_xxx where deptno = (select empno from emp_xxx where ename = 'analyst '); // It seems that this problem has not been solved. Case 10: Who has a higher salary than Zhang Wuji's [SQL] insert into emp_xxx (empno, ename, salary) values (1012, 'zhang Wuji ', 8000); select ename, salary from emp_xxx; // If there are multiple sub-queries, an error will occur. // The error is that a single row subquery returns multiple rows [SQL] select ename, salary from emp_xxx where salary> (select salary f Rom emp_xxx where ename = 'zhang Wuji '); 2.2 case 11 queries who have higher salaries than ALL calls Zhang Wuji // exceeds the maximum [SQL] select ename, salary from emp_xxx where salary> All (select salary from emp_xxx where ename = 'yellow Rong '); // greater than the maximum value, this table contains two students named Huang Rong, 2.3 Any case 12 // The value is greater than the minimum value [SQL] select ename, salary from emp_xxx where salary> any (select salary from emp_xxx where ename = 'yellow Rong '); 2.4 In case 13 who is In the same department as Liu cangsong? [SQL] select ename from emp_xxx where deptno = (select deptno from emp_xxx where ename = 'Liu cangsong '); who is in the same department as Liu cangsong? List employee names except Liu cangsong and use subqueries to implement the same function [SQL] select ename from emp_xxx where deptno = (select deptno from emp_xxx where ename = 'Liu cangsong ') and ename <> 'Liu cangsong '; 2.5 single-line comparison operator and All Any in case 14 who is in the same department as Liu cangsong? List the names of employees except Liu cangsong (if the subquery returns more than 10 results) [SQL] insert into emp_xxx (empno, ename, deptno) values (1015, 'Liu cangsong ', 20); values (1015, 'Liu cangsong', 20) // If the subquery returns multiple results, the single row comparison operator cannot be used, should be changed to in // otherwise an error is reported, the ORA-01427 single row subquery returns multiple rows [SQL] select ename, salary, job, deptno from emp_xxx where deptno in (select deptno from emp_xxx where ename = 'Liu cangsong ') and ename <> 'Liu cangsong'; Case 15: Who is Zhang Wuji's subordinate, if there is only one employee called Zhang Wuji, no problem. If there are multiple employees, you need to use in [SQL] select empno from emp_xxx where ena Me = 'zhang Wuji '// select ename from emp_xxx // where mgr in (1001,1014); select ename from emp_xxx where mgr in (select empno from emp_xxx where ename = 'zhang Wuji '); summary: the single row comparison operator and the All Any in operator use the return row based on the number of rows returned by the subquery results> <<=<> return multiple rows> ALL> Any <any in 2.6 return the subquery results case study of multiple columns 16: Who is the highest salary for each department? // First obtain the highest salary for each department. [SQL] select deptno, max (salary) from emp_xxx where deptno is not null group by deptno; // then find out who receives the highest salary from the department? [SQL] select ename, salary, deptno from emp_xxx where (deptno, salary) in (select deptno, max (salary) from emp_xxx where deptno is not null group by deptno ); // I feel that this question has not been fully mastered. // I understand, I don't understand. Please refer to case 14. 2.7 subqueries IN THE having phrase. In Case 17, which department has more people than 30? [SQL] select count (*) from emp_xxx where deptno = 10; // my mistake [SQL] select deptno, count (*) from emp_xxx group by deptno where count (*)> (select count (*) from emp_xxx where deptno = 30); // correct answer [SQL] select deptno, count (*) from emp_xxx group by deptno having count (*)> = (select count (*) from emp_xxx where deptno = 10) and deptno <> 10; // This case is classic, I learned about group by having <> case 18, which department has a higher average salary than that of Department 20? [SQL] select avg (nvl (salary, 0) from emp_xxx where deptno = 20; select deptno from emp_xxx group by deptno having avg (nvl (salary, 0)> (select avg (nvl (salary, 0) from emp_xxx where deptno = 20); // This topic is also classic and is an O (cost _ cost) made by myself) O Haha ~ Case 19 lists the names and positions of employees. The average salary of these employees in the Department is more than 5000 RMB [SQL] select ename, job from emp_xxx; // first query the departments with an average salary of more than 5000 yuan [SQL] select deptno from emp_xxx group by deptno having avg (nvl (salary, 0)> 5000; // The summary result is [SQL] select ename, job from emp_xxx where deptno in (select deptno from emp_xxx group by deptno having avg (nvl (salary, 0)> 2000); // This example is also very classic. It comprehensively examines the knowledge of group by having avg nvl subqueries.