Oracle Tutorial Point

GROUP BY, ORDER BY, HAVING and Miscellaneous Functions in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sun, 05 Nov 2017 07:09:00 +0000

Read this Oracle Tutorial Point post to know when to use GROUP BY, ORDER BY, HAVING keywords and Miscellaneous functions in Oracle SQL. If you have any queries about this post, please comment on comments section.

Creating Groups of Data:
GROUP BY Clause:
The GROUP BY clause is used to decide the rows in a table into groups.

Syntax:

SELECT COLUMN, GRP_FUN (COLUMN)
 FROM TABLE
 WHERE CONDITION
 GROUP BY GRP_BY_EXPR
 ORDER BY COLUMN;

Conditions to use GROUP BY Clause:

If the GROUP function is included in a SELECT clause, we should not use individual results columns.
Those non GROUP functional columns should declare in GROUP BY clause.
Rows can be pre excluded before dividing them into GROUP by using WHERE clause.
Column aliases cannot be used in GROUP BY clause.
By default, rows are sorted by ascending order of the columns included in the GROUP BY list.
The column applied upon GROUP BY clause need not be part of SELECT list.

Example:

SQL> SELECT DNO, AVG(SAL) FROM EMP GROUPD BY DNO;
SQL> SELECT DNO, AVG(SAL) FROM EMP GROUPD BY DNO ORDER BY AVG(SAL);
SQL> SELECT DNO, MIN(SAL), MAX (SAL) FROM EMP GROUPD BY DNO;
SQL> SELECT DNO, JOB, SUM(SAL) FROM EMP GROUPD BY DNO,JOB;

The above specification falls under the principle of GROUP within GROUP’S.

SQL> SELECT DNO, MIN(SAL), MAX(SAL) FROM EMP WHERE JOB='CLERK' GROUP BY DNO;
SQL> SELECT DNO, SUM(SAL),AVG(SAL) FROM EMP WHERE JOB='CLERK' GROUP BY DNO;

Excluding Groups of Results:
HAVING Clause:
It is used to specify which groups are to be displayed.

Syntax:

SELECT COLUMN, GROUP_FUNCTION
 FROM TABLE
 [WHERE Condition]
 [GROUP BY Group_By_expr]
 [HAVING Group_Condition]
 [ORDER BY Column_Name/Alias];

What will do HAVING Clause?

Rows are Grouped
The Group function is applied to the group
The Groups that match the criteria in the HAVING Clause are displayed.
The HAVING clause can be precede GROUP BY clause. But, it is more logical to declare it before GROUP BY.
HAVING clause can be used without a GROUP function in the SELECT list.
If rows are restricted based on the result of a group function, we must have a GROUP BY clause as well as the HAVING Clause.

Example:

SQL> SELECT DNO,AVG(SAL) FROM EMP GROUP BY DNO HAVING MAX(SAL)>2000;
SQL> SELECT JOB, SUM(SAL) PAYROLL FROM EMP WHERE JOB NOT LIKE 'SALES%' 
     GROUP BY JOB HAVING SUM(SAL)>5000 ORDER BY SUM(SAL);
SQL> SELECT DNO, MIN(SAL),MAX(SAL) FROM EMP WHERE JOB='CLERK' 
     GROUP BY DNO HAVING MIN(SAL)<1000;
SQL> SELECT DNO,SUM(SAL) FROM EMP GROUP BY DNO HAVING COUNT(DNO) > 3;
SQL> SELECT DNO, AVG(SAL),SUM(SAL),MAX(SAL),MIN(SAL) FROM EMP 
     GROUP BY DNO HAVING COUNT(*)>3;

Nesting of Group Functions:
Group functions can be nested to a depth of two.

Example:

SQL> SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DNO;
SQL> SELECT MAX(SUM(SAL)),MIN(SUM(SAL)) FROM EMP WHERE GROUP BY DNO;
SQL> SELECT MAX(SUM(SAL)),MIN(SUM(SAL)) FROM EMP GROUP BY JOB;

Miscellaneous Functions:
GREATEST Function:
It returns the GREATEST of the list of exprs. All exprs after the first are implicitly converted to the data type of the first expr before the comparison. Oracle compares the exprs using non-padded comparison semantics. Character comparison is used on the value of the character in the data base character list.

Syntax: LEAST (expr1, expr2….)

Example:

SQL> SELECT LEAST('SUBBAIAH','SUBBAREDDY') FROM DUAL;
SQL> SELECT GREATEST(1000,2000,300) FROM DUAL;
SQL> SELECT GREATEST('20-JUN-16','01-OCT-16') FROM DUAL;

USER Function:
It returns the current Oracle USERS within the VARCHAR2 data type. The function cannot be used in the condition of the CHECK constraint.

Syntax: USER

Example:

SQL> SELECT USER FROM DUAL;

UID Function:
It returns an integer that uniquely identifies the current user.

Syntax: UID

Example:

SQL> SELECT UID FROM DUAL;
SQL> SELECT USER,UID FROM DUAL;

USERENV Function:
Returns information of VARCHAR2 data type above the current session.

Syntax: USERENV(Option)

The values in Options are,

ISDBA ==> Returns TRUE if DBA role is enabled.
LANGUAGE ==> Returns the LANGUAGE and territory used in the current session.
TERMINAL ==> Returns the OS identifier for the current session’s terminal.
SESSIONID ==> Returns the Auditing session identifier.
ENTRYID ==> Returns the available auditing entry identifier.
LANG ==> Returns the ISD abbreviation for the language name.
INSTANCE ==> Returns the instance identification number of the current instance.
CLIENT_INFO ==> Returns up to 64 bytes of User session information.

Example:

SQL> SELECT USERENV(‘LANGUAGE’) FROM DUAL;

VSIZE Function:
As per the below syntax if Expr is not NULL, this function returns the No of bytes in the internal representation of Expr. VSIZE function returns NULL when Expr is NULL.

Syntax: VSIZE(Expr)

Example:

SQL> SELECT ENAME,VSIZE(ENAME) FROM EMP;

SOUNDEX Function:
It returns a character string containing the phonetic representation of char. It allows comparison of words that are spelled differently. But, sounds like in English.

Syntax: SOUNDEX(CHAR)

Example:

SQL> SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX('SUBBAREDDY');
SQL> SELECT ENAME,JOB FROM EMP WHERE SOUNDEX(JOB)=SOUNDEX('SUBBAIAH');

Aggregate or Group functions in Oracle SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 04 Nov 2017 05:55:00 +0000

This Oracle tutorial point contains the information about the Aggregate functions or Grouping functions. The below is the list of functions discussed in the current post.

Average / AVG function
Summation / SUM function
Maximum / MAX function
Minimum / MIN function
Standard Deviation / STDDEV function
Variance function
Count function

These functions return a single row based on group of rows. These can appear in SELECT list and HAVING clauses. These functions operate on sets of rows to give one result per group. The sets may be the whole table or the table split into groups.

Guidelines to use Group Functions:

The DISTINCT keyword indicates not to consider duplicate values.
ALL indicates to consider each and every value including duplicates.

Syntax:

The data type for arguments may NUMBER, DATE, CHAR or VARCHAR.
All other group functions except COUNT(*) ignore NULL values. NVL function is used to substitute a NULL value.
When a group function is declared in a SELECT list, no single row columns should be declared.
Once a group function is declared in a SELECT list, other columns can be declared. But, they should be grouped columns and all non-functional columns should be declared into GROUP BY clause.

Average / AVG function:
As per the below syntax, it returns the average value of n and it ignores NULL values.

Syntax: AVG(DISTINCT / ALL / n)

Example:

SQL> SELECT AVG(SAL),AVG(DISTINCT SAL) FROM EMP;
SQL> SELECT AVG(COMM),AVG(DISTINCT AVG) FROM EMP;

Summation / SUM function:
As per the below syntax, it returns the SUM of the values of n and it ignores NULL values.

Syntax: SUM(DISTINCT / ALL / n)

Example:

SQL> SELECT SUM(SAL),SUM(DISTINCT SAL) FROM EMP;
SQL> SELECT SUM(COMM),SUM(DISTINCT AVG) FROM EMP;

Maximum / MAX function:
As per the below syntax, it returns the maximum value of n and it ignores NULL values.

Syntax: MAX(DISTINCT / ALL / n)

Example:

SQL> SELECT MAX(SAL), MAX(DISTINCT SAL) FROM EMP;
SQL> SELECT MAX(COMM), MAX(DISTINCT COMM) FROM EMP;

Minimum / MIN function:
As per the below syntax, it returns the minimum value of the n and it ignores NULL values.

Syntax: MIN(DISTINCT / ALL /n)

Example:

SQL> SELECT MIN(SAL), MIN(DISTINCT SAL) FROM EMP;
SQL> SELECT MIN(COMM), MIN(DISTINCT COMM) FROM EMP;

Standard Deviation / STDDEV function:
As per the below syntax, it returns the standard deviation value of n and it ignores the NULL values.

Syntax: STDDEV(DISTINCT / ALL / n)

Example:

SQL> SELECT STDDEV(SAL), STDDEV(DISTINCT SAL) FROM EMP;
SQL> SELECT STDDEV(COMM), STDDEV(DISTINCT COMM) FROM EMP;

Variance function:
As per the below syntax, it returns the variance of n and it ignores the NULL values.

Syntax: VARIANCE(DISTINCT / ALL / n)

Example:

SQL> SELECT VARIANCE(SAL), VARIANCE(DISTINCT SAL) FROM EMP;
SQL> SELECT VARIANCE(COMM), VARIANCE(DISTINCT COMM) FROM EMP;

Count function:
As per the below syntax, it returns the number of rows in the query. N evaluates to something other than NULL. It is used returns all rows, including duplicated and NULLs. It can be used to specify the count of all rows or only distinct values of n.

Syntax: COUNT(* / DISTINCT / ALL / n)

Example:

SQL> SELECT COUNT(*) FROM EMP;
SQL> SELECT COUNT(JOB),COUNT(DISTINCT JOB) FROM EMP;
SQL> SELECT COUNT(SAL),COUNT(COMM) FROM EMP;

If any queries or concerns about this post, please mention it in the comments section.

DATE format models in Oracle SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Wed, 26 Jul 2017 06:53:00 +0000

Read this oracle tutorila point article to know more about different DATE format models in SQL. The Date format models can be used in the TO_CHAR function to translate a DATE value from original format to user format. The total length of a date format model cannot exceed 22 characters.

Date Format Elements:
A date format model is composed of one or more date format elements.

For input format models, format items cannot seem double and format items that represent similar information cannot be combined.

Capitalization in a spelled out word, abbreviation or Roman numeral follows capitalization in the corresponding format elements. Punctuations such are hyphens, slashes, commas, periods and colons.

AD or A.D / BC or B.C:

Indicates AD/BC with or without periods.

Examples:

SQL> SELECT TO_CHAR(SYSDATE,'AD') FROM DUAL;
SQL> SELECT TO_CHAR(SYSDATE,'B.C'), TO_CHAR(SYSDATE,'A.D') FROM DUAL;
SQL> SELECT SAL, HIREDATE, TO_CHAR(HIREDATE,'A.D') FROM EMP;

AM or A.M. / PM or P.M.:

It indicates meridian indicator with or without periods.

Examples:

SQL> SELECT TO_CHAR(SYSDATE,'A.M.'),TO_CHAR(SYSDATE,'PM') FROM DUAL;
SQL> SELECT SAL, HIREDATE, TO_CHAR(HIREDATE,'AM') FROM EMP;

CC / SCC:

Indicates the century, S prefixes BC date with,

Examples:

SQL> SELECT TO_CHAR(SYSDATE,'SCC-AD') FROM DUAL;
SQL> SELECT SAL, HIREDATE, TO_CHAR(HIREDATE,'SCC-AD') FROM EMP;

D ==> Day of the Week (1-7)

Examples:

SQL> SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'D') FROM EMP;

Day ==> Indicates spelled name of the Week Day

Pads to a length of 9 characters.

Examples:

SQL> SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;
SQL> SELECT SAL, HIREDATE,TO_CHAR(HIREDATE,'DAY') FROM EMP WHERE TO_CHAR(HIREDATE,'DAY')='WEDNESDAY';

DD ==> It indicates the Day of the Month(1-31)

Examples:

SQL> SELECT TO_CHAR(SYSDATE,'DD-DAY') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'DD-DAY') FROM EMP;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'DD-DAY') FROM EMP WHERE TO_CHAR(HIREDATE,'DD-DAY')='03-WEDNESDAY';

DAY ==> Specifies in Upper Case
DAY ==> Specifies in Initcap Case
DAY ==> Specifies in Lower Case

DDD ==> It indicates the Day of the Year (1-366);

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'DDD') FROM DUAL;
SQL> SELECT ENAME, HIREDATE,TO_CHAR(HIREDATE,'DDD') FROM EMP WHERE TO_CHAR(HIREDATE,'DAY')='WEDNESDAY';

DY ==> It indicates the abbreviated name of the day.

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'D-DY-DAY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'D-DY-DAY') FROM EMP WHERE DNO IN(10,20);

IW ==> Specifies the week of the year (1-52 or 1-53) based on the ISO standard.

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'IW') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE, 'IW') FROM EMP;

IYYY ==> Specifies 4 digits year based on the ISO standard. Similarly IYY, IY.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'IYYY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'IYYY') FROM EMP;

YYYY / SYYYY ==> Returns 4 digit year, S prefixes BC dates with ‘-’. Similarly YYY / YY / Y.
Y, YYY ==> Returns year with comma in this position.

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY') FOUR, TO_CHAR(SYSDATE,'YYY') THREE, TO_CHAR(SYSDATE,'Y,YYY') COMMA FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'YYYY') FROM EMP WHERE DNO=20;

YEAR / SYEAR ==> Returns the spelled out year.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'YEAR') FROM EMP;

W ==> Specified the week of the Month(1-5). Weeks starts on the first day of the month and ends with the seventh.

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'W') FROM DUAL;
SQL> SELECT ENAME, HIREDATE, TO_CHAR(HIREDATE,'W') FROM EMP;

WW ==> Specifies the week of the year(1-54). Week starts on the first day of the Month and ends on the seventh.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'WW') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'WW') FROM EMP;

Q ==> Returns the quarter of the Year.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'Q') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'Q') FROM EMP;

J ==> Returns the JULIAN DAY.

It is the number of the day since January 1,4712 BC.
Numbers specified with ‘J’ must be integers.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'J') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TP_CHAR(HIREDATE,'J') FROM EMP;
SQL> SELECT ENAME,HIREDATE,TP_CHAR(HIREDATE,'J-DDD-DD-DD') FROM EMP;

MM ==> Returns the digit numeric abbreviation of the Month.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'MM-YYYY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'MM-YYYY') FROM EMP;

MON ==> Returns the abbreviated name of the Month.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'MM-MON') FROM DUAL;
SQL> SELECT HIREDATE,TO_CHAR(HIREDATE,'MM-MON') FROM DUAL;

MONTH ==> spells the name of the month, padded to a length of 9 characters.

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'MM-MONTH') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'MM-MONTH') FROM EMP;

HH / HH12 ==> Returns the Hour of the day in twelve hour clock mode.

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'HH'),TO_CHAR(SYSDATE,'HH12,AM') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'HH12:AM') FROM EMP;

HH24 ==> Returns the Hour of the day in twenty four hour clock mode. (0-23)

Examples:

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'HH24') FROM DUAL;
SQL> SELECT ENAME, HIREDATE,TO_CHAR(HIREDATE,'HH24') FROM EMP WHERE DNO=10;

MI ==> Returns the Minutes from the given date (0-59)

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'MI'),TO_CHAR(SYSDATE,'HH:MI') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'MI'),TO_CHAR(HIREDATE,'HH:MI') FROM EMP WHERE DNO=10;

RM ==> Returns the Roman numeral Month (I - XII)

Examples:

SQL&gt; SELECT SYSDATE,TO_CHAR(SYSDATE,'RM') FROM DUAL;
SQL&gt; SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DD-RM-YY') FROM EMP;

SS ==> Returns seconds from the given date (0-59)

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'SS'),TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;
SQL> SELECT ENAME, HIREDATE,TO_CHAR(HIREDATE,'HH:MI:SS') FROM EMP WHERE DNO IN (10,20);

SSSS ==> Display seconds past midnight (0-86399)

Examples:

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'SSSS') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DD-MM-YY, HH:MI:SS, SSSS') FROM EMP;

The punctuation marks that can be used in Date formats are (- ; / ; !; .; ; ; : ; ‘text’)

RR Date Format Elements:

The RR date format element is similar to the YY date format element.
The RR format element provides additional flexibility for storing date values in other Centuries.
The RR date format element allows to store the date to the previous as well as the text Centuries.
If the last 2 digits of the Century Year are:

                    0-49     50-99
0-49 Returns date is in the Current Century. the returns date is in the Preceding Century. 
50-59 Returns date in the next century. Returns date in the current century.

Date Format Element Suffixes:

TH ==> Specifies the Ordinal number.

Examples: DDTH 20th

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDTH, MONTH, YYY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE, 'DDTH, MONTH, YYYY') FROM EMP;

SP ==> Spells Numbers

Examples: DDSP ==> TWENTY

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSP, MONTH, YYYY') FROM DUAL;
SQL> SELECT ENAME,SAL,TO_CHAR(HIREDATE,'DDSP, MONTH, YYYY') FROM EMP;

Spelled Ordinal number ==> Spells Numbers

Examples: DDSPTH ==> TWENTIETH

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSPTH, MONTH, YYYY') FROM DUAL;
SQL> SELECT ENAME,SAL,TO_CHAR(HIREDATE,'DDSPTH,MONTH,YYYY') FROM EMP;
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSPTH MONTH YYYYSP') FROM DUAL;

Date Format Elements Restrictions:

The suffixes when added to date return value always in English. Date suffixes are valid only on output, hence cannot be used to insert a date into the database.

Format Model Modifiers:

FM (Fill Mode) ==> it suppresses blank padding in the return value of the TO_CHAR function.
FX (Format Exact) ==> it specifies exact matching for the character argument and date format model.

Examples:

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,'DDSPTH MONTH YYYYSP'), TO_CHAR (SYSDATE,'FMDDSPTH MONTH YYYYSP') FROM DUAL;

TO_NUMBER Function:

In the below syntax it converts a char. Value of CHAR or VARCHAR2 data type containing a Number in the format specified by the optional format model fmt, to a value of NUMBER data type.

Syntax: TO_NUMBER(Char,fmt,’nlsparam’)

Examples:

SQL> SELECT TO_NUMBER('$10,000.00','L99,999.99') FROM DUAL;
SQL> SELECT TO_NUMBER('$1,000.00','L9,999.99') FROM DUAL;

TO_DATE Function:
In the below syntax, converts char of CHAR or VARCHAR2 datatype to a value of DATE data type. The fmt is a date format specifying format of char.

Syntax:/b> TO_DATE(Char,fmt,'nlsparam');

Examples:

SQL> SELECT ENAME, HIREDATE, ADD_MONTHS(TO_CHAR('17-DEC-1980','DD-MON-YY'),3) FROM EMP;

Specification Examples:

SQL> SELECT TO_CHAR(ADD_MONTHS(HIREDATE,1),'DD-MON-YYYY') "NEXT_MONTH" EMP WHERE ENAME='SMITH';
SQL> SELECT CONCAT(CONCAT(ENAME,' IS A '),JOB) DESIGNATION FROM EMP WHERE ENO=7900;
SQL> SELECT TRUNC(TO_DATE('01-OCT-90','DD-MON-YY'),YEAR) "NEW YEAR" FROM DUAL;
SQL> SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(HIREDATE),5),'DD-MON-YYYY') "FIVE_MONTHS" FROM EMP WHERE ENAME='MARTIN';
SQL> SELECT MONTHS_BETWEEN(TO_DATE('06-06-2016','MM-DD-YYYY'),TO_DATE('01-01-2016','MM-DD-YYYY')) MONTHS FROM DUAL;
SQL> SELECT NEXT_DAY('15-JUN-2016','Wednesday') "NEXT DAY" FROM DUAL;
SQL> SELECT ENAME,NVL(TO_CHAR(COMM),'NOT APPLICABLE') "COMMISSION” FROM EMP WHERE DNO=10;
SQL> SELECT ROUND(TO_DATE('14-06-2016','YEAR')) "NEW YEAR" FROM DUAL;
SQL> SELECT TO_CHAR(TO_DATE('14-JUN-16','DD-MON-RR'),'YYYY') YEAR FROM DUAL;

Aggregate or Group Functions in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Wed, 26 Jul 2017 06:52:00 +0000

DISTINCT keyword makes the function consider only non-duplicate values.
ALL keyword makes the function to consider every value including duplicates.

Syntax:

The data type for arguments may CHAR, VARCHAR, NUMBER or DATE.
All group functions except COUNT (*) ignore NULL values. The NVL function is used to substitute a value for NULL values.
If a GROUP function declared in a SELECT statement, no single row columns should be declared.
When a group function is declared in a SELECT list, other columns can be declared. But, they should be grouped columns and all the non-functional columns.

Average Function:
As per the below syntax, it returns the average value of n and it ignores NULL values.

Syntax: AVG(DISTINCT / ALL / n)
Examples:

SQL> SELECT AVG(SAL),AVG(DISTINCT SAL) FROM EMP;
SQL> SELECT AVG(COMM),AVG(DISTINCT AVG) FROM EMP;

SUM Function:
As per the below syntax, it returns the SUM of the values of n and it ignores NULL values.

Syntax: SUM(DISTINCT / ALL / n)
Examples:

SQL> SELECT SUM(SAL),SUM(DISTINCT SAL) FROM EMP;
SQL> SELECT SUM(COMM),SUM(DISTINCT AVG) FROM EMP;

MAX (Maximum) Function:
As per the below syntax, it returns the maximum value of n and it ignores NULL values.

Syntax: MAX(DISTINCT / ALL / n)
Examples:

SQL> SELECT MAX(SAL), MAX(DISTINCT SAL) FROM EMP;
SQL> SELECT MAX(COMM), MAX(DISTINCT COMM) FROM EMP;

MIN (Minimum) Function:
As per the below syntax, it returns the minimum value of the n and it ignores NULL values.

Syntax: MIN(DISTINCT / ALL /n)
Examples:

SQL> SELECT MIN(SAL), MIN(DISTINCT SAL) FROM EMP;
SQL> SELECT MIN(COMM), MIN(DISTINCT COMM) FROM EMP;

Standard Deviation Function (STDDEV):
As per the below syntax, it returns the standard deviation value of n and it ignores the NULL values.

Syntax: STDDEV(DISTINCT / ALL / n)
Examples:

SQL> SELECT STDDEV(SAL), STDDEV(DISTINCT SAL) FROM EMP;
SQL> SELECT STDDEV(COMM), STDDEV(DISTINCT COMM) FROM EMP;

Variance Function:
As per the below syntax, it returns the variance of n and it ignores the NULL values.

Syntax: VARIANCE(DISTINCT / ALL / n)
Examples:

SQL> SELECT VARIANCE(SAL), VARIANCE(DISTINCT SAL) FROM EMP;
SQL> SELECT VARIANCE(COMM), VARIANCE(DISTINCT COMM) FROM EMP;

COUNT Function:
As per the below syntax, it returns the number of rows in the query. N evaluates to something other than NULL. It is used to return all rows, including duplicates and NULL values. This function can be used to specify the count of all rows or only distinct values of n.

Syntax: COUNT(* / DISTINCT / ALL / n)
Examples:

SQL> SELECT COUNT(*) FROM EMP;
SQL> SELECT COUNT(JOB),COUNT(DISTINCT JOB) FROM EMP;
SQL> SELECT COUNT(SAL),COUNT(COMM) FROM EMP;

Number format models in Oracle SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Mon, 31 Oct 2016 04:10:00 +0000

Read this Oracle Tutorial Point article to know more about Number formatting in Oracle SQL. A formatting model is a character literal that describes the format of Number data stored in a character string.

D à 99D99: It returns the specified position of the decimal character. Period(.) is the default decimal delimiter. Only one decimal character can be specified in a number format model.

Examples:

SQL> SELECT TO_CHAR(1234,'99999D99') FROM DUAL;
SQL> SELECT TO_CHAR(1234,'999D99') FROM DUAL;

EEE à 9.9EEEE: Returns a value using scientific notation.

Examples:

SQL> SELECT TO_CHAR(1234,'9.9EEEE'), TO_CHAR(1234,'9.99EEEE') FROM DUAL;

G à 9G999: Returns the specified position of the group separator. Multiple group separators can be specified.

Examples:

SQL> SELECT TO_CHAR(12345678,'99G99G999') FROM DUAL;
SQL> SELECT SAL, TO_CHAR(SAL,'99G999G99') FROM EMP;

L à L999: Returns the specified position of the local currency symbol.

Examples:

SQL> SELECT TO_CHAR(123456,'L99999') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(1234,'L9999999') CURRENCY FROM EMP WHERE DNO=10;
SQL> SELECT SAL,TO_CHAR(SAL,'L99G999D99','NLS_CURRENCY=IndRupees') SALARY FROM EMP WHERE DNO=20;

MI à 9999MI: Returns negative value with a trailing minus sign (-). Return positive value with trailing blank. The MI format should be declared as trailing argument only.

Examples:

SQL> SELECT TO_CHAR(-1000, 'L99G999D99MI') FROM DUAL;
SQL> SELECT SAL,COMM_SAL,COMM,TO_CHAR(COMM-SAL,'L99999MI') FROM EMP WHERE DNO IN (10,20,30);

PR à 999PR: Returns negative value in <angle Brackets>. This can appear only as trailing declaration.

Examples:

SQL> SELECT TO_CHAR(-1000, 'L99G999D99PR') FROM DUAL;
SQL> SELECT SAL,COMM_SAL,COMM,TO_CHAR(COMM-SAL,'L99999PR') FROM EMP WHERE DNO IN (10,20,30);

RN à Returns a value as Roman Number in Upper case.

rn à Returns a value as Roman Number in Lower case.

The value can be an integer between 1 and 9999.

Examples:

SQL> SELECT TO_CHAR(1000, 'RN'), TO_CHAR(1000,'rn') FROM DUAL;
SQL> SELECT TO_CHAR(SAL, 'RN'), TO_CHAR(SAL,'rn') FROM EMP;

S à S9999: Returns negative value with a leading minus sign and return positive value with a leading plus sign.

9999S : Returns negative value with minus sign at trailing. Return positive value with a trailing plus sign. S can appear as first or last value.

Examples:

SQL> SELECT TO_CHAR(1000,'S9999'), TO_CHAR(-1000,'S9999') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,'9999S'), TO_CHAR(SAL,'S9999') FROM EMP;

SQL> SELECT TO_CHAR(1000,'9999S'),TO_CHAR(-1000,'9999S') FROM DUAL;

X à XXXX: Returns the hexadecimal value of the specified number of digits. If the number is not an integer, Oracle rounds it to an integer. Accept only positive values or 0.

Examples:

SQL> SELECT TO_CHAR(1000,’XXXX’) FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,’XXXX’) FROM EMP;

Comma(,) à 9,999: Returns a comma in the specified position. Multiple commas can be specified.

Examples:

SQL> SELECT TO_CHAR(10000,'99,999D99') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,'99,999D99') FROM DUAL;

Period(.) à99.99: returns a decimal point, at the specified position. Only one period can be specified in a number format model.

Examples:

SQL> SELECT TO_CHAR(10000,'L99,999.99') FROM DUAL;
SQL> SELECT TO_CHAR(SAL,'L99,999.99') FROM EMP;

$ à $9999: Return value with a leading Dollar sign.

Examples:

SQL> SELECT TO_CHAR(10000,'$99,999.99') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,'$9,99,999.99') FROM EMP;

0 (ZERO) à 0999: Returns leading zeros. 9990: returns trailing zero.

Examples:

SQL> SELECT TO_CHAR(1000,'0999999'), TO_CHAR(1000,'09999990') FROM DUAL;
SQL> SELECT SAL, TO_CHAR(SAL,'$099,999.99') FROM EMP;

9 à 9999: Return value with a specified number of digits with a leading space when positive or leading minus when negative.

Examples:

SQL> SELECT TO_CHAR(1000-600,'99999'), TO_CHAR(600-1000,'99999') FROM DUAL;
SQL> SELECT TO_CHAR(20.25-20,'99999') FROM DUAL;

C à C9999: returns specified position of the ISO Currency symbol.

Examples:

SQL> SELECT TO_CHAR(1000,'C9999.99') FROM DUAL;
SQL> SELECT SAL, TO_CHAR(SAL,'C99999.99') FROM EMP;

What are different Conversion functions in SQL?

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Fri, 30 Sep 2016 17:02:00 +0000

Read this Oracle Tutorial Point article to know what are different DATE Conversion functions in SQL. The conversion functions convert a specific value from one data type to another data type. There are two different types of Data Type conversion in Oracle. That are,

Implicit Datatype Conversion
Explicit Datatype Conversion

Implicit Datatype Conversion:

Implicit Datatype conversion work according to the conversion specified by Oracle. The assignment succeeds if the oracle servers can convert the datatype of the value. CHAR to NUMBER conversion succeed only if the character strings represents a valid NUMBER. If the character strings represents the default format DD-MON-YY then only CHAR to DATE conversions succeed.

IN ASSIGNMENT IN EXPRESSION
=====================================================================

Char / Varchar2 ==> Number Char / Varchar2 ==> Number
Char / Varchar2 ==> Date Char / Varchar2 ==> Date
Number ==> Varchar2
Date ==> Varchar2

Explicit Datatype Conversion:

SQL provides 3 different functions to convert a specific value from one datatype to another datatype. The functions are,

To_Char ==> To Convert into Characters
To_Date ==> To Convert into Date
To_Number ==> To Convert into Number

To_Char Conversion Function:

This To_Char conversion function can be used in 2 different flavours.

To_Char(Number Conversion)
To_Char(Date Conversion)

1. To_Char(Number Conversion):

The To_Char(Number Conversion) function can be used to convert number of number datatype to a value of Varchar2 datatype. In the below syntax 'FMT' is the option number format that can be used and the 'nlsparams' specifies the character returned by the number format element.

Syntax: To_Char(Number,FMT,'nlsparams')

2. To_Char(Date Conversion):

The To_Char(Date Conversion) function can be used to converts Date of DATE datatype to a value of VARCHAR2 datatype in the format specified. In the below syntax, 'FMT' is the optional DATE format that can be used and the 'nlsparams' specifies the language in which and day names and abbreviations are returned.

Syntax: To_Char(DATE,FMT,'nlsparams')

What are different DATE functions in SQL?

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Thu, 29 Sep 2016 16:39:00 +0000

Read this Oracle Tutorial Point article to know what all different DATE functions are there in SQL.

ADD_MONTHS Function:

As per the below syntax, ADD_MONTHS function will return DATE d plus n Months. The argument n can be any integer.

Syntax: ADD_MONTHS(d,n)

Examples:

SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE,3) FROM DUAL;

SQL> SELECT SAL, HIREDATE, ADD_MONTHS(HIREDATE,3) FROM EMP WHERE DNO=20;

MONTHS_BETWEEN Function:
As per the below syntax, MONTHS_BETWEEN function returns no.of Months between Date d1 and Date d2. If d1 is later than d2, the results is positive else negative. If d1 and d2 are either both last days of the Months or the same days of the months, the results is always an integer.

Syntax: MONTHS_BETWEEN(D1,D2)

Examples:

SQL> SELECT ENO,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)<222;

SQL> SELECT ENO,ENAME,MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;

NEXT_DAY Function:
As per the below syntax, NEXT_DAY function returns the DATE of the WEEK DAY named by Char, that is later than the DATE d. The CHAR must be a day of the week in the sessions date language. DAY of the week can be full name or the abbreviation

Syntax: NEXT_DAY(d,CHAR)

Examples:

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,'FRI') FROM DUAL;

SQL> SELECT SAL,HIREDATE,NEXT_DAY(HIREDATE,'MONDAY') FROM EMP;

LAST_DAY Function:
As per the below syntax, LAST_DAY function returns the date of the last day of the month that contains d. Most commonly used to determine how many days are left in the current month.

Syntax: LAST_DAY(d)

Examples:

SQL> SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LAST, LAST_DAY(SYSDATE) – SYSDATE DAYSLEFT FROM DUAL;

ROUND Function:
As per the below syntax, ROUND function returns Date rounded to the Unit specified by the format. If format is omitted, date is rounded to the nearest day.

Syntax: ROUND(DATE,FORMAT)

Examples:

SQL> SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;

SQL> SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;

SQL> SELECTT ROUND(SYSDATE,'DAY') FROM DUAL;

TRUNC / Truncating Function:
As per the below syntax, it returns Date with the time portion of the day truncated to the specified unit. If format is omitted, date is truncated to the nearest day.

Syntax: TRUNC(DATE,'FORMAT')

Examples:

SQL> SELECT ROUND(SYSDATE,'DAY'),TRUNC(SYSDATE,'DAY') FROM DUAL;

SQL> SELECT ROUND(SYSDATE,'MONTH'),TRUNC(SYSDATE,'MONTH') FROM DUAL;

SQL> SELECT ROUND(SYSDATE,'YEAR'),TRUNC(SYSDATE,'YEAR') FROM DUAL;

SYSDATE function in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Wed, 28 Sep 2016 15:52:00 +0000

Read this Oracle Tutorial Point article to know about SYSDATE and DATE arithmetic functions in SQL. The SYSDATE function in SQL is used to retrieve the current date and time, which Oracle stores in an internal numeric format with a wide range from January 1, 4712 BC to December 31, 9999 AD. SQL allows for date arithmetic, enabling users to add or subtract days and hours to/from dates, facilitating various date manipulations through simple arithmetic operations. Examples demonstrate how to implement SYSDATE and date arithmetic using SQL queries effectively. The default display and input format for any date is DD-MON-YY.

Century
Year
Month
Day
Hour
Minute
Second

SYSDATE:

SYSDATE is a data function that returns the current date and time. SYSDATE is generally selected upon a DUMMY table.

SQL> SELECT SYSDATE FROM DUAL;

Key Points:

SYSDATE is a key SQL function that returns the current date and time.
Oracle dates range from January 1, 4712 BC to December 31, 9999 AD and are stored in a numeric format.
The default input and display format for dates in Oracle is DD-MON-YY.
Date arithmetic allows users to add or subtract specific numbers of days or hours to/from dates.
SQL queries can utilize SYSDATE and perform operations such as adding days, subtracting days, or calculating intervals between dates.
Examples of using SYSDATE demonstrate practical applications in retrieving and manipulating hire dates in employee records.
The DUAL table is commonly used in SQL to select values like SYSDATE.

DATE Arithmetic:
As database stores DATE as numbers, Arithmetic operations can be implemented. Number constants can be added or subtracted upon DATE. The operations those can be applied are,

Date + Number --> Date: Adds number of days to a date
Date - Number --> Date: Number of days subtracted from a date
Date - Date --> Number of days: subtracts one date from another
Date + Number/24 --> Date: Number of hours adds to a date

Examples:

SQL> SELECT SYSDATE, SYSDATE + 5 FROM DUAL;

SQL> SELECT SYSDATE, SYSDATE – 5, SYSDATE + 120/24 FROM DUAL;

SQL> SELECT ENAME, HIREDATE, HIREDATE + 5 FROM EMP;

SQL> SELECT ENAME, HIREDATE, HIREDATE - 5 FROM EMP;

SQL> SELECT ENAME, HIREDATE, HIREDATE – SYSDATE FROM EMP;

SQL> SELECT ENAME, (HIREDATE – SYSDATE)/7 WEEKS FROM EMP WHERE DNO=10;

ROUND, TRUNCATE and FLOOR function in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Tue, 27 Sep 2016 16:20:00 +0000

Read this Oracle Tutorial Point article to know NUMBER function, ROUND function, TRUNCATE function, CEIL function, FLOOR function, MOD(MODULE) function, POWER function, SQRT(SQUARE ROOT) function, ABS(ABSOLUTE) function and SIGN functions in SQL.

NUMBER Function:

NUMBER functions accepts number as input and return number as values. Many functions return values that are accurate to 38 decimal digits.

ROUND Function:

In the below syntax, it returns ‘n’ rounded to n places right of the decimal point. If ‘m’ is omitted then n is rounded to 0 places. ‘m’ can be negative and rounds off the digits to the left of the decimal point. ‘m’ must be a integer.

Syntax: ROUND(n,m)

Examples:

SQL> SELECT ROUND(18.195,1) FROM DUAL;

SQL> SELECT ROUND(18.195,-1) FROM DUAL;

SQL> SELECT ROUND(15648,2), ROUND(7854.565,0), ROUND(785.456,-1) FROM DUAL;

TRUNCATE Function:
In the below syntax, it returns n truncated to m decimal places. If ‘m’ is omitted, ‘n’ is truncated to 0 places. ‘n’ can be negative to truncate ‘m’ digits left to the decimal point.

Syntax: TRUNC(n,m)

Examples:

SQL> SELECT TRUNC (18.195,1) FROM DUAL;

SQL> SELECT TRUNC (18.195,-1) FROM DUAL;

SQL> SELECT TRUNC (15648,2), TRUNC (7854.565,0), TRUNC (785.456,-1) FROM DUAL;

CEIL Function:
In the below syntax, it returns the smallest integer greater than or equals to ‘n’. the adjustment is done to the highest nearest decimal value.

Syntax: CEIL(n)

Examples:

SQL> SELECT CEIL(19.7) FROM DUAL;

SQL> SELECT CEIL(15.37), CEIL(45.45), CEIL(15) FROM DUAL;

FLOOR Function:

In the below syntax, it returns the largest integer less than or equals than n. The adjustment is done to the lowest nearest decimal values.

Syntax: FLOOR(n)

Examples:

SQL> SELECT FLOOR(19.7) FROM DUAL;

SQL> SELECT FLOOR(15.28), FLOOR(45,3) FROM DUAL;

MODULES Function:

In the below syntax, MOD function returns remainder m divided by n. It returns m if n is 0.

Syntax: MOD(n,m)

Examples:

SQL> SELECT MOD(14,4), MOD(40,2) FROM DUAL;

POWER Function:

In the below syntax, it returns m raised to the n power. The base m and the exponent n can be any numbers. If m is negative then n must be an integer.

Syntax: POWER(m,n)

Examples:

SQL> SELECT POWER(5,2), POWER(-5,2) FROM DUAL;

SQL> SELECT POWER(5,-2), POWER(-5,-2) FROM DUAL;

SQL> SELECT POWER(5,-2.5), POWER(-5,2.5) FROM DUAL;

SQUARE ROOT Function:

In the below syntax, it returns square root of n. The value of n cannot be negative. SQRT returns a real result.

Syntax: SQRT(n)

Examples:

SQL> SELECT SQRT(36) FROM DUAL;

ABSOLUTE Function:

In the below syntax, it returns the absolute value of n.

Syntax: ABS(n)

Examples:

SQL> SELECT ABS(-19) FROM DUAL;

SQL> SELECT SAL,COMM,SAL-COMM, ABS(SAL-COMM) FROM EMP;

SIGN Function:

In the below syntax, it returns the SIGN(signification of a number).

If n<0, return -1
If n=0, return 0
If n>0, return 1

Syntax: SIGN(n)

Examples:

SQL> SELECT SIGN(-15), SIGN(15), SIGN(0) FROM DUAL;

SQL> SELECT ENAME,SAL,COMM,SIGN(SAL-COMM) FROM EMP WHERE SIGN(SAL-COMM)=-1;

PAD, TRIM, REPLACE, TRANSLATE and ASCII Functions in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Mon, 26 Sep 2016 16:48:00 +0000

Read this Oracle Tutorial Point article to know about LPAD & RPAD functions,TRIM function, REPLACE function, TRANSLATE function, CHR function and ASCII functions in SQL.

LPAD Function:
Pads the character value left justified to a total width of n character position. Space is the default padding character.

Syntax: LPAD(Char1,n,Char2)

Examples:

SQL> SELECT LPAD('Page 1',15,'*.') FROM DUAL;

SQL> SELECT LPAD('Page 1',15) FROM DUAL;

SQL> SELECT NAME,LPAD(NAME,10,'-') FROM EMP WHERE SAL>=2500;

RPAD Function:
Pads the character value right justified to a total size of n character positions. Space is the default padding character.

Syntax: RPAD(Char1,n, Char2)

Examples:

SQL> SELECT RPAD('Page 1',15,'*.') FROM DUAL;

SQL> SELECT RPAD('Page 1',15) FROM DUAL;

SQL> SELECT NAME,RPAD(NAME,10,'-') FROM EMP WHERE SAL>=2500;

TRIM Function:

TRIM function enables to trim heading or trailing characters or together from a character string. If Leading is defined concentrates on leading characters. If Trailing is specified focuses on trailing characters. If both or none is specified concentrates both on leading and trailing. Return the VARCHAR2 type.

Syntax: TRIM(Trailing / Leading / Both, Trim character From Trim source)

Examples:

SQL> SELECT TRIM('R','RAAM') FROM DUAL;

SQL> SELECT TRIM('R','RAAMA') FROM DUAL;

SQL> SELECT TRIM('R','RRAAMRR') FROM DUAL;

SQL> SELECT TRIM(Trailing 'R' From ,'RRAAMRR') FROM DUAL;

SQL> SELECT TRIM(Leading 'R' From,'RRAAMRR') FROM DUAL;

SQL> SELECT TRIM(Both 'R' From,'RRAAMRR') FROM DUAL;

LTRIM Function:
LTRIM function enables trim heading character from a character string. All the left characters that seems within the set are removed.

Syntax: LTRIM(Char,set)

Examples:

SQL>SELECT LTRIM('XYZ LAST WORD','XY') FROM DUAL;

SQL> SELECT JOB, LTRIM(JOB) FROM EMP WHERE JOB LIKE 'MANAGER';

RTRIM Function:

RTRIM function enables the trimming of trailing characters from a character string. All the rightmost characters that appear in the set are removed.

Syntax: RTRIM(Char,set)

Examples:

SQL> SELECT RTRIM('SWIMMINGxyXxy','xy') FROM DUAL;

SQL> SELECT RTRIM(JOB,'R'),JOB FROM EMP WHERE LTRIM(JOB,'MAN') LIKE 'GER';

REPLACE Function:

REPLACE function returns the every incidence of search string replaced by the replacement string. If the replacement string is omitted or NULL, all incidences of search string are removed. It substitutes one string for another as well as to remove character strings.

Syntax: REPLACE(Char,Search_Str,Replace_Str)

Examples:

SQL> SELECT REPLACE('Jack and Jue','J','Bl');

SQL> SELECT NAME,REPLACE(JOB,'MAN','DAM') FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,REPLACE(JOB,'MAN','EXECUTIVE') FROM EMP WHERE JOB='SALESMAN';

TRANSLATE Function:
TRANSLATE function is used to Translate Character by character in a String.

Syntax: TRANSLATE(char,FROM,TO)

TRANSLATE function returns a char with all incidences of each character in ‘FROM’ replaced by its corresponding character in ‘TO’. Characters in char that aren't in FROM aren't replaced. The argument FROM can contain more characters than TO. If the extra characters seems in Char they're removed from the return value.

Examples:

SQL> SELECT  JOB,TRANSLATE(JOB,'P',' ') FROM EMP WHERE JOB='PRESIDENT';

SQL> SELECT JOB,TRANSLATE(JOB,'MN','OM') FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,TRANSLATE(JOB,'A','O') FROM EMP WHERE JOB='SALAESMAN';

CHR Function:
CHR function returns a character having the binary equivalent to ‘n’. CHR function returns the similar for ‘n’ in database character set or national character set.

Syntax: CHR(n) CHR(n using NCHAR_CS)

Examples:

SQL> SELECT CHR(67) || CHR(65) || CHR(84) SAMPLE FROM DUAL;

SQL> SELECT CHR(16705 Using NCHAR_CS) FROM DUAL;

ASCII Function:
ASCII function represents the decimal representation in the character database set of the first characters of the Char.

Syntax: ASCII(Char)

Examples:

SQL> SELECT ASCII('A'), ASCII('APPLE') FROM DUAL;

String Functions in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Tue, 20 Sep 2016 17:41:00 +0000

Read this Oracle Tutorial Point article to know SUBSTR, LENGTH and INSTR functions in String functions in SQL in Oracle database.

SUBSTR(SUBSTRING) Function:

SUBSTR function returns described characters from character value, starting from a specified position ‘m’, to ‘n’ character long.

Important Points:

If m is 0, it is treated as 1.
If m is Positive(+), Oracle counts from the beginning of char to identify the first character.
If m is Negative(-), Oracle counts backwards from the end of the character.
If n is Omitted, Oracle returns all characters to last of Char.
If n is less than 1 or 0, a NULL is returned.

Floating point numbers passed as arguments to SUBSTR are automatically converted to Integers.

Syntax:

SUBSTR(Col/Expr,m,n)

Examples:

SQL> SELECT SUBSTR('ABCDEFGHIJK',3,4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',-5,4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',0,4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',4,-2) FROM DUAL;

LENGTH Function:
Return the number of characters in a value. If the char has data type CHAR, the length includes all trailing blanks. If the char is NULL, it returns NULL.

Syntax:

LENGTH(Column/Expression)

Examples:

SQL> SELECT LENGTH ('SUBBAREDDY') FROM DUAL;

SQL> SELECT LENGTH(NAME) || ' Characters exit in ' || INITCAP(NAME) ||

's Name.' AS ' Names and Lengths ' FROM EMP;

SQL> SELECT INITCAP(NAME), JOB FROM EMP WHERE LENGTH(JOB)=7;

INSTR(INSTRING) Function:
It returns the numeric position of a named character.

Syntax:

INSTR (Column/Expression, Char, n,m)

Important Points:

Searches for Column / Expression beginning with its n’th character for the m’th occurrence of char2 and returns the position of the character of this occurrence.
N can be positive or negative, if negative searches backward from the end of column expression.
The value of ‘m’ should be positive.
The default values of both m and n are 1.
The return value is relative to the beginning of char1 regardless of the values of n and is expressed in characters.
If the search is unsuccessful, the return value is zero.

Examples:

SQL> SELECT INSTR('String','r') FROM DUAL;

SQL> SELECT INSTR('CORPORATE LOOR','OR',3,2) FROM DUAL;

SQL> SELECT INSTR('CORPORATE FLOOR','OR',-3,2) FROM DUAL;

SQL> SELECT JOB,INSTR(JOB,'A',1,2) FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,INSTR(JOB,'A',2,2) FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,INSTR(JOB,'A',2) FROM EMP WHERE JOB='MANAGER';

Character Manipulation Functions in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Mon, 19 Sep 2016 17:46:00 +0000

Read this Oracle Tutorial Point article to know behaviour of Character, LOWER, UPPER, INITCAP and CONCAT functions in SQL with examples.

Character Function : It accept Character input and can return both Character and Number value.
Number Function : It accept Number as input and return Number values.
Date Function: Operate on values of data type can return both Date and Number.
Conversation Function: Convert a value from one data type to another.
General Function : NVL,DECODE ---- Operates on any datatype and can return any datatype.

Character Functions:

Character functions returns the character values, unless stated. They return the datatype VARCHAR2 and it’s limited to a length of 4000 bytes. If the return value go beyond the length, then the return value is truncated, without an error. The functions are categorised as,

CHARACTER MANIPULATION.
CASE CONVERSION.

Character Manipulation Functions:

LOWER Function : LOWER function converts Alpha character values to LOWERcase. The return value has the same datatype as argument char type(CHAR or VARCHAR2).

Syntax:

LOWER (EXPRESSION / COLUMN)

Examples:

SQL> SELECT LOWER('SUBBAREDDY NALLAMACHU') FROM DUAL;

SQL> SELECT NAME, LOWER('MY INFORMATION') FROM DUAL;

SQL> SELECT NAME, LOWER(NAME) FROM EMP WHERE JOB = 'MANAGER';

SQL> SELECT 'The' || NAME||'s Designation is ' || JOB FROM EMP WHERE OWER(JOB)='MANAGER';

UPPER Function:
Upper function converts the Alpha character values to capital letters. The return value has the same datatype as the argument char.

Syntax:

UPPER (Column/Expression)

Examples:

SQL> SELECT UPPER('Oracle Corporation') FROM DUAL;

SQL> SELECT NAME, UPPER('My Information') FROM DUAL;

SQL> SELECT NAME, UPPER(NAME) FROM EMP WHERE JOB = 'MANAGER';

SQL> SELECT NAME, LOWER(NAME), UPPER(NAME) FROM EMP WHERE JOB = 'MANAGER';

SQL> SELECT NAME,JOB FROM EMP WHERE JOB=UPPER('Manager');

INITCAP Function:
INITCAP function converts the Alpha character values to capital for the first letter of every word, keeping all other letters in Lowercase. Words are delimited by white spaces or character those are not alphanumeric.

Syntax:

INITCAP(Column / Expression)

Examples:

SQL> SELECT INITCAP('subbareddy nallamachu') FROM DUAL;

SQL> SELECT 'The Job Title for '|| INITCAP(NAME)|| ' is ' ||LOWER(JOB) Details FROM EXP;

SQL> SELECT NAME, LOWER(NAME),INITCAP(NAME),UPPER(NAME) FROM EMP;

CONCAT Function:
CONCAT function concatenates the first character value to the second character value. Only two parameters accept. It returns the character data type

Syntax:

CONCAT(Column1/Expr1, Column2/Expr2)

Examples:

SQL> SELECT CONCAT('ORACLE','CORPORATION') FROM DUAL;

SQL> SELECT NAME,JOB,CONCAT(NAME,JOB) FROM EMP WHERE DNO=10;

SQL> SELECT CONCAT('The Employee Name is ', INITCAP(NAME)) AS 'Employee Names ' FROM EMP

 WHERE DNO IN(10,30);

Case conversion functions will discuss in coming articles.

ORDER BY Clause in SQL with examples

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 23 Jul 2016 12:26:00 +0000

Read this Oracle Tutorial Point article to know what is the use of ORDER BY clause in SQL in ORACLE database. The order of rows returned in a query results are undefined. The ORDER BY clause can be used to sort the rows. The ORDER BY clause should be the last clause in the order of all clauses declared in the SELECT statement. An expression or an alias can be prescribed to sort. By default ordering of data is ascending.

Numbers 1 to 999
Dates Earliest – latest
String A-Z; NULLS --> Last

Syntax:

SELECT [*|COLUMN_NAME(separated by comma(,))] FROM TABLE_NAME

[WHERE CONDITION(S)]

[ORDER BY {COLUMN,EXPR}[ASC/DESC]];

The default ordering on a column is ascending to modify the default ordering DESC should be used after the column name. Sorting is implemented on column aliases and also be implemented upon multiple columns. The controversy of sorting is broken only when there rises a conflict of consistency upon the data in a column.

Query using ORDER BY clause:

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP ORDER BY HIREDATE;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP ORDER BY HIREDATE DESC;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP WHERE JOB='MANAGER' ORDER BY SAL;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP WHERE SAL>2500 ORDER BY JOB,NAME DESC;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP ORDER BY DNO,SAL,HIREDATE;

Single and Multiple Row functions:
The single row function can appear in

SELECT statement
WHERE clause
START WITH clause
CONNECT BY clause

Types of single row functions are,

NUMBER.
CHARACTER.
DATE.
CONVERSION.

Single Row Function:
They are used to manipulate data items. They accept one or more arguments and returns only one value for each row returned by the query. An argument can be,

User Supplied Constants.
Variable Values.
Column Names.
Expressions.

Syntax:

Func_name (column/expr [arg1,arg2,.....])

Features of Single Row Functions:

Acts on every row returned in the QUERY.
Return only one result per row.
May return a different type of value data value than the referenced data type.
May expert one or more than one argument.
Can be used in SELECT, WHERE and ORDER BY Clause.
Can be nested.

Multiple Row Functions:
These functions manipulate multiples of rows to give one result per group of rows.

SQL PLUS Operators in Oracle

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 23 Jul 2016 07:45:00 +0000

Read this Oracle Tutorial Point article to know more about the BETWEEN - AND and NOT BETWEEN - AND, IN and NOT IN, IS NULL or IS NOT NULL, LIKE and NOT LIKE operators in SQL in Oracle Database.

BETWEEN - AND and NOT BETWEEN - AND:
This order is used to show rows based on a range of values. The declared range is inclusive. The lower limit should be declared first.

Query for Between….And:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE SAL BETWEEN 1000 AND 1500;

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE JOB BETWEEN 'MANAGER' AND 'CLERCK';

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE BETWEEN '19-APR-16' AND '01-OCT-16';

Query for Not Between…And:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE SAL NOT BETWEEN 1000 AND 1500;

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE JOB NOT BETWEEN 'MANAGER' AND 'CLERCK';

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE NOT BETWEEN '19-APR-16' AND '01-OCT-16';

IN & NOT IN Operator:
The operator is used to test for values in a defined list. The operator can be used upon any data type.

Query using IN Operator:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE NAME IN ('SUBBA','REDDY');

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE DNO IN (10,20);

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE IN ('19-APR-16' , '01-OCT-16');

Query using NOT IN Operator:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE NAME NOT IN ('SUBBA','REDDY');

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE DNO NOT IN (10,20);

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE NOT IN ('19-APR-16' , '01-OCT-16');

IS NULL & IS NOT NULL Operator:
The operator tests for NULL values. It is the only operator that is used to test for NULL’s. NULL means the value is unavailable, unassigned, unknown or inapplicable.

Query using NULL Operator:

SQL> SELECT NAME,DNO,COMM,JOB FROM EMP WHERE COMM IS NULL;

SQL> SELECT NAME,DNO,JOB FROM EMP WHERE MGR IS NULL;

Query using NOT NULL Operator:

SQL> SELECT NAME,DNO,COMM FROM EMP WHERE COMM NOT NULL;

SQL> SELECT NAME,DNO,JOB FROM EMP WHERE MGR IS NOT NULL;

LIKE & NOT LIKE Operator:
The LIKE Operator is used to search for a matching character. The character pattern matching operator is referred as a wild chad search. The accessible wild cards in Oracle are,

/ represent any one character at that position.
% used to represent any sequence of ZERO or more characters.

The wild card symbol can be used in any combination with literal characters. For getting exact match for ‘%’ and ‘-’ the ESCAPE option has to be used, which is ‘/’ symbol with ESCAPE option.

Query using LIKE Operator:

SQL> SELECT NAME,JOB FROM EMP WHERE NAME LIKE 'S%;;

SQL> SELECT NAME,JOB FROM EMP WHERE NAME LIKE '_A%';

SQL> SELECT NAME,SAL FROM EMP WHERE NAME='SM%;;

SQL> SELECT * FROM DEPT WHERE DNAME LIKE '%\_%' ESCAPE '\';

Query using NOT LIKE Operator:

SQL> SELECT NAME,JOB FROM EMP WHERE NAME NOT LIKE 'S%';

SQL> SELECT NAME,JOB FROM EMP WHERE NAME NOT LIKE '_A%';

SQL> SELECT NAME,SAL FROM EMP WHERE 'SM%' NOT LIKE;

SQL> SELECT * FROM DEPT WHERE DNAME NOT LIKE '%\_%' ESCAPE '\';

Logical Operators in Oracle SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 23 Jul 2016 05:54:00 +0000

Read this Oracle Tutorial Point point article to know about Logical operators in SQL. The Logical operator combines the results of two components conditions to produce a single result in SQL. The logical operators exists in Oracle are,

AND Operator:
AND Operator returns TRUE if both or all component conditions are TRUE. AND operator returns FALSE if either is FALSE else returns Unknown.

Truth Table of AND:

AND	TRUE	FALSE	NULL
TRUE	T	F	NULL
FALSE	F	F	F
NULL	NULL	F	NULL

Examples:

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE SAL>=1100 AND JOB='CLERK';

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE DNO=10 AND JOB='MANAGER';

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>=1500 AND SAL<5000;

OR Operator:
OR operator returns TRUE if either component conditions is TRUE. OR operator returns FALSE if both are FALSE else returns unknown.

Truth Table of OR:

OR	TRUE	FALSE	NULL
TRUE	T	T	T
FALSE	T	F	NULL
NULL	T	NULL	NULL

Examples:

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE SAL >= 1100 OR JOB = 'CLERK';

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE DNO = 10 OR JOB = 'MANAGER;;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE SAL >= 1200 OR SAL < 6000;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE DNO = 10 OR DNO = 20;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE JOB >= 'CLERK' OR JOB = 'MANAGER';

NOT Operator:
NOT operator returns TRUE is the following condition is FALSE. If the following condition it TRUE, NOT operator return FALSE. If the condition is unknown, NOT operator returns unknown.

Truth Table of NOT:

	TRUE	FALSE	NULL
NOT	F	T	NULL

Combination of AND and OR Operator:

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP

WHERE (SAL > 1400 OR JOB = 'CLERK') AND DNO = 20;

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP

WHERE (DNO = 10 AND JOB = 'MANAGER') OR SAL >= 2000;

Examples:

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT JOB = 'MANAGER';

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT SAL > 6000;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT SAL < 6000;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT HIREDATE = '20-MAY-16';

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT JOB = 'SALESMAN' AND DNO = 30;

Precedence Rules:
The default precedence order is,

All comparison operators.
NOT Operator.
AND Operator.
OR Operator.

The precedence is controlled by using parenthesis.

DISTINCT and WHERE clause in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 23 Jul 2016 05:21:00 +0000

Read this Oracle Tutorial Point article to know how to use DISTINCT and WHERE clause and what are all conditions to follow while using these clauses in SQL in Oracle Database.

DISTINCT CLAUSE:
Until it is instructed SQL * Plus displays the results of a query while not eliminating duplicate rows. To eliminate the duplicate rows in the result the DISTINCT keyword is used. Multiple columns are declared next to the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns and describe a distinct combination of the columns.

Example:

SQL> SELECT DISTINCT DNO FROM EMP;

SQL> SELECT DISTINCT JOB, DNO FROM EMP;

WHERE CLAUSE:
The number of rows returned by a query can be limited/filter by using the WHERE clause. A WHERE clause contains a restriction that must be met and should directly follow the FROM clause.

Syntax:

SELECT [DISTINCT] {*|Column1 [alias],Column2 [alias],....} FROM TABLE

       [WHERE condition(s)];

The WHERE clause can compares,

Value in columns
Arithmetic Expressions
Literal Values
Functions

The components of WHERE clauses are,

Column Name
Comparison Operator
Column Name, Constants or list of values.

The character string and date should be enclosed in single quotes marks. Character value is case sensitive and date value is format sensitive {DD-MMM-YY}. The comparison operator is used in conditions that compare one expression to a different expression. The different comparison operators are (=, <>, != ,^=,, <, <=,>, >=). The format of the WHERE clause is WHERE expr operator value.

Examples:

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';

SQL> SELECT ENAME,HIREDATE,DNO,SAL  FROM EMP WHERE DNO=10;

SQL> SELECT ENO,ENAME,SAL  FROM EMP WHERE SAL>=5000;

SQL> SELECT ENAME || 'JOINED On' || HIREDATE 'EMPLOYEES JOINING DATES' FROM EMP

WHERE HIREDATE='01-OCT-90';

SQL> SELECT ENAME || 'Works in Department' || DNO 'EMPLOYEES  AND DEPARTMENTS' FROM EMP

WHERE DNO<>20;

SQL> SELECT ENAME,SAL,DNO,JOB FROM EMP WHERE JOB <> 'CLERK';

Literals in SQL Statements

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 23 Jul 2016 04:54:00 +0000

Read this Oracle Tutorial Point article to know different types of literals in SQL statement. A Literal and a Constant value are synonyms to atleast one another and refer to a fixed data value. The types of Literals recognized by Oracle are,

Text Literal
Integer Literal
Number Literal
Interval Literal

Text Literals:
Text Literal specifies a text or character literal. It used to specify values wherever ‘text’ or CHAR appear in

Expression
Condition
SQL Function
SQL Statement

It should be enclosed in singe quotes. They have properties of both CHAR and VARCHAR2 data types. A text literal will have a maximum length of 4000 Bytes.

Examples: ‘Employee Information’
‘Managers Specification’
N ‘n char Literal’

Using Literal Character String:
A Literal is declared in a SELECT list can be a character, a Number or a Date. A literal isn't a column name or a column alias. A literal is printed for all rows that are retrieved by the SELECT statement. Literal String of Free-Test form is enclosed in the query as per the requirement. Date and character literals must be enclosed within the single quotation marks(' '). Literals improve the readability of the output.

Examples:

1. SQL> SELECT ENAME || '.' ||' Month Salary='|| SAL as SALARIES FROM EMP;

2. SQL> SELECT ' The Designation of ' ENAME || ' is ' ||JOB as DESIGNATION FROM EMP;

3. SQL> SELECT ' The Annual Salary of ' ENAME || ' is ' ||JOB as SAL * 12 as ANNUAL_SALARY FROM EMP;

4. SQL> SELECT DNAME ||' Department is Located at ' || LOC FROM DEPT;

5. SQL> SELECT DNAME ||' Joined the Organization on ' || HIREDATE FROM EMP;

6. SQL> SELECT ENAME ||' Works in Department Number ' || DNO || ' as ' || JOB FROM EMP;

Applying Concatenation Operator:
Concatenation operator link columns to other columns are constant values or arithmetic expressions. Columns on either side of the operator are combined to create a single output column. The resultant column is treated as character expression. The concatenation operator is pictured in Oracle by double pipe symbol (||).

Examples:

1. SQL> SELECT ENO || ENAME || '. Designation is ' || JOB || ' Employee Details ' FROM EMP;

2. SQL> SELECT ' The Basic Salary of ' || ENAME || ' is RS ' || SAL FROM EMP;

NULL and NVL functions in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Wed, 20 Jul 2016 17:17:00 +0000

Read this Oracle Tutorial Point article to know how to use the NULL and NVL Function and what are the conditions applicable on those functions in SQL in Oracle database.

NULL FUNCTION:
NULL is a value which is Unavailable, Unassigned, Unknown and Inapplicable. A NULL is not same as ZERO or blank space. If a row lacks the data for a particular column that value is said to be NULL or to contain NULL.

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP;

If any column value is an Arithmetic expression is NULL, the overall result is also NULL. In this situation is termed as NULL propagation and has to be handled very carefully.

SQL> SELECT ENAME,JOB,SAL,COMM,15*SAL+COMM FROM EMP;

SQL> SELECT ENAME,JOB,SAL,COMM,5*SAL+COMM FROM EMP;

NVL FUNCTION:
The NVL function is used to convert a NULL value to an actual value.

Syntax:

         NVL (Exp1, Exp2)

Exp1: is the source value or expression that may contain NULL.
Exp2: is the target value for converting NULL.

NVL function can be convert any data type, the return value is always the same as the data type of Exp1. The data types of the source and destination must match.

NVL(comm,0)
NVL(Hiredate,’14-SEP-15’)
NVL(job,’Not Assigned’)

Examples:

SQL> SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP;

SQL> SELECT ENAME,SAL,COMM,(SAL*12)+NVL(COMM,0) FROM EMP;

SQL> SELECT ENAME,SAL,COMM,(SAL+500)+NVL(COMM,0) FROM EMP;

Working with Aliases:
An Alias is the alternate name given for any Oracle object. Aliases in Oracle are two types,

Column Alias
Table Alias

The column alias renames a column heading. It is specified in the SELECT list by declaring the alias after the column name by using the space separator. Alias heading appear is UPPER casting by default. The alias should be declared in double quotes if it is against the specifications of Naming Convention.

The AS keyword can be used between the column name and alias. An alias effectively renames the SELECT list item for the duration of the query. An alias cannot be used anywhere in the SELECT list for operational purpose.

Examples:

SQL> SELECT ENO NUMBERS,
         ENAME NAME,
         SAL “BASIC SALARY”,
         JOB DESIGNATION 
     FROM EMP;

SQL> SELECT DNO AS “DEPARTMENT ID”,
  DNAME AS “DEPARTMENT NAME”
  LOC AS PLACE
     FROM DEPT;

SQL> SELECT HISAL AS “MAXIMUM SALARY”,
   LOSAL AS “MINIMUM SALARY”,
   GRADE
     FROM SALGRADE;

Arithmetic Operations in SQL

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Wed, 20 Jul 2016 16:42:00 +0000

Read this Oracle Tutorial Point article to know about arithmetic operations in SQL and how to apply in SELECT statement. The Arithmetic operators will be used to create expressions on NUMBER and DATE data. The Arithmetic Operators supported are,

Addition(+)
Subtraction(-)
Multiply(*)
Divide(/)

Arithmetic Operators can be used in any of the SQL statement except the FROM clause. SQL * Plus forget Back spaces(<---) before and after the Arithmetic operator.

Applying Arithmetic Operations in SELECT Statement:
By using SELECT statements, Arithmetic Expressions can be implemented. Arithmetic Expressions can be implemented to,

Perform Calculations.
Modify the way the data is displayed.
Implement WHAT-IF Scenario.

An Arithmetic Expression can contain,

Simple Column names.
Arithmetic operators.
Constant numeric values.

Examples:

SQL> SELECT ENO,ENAME,SAL,SAL+100 FROM EMP;

SQL> SELECT ENO,ENAME,SAL,SAL-500 FROM EMP;

Operator Precedence:
Multiplication(*) and Division(/) take preference over addition(+) and subtraction(-). Operators of the same priority are evaluated from left to right. To increase clarity and to prioritize evaluation parentheses can be implemented.

SQL> SELECT ENO,ENAME,SAL,12 * SAL + 100 FROM EMP;

SQL> SELECT ENO,ENAME,SAL,(12*SAL)+100 FROM EMP;

SQL> SELECT ENO,ENAME,SAL,12*(SAL+100) FROM EMP;

Based on the above SQL queries you try for other Arithmetic operators.

How to SAVE SQL prompt content into Text file?

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Wed, 20 Jul 2016 16:40:00 +0000

Read this Oracle Tutorial Point article to know how to save SQL prompt content into a file. In SQL prompt we will execute the multiple queries. If once we reopen SQL prompt, we miss the list of queries which we have executed previously. To avoid this problem, it's always better to store the data into the physical file. The SAVE command is used to store the current content of the SQL buffer.

Steps are:

At the SQL Prompt type the full name of the path where the file has to be saved.
Give line name of the file with .sql extension.
If the file has to be replaced with already existing name then use the REPLACE ALL clause.

Example:

SQL> SAVE C:\Users\Subba Reddy(Your computer account id)\Documents\Script files\Insert_Prompt.sql

SQL> SAVE C:\Users\Subba Reddy(Your computer account id)\Documents\Script files\Insert_Prompt.sql REPLACE ALL

SELECT Statement in SQL with examples

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sun, 17 Jul 2016 12:36:00 +0000

Read this Oracle Tutorial Point article to know how to Query / fetch data from the table will use the SELECT Statement in SQL in Oracle Database. To know more about the SELECT Statement, continue reading this article.

SELECT Statement:
The SELECT statement is used to retrieve data from one or more than one table, object tables, views, object views or management views.

Prerequisites:
The user must have the SELECT privileges on the defined object. The SELECT ANY TABLE allows selecting the data from any recognized object.

Efficiency of SQL SELECT Statement:
The SELECT statement can be used to select or retrieve data from the object using any of the following criteria.

SELECTION: Selection chooses the rows in a table that are expected to return by a query.
PROJECTION: Projection chooses the columns in a table that are expected to return by a query.
JOIN: Join chooses the data in from one or more than one table by creating a link between them.

Basic SELECT Syntax:

SELECT [DISTINCT] {* , Column [Alias],- - -} FROM TABLE;

SELECT --> Defined Columns FROM --> Declared Tables.

SELECT : Specifies a list of Column (one/more)
DISTINCT : Suppresses Duplicates
* -- Indicates Select all columns
COLUMN : Select the named column
Alias : Provide selected columns in different columns
FROM Table: Specifies the table containing the columns.

Writing SQL Statements to SELECT or RETRIEVE DATA FROM TABLES: Sample table used,

Table1 - EMP:

Column Names          Data Types
------------         ----------
ENO                     NUMBER
ENAME                   VARCHAR
DNO                     NUMBER
JOB                     VARCHAR
SAL                     NUMBER
COMM                    NUMBER
MGR                     NUMBER
HIREDATE                DATE

Table2 - DEPT:

Column Names             Data Types
--------------          ---------------
DNO                     NUMBER
DNAME                   VARCHAR
LOC                     VARCHAR

Table3 - SALGRADE:

Column Names             Data Types
--------------          ---------------
HISAL                   NUMBER
LOSAL                   NUMBER
GRADE                   NUMBER

Retrieving Data from ALL columns in the table:

SQL> SELECT * FROM EMP;       

SQL> SELECT * FROM DEPT;      

SQL> SELECT * FROM SALGRADE;

In the above the * is a projection operator. It shows data from all the columns defined in the table with all records. The data is displayed in a table format.

Retrieving Data from particular columns:

SQL> SELECT ENO,ENAME,SAL FROM EMP;

SQL> SELECT ENAME,JOB,SAL,DNO FROM EMP;

SQL> SELECT ENO,ENAME,SAL FROM EMP;

SQL> SELECT DNO,DNAME,LOC FROM DEPT;

SQL> SELECT HISAL,LOSAL,GRADE FROM SALGRADE;

The column names need not be in the same order as exists in table. The columns should be separated by using comma (,). The column names can be separated into different lines within the SQL Buffer. The casing of column names is not important.

Column Heading Defaults:
The default conclusion of the data after it is retrieved from the table is,

LEFT: Character and Date data
RIGHT:Numeric Data

By default the data will be shown in UPPERcase. The character and date column headings can be truncated. But NUMBER cannot be truncated.

INSERT statement in Oracle Database

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sun, 17 Jul 2016 12:34:00 +0000

Read this Oracle Tutorial Point article to know how to populate data into the SQL table in Oracle Database, we should know how to use the INSERT statement in SQL. To know more about the INSERT statement in Oracle Database, continue reading this article.

INSERT statement:
The INSERT statement is used to add rows into a

Table
View's Base Tables
A partition of a partition table
A subpartition of Composite_Partitioned Table
An object table
An object view’s base table

Insertion of data into a table can be executed in 2 different ways.

Conventional INSERT
Direct-path INSERT

In Conventional Insert statement, ORACLE reuses free space within the table into that the data in being inserted and maintain referential integrity constraints.
In Direct-path insert in ORACLE appends the inserted data after executing data in the table. The free space not reused.

Syntax:

INSERT INTO <TABLE_NAME>(col1,col2, - - ,col-n)VALUES(value1,value2, - - ,value-n);

Examples:
INSERTING DATA INTO ALL COLUMNS OF A TABLE:

SQL> INSERT INTO STUDENTS VALUES (111,’Subba’,’Reddy’,’1-Oct-90’,’M’);

In this case, the values should provide all the columns that exist inside the table. The order in which the values are declared in the values clause should follow the same order as that of the initial table order. The char, varchar and Date type data should be declared in single quotes.

INSERTING DATA INTO THE SPECIFIED COLUMNS:

SQL> INSERT INTO STUDENTS (SID, FNAME, LNAME, DOJ, GENDER)

     VALUES (123,’Nalla’,’Machu’,’19-APR-89’,’M’);

In this case, the order of the columns declared in the SELECT list need not be the same order as that of the initial table. The data values in the VALUES clause ought to match with that SELECT list. The columns for which the data is not provided are filled with NULL values. The INSERT statement will get rejected, if the columns that weren't provided with data are defined with NOT NULL constraints.

INSERTING NULL VALUES:
NULL values can be inserted into two ways.

IMPLICIT – Omit the column from the list.
EXPLICIT – Specify the NULL keyword. Specify the Empty String (‘ ‘) only for character Strings and Dates.

The targeting column should not be set as NOT NULL constraint.

SQL> INSERT INTO STUDENTS (SID, FNAME, LNAME, DOB, DOJ, FEES, GENDER)

     VALUES (359,’Subba’, NULL,’01-OCT-90’, NULL,’M’);

INSERTING SPECIAL VALUES:
SYSDATE FUNCTION:
It's a pseudo column provided by the ORACLE. The function returns the current date and time.

SQL> INSERT INTO STUDENTS (SID, FNAME, DOJ, FEES, GENDER)

     VALUES (359,’Subba’, SYSDATE, 25000,’M’);

USER FUNCTION:
It's a special function which records the present USER name.

SQL> INSERT INTO STUDENTS (SID, FNAME, LNAME, DOJ, DOB, FEES)

     VALUES (359,’Subba’, User, SYSDATE,’01-OCT-90’, 30000);

SUBSTITUTION VARIABLES:
These variables are used to store values temporarily. The values can store temporarily through,

Single Ampersand (&)
Double Ampersand (&&)
DEFINE and ACCEPT Commands

The single ampersand substitution variable apples for each instance when the SQL statement is create or executed. The double ampersand variables applied for all instances until that SQL statement exists.

USING SINGLE AMPERSAND SUBSTITUTION VARIABLE:

SQL> INSERT INTO STUDENT (SID, FNAME, LNAME, DOB, FEES)

     VALUES (&SID, ‘&FNAME’,’&LNAME’, ’&DOB’, 25000);

USING DOUBLE AMPERSAND SUBSTITUTION:

SQL> INSERT INTO STUDENT (SID, FNAME, LNAME, DOB, DOJ, FEES, GENDER)

     VALUES (&SID, ‘&FNAME’,’&LNAME’, ’&DOB’, SYSDATE, &&FEES, ‘&GENDER’);

DEFINING CUSTOMIZED PROMPTS:
The prompt can be customized by using the ACCEPT command of SQL * Plus. ACCEPT Command stores the values in a variable. PROMPT command displays the custom text. When creating a script using the substitutions variable to a file it can called with ACCEPT and PROMPT with greater meaning.

Example:

ACCEPT SID PROMPT ‘Please Enter the Student Number’.
ACCEPT FNAME PROMPT ‘Please Enter the First Name’.
ACCEPT FEES PROMPT ‘Please Enter the Course Fees’.

SQL> INSERT INTO STUDENTS (SID, FNAME, FEES) VALUES (&SID, ‘&FNAME’, &FEES);

Data types in Oracle DBMS

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sun, 17 Jul 2016 12:34:00 +0000

Read this Oracle Tutorial Point article to know about how to manage data and what are different data types available in SQL / Oracle. Each value in Oracle Database is manipulated by a data type. The data type value associates fixed set of properties with the value. The value of one data type is different from another data type. The data type defines the domain of values that every column can contain. The built-in data types of Oracle are categorized as,

CHARACTER DATA TYPES,
ROWID DATA TYPES,
NUMBER DATA TYPES,
LONG AND RAW DATA TYPES,
LARGE OBJECT DATA TYPES,
DATETIME DATA TYPES,

Character data types:
They store character data which can be alphanumeric data. The information can be,

Words,
Free – Form Text,
National character set,
Database character set,

They are minimal restrictive than other data types and have very few properties. The data is stored in strings with byte values. The data will belong to either,

7 – list ASCII character set
EBCDIC

Oracle support both single byte and multibyte character sets. The different character data types are,
CHAR, NCHAR, VARCHAR2, NVARCHAR2

CHAR Data type:
It represents fixed length character string. The size should be specified. If the data is less than the original given size, blank pads are applied. The default length is 1 Byte and the maximum is 2000 Bytes. The size of a character will range from one(1) Byte to four(4) Bytes depending on the database character set.

NCHAR Data type:
It is first time defined in Oracle 9i and contains only Unicode data. The columns maximum length is regulated by the National Character set definition. The largest size allowed is 2000 bytes and size has to be specified. If the data is shorter than the defined size, then the blank pads are applied.

VARCHAR2 Data type:
It define the variable length character string. The minimum size is one(1) Byte and the maximum size is 4k(4000) Bytes. It occupies only that space for which the data is provided.

NVARCHAR2 Data type:
It is first time defined in ORACLE 9i. It's defined for Unicode only date. The minimum for the scale is one(1) Byte and maximum is 4k(4000) Bytes.

Number Datatype:
It stores zero(0), positive and negative fixed and floating point numbers. The range of magnitude is,

                   1.0 * 10^-139 to 9.9 * 10¹²⁵

The general declaration is,

NUMBER (p,s):

P --> It specifies the precision. i.e. the total number of digits (1 to 38)
S --> It specified the scale. I.e. the number digits to the right of the decimal point. It will vary from -84 to 127.

Float Datatype:
It specifies to have a decimal point anywhere from the first to the last digit or will don't have any decimal point at all. The scale value is not applicable to floating point numbers as the range of digits which will appear after the decimal point is not restricted.
Syntax:
Float --> It specifies a floating point range with binary decimal precision 38 or binary precision of 126.
Float (B) --> It specified a floating point range with binary precision b.

The precision can range from 1(one) to 126.
Convert from binary to decimal precision multiply ‘b’ by 0.30103.
Convert from decimal to binary precision multiply the decimal precision by 3.32193.

The maximum of 126 digits of binary preciseness is roughly equivalent to 38 digits of decimal precision.

Long & Raw Data Types:
This data type stores variable length character string. It's used to store very lengthy text strings. The length of LONG values could also be restricted by the memory available on the computer. LONG column can be referenced in,

SELECT List.
SET clause of UPDATE statements.
VALUES clause of INSERT statements.

Restrictions:

A single table can contains only one LONG column.
Object types can't be created on LONG attributes.
LONG columns cannot appear in WHERE clauses or in Integrity Constraints.
Indexes can't be created on LONG Columns.
LONG can be returned through a function, but not through a stored procedure.
It may be declared in a PL/SQL unit but cannot be referenced in SQL.

DATE & TIME DATA TYPES:
Date Data Type:
It's used to store date and time information. The dates can be specified as literals using the Gregorian calendar. The information revealed by date is:

Century
Year
Month
Date
Hour
Minute
Second

The default date format in ORACLE is DD-MON-YY and it's specified in NLS-DATE_FORMAT. The default time accepted by ORACLE date is 12:00:00 AM. The default date specified in ORACLE data is the first day of the Current Month. The Date range in ORACLE is,

JANUARY 1, 4712 BC to DECEMBER 31, 9999 AD.

TIMESTAMP DATA TYPE:
It is an extension of the DATE data type. It store,

Day, Month, Year
Hour, Minute, Second

Syntax: TIMESTAMP [{Fractional – Seconds – Precision}]
Fractional – Seconds – Precision data types are interested for storage of binary data or Byte Strings. RAW and LONG RAW are variable range data types. They are mostly used to store graphics, sounds documents etc. The ORACLE converts the RAW & LONG RAW data into Hexadecimal type. Every Hexadecimal character represents four bits of RAW data.

Large Object Datatypes (LOB):
The built in LOB data types are BLOB, CLOB, NCLOB. These data types are stored internally. The Bfile is an LOB that is stored externally. The LOB data types can store large and unstructured data like Text, Image and Video and Spatial data. The maximum size is up to 4 GB. A LOB column contains LOB locators which can refer to out-of-line or in-line LOB values. LOB’s selection basically returns the LOB locator.

BFILE Datatypes:
It enables access to binary file LOB's those are stored in the file systems outside ORACLE. A BFILE column or the attribute stores the BFILE locator. BFILE locator manages the directory alias and the filename. A Binary file LOB does not participate in transactions and aren't recoverable. The maximum size is 4 GB.

BLOB Datatype:
It stores unstructured Binary Large Objects. They're Bit streams with no Character set semantics. They're provided with full transactional support.

CLOB Datatype:
CLOB stores single byte and multiple byte character data. Both fixed width and variable width character sets are supported. They're provided with full transactional support.

NCLOB Datatype:
It stores UNICODE data using the National Character set.

ROWID Datatype:
Each and every row in the database has an address. The rows address can be queried using the pseudo column ROWID. ROWID efficiently support partitioned tables and indexes.

Example-1:

SQL> Create table STUDENTS
     (
      SID    NUMBER(8),
      FNAME  VARCHAR2(50),
      LNAME  VARCHAR2(50),
      DOB    DATE,
      DOJ    DATE,
      FEES   NUMBER(6),
      GENDER VARCHAR2(1)
     );

Example-2:

SQL> CREATE TABLE LABSPECIFICATIONS
     (
      PROD_ID      NUMBER(6),
      PROD_PHOTO   BLOB,
      PROD_GRAPHIC BFILE,
      PROD_DESC    LONG
     );

DATABASE MANAGEMENT SYSTEM

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 16 Jul 2016 12:05:00 +0000

Read this Oracle Tutorial Point article to know about Oracle Database Management System and what are different models present in oracle and how to communicate with DBMS. Database Management System is a program. This takes care of,

Storing of Information
Retrieving of Information
Modification of Information
Deletion of Information

Different types of Databases:

Hierarchical
Network
Relational
Object Oriented
Object Relational

Relational Models: The Model was first outlined by E.F.Codd in 1970. The components of Relational Model are,

Collection of relations or objects that stores the data.
A set of operations that can act on the relations to produce the other relations.
Data integrity for consistency and accuracy.

A Relational database uses relations or two dimensional tables to store information.

Data Model:
The Data model's helps in exploring ideas and improve the understanding of the database design.

Purpose of Data Model:

Categorize
Communicate
Describe
Specify
Investigate
Evolve
Analyse
Imitate

Objective of Models:
To produce a model that fits a multitude of uses. It should be understood by an end user. It should contain enough details for a developer to make the database system.

Relational Database Properties:
It can be accessed and modified by executing SQL(Structured Query Language) statements. It contains a collection of tables with no physical pointers. It uses a group of operators. We need not specify the route to the tables. There is no need to identify how the data is organised physically.

Communicating with RDBMS:
The Structured Query Language is used to communicate with RDBMS.

Structured Query Language:
SQL permits the user to communicate with server. It is efficient and it is easy to learn and use. It's functionally complete, by permitting the user to define, retrieve and manipulate the data.

Entity Relationship Model (ER Model)

subbareddynallamachu@gmail.com (Subbareddy Nallamachu) — Sat, 16 Jul 2016 12:05:00 +0000

Read this Oracle Tutorial Point article to know benefits and key components of Entity Relationship (ER) Model in oracle database. In an effective system data is divided into describe categories or entities. An Entity Relationship(ER) Model is an illustration of multiple entities in a business and the relationships between them. It is built during the analysis phase of the System Development Life Cycle (SDLC). Entity Relationship(ER) Model separates the information required & the business from the activities performed.

Entity Relationship(ER)-Model Benefits:

ER-Model documents information for the organization in a clear, precise format.
ER-Model provides a clear picture of the scope of the information requirement.
ER-Model provides an easily understandable pictorial map for the database design.
ER-Model offers an effective framework for integrating several applications.

Key Components in Entity Relationship(ER)-Model:

ENTITY:
It's a thing of significance about which the information needs to be known.
ATTRIBUTES:
It's something that describe or qualifies an entity. Each attribute might be mandatory or optional.

Relational Database Terminology:
Tuple or Row:
It represents all data required for a specific instance in an entity. Every row is an entity is uniquely identified by declaring it as UNIQUE or PRIMARY KEY. The order of the rows isn't significant, while retrieving the data.

Attribute or Column:
It represents one kind of data in a table. The column order isn't significant when storing the data.

Field:
It can be found at the intersection of a row and a column. A field will have only one value or may not have a value at all. The absence of value in Oracle is described as NULL.

Relating Multiple Tables:
Each and every table contains data that are describes exactly only one entity per table. Data of multiple entities is stored in multiple tables. RDBMS enables the data in one table is related to another table by using the foreign keys. Foreign Key is a column or a group of columns that refer to a Primary Key in the same table or another table.

Relational Database Properties:
It shouldn't specify the access route to the tables and shouldn't reveal the physical arrange. The database is accessed by using Structured Query Language(SQL). The language is a collection of set of operations.

Structured Query Language(SQL) Statements:
SQL(Oracle) complies with industry accepted standards. The multiple categories into which the SQL statements falls are,

Data Retrieval Statements
- SELECT
Data Control Language Statements (DCL)
- GRANT, REVOKE
Data Manipulation Language Statements (DML)
- INSERT, UPDATE, DELETE, MERGE
Data Definition Language Statements (DDL)
- CREATE, ALTER, DROP, RENAME, TRUNCATE
Transaction Control Language Statements (TCL)
- COMMIT, ROLLBACK, SAVEPOINT

About the SQL Buffer:
All commands of SQL are typed at the SQL prompt. Only one(1) SQL statement is managed in the SQL Buffer. The current SQL statement replaces the previous SQL statement within the Buffer. The SQL statement will be divided into different lines within the SQL Buffer. Only one line that is, the current line can be active at a time in the SQL Buffer. At SQL prompt, editing is possible only in the current SQL Buffer line.

Each statement of SQL ought to be terminated using semicolon. One SQL statement can contains only one. To run the previous (old) or current SQL statement in the Buffer type ‘/’ at SQL prompt. To open the SQL Editor type ed at SQL prompt.

Create and Manage Tables:
Database Objects:
Oracle database can contain multiple data structures. The different Oracle Database objects are,

TABLE: Table is used to store data, Basic Unit.
VIEW: View is logically represents subsets of data from one or more(additional) tables.
SEQUENCE: Sequence is used to generate Primary Key values.
INDEX: It used to improve the performance of some queries.
SYNONYM: Synonym is used to give alternate names to objects.

Tables in Oracle:
Table can be created at any time, even when users are using the database. No need to specify size of the table. The structure of the table can be modified online.

Table Creation Rules to remember:

The user must have permission or CREATE TABLE command and storage area.
The table name should starts with a letter within the range of 1 – 30 characters long.
Names can contain: 0-9, A-Z, a-z, _,$, #
Names can't be duplicated for another object in the same Oracle server.
Name can't be Oracle server reserved word.
Names aren't case sensitive

Create Table Statement:
Syntax:

SQL> CREATE TABLE <TABLE_NAME>
(
Column_Name1 <Data type> (Width),
Column_Name2 <Data type> (Width),
     --
     --
Column_Namen <Data type> (Width)
);