Date Functions in SQL

Select Getdate() as CurrentDate;

Select Getdate() -1 PreviousDate;

Select Getdate() +1 NextDate;

Select DATEADD(dd,1,getdate()) as NextDate;

Select DATEADD(mm,1,getdate()) as NextMonth;

Select DATEADD(yy,1,getdate()) as NextYear;

Select MONTH(getdate()) as CurrentMonth;

Select EOMONTH(getdate()) as MonthEndDate;

Select EOMONTH(getdate(),1) as NextMonthEndDate;

Select YEAR(getdate()) as CurrentYear

Select DATEDIFF(dd,getdate(),eomonth(getdate())) as NoOfDaysLeftThisMonth;

Select DATEPART(year, CURRENT_TIMESTAMP) as ExtractingYear;

Select DATEPART(year, getdate()) as ExtractingYear;

Aggregate functions

Aggregate functions :-
The functions SUM, COUNT, AVG, MIN, MAX are the common aggregate functions the result of which does not depend on the order of the records.

Aggregate functions Return a single output by calculating values from the table or column. They return the group value multiple times with each record.

SELECT DEPTNO, COUNT(*) DEPT_COUNT FROM EMP 
WHERE DEPTNO IN (20, 30)
GROUP BY DEPTNO;

DEPTNO                 DEPT_COUNT             
---------------------- ---------------------- 
20                     5                      
30                     6 


SUM() function :-
It returns the sum of a column. The column should be numeric. 

SELECT SUM(SAL) FROM EMP;
SELECT EMPNO, SUM(SAL) FROM EMP GROUP BY EMPNO;


COUNT() function :-
It returns/counts the number of rows in a query. But, it will not count any null values/column in a table. 

SELECT COUNT(*) FROM EMP;
OR 
SELECT COUNT(EMPNO) FROM EMP;
SELECT EMPNO, COUNT(EMPNO) FROM EMP GROUP BY EMPNO;


AVG() function :-
It returns the average value of a column. The column should be numeric.

SELECT AVG(SAL) FROM EMP;
SELECT EMPNO, MIN(SAL) FROM EMP GROUP BY EMPNO;


MIN() function :-
It returns the minimum or smallest value of a column. The column should be numeric.

SELECT MIN(SAL) FROM EMP;
SELECT EMPNO, AVG(SAL) FROM EMP GROUP BY EMPNO;


MAX() function :-
It returns the maximum or biggest value of a column. The column should be numeric. 

SELECT MAX(SAL) FROM EMP;
SELECT EMPNO, MAX(SAL) FROM EMP GROUP BY EMPNO;

Analytical Functions

The functions SUM, COUNT, AVG, MIN, MAX are the common aggregate functions the result of which does not depend on the order of the records.
Analytical functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records.

Analytical functions :-
----------------------
Analytic functions Return the same number of rows as the input. Analytic functions give aggregate result they do not group the result set.
In order to arrange employees in a particular order like ascending or descending order with the help of analytical functional like rank, dense_rank

SELECT empno, deptno, 
COUNT(*) OVER (PARTITION BY 
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT
---------- ---------- ----------
      7369         20          5
      7566         20          5
      7788         20          5
      7902         20          5
      7876         20          5
      7499         30          6
      7900         30          6
      7844         30          6
      7698         30          6
      7654         30          6
      7521         30          6



Types of Analytical Functions
- Rank
- Dense_rank
- Row_number
- Count
- Lag
- Lead
- First_value
- Last_value
- First
- Last


RANK function :-
It is used to rank a record within a group of rows
RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped)

SELECT EMPNO, ENAME, JOB, RANK() OVER (ORDER BY JOB) AS RANK FROM EMP;

(OR) 

 SELECT E.*, RANK() OVER (ORDER BY JOB DESC) AS RANK FROM EMP E;


DENSE_RANK function :-
Dense_rank function acts like the RANK function except that it assigns consecutive ranks.
DENSE_RANK() will return {1,1,2,3,3,4}

SELECT EMPNO, ENAME, JOB, DENSE_RANK() OVER (ORDER BY JOB) AS RANK FROM EMP;

(OR) 

SELECT E.*, DENSE_RANK() OVER (ORDER BY JOB DESC) AS DRANK FROM EMP E;

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+



ROWNUM function :-
Rownum is temporary, It is the sequential number. 

SELECT ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY JOB) AS ROWNUMBER,
EMPNO, ENAME, JOB FROM EMP;

(OR)

SELECT E.*, ROW_NUMBER() OVER (ORDER BY JOB DESC) AS RN FROM EMP E;


ROWID function :-
Rowid is permanent, It is physical address of the rows or permanent unique identifier.

SELECT EMPNO, ENAME, JOB, ROWID FROM EMP;


LEAD function :-
It is used to display Subsequent row data along with current row data
For example, by using the LEAD() function, from the current row, you can access data of the next row, or the second row that follows the current row, or the third row that follows the current row, and so on.

Syntax :
LEAD ( scalar_expression [ ,offset ] , [ default ] )   
OVER ( [ partition_by_clause ] order_by_clause )

SELECT EMPNO, ENAME, JOB, SAL, LEAD(SAL) OVER(PARTITION BY JOB ORDER BY SAL) AS HIGHEST_SAL FROM EMP;

(OR)

SELECT E.*, LEAD(SAL) OVER (ORDER BY SAL DESC) AS LEAD
FROM EMP E;


LAG function :-
It is used to display previous row data along with current row data. Here 1 indicated beside lag column skips 1 row and by default its 1 there is no use of writing it.
2 is indicated to skip two rows in the result.

Syntax :
LAG ( scalar_expression [ ,offset ] , [ default ] )   
OVER ( [ partition_by_clause ] order_by_clause )

SELECT EMPNO, ENAME, SAL, LAG(SAL) OVER (ORDER BY SAL DESC) AS PREV_ROW FROM EMP;
(OR)
SELECT EMPNO, ENAME, SAL, LAG(SAL,1) OVER (ORDER BY SAL DESC) AS PREV_ROW FROM EMP;


SELECT EMPNO, ENAME, SAL, LAG(SAL,1,0) OVER (ORDER BY SAL DESC) AS PREV_ROW FROM EMP;

(OR)

SELECT E.*, LAG(SAL) OVER (ORDER BY SAL DESC) AS LAG
FROM EMP E;






FIRST_VALUE function :-
Its used to display first value from the specified column based on the order by clause.

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(SAL) OVER (ORDER BY SAL) AS LOWEST_SAL FROM EMP;

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(SAL) OVER (PARTITION BY JOB ORDER BY SAL) AS LOWEST_SAL FROM EMP;



LAST_VALUE function :-
Its used to display last value from the specified column based on the order by clause.

SELECT EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (ORDER BY SAL) AS LOWEST_SAL FROM EMP;
As a result of this syntax it does not return expected results but it returns same rows.

SELECT EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_SAL FROM EMP;
This syntax gives you correct results.


PARTITION BY clause :-
This clause is used only with "Over" clause. It is used to breakup or divide table data into many partitions, we can apply aggregate functions and analytical functions.


SELECT DEPTNO, ENAME, SAL, AVG(SAL) OVER (PARTITION BY DEPTNO) AS AVG_SAL FROM EMP;

SQL Database Objects

Database objects are used to store or reference data. Some of them are indexes, stored procedures, sequences, views and so on.

INDEXES :-

Indexes are of different types and they are as below

Single column index 
A single-column index is created based on only one table column.
The basic syntax is as follows.

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME);

OR

CREATE INDEX INDEX_EMP ON EMP1 (EMPNO);

Unique Indexes
A unique index does not allow any duplicate values to be inserted into the table. 
The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes
A composite index is an index on two or more columns of a table. 
Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Implicit Indexes
Implicit indexes are indexes that are automatically created by the database server when an object is created.

To Drop Index 
Drop index index_name;

SQL Interview Questions

SQL QUERIES :-

-----------------

* How to create a copy of table from original table in Oracle? 

create table emp2 as select * from emp;

* How to find 2nd highest salary ?

select max(sal) from emp where sal < (select max(sal) from emp);

                                               or

select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from   emp));

* How to find 3rd highest salary ?

select max(sal) from emp where sal < (select max(sal) from emp where sal < (select  max  (sal) from emp));

                                                   or

select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from   emp where sal < (select max  (sal) from emp)));

* How to find 2nd lowest salary ?

select min(sal) from emp where sal > (select min(sal) from emp);

                                                  or

select * from emp where sal = (select min(sal) from emp where sal > (select min(sal) from emp));

* How to find 3rd lowest salary ?

(select min(sal) from emp where sal > (select min(sal) from emp where sal > (select min(sal) from emp));

                                                     or

select * from emp where sal = (select min(sal) from emp where sal > (select min(sal) from emp where sal > (select min(sal)   from emp)));

* How to find first half rows in table ?

select * from emp where rownum <= (select count(*)/2 from emp);

* How to find last half rows in table ?

select * from emp minus select * from emp where rownum <= (select count(*)/2 from emp);

* How to find first 3 highest salaries from emp table ?

select * from (select * from emp order by sal desc) where rownum<=3;

* How to find first 3 lowest salaries from emp table ?

select * from (select * from emp order by sal) where rownum<=3;

* Display the employees who joined in the year 1981.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY')='1981';


* Display the employees who joined before 1981.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY') < '1981';


* Display the employees who does not belong to department 10.
SELECT * FROM EMP
WHERE
DEPTNO NOT IN (10);


* Display the average salaries of all the employees who works as ANALYST.
SELECT AVG(SAL) FROM EMP
WHERE
JOB='ANALYST';


* Display all the details of the employees whose commission is more than their salary
SELECT * FROM EMP
WHERE
COMM > SAL;


* Display the employees whose salary is more than 3000 after giving 25% increment.
SELECT * FROM EMP
WHERE
SAL+SAL*25/100 > 3000;


* Display the name of the employees, those having six characters to their name.
SELECT * FROM EMP
WHERE
LENGTH(ENAME)=6;


* Display the employees who joined in the month January.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'MON')='MAY';


* Display the name of employees and their manager separated by the string 'works for'.
SELECT E.ENAME || ' WORKS FOR ' || M.ENAME
FROM EMP E, EMP M
WHERE
E.MGR = M.EMPNO;

* Display the employees whose experience is more than 27 years.
SELECT * FROM EMPLOYEES
WHERE
HIRE_DATE - SYSDATE < 27

* Employees who earn more than 100 as daily salary
SELECT * FROM EMP WHERE SAL/30 > 100;

* Write a query in SQL to list all the employees joined on 1st may 91
SELECT * FROM EMP WHERE HIREDATE = '1-MAY-91';

* Write a query in SQL to list the employees who are retiring after 31-Dec-99 after completion of 8 years of service period.
SELECT emp_name FROM employees
WHERE hire_date + interval '96 months' > '1999-12-31';

* Write a query in SQL to list those employees whose salary is an odd value.
SELECT * FROM EMP WHERE MOD(SAL,2)=1;

* Display all the records but common records only once from both the table EMP and EMP1
SELECT * FROM EMP
UNION
SELECT * FROM EMP1;


* Fetch only common records from two tables emp and emp1
SELECT * FROM EMP
INTERSECT
SELECT * FROM EMP1;


* Display all records of emp1 those should not present in emp2
SELECT * FROM EMP
MINUS
SELECT * FROM EMP1;

SQL SUB-QUERIES :-

--------------------- 

* Query to Display only duplicates from a table ?

SELECT * FROM EMP

WHERE

EMPNO IN

(SELECT COUNT(EMPNO) FROM EMP

GROUP BY EMPNO

HAVING

COUNT(EMPNO)>1);

* How do you delete duplicate records ?

DELETE FROM EMP

WHERE

EMPNO IN

(SELECT COUNT(EMPNO) FROM EMP

GROUP BY EMPNO

HAVING

COUNT(EMPNO)>1);

* Query to delete duplicates only single instance should be present.

SELECT * FROM EMP 

WHERE EMPNO NOT IN 

(SELECT MIN(EMPNO) FROM EMP 

GROUP BY EMPNO)

* Write a query in SQL to display all the details of managers.
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID IN
(SELECT MANAGER_ID FROM EMPLOYEES);

* Write a query in SQL to display the employee ID, name, job name, hire date, and experience of all the managers.

SELECT EMPNO, ENAME, JOB, HIREDATE FROM EMP 

WHERE EMPNO IN

(SELECT EMPNO FROM EMP WHERE JOB = 'MANAGER')

* Write a query in SQL to list all the employees of grade 2 and 3.
SELECT * FROM EMPLOYEES, SALARY_GRADE
WHERE GRADE IN (2,3);

* Write a query in SQL to list all the employees where salary between min & min salary, grade is 2 and 3.
SELECT * FROM EMPLOYEES, SALARY_GRADE
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL
AND GRADE IN (2,3);

(OR)

SELECT * FROM EMPLOYEES E, SALARY_GRADE S
WHERE E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL
AND S.GRADE IN (2,3);

* Write a query in SQL to display all the employees of grade 4 and 5 who are working as ANALYST or MANAGER.
SELECT * FROM EMPLOYEES E, SALARY_GRADE S
WHERE GRADE IN (4,5)
AND JOB_NAME IN ('ANALYST','MANAGER');

* Write a query in SQL to list the details of the employees whose salary is more than the salary of JONES.

SELECT * FROM EMP WHERE SAL > 

(SELECT SAL FROM EMP WHERE ENAME = 'JONES')

* Write a query in SQL to list the employees who works in the same designation as CLARK.

SELECT * FROM EMP WHERE JOB =

(SELECT JOB FROM EMP WHERE ENAME = 'CLARK')

* List the employees who are senior to KING

SELECT * FROM EMP WHERE HIREDATE >

(SELECT HIREDATE FROM EMP WHERE ENAME = 'KING')

 

SQL INTERVIEW QUESTIONS :-

---------------------------------------------
* What are the different comparison conditions in oracle ? What is the importance of them ?
- ALL, ANY and SOME are different Comparison Conditions in SQL.
- ALL comparison condition is used to compare to a subquery, It should be preceded by =,>,<,>=,<=, operators in sql.
- ALL operator returns true, if all of the subquery values meet the condition.
 

* How do you tune or optimize sql queries ?
- select field name instead of using select *
- avoid using distinct command

- Use Order by at the end of statement or comment it (--)

- User Indexes for make data retrieval faster

- User View or Materialized views     
- create joins using inner joins not by where clause

* What is the difference between Order by and Group by ?
Group by is used with Aggregate functions and arrange the data into columns.
Order by is used to sort data in either ascending and decending order.


* What is the difference between Having clause and Where clause ?
- Where clause cannot be used with aggregates, but the having clause can be used.
- Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
- WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
- One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause


* What is the difference between Aggregate and Analytical Functions ?
- The functions SUM, COUNT, AVG, MIN, MAX are the common aggregate functions, the result of which does not depend on the order of the records, where as Analytical functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records.
- Aggregate functions Return a single output by calculating values from the table, where as Analytic functions Return the same number of rows as the input. 


* What is a Partitioning clause ?
- It is used to breakup or divide table data into many partitions or parts.
- The PARTITION BY clause is a sub clause of the OVER clause.




* What is difference between index and bitmap index ?

* How do you join two tables with SQL inner join ?
SELECT EMPNO, ENAME FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;

or

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO 

AND DNAME ='ACCOUNTING'

* How do you join two tables with mulitple conditions with SQL inner join ?
SELECT EMPNO, ENAME FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
AND EMP.DNAME=DEPT.DNAME; 

* How do you join more than two tables with SQL inner join ?
Select * from Emp 
Inner Join Dept
on Emp.deptno = Dept.deptno
Inner join Emp1
On Dept.deptno = Emp1.deptno;


* How to get Nth Max Salary?

SELECT * FROM

(SELECT *, DENSE_RANK() OVER (ORDER BY SAL DESC) AS HSAL

FROM EMP ) AS E

WHERE HSAL=5;

OR 

SELECT * FROM

(SELECT E.*, DENSE_RANK() OVER (ORDER BY SAL DESC) AS HSAL

FROM EMP E) AS E

WHERE HSAL=5;

* Query to display unique Job from Emp table.
SELECT DISTINCT JOB FROM EMP;

* Query to display unique Job from Emp table without using Distinct keyword
SELECT JOB FROM EMP GROUP BY JOB;

* Difference between delete and truncate command ?
We can rollback the data, when we use delete command but in the case of truncate its not possible.
Delete removes only the rows from table but it cannot delete space containing the table where as truncate deletes all the rows and free the space containing the table
We can use where clause in delete but in the case of truncate its not possible.

* Subquery or join is better based on performance ?
JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

* What is the functional difference between oracle and sql ?
- Language used by Oracle is PL/SQL (Procedural Language/ SQL), where as Language used by SQL Server is T-SQL (Transact-SQL).
- In Oracle, no transaction is committed until DBA explicitly issues COMMIT command, where as If BEGIN TRANSACTION and COMMIT commands are not specified, it executes and commit each command individually.
- The syntax of the commands used by Oracle and SQL also differs.
- In Oracle, the procedures, functions and variables are encapsulated into Packages. However, SQL does not have Packages.

* Difference between stop and abort function ?
When you issue a stop command on a session, the integration service first stops reading the data from the sources. It continues processing and writing data to the targets and then commits the data.
Abort command is handled the same way as the stop command, except that the abort command has timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.


* All types of Joins and their outputs
 
Example 1:-

    EMP_NO EMP_NAME             JOB_NAME       MGR_ID    DEPT_NO
    ----------     -------------------- ---------- ---------- ----------
      1234 Alex                 Clerk            4567         15
      2345 Jack                 Consultant       3456         25
      3456 Paul                 Manager          1234         15
      4567 Jenefer              Engineer         2345         45

   DEPT_NO DEP_NAME   LOCATION
    ---------- ---------- ----------
        15 FINANCE    PARIS
        25 MARKETING  LONDON
        35 HR         DELHI

Innerjoin: 2
Left join: 4
Rightjoin: 3
Full join: 5

Example 2:-

TableX    TableY
------    ------
A    C
B    D
C    E
D    F

Innerjoin output:

X    Y
--    --
C    C
D    D

Leftjoin output:

X    Y
--    --
A    NULL
B    NULL
C    C
D    D

Rightjoin output:

X    Y
--    --
C    C
D    D
NULL    E
NULL    F 

* HOW DO YOU SWAP GENDER IN ORACLE ?
UPDATE GENDER
SET GENDER = CASE GENDER WHEN 'MALE' THEN 'FEMALE'
ELSE 'MALE'
END;

* What the different Operator available in SQL?

Arithmetic Operators 

Comparison Operators

Logical Operators

" + - * / % " are the list of Arithmetic Operators

" = > < >= <= !< !> " are the list of Comparison Operators

" ALL AND ANY SOME LIKE IN BETWEEN NOT EXISTS OR NULL " are the list of Logical Operators.

* How to combine two commands in SQL?

UNION, EXCEPT and INTERSECT Clause are used to combine the results of two SELECT statements.

* Query to find the employees who are managers

Sol:

----

Using Subquery:

select * from emp where empno in

(select mgr from emp)

Using Join:

select * from emp e1 

Inner Join 

(select DISTINCT mgr from emp) e2 

ON e1.empno=e2.mgr;

* Query to find the employees who are not managers

Sol:

----

Using Subquery:

select * from emp where empno NOT IN

(select mgr from emp)

Using Join:

select * from emp e1 

Inner Join 

(select DISTINCT mgr from emp) e2

ON e1.empno!=e2.mgr;

* Query to find the employees who does not belong to any of the departments
Select * from emp where deptno is null;

* Query to find the employees who are serving more than 30 years

Oracle:

-------

select * from emp where to_char(sysdate,'YYYY') - to_char(hiredate,'YYYY') > 30;

Sql:

----

select * from emp where year(getdate()) - year(hiredate) > 30;

* Query to find the employees who are retiring in next 7 days

SQL Functions

TO_DATE Function :
---------------------

The Oracle TO_DATE function converts a string to a date.

select to_date('20020315','yyyymmdd') from dual;
select to_date('07112003','mmddyyyy') from dual;


LPAD, RPAD, LTRIM, RTRIM, INSTR, SUBSTR, LOWER, UPPER, INITCAP Functions

SQL> SELECT * FROM EMP;

EMPNO ENAME                  SAL  COMM DEPTNO
----- -------------------- ----- ----- ------
 7369 SMITH                  900   100     20
 7499 ALLEN                 1600   300     30
 7521 WARD                  1250   500     30
 7566 JONES                 2975   100     20
 7654 MARTIN                2975   100     20
 7412 STONECOLD             1244   200     20
 7412 STONECOLD             1244   200     20

SQL> select substr(ename,2) from emp;

(OR)

SELECT E.*, SUBSTR(ENAME,2) SUBSTR FROM EMP E;



SUBSTR(ENAME,2)
-------------------
MITH
LLEN
ARD
ONES
ARTIN
TONECOLD
TONECOLD


SQL> select instr(ENAME,'M') from emp;

INSTR(ENAME,'M')
----------------
               2
               0
               0
               0
               1
               0
               0



SQL> SELECT UPPER(ENAME) from emp;

UPPER(ENAME)
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
STONECOLD
STONECOLD


SQL> SELECT LOWER(ENAME) FROM EMP;

LOWER(ENAME)
--------------------
smith
allen
ward
jones
martin
stonecold
stonecold


SQL> SELECT INITCAP(ENAME) FROM EMP3;

INITCAP(ENAME)
--------------------
Balu



SQL> SELECT LTRIM(ENAME,'S') FROM EMP; OR SUBSTR(Field1, INSTR(Field1,':')+1)

LTRIM(ENAME,'S')
--------------------
MITH
ALLEN
WARD
JONES
MARTIN
TONECOLD
TONECOLD



SQL> SELECT LTRIM(ENAME,'CSS-, *^') LTRIM FROM EMP1;

LTRIM
-------
KING
BLAKE
LARK
JONES
OTT
FORD
MITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
KING%$

KING-AXYZ


SQL> SELECT RTRIM(ENAME,'D') FROM EMP;

RTRIM(ENAME,'D')
--------------------
SMITH
ALLEN
WAR
JONES
MARTIN
STONECOL
STONECOL


SQL> SELECT LTRIM(ENAME,'S,M'), RTRIM(ENAME,'H,N') FROM EMP;

LTRIM(ENAME,'S,M')   RTRIM(ENAME,'H,N')
-------------------- --------------------
ITH                  SMIT
ALLEN                ALLE
WARD                 WARD
JONES                JONES
ARTIN                MARTI
TONECOLD             STONECOLD
TONECOLD             STONECOLD


SQL> SELECT LPAD(ENAME,10,'0') FROM EMP;

LPAD(ENAME
----------
00000SMITH
00000ALLEN
000000WARD
00000JONES
0000MARTIN
0STONECOLD
0STONECOLD


SQL> SELECT RPAD(ENAME,10,'0') FROM EMP;

RPAD(ENAME
----------
SMITH00000
ALLEN00000
WARD000000
JONES00000
MARTIN0000
STONECOLD0
STONECOLD0

SQL Joins

If you want to fetch multiple data types of data from multiple tables we can write joins. Joins combines two or more tables. 
There are different types of joins like left, right, inner, outer, full join.

Self-join :-
A Table which is joined by itself is know as self-join. 
Alias names like E1,E2 are given as prefix to column with condition to impliment selfjoin.

(select e1.eno, e2.ename, e1.dno from emp e1, emp e2 where e1.eno = e1.eno) 
                                           or      
(select d1.dno, d2.dname from dept d1, dept d2 where d1.dno=d2.dno)

Result: 

Equi-join :-
Where equal to operator is used. or we select both the table and give condition between both tables.

(select * from emp, dept where dept.dno = emp.dno;)
                                  or
(SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;) 

Result: 

Non equi join :-
Where equal to operator is not used in the query it is called as Non equi join.

( select * from emp, dept where dept.dno > emp.dno ).

Result: 

Natural join :- 
It return only matching columns

(select * from emp natural join dept)

Result:

Cross Join :-
It multiplies first table by the number of rows in the second table.
(select * from emp cross join dept)

Result:

Inner Join :-
It returns matching rows from both the tables.

(SELECT * FROM EMP 

INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO)
                            or

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO AND DNAME ='ACCOUNTING'

Result:

Left outer join :- 
It is  used to get 'all rows from left table and only matching rows from right table'. A condition should be given to execute an left outer join.

(select * from emp  left outer join dept on emp.dno=dept.dno)
                                           or
(SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
LEFT OUTER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO)

Result:

Right Outer Join :-
It is  used to get 'all rows from Right table and only matching rows from Left table'. A condition should be given to execute an right outer join

(select * from emp right outer join dept on emp.dno=dept.dno)
                                            or
(SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO)
                                            or
(SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO=D.DEPTNO)

Result:

The FULL OUTER JOIN :-
This keyword return all records when there is a match in either left (table1) or right (table2) table records. A condition should be given to execute an right outer join

(select * from emp right outer join dept on emp.dno=dept.dno)
                                                or
(select * from emp2013 full outer join emp2014 on emp2013.id = emp2014.id)
                                                or
(SELECT * FROM EMP E FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO)

Result: