Oracle Complex Queries

Nov 182015
 

Basic Tuning for SQL Query   Ordering of the FROM clause.  When we are fetching data from more than one table, write the table with fewer rows at the end of the FROM clause. Therefore, the table returning the least number of rows should be last in the FROM list. Example: SELECT COL1 FROM TAB1,

[Read More]
Dec 232014
 

14) SQL Query to compare NULL values in Oracle table Step 1: Create two Table “Test1” and “Test2”: CREATE TABLE TEST1 (A NUMBER,B NUMBER); CREATE TABLE TEST2 (A NUMBER,B NUMBER); Step 2: Insert Rows: INSERT INTO TEST1 VALUES (1,25); INSERT INTO TEST1 VALUES (2,20); INSERT INTO TEST1 VALUES (3,NULL); INSERT INTO TEST1 VALUES (4,30); INSERT

[Read More]
Dec 222014
 

1) SQL query to fetch first 50 rows from a Table.  SELECT   * FROM   employee WHERE   ROWNUM < 51; 2) SQL query to fetch 50 random rows from a Table  SELECT   * FROM   (SELECT   * FROM   employee ORDER BY   DBMS_RANDOM.VALUE) WHERE   ROWNUM < 51; 3) SQL Query to generate Serial/Sequence Number for a Table.  SELECT  

[Read More]
Nov 212014
 

In many scenarios’ you required to convert column data into Delimited (comma or pipelined) separated values into rows in SQL. Follow following two examples step by step to convert column data into row with Comma and Pipelined separated values. Column to Row Data with Comma Separated Example 1: Convert column data into row with Comma Separated

[Read More]
Nov 192014
 

In order to find missing numbers from a series in oracle follow following steps: Step 1: Create one “TEST” table with single column “A”. Create table test (A number); Step 2: Insert few random numbers into “TEST” table. insert into test values (3); insert into test values (7); insert into test values (8); insert into

[Read More]
Nov 172014
 

SQL query to fetch Day from date in Oracle [Suppose Date Time Field value is “17-Nov-2014 15:35:26”] Query to fetch day from date: select extract (day from to_date(’17-Nov-2014 15:35:26′, ‘DD-MON-YYYY HH24:MI:SS’)) “DAY” from dual; Output: DAY 17   SQL query to fetch Month from date in Oracle [Suppose Date Time Field value is “17-Nov-2014 15:35:26”]

[Read More]
Nov 052014
 

  Many times it is required to fetch data in a hierarchical form. The result can be fetched using Recursion. Recursion means self-reference. To explain it in a better way look at the following example: Step 1: Create “Category” table with 3 columns, ID as Primary key, Name and ParentID that references ID column of

[Read More]
Oct 302014
 
Loading DAT Files through SQL Loader Using TOAD

In order to import DAT file into Oracle table through TOAD follow following steps: Step1: Create Table “Employee” as below: CREATE TABLE EMPLOYEE (   EMPNAME  VARCHAR2(100 BYTE),   SALARY   NUMBER ); Step 2: Create one DAT file with name “Insert_Employee_Data.dat” containing data of Employee table for insertion and save it in D:\ drive as

[Read More]
Oct 292014
 

ROW_NUMBER is a built-in Oracle function that assigns unique number to each row it is applied to. Through ROW_NUMBER function you can add sequence number to row data, can fetch rank-wise data, and can also apply conditions to the data fetched. Syntax: ROW_NUMBER() OVER () To understand better the use of ROW_NUMBER consider following example:

[Read More]
Oct 272014
 

To find the difference between RANK () and DENSE_RANK consider Following Example: Step1: Create Table “Employee” as below: CREATE TABLE EMPLOYEE (   EMPNAME  VARCHAR2(100 BYTE),   SALARY   NUMBER ); Step 2: Insert few Records into “Employee” Table: insert into Employee values (‘John’, 500); insert into Employee values (‘Bill’, 1000 ); insert into Employee values

[Read More]