Single-row functions of oracle reinforcement exercises

Single-row functions of oracle reinforcement exercises

1. dname AND loc are separated-

Select dname | '-' | loc From dept;

2. Fill the team name with 10 digits left

Select lpad (dname, 10) From dept;

3. Replace the employee's name'

Select replace (ename,'s ','s') From emp;

4. Search for the first three employees

Select substr (ename, 1, 3) From emp;

5. Find the location of the employee's first appearance

Select instr (ename,'s ', 1, 1) From emp;

6. Find the second occurrence of 'T' in the employee name

Select instr (ename, 't', 1, 2) From emp;

7. Search for the length of employee names

Select length (ename) From emp;

8. display the date of today one year ago and the date of today one year later.

Select add_months (sysdate,-12), add_months (sysdate, 12) from emp;

9. display the date of the third day of the month

Select sysdate-(to_char (sysdate, 'dd') + 3 From dual;

10. display the number of months between today and '2017-12-12'

Select months_between (sysdate, '12-December-2012 ') from dual;

11. Currency representation of employees' salaries, such as RMB5, 000

Select to_char (sal, '$9,999') From emp;

12. display the string format of April 9, October 1, 1949, and the final result is April 10, October 1, 1949.

Select to_char (to_date ('1970-10-1 ', 'yyyy-mm-dd'), 'fmyyyy "year" MM "month" dd "day"') from dual;

October 1, 1949

13. display data as follows:

Sal = 800 shows low wages
Sal = 3000 normal salary
Sal = 5000 high salary

Select sal, decode (sal, 800, 'low wage ', 3000, 'normal wage', 5000, 'high wage ') from emp;

14. The following fields and string connections are displayed: "The job id for", name (in upper case), "is", and work (in lower case) such as: The job id for ALLEN is salesman

Select 'the job id for '| ename | 'is' | job from emp;

15. display the name of the employee whose last letter is "N" (use substr or instr)

Select ename From emp where substr (ename,-1) = 'n ';

16. query the names of employees who participate in work hours after the 15th day of each month.

Select ename, hiredate From emp whereto_char (hiredate, 'dd')> 15;

17. The number of "*" indicates the number of "thousands" in the employee's salary. For example, if the number is 3500, it indicates '***', 5600, and '*****'

Select ename, sal, lpad ('*', sal/1000, '*') From emp;

18. display the employee name, participation time, first Monday after 6 months of work

Select ename, hiredate, next_day (add_months (hiredate, 6), 2) From emp;

19. display the employee name, monthly salary, and annual salary (13-month monthly salary + 10000 yuan prize + comm). The alias must be annual_salary, and the annual salary of all people must be displayed.

Select ename, sal, (sal * 13 + 10000 + nvl (comm, 0) "annual_salary" Fromemp;