Remove duplicates from a string separated by colon Oracle SQL using REGEXP

Remove duplicates from a string separated by colon or by any keyword

select distinct str from (

select regexp_substr('abc:xyz:abc:pqr:xyz','[^:]+',1,level) str from dual

connect by level <= regexp_count('abc:xyz:abc:pqr:xyz','[^:]+', 1)

);

How to get MAX DATE for each ID in Oracle?

Take the max from sub-query using group by and in outer query set equal (=) condition of max_date

SELECT T1.ACC_ID, T1.DATE AS MAX_DATE, T1.COL1, T1.COL2

  FROM EMP T1,

       (SELECT ACC_ID, MAX(DATE) AS MAX_DATE_I FROM EMP GROUP BY ACC_ID) T2

 WHERE T1.ACC_ID = T2.ACC_ID

   AND T1.MAX_DATE = T2.MAX_DATE_I

   AND ACTIVE_FLAG = 'Y';

--===============================================================

SELECT T1.DEPTNO, T1.HIREDATE

  FROM EMP T1,

       (SELECT DEPTNO, MAX(HIREDATE) AS MAX_DATE FROM EMP GROUP BY DEPTNO) T2

 WHERE T1.DEPTNO = T2.DEPTNO

   AND T1.HIREDATE = T2.MAX_DATE;

Defining 12c IDENTITY Columns in Oracle SQL Developer Data Modeler

Defining Triggers and Sequences to populate identity columns in Oracle Database is no longer required. You have an Oracle Database 12c instance up and running, and you’re ready to hit the ground running.

Now How Do I Draw That Up in SQL Developer Data Modeler?

Draw your table. You’ll want a column. 

RELATIONAL MODEL, COLUMN PROPERTIES

Ok, the Modeler now knows that this column is identifying, and that’s it’s going to be self-incrementing. Next we need to fill in the details.

MIN, MAX, INCREMENT BY, CACHE?

Last thing.

The modeler knows what you want to do with the column, but it doesn’t know what RDBMS features it has at its disposal. We need to go into the Physical Model level, ensuring we create a 12c physical model.

AFTER YOU’VE CREATED THE 12C PHYSICAL MODEL, GO TO THE TABLE, COLUMN AND ACCESS ITS PROPERTIES

YOU WANT THE ONE THAT SAYS ‘IDENTITY’ 

Here you go >>

THAT LOOKS RIGHT TO ME…

Auto Generate Sequence in Oracle 12c

Oracle 12c - No need to create sequences.

Use the below highlighted syntax to auto generate Sequences in Oracle 12c.

CREATE TABLE ABC (
    abc_id           NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name             VARCHAR2(50)
)
ALTER TABLE ABC ADD CONSTRAINT abc_id_pk PRIMARY KEY ( abc_id );

Note: In case you are passing the value for primary key manually, Oracle won't generate the sequence for that transaction and will start from the same number where it left last time irrespective of manual insertion.

How to split comma separated string in new row using CONNECT BY and REGEXP?

SELECT REGEXP_SUBSTR('APPLE,BOB,CARS', '[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('APPLE,BOB,CARS', '[^,]+', 1)

select * from
(select 'GIOVANNI COSTELLO' writer, 'RODRIGUEZ' author from dual) tt
where exists
(select * from (
select trim(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) as c1
from
(
select 'ARMIN RODRIGUEZ, GIOVANNI COSTELLO, RUBEN RODRIGUEZ ALARCON, RUEDIGER SKOCZOWSKY, XAVIER NAIDOO' as str
from dual) CONNECT BY LEVEL <= REGEXP_COUNT(str, '[^,]+', 1)
) where c1 = tt.writer);

select regexp_substr('abc:xyz:abc:pqr:xyz','[^:]+',1, level) str from dual

connect by level <= regexp_count('abc:xyz:abc:pqr:xyz','[^:]+', 1)

Get Data of Particular Month in Oracle

This is the easiest way of getting data of particular month

Select * from table where extract(MONTH from column_name ) = 4; -- April

Column_name should be of date type or timestamp.

How to find the value before or after Comma "," in Oracle sql?

using REGEXP_SUBSTR we can find out values before or after comma.

Here I am extracting the value before comma based on occurrence. The below code searches for a comma, return one or more occurrences of non-comma characters

SELECT REGEXP_SUBSTR ( 'KUMAR,SHIVAM,MYTHASS' , '[^,]+' , 1, 1) FROM DUAL;

The above code will extract the first occurrence of non-comma character.

If you want to extract the second occurrence of non-comma character, you need to find the second appearance-

SELECT REGEXP_SUBSTR ( 'KUMAR,SHIVAM,MYTHASS' , '[^,]+' , 1, 2) FROM DUAL;

Same way you can find Nth appearance of non-comma character.

How to get more than 4000 character like LISTAGG?

In case we want to list more than 3999 characters in one column-

SELECT TBL1.COL1,
       SUBSTR(XMLCAST(XMLAGG(XMLELEMENT(E,' | ' || TBL1.COL2)
                                       ORDER BY TBL1.COL3) AS CLOB),
             4) AS COL2
       FROM
       (
          SELECT DISTINCT COL1, COL2
          FROM TABLE_1 TB1,
          TABLE_2 TB2
          WHERE TB1.COL = TB2.COL
       ) TBL1
       WHERE ...
       GROUP BY TBL1.COL1

How to get every first letter from a string ?

SELECT REGEXP_REPLACE('KUMAR SHIVAM MYTHASS','(^| )([^ ])([^ ])*','\2') FIRST_LETTERS FROM DUAL;

FIRST_LETTERS
-------------
KSM

How to CREATE USER in Oracle 12C ?

In oracle 12c there is two types of users: common user and local user.

• Common User : The user is present in all containers (root and all PDBs).
• Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Likewise, there are two types of roles.

• Common Role : The role is present in all containers (root and all PDBs).
• Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Common users belong to Container Databases (CDB) as well as current and future Pluggable Databases (PDB). It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

Create Common Users

Create user start with C## and c##, as follows:

CREATE USER c##test_user IDENTIFIED BY password1; -- CONTAINER=ALL;

Create Local Users

ALTER SESSION SET CONTAINER = pdb1;

CREATE USER test_user IDENTIFIED BY password1; -- CONTAINER=CURRENT;

What is INSTR & SUBSTR in Oracle ?

SUBSTR

To extract the substring from any String we use SUBSTR
for e.g., mythass → thass

SUBSTR (STRING, START, LENGTH);

select SUBSTR('mythass', 3, 5) from dual;

------------------------
output: thass


INSTR

To get the position of any string we use INSTR
for e.g., mythass → thass


INSTR (STRING, SUBSTR, BEGIN_with_Nth_character_for_SUBSTR, RETRN_POS_Nth_OCCURENCE_OF_SUBSTR);
 /* N IS NUMBER */

SELECT INSTR('mythass','thass', 1, 1) FROM DUAL;
------------------------

output: 3

Some other examples-

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 1) FROM DUAL;

-- starting from the 1st character of substring, for 1st occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 2) FROM DUAL;

-- starting from the 1st character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 2, 2) FROM DUAL;         

-- starting from the 2nd character of substring, for 2nd occurence

-- same output as above query because it is getting the same position of OR

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

-- starting from the 3rd character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 3) FROM DUAL;

-- starting from the 3rd character of substring, for 3rd occurence

How to use & When to use Invoker Rights, Definer Rights in Stored Procedures & SQL methods ?

Sub-programs by default i.e., without AUTHID clause are called "Definer Rights" sub-programs.

Sub-programs with AUTHID clause are called "Invoker Rights" sub-programs.

How to use? Let see-

Assume you have two Schemas - MySchema_1, MySchema_2.

Both the Schemas are having table called EMP. Now, create a standalone procedure in MySchema_1.

CREATE PROCEDURE emp_details (

             p_emp_no NUMBER

            ,p_emp_name VARCHAR2

            ,p_emp_email VARCHAR2) AS

BEGIN

UDPATE EMP 

             SET emp_email = p_emp_email

             WHERE emp_no = p_emp_no;

END;

The above written is a "Definer Rights" sub-program. 

Assume that user MySchema_1 has granted the EXECUTE privilege on this procedure to user MySchema_2.

This will execute with the privileges of their owner (MySchema_1), not their current user (MySchema_2). So, it will update the EMP table of MySchema_1.

One way is to fully qualify references to the objects, as in

INSERT INTO MySchema_1.EMP...

CREATE PROCEDURE emp_details (

             p_emp_no NUMBER

            ,p_emp_name VARCHAR2

            ,p_emp_email VARCHAR2) AUTHID CURRENT_USER AS

BEGIN

UDPATE EMP 

             SET emp_email = p_emp_email

             WHERE emp_no = p_emp_no;

END;

The above written is an "Invoker Rights" sub-program.

Such invoker-rights subprograms are not bound to a particular schema.

When to use? Let see-

Invoker-rights subprograms let you reuse code and centralize application logic.

They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base.

e.g.,

Consider a company that uses a definer-rights (DR) procedure to analyze sales. To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, the procedure must also reside at each regional site. This causes a maintenance problem.

To solve the problem, the company installs an invoker-rights (IR) version of procedure analyze at headquarters. Now, all regional sites can use the same procedure to query their own sales tables.

To restrict access to sensitive data, you can have an invoker-rights subprogram call a definer-rights subprogram. Suppose headquarters would like procedure analyze to calculate sales commissions and update a central payroll table.

That presents a problem because current users of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data. The solution is to have procedure analyze call definer-rights procedure calc_comm, which in turn updates the payroll table.

How to find nth highest salary using rank and row_number?

2nd ..3rd highest salary using Aggregate Function MAX-

MAX returns maximum value of expr. You can use it as an aggregate or analytic function.

2nd highest salary-

select MAX(sal) from EMP 
WHERE 
sal <> ( select max(sal) from emp );    -- single value in a WHERE clause

3rd highest salary-

select MAX(sal) from EMP 
WHERE 
sal NOT IN            -- multiple values in a WHERE clause
( 
         (select max(sal) from emp)   -- till here >> return 2nd highest
       , (select max(sal) from emp where sal <> (select max(sal) from emp)) -- till here >> return 3rd highest
);

4th highest salary-

select MAX(sal) from EMP 
WHERE 
sal NOT IN         -- multiple values in a WHERE clause
( 
         ( select max(sal) from emp )   -- till here >> return 2nd highest
       , ( select max(sal) from emp where sal <> (select max(sal) from emp) ) -- till here >> return 3rd highest
       , ( select MAX(sal) from EMP WHERE sal NOT IN ( (select max(sal) from emp) , (select max(sal) from emp where sal <> (select max(sal) from emp))) )
);

Using this logic, you can find nth highest salary.
Note: The answer above is actually not optimal from a performance standpoint since it uses a subquery.

Find the nth highest salary in Oracle using ROWNUM (Best)

select * from
 (
    select EMPNO, Sal
    , row_number() over (order by Sal DESC) ROW_NUMBER
    from Emp
)
where ROW_NUMBER = n;  /*n is nth highest salary*/

Find the nth highest salary in Oracle using RANK

select * FROM 

(
    select EMPNO, Sal
    ,rank() over (order by Sal DESC) ranking
    from Emp
)
WHERE ranking = n;  /*n is nth highest salary*/

Is there a site for Online Oracle SQL practice?

Yes, there are so many~

http://www.quora.com/Is-there-a-site-for-online-SQL-practice

How SELECT statement executes in ORACLE SQL?

A common source of confusion is the simple fact that SQL syntax elements are not ordered in the way they are executed. The lexical ordering is:

• SELECT [ DISTINCT ]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

For simplicity, not all SQL clauses are listed. This lexical ordering differs fundamentally from the logical order, i.e. from the order of execution:

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY

There are three things to note:

1. FROM is the first clause, not SELECT. The first thing that happens is loading data from the disk into memory, in order to operate on such data.
2. SELECT is executed after most other clauses. Most importantly, after FROM and GROUP BY. This is important to understand when you think you can reference stuff that you declare in the SELECT clause from the WHERE clause. The following is not possible:

   SELECT A.x + A.y AS z
   FROM A
   WHERE z = 10 -- z is not available here!

   If you wanted to reuse z, you have two options. Either repeat the expression:

   SELECT A.x + A.y AS z
   FROM A
   WHERE (A.x + A.y) = 10

   ... or you resort to derived tables, common table expressions, or views to avoid code repetition. See examples further down.
3. UNION is placed before ORDER BY in both lexical and logical ordering. Many people think that each UNION subselect can be ordered, but according to the SQL standard and most SQL dialects, that is not true. While some dialects allow for ordering sub queries or derived tables, there is no guarantee that such ordering will be retained after a UNION operation