SQL Server - Creating Index

Index is a database object created on a table to arrange records in such a way that the data can be searched quickly and efficiently. The table with index is best suitable when there is frequent search against the table. If the table is updated or new records are inserted frequently, the existence of Index will slow down this process. So it is not advisable to use Index on those kind of tables. There are different types Indices that can be created in SQL Server. Some of the options and types of Indices are explained below.

UNIQUE

Creates a unique index on a table or view. In case of UNIQUE index no two records are allowed to have same value on the fields the index is created upon. A clustered index on a view must be unique. UNIQUE Index cannot be created, if the table contains duplicate value even if the IGNORE_DUP_KEY property is set to ON. Only columns with NOT NULL constraint is allowed to be part of Unique Index columns. Multiple NULL values are considered as duplicate.

CLUSTERED

Creates an index which orders the key values physically as it is ordered logically. Only one clustered index is allowed for a table or view. Creating a unique clustered index on a view physically materializes the view. When we create unique Clustered Index on a view, it should be created before any other index is created on the view. Clustered Index should be created before creating any Non-Clustered indices. The word CLUSTERED is used to create the Clustered index, absence of the word will create a Non-Clustered Index. A view with a unique clustered index is called an indexed view.

NONCLUSTERED

Creates an index that specifies the logical ordering of a table. With a non-clustered index,  the records are ordered logically, not physically using index tables. The index keys are ordered as in the logical order specified and physical order is independent of logical order. A table can have maximum 999 Non-Clustered Indexes. Indexes can be created implicitly with PRIMARY KEY or UNIQUE or explicitly with CREATE INDEX.

In simple words, Clustered Indexes orders the records physically. Non-Clustered Indexes orders records logically. Below is the syntax for creating the indexes.

The difference between above 3 are: Unique index decides whether the key values are unique are not. Unique index allows only one NULL value in the key column whereas the Clustered allows multiple NULL values in key columns. Clustered and Non-clustered decides whether to organize records physically or logically.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX Index_Name 

    ON Table_Name ( column_1 [ ASC | DESC ] [ ,...n ] ) 

    [ WHERE <Filter_Condition> ]

Example 1:

CREATE UNIQUE INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 1 creates a Unique Index on the table Emp_Tab.

Example 2:

CREATE CLUSTERED INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 2 creates a Clustered Index on the table Emp_Tab and physically order the records on the basis of emp_id.

Example 3:

CREATE INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

or

CREATE NONCLUSTERED INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 3 creates a Non-Clustered Index on the table Emp_Tab and order the records on the basis of emp_id logically.

Thanks for reading this post !

Read More

MySQL - String Functions

Hi Everyone,

Here are the some of the string functions in MySQL . Before we start we should keep below points in mind.

• String functions return NULL, if the length of the result would be greater than the value of the max_allowed_packet system variable.
• For functions that operate on string positions, the first position is numbered 1.
• For functions that take length arguments, non-integer arguments are rounded to the nearest integer.

ASCII(str) 

It returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL.

mysql> SELECT ASCII('2');
+------------+
| ASCII('2') |
+------------+
|              50 |
+------------+

mysql> SELECT ASCII(2);
+----------+
| ASCII(2) |
+----------+
|            50 |
+----------+

mysql> SELECT ASCII('dx');
+-------------+
| ASCII('dx') |
+-------------+
|              100 |
+-------------+

BIN(N)
It returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

mysql> SELECT BIN(12);
+----------+
| BIN(12)  |
+----------+
| 1100        |
+----------+

BIT_LENGTH(str)
It returns the length of the string str in bits.

mysql> SELECT BIT_LENGTH('Tech Volcano');
+------------------------------------+
|   BIT_LENGTH('Tech Volcano') |
+------------------------------------+
|                                                   96 |
+------------------------------------+

CHAR(N,... [USING charset_name])
This function interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values will be skipped.

mysql> SELECT CHAR(84,101,99,104,32,86,111,108,99,97,110,'111');
+---------------------------------------------------------+
| CHAR(84,101,99,104,32,86,111,108,99,97,110,'111') |
+---------------------------------------------------------+
| Tech Volcano                                                               |
+---------------------------------------------------------+

mysql> SELECT CHAR(111,111.5,111.7,'111.2');
+---------------------------------+
| CHAR(111,111.5,111.7,'111.2')  |
+---------------------------------+
| oppo                                          |
+---------------------------------+

CHAR_LENGTH(str) / CHARACTER_LENGTH(str)
It returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

mysql> SELECT CHARACTER_LENGTH('Tech Volcano');
+----------------------------------------------+
| CHARACTER_LENGTH('Tech Volcano') |
+----------------------------------------------+
|                                                                  12 |
+----------------------------------------------+

CONCAT(str1,str2,...) It concatenates all the arguments and produce the result. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form. It returns NULL if any argument is NULL.

mysql> SELECT CONCAT('Tech',' ','Volcano');
+--------------------------------+
| CONCAT('Tech',' ','Volcano') |
+--------------------------------+
| Tech Volcano                          |
+--------------------------------+

CONCAT_WS(separator,str1,str2,...)
It stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

mysql> SELECT CONCAT_WS(' ','Tech','Volcano');
+--------------------------------------+
| CONCAT_WS(' ','Tech','Volcano') |
+--------------------------------------+
| Tech Volcano                                  |
+------------------------------- ------+

ELT(N,str1,str2,str3,...)
It returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.

mysql> SELECT ELT(5,'Anything','and','Everything','in','IT');
+------------------------------------------------+
| ELT(5,'Anything','and','Everything','in','IT') |
+------------------------------------------------+
| IT                                                                     |
+------------------------------------------------+

FIELD(str,str1,str2,str3,...)
Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

mysql> SELECT FIELD('IT','Anything','and','Everything','in','IT');
+-----------------------------------------------------+
| FIELD('IT','Anything','and','Everything','in','IT') |
+-----------------------------------------------------+
|                                                                               5 |
+-----------------------------------------------------+

FORMAT(X,D)
It formats the number X to a format like '9,999,999.99', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

mysql> SELECT FORMAT(12345.123456, 3);
+--------------------------------+
| FORMAT(12345.123456, 3)   |
+--------------------------------+
| 12,345.123                               |
+--------------------------------+

mysql> SELECT FORMAT(12345.1,4);
+-------------------------+
| FORMAT(12345.1,4)   |
+-------------------------+
| 12,345.1000                 |
+-------------------------+

mysql> SELECT FORMAT(12345.2,0);
+-------------------------+
| FORMAT(12345.2,0)   |
+-------------------------+
| 12,345                           |
+-------------------------+

HEX(str)/ HEX(N) /UNHEX(HxN)
For a string argument str, HEX() returns a hexadecimal string representation of str where each character in str is converted to two hexadecimal digits. The inverse of this operation is performed by the UNHEX() function.
For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed by CONV(HEX(N),16,10).

mysql> SELECT 0x5465636820566F6C63616E6F, HEX('Tech Volcano'), UNHEX(HEX('Tech Volcano'));
+-------------------------------------+----------------------------------+------------------------------------+
| 0x5465636820566F6C63616E6F  | HEX('Tech Volcano')                | UNHEX(HEX('Tech Volcano')) |
+-------------------------------------+----------------------------------+------------------------------------+
| Tech Volcano                                 | 5465636820566F6C63616E6F | Tech Volcano                                |
+-------------------------------------+----------------------------------+------------------------------------+

mysql> SELECT HEX(255), CONV(HEX(255),16,10);
+------------+----------------------------+
| HEX(255) | CONV(HEX(255),16,10) |
+------------+----------------------------+
| FF              | 255                                   |
+------------+----------------------------+

INSERT(str,pos,len,newstr)
It returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

mysql> SELECT INSERT('Tec-----cano', 4, 5, 'h Vol');
+------------------------------------------+
| INSERT('Tec-----cano', 4, 5, 'h Vol')   |
+------------------------------------------+
| Tech Volcano                                         |
+------------------------------------------+

mysql> SELECT INSERT('Tec-----cano', -1, 5, 'h Vol');
+------------------------------------------+
| INSERT('Tec-----cano', -1, 5, 'h Vol')  |
+------------------------------------------+
| Tec-----cano                                          |
+------------------------------------------+

mysql> SELECT INSERT('Tec-----cano', 4, 50, 'h Vol');
+------------------------------------------+
| INSERT('Tec-----cano', 4, 50, 'h Vol') |
+------------------------------------------+
| Tech Vol                                                 |
+------------------------------------------+

INSTR(str,substr)
It returns the position of the first occurrence of substring substr in string str.

mysql> SELECT INSTR('Tech Volcano', 'vol');
+--------------------------------+
| INSTR('Tech Volcano', 'vol') |
+--------------------------------+
|                                              6 |
+--------------------------------+

mysql> SELECT INSTR('Tech Volcano', 'Voll');
+---------------------------------+
| INSTR('Tech Volcano', 'Voll') |
+---------------------------------+
|                                               0 |
+---------------------------------+

LCASE(str) / LOWER()
It returns the string str in lowercase.

mysql> SELECT LCASE('TECH VOLCANO');
+---------------------------------+
| LCASE('TECH VOLCANO')   |
+---------------------------------+
| tech volcano                            |
+---------------------------------+
mysql> SELECT LOWER('TECH VOLCANO');
+---------------------------------+
| LOWER('TECH VOLCANO') |
+---------------------------------+
| tech volcano                            |
+----------------------------- ---+

LEFT(str,len)
It returns the leftmost len characters from the string str, or NULL if any argument is NULL.

mysql> SELECT LEFT('TECH VOLCANO', 6);
+---------------------------------+
| LEFT('TECH VOLCANO', 6)  |
+---------------------------------+
| TECH V                                    |
+---------------------------------+

LPAD(str,len,padstr)
It returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

mysql> SELECT LPAD('Tech Volcano',15,'?');
+-------------------------------+
| LPAD('Tech Volcano',15,'?') |
+-------------------------------+
| ???Tech Volcano                   |
+-------------------------------+

mysql> SELECT LPAD('Tech Volcano',10,'?');
+---------------------------------+
| LPAD('Tech Volcano',10,'?')    |
+---------------------------------+
| Tech Volca                                |
+---------------------------------+

LTRIM(str)
It returns the string str with leading space characters removed.

mysql> SELECT LTRIM('  Tech Volcano');
+-----------------------------+
| LTRIM('  Tech Volcano')  |
+-----------------------------+
| Tech Volcano                     |
+-----------------------------+

MAKE_SET(bits,str1,str2,...)
It returns a set value (a string containing substrings separated by “,” characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.

mysql> SELECT MAKE_SET(1,'a','b','c');
+--------------------------+
| MAKE_SET(1,'a','b','c') |
+--------------------------+
| a                                     |
+--------------------------+

mysql> SELECT MAKE_SET(1 | 4,'a','b','c');
+-------------------------------+
| MAKE_SET(1 | 4,'a','b','c')   |
+-------------------------------+
| a,c                                          |
+-------------------------------+

mysql> SELECT MAKE_SET(1 | 4,'a','b',NULL,'d');
+--------------------------------------+
| MAKE_SET(1 | 4,'a','b',NULL,'d') |
+--------------------------------------+
| a                                                       |
+--------------------------------------+

mysql> SELECT MAKE_SET(0,'a','b','c');
+--------------------------+
| MAKE_SET(0,'a','b','c') |
+--------------------------+
|                                         |
+--------------------------+

MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

mysql> SELECT MID('Tech Volcano',3,5);
+----------------------------+
| MID('Tech Volcano',3,5) |
+----------------------------+
| ch Vo                                |
+----------------------------+

OCT(N)
It returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

mysql> SELECT OCT(12);
+------------+
| OCT(12)    |
+------------+
| 14              |
+------------+

OCTET_LENGTH()
It is a synonym for LENGTH().

mysql> SELECT OCTET_LENGTH(14);
+--------------------------+
| OCTET_LENGTH(14) |
+--------------------------+
|                                     2 |
+--------------------------+

QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (“\”), single quote (“'”), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.

mysql> SELECT 'Don\'t!';
+-----------+
| Don't!       |
+-----------+
| Don't!       |
+-----------+

mysql> SELECT QUOTE('Don\'t!');
+---------------------+
| QUOTE('Don\'t!')  |
+---------------------+
| 'Don\'t!'                  |
+---------------------+

REPEAT(str,count)
It returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.

mysql> SELECT REPEAT('#', 5);
+------------------+
| REPEAT('#', 5)  |
+------------------+
| #####               |
+------------------+

REPLACE(str,from_str,to_str)
It returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('w.techvolcano.co.in', 'w', 'www');
+--------------------------------------------------+
| REPLACE('w.techvolcano.co.in', 'w', 'www')  |
+--------------------------------------------------+
| www.techvolcano.co.in                                     |
+--------------------------------------------------+

REVERSE(str)
It returns the string str with the order of the characters reversed.

mysql> SELECT REVERSE('onacloV hceT');
+------------------------------+
| REVERSE('onacloV hceT') |
+------------------------------+
| Tech Volcano                       |
+------------------------------+

RIGHT(str,len)
It returns the rightmost len characters from the string str, or NULL if any argument is NULL.

mysql> SELECT RIGHT('Tech Volcano', 4);
+------------------------------+
| RIGHT('Tech Volcano', 4) |
+------------------------------+
| cano                                     |
+------------------------------+

RPAD(str,len,padstr)
It returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

mysql> SELECT RPAD('Volcano',10,'*');
+-------------------------+
| RPAD('Volcano',10,'*') |
+-------------------------+
| Volcano***                    |
+-------------------------+

mysql> SELECT RPAD('Volcano',6,'*');
+------------------------+
| RPAD('Volcano',6,'*') |
+------------------------+
| Volcan                         |
+------------------------+

RTRIM(str)
It returns the string str with trailing space characters removed.

mysql> SELECT RTRIM('Tech Volcano     ');
+-------------------------------+
| RTRIM('Tech Volcano     ')  |
+-------------------------------+
| Tech Volcano                        |
+-------------------------------+

SOUNDEX(str)
It returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.
expr1 SOUNDS LIKE expr2

This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N)
It returns a string consisting of N space characters.

mysql> SELECT CONCAT('Tech',SPACE(6),'Volcano');
+------------------------------------------+
| CONCAT('Tech',SPACE(6),'Volcano') |
+------------------------------------------+
| Tech      Volcano                                   |
+------------------------------------------+

SUBSTR(str,pos) / SUBSTR(str FROM pos) / SUBSTR(str,pos,len) / SUBSTR(str FROM pos FOR len)
SUBSTR() is a synonym for SUBSTRING().

mysql> SELECT SUBSTRING('Volcano',4);
+------------------------------+
| SUBSTRING('Volcano',4) |
+------------------------------+
| cano                                    |
+------------------------------+

mysql> SELECT SUBSTRING('Volcano' FROM 4);
+--------------------------------------+
| SUBSTRING('Volcano' FROM 4) |
+--------------------------------------+
| cano                                                |
+--------------------------------------+

mysql> SELECT SUBSTRING('Volcano',2,3);
+----------------------------------+
| SUBSTRING('Volcano',2,3)     |
+----------------------------------+
| olc                                              |
+----------------------------------+

mysql> SELECT SUBSTRING('Volcano', -3);
+--------------------------------+
| SUBSTRING('Volcano', -3)   |
+--------------------------------+
| ano                                          |
+--------------------------------+

mysql> SELECT SUBSTRING('Volcano', -5, 3);
+----------------------------------+
| SUBSTRING('Volcano', -5, 3) |
+----------------------------------+
| lca                                              |
+----------------------------------+

mysql> SELECT SUBSTRING('Volcano' FROM -4 FOR 2);
+-----------------------------------------------+
| SUBSTRING('Volcano' FROM -4 FOR 2) |
+-----------------------------------------------+
| ca                                                                  |
+-----------------------------------------------+

If len is less than 1, the result is the empty string.

SUBSTRING_INDEX(str,delim,count)
It returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

mysql> SELECT SUBSTRING_INDEX('www.techvolcano.co.in', '.', 1);
+------------------------------------------------------------+
| SUBSTRING_INDEX('www.techvolcano.co.in', '.', 1) |
+------------------------------------------------------------+
| www                                                                                 |
+------------------------------------------------------------+

mysql> SELECT SUBSTRING_INDEX('www.techvolcano.co.in', '.', 2);
+------------------------------------------------------------+
| SUBSTRING_INDEX('www.techvolcano.co.in', '.', 2) |
+------------------------------------------------------------+
| www.techvolcano                                                            |
+------------------------------------------------------------+

mysql> SELECT SUBSTRING_INDEX('www.techvolcano.co.in', '.', -2);
+-------------------------------------------------------------+
| SUBSTRING_INDEX('www.techvolcano.co.in', '.', -2) |
+-------------------------------------------------------------+
| co.in                                                                                    |
+-------------------------------------------------------------+

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
It returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM('  volcano   ');
+----------------------+
| TRIM('  volcano   ') |
+----------------------+
| volcano                    |
+----------------------+

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxvolcanoxxx');
+---------------------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxvolcanoxxx') |
+---------------------------------------------------+
| volcanoxxx                                                         |
+---------------------------------------------------+

mysql> SELECT TRIM(BOTH 'x' FROM 'xxxvolcanoxxx');
+----------------------------------------------+
| TRIM(BOTH 'x' FROM 'xxxvolcanoxxx') |
+----------------------------------------------+
| volcano                                                        |
+----------------------------------------------+

mysql> SELECT TRIM(TRAILING 'ano' FROM 'volcano');
+----------------------------------------------+
| TRIM(TRAILING 'ano' FROM 'volcano') |
+----------------------------------------------+
| volc                                                               |
+----------------------------------------------+

UCASE(str)
UCASE() is a synonym for UPPER().

mysql> SELECT UPPER('Tech Volcano');
+---------------------------+
| UPPER('Tech Volcano') |
+---------------------------+
| TECH VOLCANO          |
+---------------------------+

Thanks for reading this article !

Read More

Oracle - String Aggregate Functions

Hi Everyone,

If there is a requirement to list values from multiple rows to a single row related with a specific value, Oracle string aggregation function can be used to achieve this. Below are the different methods available in different versions of Oracle. 

LISTAGG Analystic Function in 11g Release 2

SELECT DEPTNO, LISTAGG(ENAME, ',') 

WITHIN GROUP (ORDER BY ENAME) AS EMPLOYEES

FROM   EMP

GROUP BY DEPTNO;

WM_CONCAT Function (Works with 11g Release 1)

SELECT DEPTNO, WM_CONCAT(ENAME) AS EMPLOYEES

FROM   EMP

GROUP BY DEPTNO;

Table Content

Result of the query

Thanks for visiting my blog and reading this article !

Read More

Oracle – Hierarchical Queries

Hi Everyone,

I am here to explain the Oracle Hierarchical queries with the help of an example.

Oracle stores data in terms of records and there is no mechanism to store data in hierarchically. But there are methods available in Oracle 11g package to relate and display the hierarchical data. I will introduce a Sample Table TBL_ORGN, which stores the reporting structure of organization.

--Creates table

CREATE TABLE TBL_ORGN

(

                EMPID NUMBER,

                ENAME VARCHAR2(100),

                MGRID NUMBER

);

--Inserts records into the table

BEGIN

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (1, 'SAM',NULL);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (2,'JIM',1);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (3,'GREG',1);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (4,'HARRY',2);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (5,'SHANE',3);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (6,'HILTON',3);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (7,'CHRIS',2);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (8,'JENNA',4);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (9,'MARK',5);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (10,'ANGEL',6);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (11,'JANE',7);

END;

--Select the records

SELECT * FROM TBL_ORGN;

If we need to know who is reporting to JIM.

SELECT EMPID, ENAME FROM TBL_ORGN WHERE MGRID=2;

But if we need to know all the employees working under JIM. Oracle helps to traverse the hierarchical data in the table. There are some keywords used for this.

START WITH

It specifies the start of the hierarchy. In this example, JIM’s record in the starting point in the hierarchy.

CONNECT BY PRIOR

It defines how two records are related. In this example, EMPID (child) and MGRID (parent) are related.

PRIOR

This is used to achieve the traversal of the hierarchical data in the table.

SELECT

EMPID, ENAME, MGRID, LEVEL

FROM TBL_ORGN

START WITH EMPID = 2 --JIM'S ID

CONNECT BY PRIOR EMPID = MGRID

ORDER BY LEVEL, EMPID

Direction of the Data Traversal

The direction of the traversal is decided by how we set the child-parent relation in the CONNECT BY PRIOR clause.

In the above example, it gives a downward reporting structure. i.e. employees under JIM.

If we need to know who are the reporting managers of JIM.

SELECT

EMPID, ENAME, MGRID

FROM TBL_ORGN

START WITH EMPID = 2 --JIM'S ID

CONNECT BY PRIOR MGRID = EMPID

ORDER BY EMPID

Thanks for reading my Blog !

Read More