SQL Tutorial part 1

Let's study the basic command of SQL

Database table :

empno=employee number / ename = employee name / sal=salary / com=commision number / deptno=department number

1. Print out the names and salaries of the employees who are paid between 1500 and 4500.

Oracle

select ename, sal
from emp
where sal>=1500 and sal<4500;

select ename, sal
from emp
where sal between 1500 and 4500;

MySQL

select ename, sal
from emp
where sal >= 1500 and sal <=4500;

select ename, sal
from emp
where sal between 1500 and 4500 ;

PostgreSQL

select ename, sal
from test.emp
where sal>=1500 and sal<=4500;

select ename, sal
from test.emp
where sal between 1500 and 4500;

2. Print out names, sum of salary and commision(name it as 'total'), and jobs of employees whose jobs are MANAGER and arrange them in order starting with those with higher salaries.

Oracle

select ename, job, sal+nvl(comm,0) as total
from emp
where job='MANAGER'
order by total desc;

MySQL

select ename, job, sal+coalesce(comm,0) as total
from emp
where job='MANAGER'
order by total desc;

PostgreSQL

select ename, job, sal+coalesce(comm,0) as total
from test.emp
where job='MANAGER'
order by total desc;

3. Print out ‘tt’ from ‘scott’

Oracle

select substr('scott',4,2)
from dual;

select substr('scott',-2,2)
from dual;

MySQL

select substr('scott',4,2)
from dual;

select substr('scott',-2,2)
from dual;

PostgreSQL

select substr('scott',4,2)
as dual;

select substr('scott',-2,2)
as dual;

4. Print out the names and hiredate of the employees whose second letter is A and sort in order of employment

Oracle

select ename, hiredate
from emp
where ename like '_A%'
order by hiredate asc;

Mysql

select ename, hiredate
from emp
where ename like '_A%'
order by hiredate asc;

PostgreSQL

select ename, hiredate
from test.emp
where ename like '_A%'
order by hiredate asc;

5. Select year of hiredate and print out it in ascending order. Place 'NULL' at the bottom.

   Oracle

select distinct substr(hiredate,1,4) as hireyear
from emp
order by hireyear asc nulls last;

   MySQL

select distinct substr(hiredate,1,4) as hireyear
from emp
order by hireyear is null asc, hireyear asc;

   PostgreSQL

select distinct split_part(hiredate::TEXT,'-',1) as hireyear
from test.emp
order by hireyear asc nulls last;