SQL 함수 - 연습문제

1.
SQL> select name, lower(userid)

from student
where length(userid)>=7;

2.
SQL> select name, lower(userid), to_char(birthdate, 'yyyy-mm-dd')

from student
order by idnum;

3.
SQL> select name, lpad(to_char(nvl(profno,0)),4,'0')

from student;

4.
SQL> select studno, name, deptno,

decode(mod(studno,3),0,'A',1,'B',2,'C')
from student;

5.
SQL> select name, tel,

case substr(tel,1,instr(tel,')',1,1)-1)
when '02' then '서울'
when '051' then '부산'
when '052' then '울산'
when '053' then '대구'
else 'etc'
end Loc
from student;

6.
SQL> select name, sal,

round(sal*1.15,0) round,
trunc(sal*1.15,0) trunc
from professor
where to_char(hiredate, 'mm') between 1 and 3;

7.
SQL> select name,

trunc(months_between(sysdate, hiredate), 0) months

from professor

order by months desc;

8.
SQL> select concat(concat(dname,'는 '),loc) from department;

9.
SQL> select rpad(ename,10,substr('1234567890',length(ename)+1)) ename from emp;