Summary of SQL language during Oracle Learning

Summary of the SQL language during Oracle Learning 1. query the structure of the dept table www.2cto.com in the Command window input: desc dept; 2. Retrieve all column information in the dept table select * from dept 3. Search for the employee name, monthly income, and department number in the emp table select ename "employee name", sal "Monthly Income ", empno "department no." from emp 4. The default display format for retrieving employee names and employment time date data in the emp table is DD-MM-YY. If you want to use another display format (YYYY-MM-DD ), the TO_CHAR function must be used for conversion. Select ename "employee name", hiredate "hire time 1", to_char (hiredate, 'yyyy-MM-DD ') "hire time 2" from emp note: the first time is of the date type, and there is a calendar next to it on the Oracle query interface. The second time is linear. Do not remove duplicate rows with double quotation marks 5 and distinct. Retrieve the Department numbers and types of work in the emp table and remove duplicate rows. Select distinct deptno "department no ", job "job type" from emp order by deptno 6. Use an expression to display the names of employees in the e-mapreduce table and select ename "employee name" for the annual monthly income (sal + nvl (comm, 0) * 12 "annual income" from emp Note: to prevent the commission of comm from being empty, use nvl function 7. Use column aliases to display employee names with names, the annual income shows the annual monthly income. Select ename "employee name", sal * 12 "annual revenue" from emp 8. The connection string is completed using the "|" Operator in oracle. When the string is connected, if you want to add a numeric value to a string, you can directly specify a number after "|". If you want to add a character or date value to the string, you must use single quotation marks. To retrieve the emp table, use the string "is a" to connect two fields, "select ename" | 'is a' | job "their respective positions" from emp note: single quotes are used !!! 9. Use the WHERE clause to retrieve the names and monthly incomes of employees whose monthly income exceeds 2000. Select ename "name", sal "monthly salary" from emp where sal> 2000 search for names, monthly income, and employment time of employees whose monthly income ranges from 1000 RMB to 2000 RMB. Select ename "name", sal "monthly salary", hiredate "employment time" from emp where sal between 1000 and 2000 10, like usage: Search for names of employees starting with S and monthly income. Select ename "employee name", sal "Monthly Income" from emp where ename like's % 'search the third character in employee name is the employee name and monthly income of. Select ename "employee name ", sal "Monthly Income" from emp where ename like '_ A %' 11. IN the WHERE condition, use the IN operator to retrieve the names and department numbers of employees whose monthly income is 800 or 1250 IN the emp table. select ename "name ", deptno "department no.", sal "salary" from emp where sal in () Note: IN means or. It is 800 OR 1250 instead of 12 in the range. Use the logical operators (AND, OR, NOT) in the where condition) select * from emp where deptno = '20' and job = 'wheel' show all employees whose salary is higher than 2500 or whose position is MANAGER * from emp where sal> '20180101' or job = 'manager' note: the conditions in the where clause are all single quotes. 13. The data in the query table is null. The e-mapreduce table contains the name of the employee, monthly income, and commission. Select ename "name", comm "Commission", sal "salary" from emp where comm is not null 14. sort BY using the order by clause. Search for the name, monthly income, and commission of the employee whose department number is 30 in the emp table, and ask the result to be displayed in ascending order of monthly income and then in descending order of the percentage. Select ename "name", comm "Commission", sal "salary" from emp where deptno = '30' order by sal asc, comm desc 15. query the names and salaries of employees with salaries greater than 1200 select ename "name ", sal "salary" from emp where sal> 1200 16. query the name and department number of an employee whose employee number is 7934 select ename "name ", deptno "department no." from emp where empno = '000000' 17. select the name and wage select ename "name" for employees whose salaries are not between 7934 and 5000 ", sal "salary" from emp where sal not between 5000 and 12000 Note: not is not 18. Select the employee's surname from. Name, job, and employment time, sorted from morning to night. select ename "name", job "position", hiredate "hire time" from emp where hiredate between to_date ('2017-02-01 ', 'yyyy-MM-DD ') and to_date ('2017-05-01 ', 'yyyy-MM-DD ') order by hiredate asc 19. select ename "name", deptno "department no." from emp where deptno in (20, 10) 20. select the name and time of the employee hired on April 9, 1987 select ename "name", hiredate "employment time" from emp where to_char (hiredate, 'yyyy ') = '000000' indicates the year of employment time. And convert it to the character form. Then compare the select ename "name", hiredate "employment time" from emp where to_char (hiredate, 'mm') with '200 ') = '04 'select ename "name", hiredate "employment time" from emp where to_char (hiredate, 'mm') = '4' the former is acceptable, the latter cannot be 21. select the employee name and job select ename "name" without managers in the company ", job "job" from emp where mgr is null 22. Select the name of the employee whose company has a bonus (COMM is not empty and not 0), and the ratio of salary to bonus, sort by wage inverse, and reverse order by bonus ratio. select ename "name", sal "salary", comm "bonus ratio" from emp where comm is not null and comm! = 0 order by sal desc, comm desc 23. The third letter of employee name is select ename "name" from emp where ename = '_ a' select ename "name" from emp where ename like '_ A %' summary: 1. The former is wrong, so it is fixed: there are only three letters in total, and the last one is A 2. The latter is correct. It indicates that the system time is not limited to 24 after A (the alias is "DATE "). select sysdate "Date" from dual Note: dual is a virtual table 25 in the Oracle system. It contains the employee ID, name, and salary (if it is NULL, it is processed as 0 ), subsidy (if it is NULL, it will be treated as 0) and result of rounding to an integer after the salary is increased by 20% (alias: new salary) select ename "name", empno "employee number ", nvl (sal, 0) "original sal", round (sal * (1 + 0.2), 2) "new salary", nvl (comm, 0) "subsidy" from emp Summary: two important functions are used here: nvl and round () for Rounding-for example, dividing the original salary by 7, retain only three decimal places select round (sal/7,3) "new sal" from emp 26. Name of the employee (alias: "Name") in the alphabet Sort and write the Name length (alias: "length") select ename "Name", length (ename) "length" from emp order by ename asc select ename "name", length (ename) "Name length" from emp order by ename asc 27. query the names of employees, the number of months that each employee has worked in the company (the alias is "worked_month") is rounded to an integer. select ename "name", round (months_between (sysdate, hiredate), 0) "worked_month" from emp calculates the number of months from the specified date to the employment date? Select ename "name", round (months_between (to_date ('2017-01-01 ', 'yyyy-MM-DD'), hiredate), 0) "worked_month" from emp calculates the number of months between two dates? Select ename "name", round (months_between (to_date ('2017-01-01 ', 'yyyy-MM-DD'), to_date ('2017-01-01 ', 'yyyy-MM-DD '), 0) "worked_month" from emp Note: Two Parameters in the months_between () function are date types, so transformation is required! 28. query the employee's name and salary. The result is displayed as follows (the salary field must be 15 digits and the blank space should be filled with $) name salary KING $24000 MIKE $4800 select ename "name", lpad (sal, 15, '$') "salary" from emp pay attention to the last lpad parameter. Because it is of the balanced type, it is quoted in single quotes 29. query the employee name, and how many months (worked_month) have been working in the company, and sort select ename "name", months_between (sysdate, hiredate) in descending order by the number of copies per month) "worked_month" from emp order by having worked_month desc (incorrect) select ename "name", months_between (sysdate, hiredate) "worked_month" From emp order by "worked_month" desc (incorrect) Pay attention to the double quotation mark "select ename" name "after order by, trunc (months_between (sysdate, hiredate )) "worked_month" from emp order by "worked_month" desc trunc () function indicates that only the integer is used! The question requires "how many months have it been filled", so we should take the entire 30, query the maximum, minimum, average, and total of the company's employees 'salary select max (sal) "highest salary ", min (sal) "minimum wage", round (avg (sal), 2) "average wage", sum (sal) "sum of wages" from emp 31. query the maximum, minimum, and average values of the employees' salaries of each job. select job ", max (sal)" highest salary ", min (sal) "minimum wage", round (avg (sal), 2) "average wage", sum (sal) "sum of wages" from emp group by job 32. select job "job type", count (*) "Number of employees in this type of work" from emp group by job 33. query the DIFFERENCE between the highest wage and the minimum wage (DIFFERENCE) select max (sa L)-min (sal) "DIFFERENCE" from emp 34. query the number of employees in the company and the number of employees hired each year in, the result is similar to the following format: total 1980 1981 1982 1987 14 1 10 1 2 select count (*) "total", sum (decode (to_char (hiredate, 'yyyy'), 1980, 1, 0) "1980", sum (decode (to_char (hiredate, 'yyyy'), 1981,1, 0) "1981", sum (decode (to_char (hiredate, 'yyyy'), 1982,1, 0) "1982", sum (decode (to_char (hiredate, 'yyyy'), 1987,1, 0 )) "1987" from emp is very important! Select decode (to_char (hiredate, 'yyyy'), 1980, 1, 0) from emp is the core statement. If the year is, set it to 1; otherwise, set it to 0, in this way, all the years (equivalent to a temporary table) are operated, and sum is used to calculate the sum of the temporary tables (that is, the sum of the temporary tables is calculated to know how many 1980 )!!! Similarly, you can determine the number of employees in each month. select sum (decode (to_char (hiredate, 'mm'), 0) "February ", sum (decode (to_char (hiredate, 'mm'), 1, 0) "March", sum (decode (to_char (hiredate, 'mm, 0) "April employment", sum (decode (to_char (hiredate, 'mm'), 1, 0) "May employment" from emp