Oracle Learning record seven continues to receive six

Here is the conversion function

1. To_char

Select To_char (sysdate, ' yyyy ') from dual;

Select To_char (sysdate, ' Fmyyyy-mm-dd ') from dual;

Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;

Select To_char (sysdate, ' DDD ') from dual; The day ordinal of a year

Select To_char (sysdate, ' DD ') from dual; The day of the January

Select To_char (sysdate, ' D ') from dual; The day of the week, this is not the days of the week, because the first days are counted from Sunday

There are FM representatives to remove the extra 0 and space

Http://zhidao.baidu.com/link?url=VtGQ0DbPYOmlk99vhz_Nm2O4bL26srNg24q4nZHgzYCYez22AuVM8mujT6lPvguyTgxbIKk1LsCmzzJZbQpiv_

Http://www.cnblogs.com/tider/archive/2009/05/07/1451296.html

2. To_number

Select To_number (' +to_number ') from dual;

3. To_date

Select To_date (' 20150305 ', ' YYYYMMDD ') from dual;

Here is the general function

1. NVL () function

Select NVL (comm, 0) from EMP;

This is used in the previous chapter, that is, Comm is null to return the second argument, if it is not NULL, the first argument is returned, if two parameters are null

it returns NULL.

2. NVL2 () function

Select Ename, NVL2 (comm, Comm+sal, Sal) from EMP;

If the first parameter is NULL, the third argument is returned, and if the first one is not NULL, the second argument is returned.

3. Nullif () function

Select Nullif from dual;

Select Nullif from dual;

Returns null if the first and second arguments are equal, otherwise the first argument is returned;

4. COALESCE () function

Look at it, and return to this parameter immediately if you encounter a non-null parameter

Select Ename, COALESCE (Sal+comm, Sal, 0) from EMP;

This is almost the same as it was done with NVL.

5. Case expression

Select Empno, ename, Case deptno

When ten then ' Caiwubu '

When the then ' Yanfabu '

When the ' Xiaoshoubu '

Else ' Qitabu '

End Bumen

from EMP;

6. Decode () function

This is also the condition function and case, one is the function one is the keyword

Select Empno, ename, Decode (Deptno, ten, ' Caiwubu ', +, ' Yanfabu ', ', ' Xiaoshoubu ', ' Qitabu ') bumen from EMP;

This is understood in contrast to the 5 example.

Http://www.cnblogs.com/juddhu/archive/2012/03/07/2383101.html

7. Find the employee hired on the third day of the month

Select ename from emp

where Hiredate= (Last_day (HireDate)-2);

8. Identify employees who have been employed for more than 30 years

Select ename from emp

where Hiredate<add_months (sysdate,-30*12);

9. Precede each employee's name with Dear and capitalize the first letter

Select (' Dear ' | | "| | Initcap (ename)) "New name" from EMP;

This "New name" cannot be quoted in single quotes, and I now find that if the string is used as a property, use double quotes,

If it is for the data, use single quotes, as if it were.

10. Find an employee with a name of 5 letters

Select ename from emp

where length (ename) = 5;

11. Find the employee whose name does not have R, this time I see what kind of substring to look for, such as InStr these?

Select ename from emp

where ename not like '%r% ';

12. Show the first letter of all employee names

Select substr (ename,0,1) from EMP;

13. Show all employees, name in descending order, if same, in ascending order by salary

SELECT * FROM emp

ORDER BY ename DESC, sal ASC;

14. According to one months and 30 days, calculate the employee's salary for one day, regardless of decimal

Select Trunc (sal/30,0) from EMP;

15. Identify employees employed in February

Select ename from emp

where To_char (hiredate, ' fmmm ') = ' 2 ';

16. Calculate the number of days employees join the company, this is more interesting, I was also thinking about how many days a year or so, and then looked on the Internet, you have to remember

Date can be added and reduced directly to get the number of days.

Select Sysdate-hiredate from EMP;

If you don't want decimals

Select Round (sysdate-hiredate,0) from EMP;

Http://www.myexception.cn/oracle-management/1245803.html

The following is a grouping function exercise.

1. Count ()

Calculation department 10 How many people are there?

Select COUNT (*) from EMP

where deptno=10;

2. Avg/max/min/sum

Select round (AVG (SAL), 2) from EMP

where deptno=30;

Select Max (SAL) from EMP

where deptno=20;

Select min (sal) from EMP;

Select sum (SAL) from EMP;

3. GROUP BY

Total wages by Department

Select Deptno, sum (SAL) from EMP

Group BY Deptno;

Calculate total wages by department and supervisor's number

Select Mgr, Deptno, sum (SAL) from EMP

Group by MGR, Deptno;

4. Having

Calculate the average wage employed by each employee in each department after May 1, 1981 .... I'm not going to describe this.

Select Deptno, Job, avg (SAL) from EMP

where Hiredate>=to_date (' 19810501 ', ' YYYYMMDD ')

Group by DEPTNO, job

Having avg (SAL) >1200

Order by deptno, job;

Here I come across a problem that has not been resolved, first recorded

Select Job, avg (SAL)

From EMP

Group by Job;

That's it, but it's not going to happen.

Select Job, Max (avg (SAL))

From EMP

Group by Job;

Here, we need the help of God to see what's going on, or else we'll solve it slowly.

Oracle Learning record seven continues to receive six