 # SQL Tutorial part 2

### Let's study the convert function of SQL

Database table : empno=employee number / ename = employee name / mgr=manager/ sal=salary / com=commision number / deptno=department number

1. Print out the day after 200 days from today. Oracle

``````select TO_CHAR(TO_DATE(sysdate)+200,'day') AS DAY
from dual;
``````

Mysql

``````select DATE_FORMAT(DATE_ADD(sysdate(),INTERVAL 200 DAY),'%W') AS DAY
from dual;
``````

PostgreSQL

``````select to_char(now()+'200 day','day') as DAY;
``````

2. Print out what state it is today. Oracle

``````select sysdate, TO_CHAR(sysdate, 'WW')
AS WEEK
from dual;
``````

Mysql

``````select sysdate(), DATE_FORMAT(sysdate(), '%u')
AS WEEK
from dual;
``````

PostgreSQL

``````select now(), to_char(now(), 'WW')
as week;``````

3. Take name, manager, salary and hiredate from the database table and print the results as shown below. Use the link operator. If the manager's employee number is 'NULL', print out 'no manager'. Oracle

``````select ename||' has '||nvl(to_char(mgr),'no manager')
||' and his annual salary is'||to_char(sal*12, '\$999,999')
||'. He was employed in '||to_char(hiredate,'RRRR')||'.'
AS KING
from emp
where ename='KING';``````

Mysql

``````SELECT CONCAT(ename, ' has', IFNULL(CONCAT(mgr), ' no manager'),
' and his annual salary is ',
CONCAT('\$', sal*12),
'. He was employed in ',
DATE_FORMAT(hiredate, '%Y'), '.')
AS KING
from emp
where ename='KING';
``````

PostgreSQL

``````select ename||' has '||coalesce(mgr::TEXT, 'no manager')||' and his annual salary is '||
to_char(sal*12, '\$999,999')||'. He was employed in '||to_char(hiredate,'yyyy')||'.'
as KING
from test.emp
where ename='KING';
``````

4.Print out name, salary, department number, bonus and total wage. The bonus 400 and 700 will be given to employees who are in department no.10 and 20 respectively. (Calculate total wage as sum of salary and bonus.) Oracle

``````select ename, sal, deptno,decode(deptno,10,400,
20,700,
0) as bonus,
sal+decode(deptno,10,400,20,700,0) as total
from emp;
``````

Mysql

``````select ename, sal, deptno, if(deptno=10,400,
if(deptno=20,700,
0)) as bonus,
sal+if(deptno=10,400,if(deptno=20,700,0)) as total
from emp;``````

PostgreSQL

``````select ename, sal, deptno, case when deptno=10 then 400
when deptno=20 then 700
else 0 end as bonus,
sal+case when deptno=10 then 400 when deptno=20 then 700 else 0 end as total
from test.emp;
``````

5. Print out name, year of employment and bonus. The bonus 6000 will be given to employees who are in department no.10 and salary is more than 3000. The bonus 4000 will be given to employees who are in department no.20 and salary is less than 3000 and over 2000. The bonus 2000 will be given to employees who are in department no.30 and salary is less than 2000. Print 500 for other employees. Oracle

``````SELECT ename, TO_CHAR(hiredate, 'RRRR') AS hireyear,
CASE WHEN(sal>3000 and deptno=10) THEN 6000
WHEN(sal<=3000 and sal>2000 and deptno=20)
THEN 4000 WHEN(sal<=2000 and deptno=30) THEN 2000
ELSE 500
END AS bonus
from emp;
``````

Mysql

``````select ename, date_format(hiredate,'%Y') AS hireyear,
CASE WHEN (sal>3000 and deptno=10) THEN 6000
WHEN(sal<=3000 and sal>2000 and deptno=20) THEN 4000
WHEN (sal<=2000 and deptno=30) THEN 2000
ELSE 500
END AS bonus
from emp;
``````

PostgreSQL

``````select ename, TO_CHAR(hiredate, 'YYYY') AS hireyear,
CASE WHEN(sal>3000 and deptno=10) THEN 6000
WHEN(sal<=3000 and sal>2000 and deptno=20) THEN 4000
WHEN(sal<=2000 and deptno=30) THEN 2000
ELSE 500
END AS bonus
from test.emp;
``````
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]