Basic SQL - select statements based on some criteria

Data Manipulation Language Statements (DML):
Types :
1. Select
2. Delete
3. Update
4. Insert

Below were few Select statement examples:
Example 1 : Using Where condition
select name, designation, salary from ex_emp
where (designation ='Executive' or designation ='Manager') and salary>5000

Example 2 : Manipulating columns data, and displaying the result
select name, salary SAL, 12*salary from ex_emp order by SAL;

Example 3 :Using IN operation
select name, incentives from ex_emp where incentives not in (NULL,0);

Example 4 : Using Column alias name to sort result
select code, name,salary, incentives, 12*salary ANNUAL_INCOME from ex_emp
order by ANNUAL_INCOME

Example 5 : Using Length, LPAD, NVL in-built functions
select name, length(name) "Name Length",
LPAD(salary,10,'*') Salary,NVL(Incentives,0) Incentives,salary*12 "Anuual Income" from ex_emp;

Note: NVL function is used to replace Null's with Zero when performing some column operation

Example 6 : Using sub string function
select name, substr(name,length(name)-6) "String" from ex_emp where name like '%P';

Example 7 : Other useful in-built funtions
select code, name,salary,salary/30 "Per Day", Round(salary/30,2) ROUND, trunc(salary/30,2) TRUNCATE from ex_emp;

select name || ' earns ' || to_char(salary,'$9,999.99') || ' monthly but wants ' || to_char(3*salary,'$99,999.99') "Dream Salaries" from ex_emp;

select name,salary,ROUND(to_char((sysdate-doj)/7)) "No. of Weeks" from ex_emp;

select name || || ' Salary is ' || salary from ex_emp;

Date Format Functions:
1. select name, to_char(doj,'DDth MONTH RRRR') || to_char(doj,'DAY') from ex_emp;

2. select name, to_char(doj,'DDth MONTH RRRR DAY'),to_char(doj,'DD') from ex_emp order by to_char(doj,'DD');

3. select name, to_char(doj,'DDth MONTH RRRR DAY'),DAY_WEEK(doj,'DD') from ex_emp;

4. select name, to_char(doj,'DDth MONTH RRRR DAY'),to_char(doj,'D') from ex_emp;

5. SELECT name, to_char(doj,'DDth MONTH RRRR DAY'),to_char(doj,'D'),
CASE to_number(to_char(doj,'D')) WHEN 1 THEN to_number(to_char(doj,'D'))+7
ELSE to_number(to_char(doj,'D')) END "DOJ"
FROM ex_emp order by DOJ asc