No image

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]