SQL Statement Used In This Course:-
1. Data Manipulate Lang(DML):-
** 1 Insert 2 Update 3 Delete 4 Merge**
2. Data Definition Lang(DDL):-
** 1 Create 2 Alter 3 Drop 4 Rename 5 Truncate**
3. Data Control Lang(DCL):-
** 1 Grant 2 Revoke**
4. Transaction Control Lang:-
** 1 Commit 2 Rollback 3 Save point**
-----------------------------------------------------------------------------------
-----------------------------------------------
Oracle Data Types:-
1 Varacher2(size):- Variable-length character data
2 CHAR(size):- Fixed-length character data
3 Number(p,s):- Variable-length numeric data
4 Date:- Date and time values
5 LONG:- Variable-length character data (up to 2 GB)
6 Row and Long Row:- Row binary data
7 BLOB :- Maximum size is (4 gigabytes-1)*(DB_BLOCk_SIZE initialize parameter (8 TB
to 128 TB)
8 BFILE :- Binary data stored in an external file(up to 4 GB)
9 ROWID :- A base 64 number system representing the address of a row in its table
-----------------------------------------------------------------------------------
----------------------------------
Describe Command :-
Provides a describe of the specified table.
Return Column names, nullable or not, and datatypes.
** DESC[RIBE] table_name;**
Can be used with DESC or DESCRIBE keywords.
** --------------------------------------------------------------------------------
------------------**
SQL Statement Basic :-
** **
** SQL statements are not case-sensitive.**
** SQL statements can be separated or lines.**
** Keywords cannot be abbreviated or splitted.**
** In SQL Developer, SQL statement can be terminated by a semicolon ";" or a
slash "/" sign. Query execution continues until these signs. So semicolons should
be used in writing SQL statements.**
** In SQL*Plus, you are required to end each SQL statement with a semicolon
";".**
-----------------------------------------------------------------------------------
----------------------------
Using SELECT Statement:-
** EX:- SELECT * {column_name1, column_name2,.....} from table_name;**
** Retrieves data from database.**
** "*" retrieves all data without knowing table metadata.**
** You can retrieve specific columns with writing column names.
** EX:- SELECT * FROM table_name;**
-----------------------------------------------------------------------------------
----------------------------------------
Using Column Alias:-
** Rename a column heading.**
** As keyword is used to increase readability.**
** Useful for calculations**
** Requires double quotation marks if used with space , special characters or
handling case-sensitivity.**
** EX:- SELECT first_name AS name,Last_name,"Last Name", salary+12,"Annual
sal", From emp;**
-----------------------------------------------------------------------------------
--------------------------------------------
Using Quote(q)Operator:-
** **
** Quotation mark is used to increase readability and usability.**
** **
** EX:- Select q'[My Name is Shantilal and my friend's name is Vaibhav ]'**
** my_text From Dual;**
** **
** You can use any character as quotation mark delimeter.**
** - [],{},(),<>,or even any character like 'A','*'...**
** - Generally [] is used as quotation mark delimiter.**
** **
** EX:- Select q'*My name is Shantilal *'text1,q'My name is Shantilal' text2**
** From Dual;**
-----------------------------------------------------------------------------------
---------------
CONCATENATION OPERATORS:-
** **
** Concatenation column or strings and returns as a single output column
value.**
** Can be done by two vertical bars(||).**
** Concatenating with NULL value does not return NULL, it returns the other
character strings.**
** Using with alias increases readability.**
EXAMPLE:- select first_name||'-'||last_name as "my_name" from emp;
** select city ,state_province, city||'-'|| state_province from loc;**
-----------------------------------------------------------------------------------
-------------------------
ARITHMETIC EXPRESSIONS AND NULL VALUES:-
** Arithmetic expression are used for making arithmetic operations with number
and date values.**
** **
** EX:- select employee_id,salary,salary*12 as "annual salary " from emp;**
** **
** Multiplication and Division operates before addition and subtraction.**
** Use parentheses to change precedence and increase readability.**
** OPERATOR DESCRIPTION**
** + Add**
** - Subtract**
** * Multiply**
** / Divide**
** **
** EX:- select employee_id,salary,(salary+200)*12 as "annual salary" from emp;**
** Arithmetic operations with date values return new date values.**
** **
** Example:- select sysdate+3 from dual;**
** **
** Arithmetic operation with NULL values returns NULL.**
** NULL is a value that is unassigned, unavailable, unknown value. That NULL is
not a zero or a space.**
-----------------------------------------------------------------------------------
------------------------------------------------------
USING WHERE CLAUSE:-
** **
** Where clause limits the row by a query.**
** **
** Syntax:- select *|{[DISTINCT] column [alias],...}**
** from table_name**
** [Where logical expression(s)];**
** **
** Where clause is use with comparison operators
(=,<,>,<=,>=,<>,BETWEEN,IN,LIKE and NULL) and logical operators like AND,OR,NOT
operators.**
-----------------------------------------------------------------------------------
-------------------------------------------------------------------
Using Comparison Operators:-
** Comparison Operator:<,>,<=,>=,<>,!= and BETWEEN,LIKE,IS NULL.**
** **
** Syntax:- select *|{[DISTINCT] column [alias],...}**
** from table**
** [Where logical expression (s)];**
-----------------------------------------------------------------------------------
-----------------------------------------------------
Using BETWEEN....AND Operator:-
** **
** Retrieves data between the lower limit and upper limit. Lower and Upper limit
values are INCLUDE!**
** Many data types like number, date, character, values can be restricted with
between operator.**
** **
** Ex Syntax:- select first_name,last_name,salary from emp**
** where salary BETWEEN 120000 AND 150000;**
-----------------------------------------------------------------------------------
--------------------------------------------
USING IN OPERATOR:-
** IN operator retrieves the restricted values in the specified list.**
** Many data types like number, date, character, values can be restricted with
IN operator.**
** Order of the specified values is not important.**
EX:- select employee_id, first_name, last_name, salary, manager_id
** from emp**
** where employee_id IN (100,105,102,200);**
-----------------------------------------------------------------------------------
---------------------------------------------------------
USING LIKE OPERATOR:-
** Like operator is used for searching in string values.**
** %_character is used with LIKE operator.**
** **
** %means 0 or more characters.**
** _means 1 character.**
** Like operator can be used without wildcard character(%,_)but is nonsense
because it will be equal to(=) and LIKE is slower than(=) operator .**
** **
** EX:- select first_name,last_name**
** from emp**
** where first_name LIKE 'A%';**
** EX:- select first_name**
** from emp**
** where first_name LIKE '_E%';**
-----------------------------------------------------------------------------------
---------------------------------------------------
USING IS NULL OPERATOR:-
** **
** IS NULL operator is used for searching NULL values.**
** = NULL is not the same as IS NULL, because NULL means nothing and can not be
equal or unequal to any value.**
** **
** EX:- select first_name**
** from emp**
** where manager_id = NULL;**
** **
** NOT EQUAL**
** EX:- select first_name**
** from emp**
** where manager_id IS NULL;**
-----------------------------------------------------------------------------------
--------------------------------------------------
** **
USING LOGICAL OPERATOR:-
** Logical operators are used to increased restriction level which means
restricts data to be retrieved with more than one condition.**
** **
** OPERATOR MEANING**
** AND Return TRUE if both component conditions**
** are true.**
** OR Returns TRUE if either component**
** condition is true.**
** NOT Return TRUE if the condition is false**
** **
** 1) AND(OPERATOR):- AND means both condition must be true.**
** **
** AND TRUE FALSE NULL**
** --------------------------------------------------------**
** TRUE TRUE FALSE NULL**
** FALSE FALSE FALSE FALSE**
** NULL NULL FALSE NULL**
** -----------------------------------------------------------**
** **
** EX:- select first_name,last_name,salary, job_id**
** from emp**
** where job_id='IT_PROG'AND salary>=5000;**
** 2) OR(OPERATOER):- OR means one or more condition must be true.**
** OR TRUE FALSE NULL**
** -----------------------------------------------**
** TRUE TRUE TRUE TRUE**
** FALSE TRUE FALSE NULL**
** NULL TREUE NULL NULL**
** ------------------------------------------------**
** EX:- select first_name,last_name,salary,job_id**
** from emp**
** where job_id = 'IT_PROG' OR salary>=5000;**
** **
3) NOT(OPERATOR):- NOT operator is used to retrieve data that doesn't provide
conditions.
** **
** NOT TRUE FALSE NULL**
** ---------------------------------------------------------**
** FALSE TRUE NULL**
** ----------------------------------------------------------**
** EX:- select first_name,last_name,job_id,salary**
** from emp**
** where salary>10000 AND job_id NOT IN('SA_MAN','ST_CLERK');**
-----------------------------------------------------------------------------------
---------------------------------------------------------
RULES OF PRECENDENCE:-
** We should use parentheses to avoid logical order confusion or change the
order explicitly.**
** EX:- select first_name,last_name,job_id,salary**
** from emp**
** where job_id = 'IT_PROG' OR job_id = 'ST_CLERK'**
** AND salary > 5000;**
** EX:- select first_name,last_name,job_id,salary**
** from emp**
** where(job_id = 'IT_PROG' OR job_id = 'ST_CLERK')**
** AND salary > 5000;**
-----------------------------------------------------------------------------------
--------------------------------------------------
SECTION 3
SORTING DATA
USING OREDER BY CLAUSE:-
** Sorts the retrieved rows in ascending (ASC) or descending (DESC) order.**
** Default order is Ascending.**
** **
** EX:- select first_name,last_name,salary**
** from emp**
** order by first_name;**
-----------------------------------------------------------------------------------
-------------------------------------------------------
USING ASC and DESC OPERATORS:-
** **
** Queries can be sorted with multiple columns, in ASC or DESC order
individually.**
** Order by can be used with the given alias in select statement.**
** NULL values are displayed last in ascending order.**
EX:- select first_name,last_name,salary
** from emp**
** order by first_name ASC , last_name DESC;**
-----------------------------------------------------------------------------------
------------------------------------
NULLS FIRST and NULLS LAST OPERATORS:-
** **
** NULL FIRST or NULLS LAST can be used to changed order of null values.**
** Sorting can be done with columns numeric position.**
EX:- select first_name, last_name
** from emp**
** order by 1 desc, 2 asc;**
-----------------------------------------------------------------------------------
----------------------------------------------
SECTION -4
SINGLE-ROW FUNCTION:-
** **
** FUNCTION :- Function is nothing but set of instruction or list of instruction
to perform specific task.**
** Function are used (created) for frequently used codes.**
** There are two types of function.**
** 1) Single-row functions**
** 2) Multiple-row functions**
1) Single-row Function:-
** single-row function execute row by row.**
** Accept one or more arguments and returns one value.**
** Can be used alone or nested.**
** Can be used in SELECT,WHERE,ORDER BY clauses.**
** Return data type can be different than input values.**
EX SYNTAX:-
** SELECT FUNCTION_NAME [(ARG1,ARG2,...)]FROM DUAL;**
** Single-row function type:-**
** 1) Character function:- accept character as input value and return
character or number values.**
** 2) Number Function:- Accept numeric as input and numeric as output.**
** 3) Date function :- Operate on values of the DATE data type.**
** 4) Conversion Function:- Convert a value from one data type to another.**
** 5) General Function:- These function take any data type and can also handle
NULLS.**
-----------------------------------------------------------------------------------
-------------------------------------------------------
1) CHARACTER FUNCTION :-
** Character function get character data as input and return
character or numeric data as output.**
** -Two different types of character function**
** 1) CASE conversion function :-**
** - LOWER FUNCTION:-Convert all the input to lowercase.**
** - UPPER FUNCTION:-Covert all the input to uppercase.**
** - INITCAP FUNCTION:-Convert first characters of each world to
uppercase and lowercase the rest.**
** Ex:- SELECT UPPER(FIRST_NAME),LOWER(LAST_NAME),INTICAP(JOB_ID)**
** FROM EMP;**
** 2) Character MANIPULATION function:-**
** - SUBSTR function ('sql course',1,3)->sql**
** - LENGTH function ('sql course')-> 10**
** - CONCAT function ('sql','course')-> sqlcourse**
** - INSTR function ('sql course','o')->6**
** - TRIM function (' sql course ')->sql course**
** - LTRIM function (' sql course ')->sql course**
** - RTRIM function ('sql course ')->sql course**
** - REPLACE function ('sql course','s','*')->sql cour*e**
** - LPAD function('sql',10,'-')-> -------sql**
** - RPAD function ('sql',10,'-')-> sql------
-----------------------------------------------------------------------------------
---------------------------------------------------------------------
NUMBER FUNCTION:-
** Numeric functions get number data type as input and return a number
value.**
** 1) ROUND:- Round value to a specified decimal.**
** 2) TRUNC:- Truncate values to a specified decimal.**
** 3) CEIL:- Returns the smallest integer number greater or equal to a
specified number.**
** 4) FLOOR:- Returns the largest integer number equal or less than a
specified number.**
** 5) MOD:- Returns remainder of division.**
-----------------------------------------------------------------------------------
-------------------------------------------------------------------
** **
NESTING FUNCTIONS:-
** **
** We can use a function into another function. This is called as NESTING
FUNCTION.**
** Result of the innest function will be the input of the outer
function.**
** We can use as many function as we want nestedly.**
EX:- SELECT
FIRST_NAME,LAST_NAME,LPAD(UPPER(CONCAT(FIRST_NAME,LAST_NAME)),20,'*')NESTING
FROM EMP;
-----------------------------------------------------------------------------------
------------------------------------------------------------------------
DATE OPERATIONS-DATE FUNCTION:-
** **
** Oracle database stores dates in internal numeric format but we see dates in
different formats like('DD-MON-RR',DD/MM/YYYY).**
** Date value includes century , year, month, day, hour, minutes and second.**
** By default, date display format is 'DD-MON-RR'.**
** RR date format means different as system's date.**
-----------------------------------------------------------------------------------
-------------------------------------------------------
USING SYSDATE and CURRENT_DATE Functions:-
** **
** - SYSDATE:-Returns the system's date.**
** - CURRENT_DATE:- Returns current date from the user's session .**
** - SESSIONTIMEZONE:- Returns timezone of the user's session.**
** - CURRENT_TIMESTAMP:- Returns current date and time from user's session.**
EX:- SELECT CURRENT_DATE,SESSIONTIMEZONE,CURRENT_TIMESTAMP FROM EMP;
-----------------------------------------------------------------------------------
-----------------------------------------------------------
ARTHIMETIC OPERATION WITH DATES:-
** We can add or subtract a number from a date and that results date again.**
** Subtracting two dates returns number of days between these dates.**
EX:-
** SELECT FIRST_NAME,LAST_NAME,(SYSTEM-HIRE_DATE)/365 AS "WORKING PERIOD"**
** FROM EMP;**
-----------------------------------------------------------------------------------
---------------------------------------------------
DATE FUNCTIONS:-
** **
** Date function operators with dates and returns dates, number or texts.**
** FUNCTIONS RESULT**
-----------------------------------------------------------------------------------
---------
** ADD_MONTHS Add specific month to specified date**
** MONTHS_BETWEEN Number of months between two dates**
** ROUND Round date**
** TRUNC Truncate date**
** NEXT_DAY Returns next specified day of week**
** LAST_DAY Returns last day of the month**
-----------------------------------------------------------------------------------
---------
** ADD_MONTHS('31-AUG-15',1) '30-SEP-15'**
** MONTHS_BETWEEN('03-SEP-15','18-FEB-15') 6.51612903225**
** ROUND(sysdate,'MONTH') '01-JAN-15'**
** TRUNC(sysdate,'year') '01-jan-15'**
** NEXT_DAY('04-JUN-15','TUESDAY') '11-JUN-15'**
** LAST_DAY('04-JUN-15') '30-JUN-15'**
-----------------------------------------------------------------------------------
-----
** EX:-**
** select sysdate from dual;**
** select sysdate,add_months(sysdate,2) from dual;**
** select sysdate,add_months(sysdate,-2)from dual;**
** select sysdate,months_between('03-sep-15','18-feb-15')from dual;**
** select sysdate,round(sysdate,'month')from dual;**
** select sysdate,trunc(sysdate,'year')from dual;**
** select sysdate,next_day('04-jun-15','tuesday')from dual;**
** select sysdate,last_day('04-jun-15')from dual;**
-----------------------------------------------------------------------------------
-
SECTION 5:-
CONVERSION FUNCTIONS:-
** **
** Conversion data to another data type.**
** **
** 1) Implicit Conversion:- Oracle servers automatically conversion.**
** Although implicit conversion convert some data type automatically it
is recommended to use explicit conversion function to ensure reliability of SQL
statements.**
** 2) Explicit Conversion:- Conversion with using conversion function.**
** **
** CONVERSION FUNCTIONS---------->IMPLICIT DATA TYPE CONVERSION**
** **
** 1) IMPLICIT DATA TYPE CONVERSION:-**
** In SQL queries while comparison or equalization occurs between two
different data types and this data types are characters,**
these can be automatically converted number or date by the Oracle server.
** FROM TO**
---------------------------------------------
VARCHAR2 OR CHAR NUMBER
VARCHAR2 OR CHAR DATE
-----------------------------------------
EX:-
SELECT FIRST_NAME,LAST_NAME,HIRE_DATE
FROM EMP
WHERE HIRE_DATE='21/SEP/05';
2) EXPLICIT DATA TYPE CONVERSION:-
** Used converting data to another data type explicitly.**
TO_NUMBER-->NUMBER-->TO_CHAR-->CHARACTER.
TO_DATE-->DATE-->TO_CHAR-->CHARACTER.
USING TO_CHAR FUNCTION:-
** **
SYNTAX:- TO_CHAR(date|number[,'format_model]')
** **
** Convert data to character data type.**
** Case sensitive**
** Can convert number or date values to character data type.**
** Format model is used to convert character data.**
EX:-
** SELECT FIRST_NAME,LAST_NAME,**
** TO_CHAR(HIRE_DATE,'YYYY')**
** FROM EMP**
** WHERE TO_CHAR(EMPLOYEE_ID)=100;**
FORMATE MODEL ELEMENTS FOR DATES:-
** ELEMENT RESULT**
-----------------------------------------------------------------------------------
-
** YYYY FULL YEAR IN NUMBER (2025)**
** RR-YY Last two digits of the year(25)**
** YEAR Spelling of the year in English(twenty fifteen)**
** MM Two digit of the month(25)**
** MON Three letter abbreviation of the month('sep')**
** MONTH Full name of the month in English ('September')**
** DD Two digit number of the month (25)**
** DY Three letter abbreviation of the day('sat')**
** DAY Full name of the day in English('saturday')**
** HH(HH12-HH24) Two digit hour in 12-24 format(06)(18)**
** MI Two digit minutes(29)**
** SS Two digit second(52)**
** TH Ordinal Number(DDTH->5TH)**
** SP Spelling of number (DDSP--> FOUR)**
** SPTH OR THSP Spelling of ordinal number(DDSPTH->FOURTH)**
-----------------------------------------------------------------------------
FORMAT MODEL ELEMENTS FOR NUMBER:-
** **
** Display number format elements are used in TO_CHAR function to display in a
different format.**
** ELEMENT RESULT**
------------------------------------------------------
** 9 Represent a number**
** 0 Display zero**
** $ Display a dollar sign**
** L Display local currency symbol**
** . Display a decimal point**
** , Display comma as indicator**
------------------------------------------------------------
** EX:-**
** SELECT TO_CHAR(SALARY,'$99,999.99'),**
** SALARY FROM EMP;**
** **
** EX:-**
** SELECT SALARY*COMMISSION_PCT BEFORE_FORMAT,**
** TO_CHAR(SALARY*COMMISSION_PCT,'$099,999.99')FORMATTED**
** FROM EMP**
** WHERE COMMISSION_PCT IS NOT NULL;**
-----------------------------------------------------------------------------------
---------------------------------
TO_NUMBER FUNCTION:-
** SYNTAX:-**
** TO_NUMBER(CHAR[,'FORMAT_MODEL'])**
** **
** Convert a character string to a number with the given format model.**
EX:-
** SELECT TO_NUMBER('$6,152.21','$99,999.99')**
** FROMATTED_NUMBER FROM DUAL;**
------------------------------------------------------------------------------
TO_DATE FUNCTION:-
** **
** SYNTAX:-**
** TO_DATE(CHAR[,'FORMAT_MODEL'])**
** Convert a character string to a date with the given format model.**
EX:-
** SELECT FIRST_NAME,LAST_NAME,**
** TO_CHAR(HIRE_DATE,'DDSPTH Month YYYY')"HIRE_DATE"**
** FROM EMP**
** WHERE HIRE_DATE > TO_DATE('JUN 12,2005','Mon DD,YYYY');**
-----------------------------------------------------------------------------------
------------
NVL FUNCTION:-
** **
SYNTAX:-
** NVL(Expression1, Expression2)**
** **
** If Expression1 is NULL, then returns Expression2.**
** Data types can be character, number or date.**
** Data types must match(number-number, character-character,---)**
** Especially useful in arithmetic operations to avoid calculation error.**
Ex:-
** SELECT JOB_ID,FIRST_NAME,LAST_NAME,**
** NVL(COMMISSION_PCT,0)**
** COMM_PCT,COMMISSION_PCT**
** FROM EMP**
** WHERE JOB_ID IN('SA_REP','IT_PROG');**
-----------------------------------------------------------------------------------
----------------------
NVL2 FUNCTION:-
SYNTAX:-
** NVL2(Expression1,Expression2,Expression3)**
** If Expression1 is not NULL, then returns Expression2, return Expression3.**
** Expression1 dose not have to be same data type with Expression2 and
Expression3, but Expression2 and Expression3 must be same data type.**
** Data types can be character, number or date.**
** **
EX:-
** SELECT JOB_ID,FIRST_NAME,LAST_NAME,**
NVL2(COMMISSION_PCT,'HAS','HAS NOT')
COMM_PCT,COMMISSION_PCT
FROM EMP
WHERE JOB_ID IN('SA_REP','IT_PROG');
-----------------------------------------------------------------------------------
------------------------------------------------------
** **
NULLIF FUNCTION:-
** **
** SYNTAX:-**
** NULLIF(Expression1,Expression2)**
** **
** Compares Expression1 and Expression2. If they are equal returns NULL. If
they are not equal returns Expression1.**
** Expression1 and Expression2 must be same data type.**
Ex:-
** SELECT FIRST_NAME,LAST_NAME,LENGTH(first_name)**
** "Expression1",LENGTH(last_name)**
** "Expression2"**
** FROM EMP**
** NULLIF(LENGTH(first_name),**
** LENGTH(last_name))is null;**
-----------------------------------------------------------------------------------
-------------------------------------------------------
** **
COALESCE FUNCTION:-
** **
** SYNTAX:-**
** COALESCE(Expression1, Expression2,....,ExpressionX)**
** COALESCE is an advanced function of NVL function.**
** It can take multiple alternative values.**
** IF Expression1 us null then look at Expression2. if is NULL either then look
at the next one. this will continue until the ExpressionX arrives.**
** Returns first not NULL values or Expression if all the previous values NULL.**
** All the expression must be of the same data type.**
EX:-
** SELECT STATE_PROVINCE,CITY,COALESCE(state_province,city,'not as')**
** FROM LOCTION;**
**---------------------------------------------------------------------------------
---------------------------------------------------------------- **
** **
SECTION 6:-
CONDITIONAL EXPRESSIONS:-
CASE EXPRESSION:-
Used for providing if-them-else logic in SQL statements.
Expr and comparison_expr must be of the same data type.
can be used in both SELECT and WHERE clauses.
Syntax:-
CASE expr When comparison_expr1 Then return_expr1
[When comparison_expr2 Then return_expr2
When comparison_exprn Then return_exprn
ELSE else_expr]
END
1) CASE expression in SELECT Statement:-
Syntax:-
SELECT first_name,last_name,job_id,salary,hire_date,
CASE job_id WHEN 'ST_MAN' THEN 1.20*salary
WHEN 'SH_MAN' THEN 1.30*salary
WHEN 'SA_MAN' THEN 1.40*salary
ELSE salary END "UPDATED_SALARY"
FROM Employees WHERE job_id
IN('ST_MAN','SH_MAN','SA_MAN');
2) CASE Expression in WHERE Clause:-
SYNTAX:-
SELECT first_name,last_name,job_id,salary
FROM EMP WHERE (CASE WHEN job_id='IT_PROG'
AND salary > 5000 THEN 1
WHEN job_id = 'SA_MAN'
AND salary > 10000 THEN 1
ELSE 0
END)= 1;
-----------------------------------------------------------------------------------
--------------------------------------------
DECODE FUNCTION:-
DECODE Function is Oracle's specify, easy to use alternative of CASE
expression.
Use DECODE for simple mappings or equality checks.
It is used to provide if-then-else logic.
Search expression and result expression must be of the same data type
internally.
Which means all the search expressions are of the same data type.
And all the result expression are of the same data type. But search and
result expressions doesn't have to be of the same data type.
Syntax:-
DECODE(col | expression,search1,result1,result1
[,search2,result2,....,]
[,default]);
EXAMPLE:-
SELECT first_name,last_name,job_id,salary,hire_date,
DECODE(job_id,'ST_MAN',1.20*salary,
'SH_MAN',1.30*salary,
'SA_MAN',1.40*salary) Updated_salary
FROM Empployees WHERE job_id
IN('ST_MAN','SH_MAN','SA_MAN');
-----------------------------------------------------------------------------------
-------------------------------------------------