SQL

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]
Mar 312015
 
CAST Function-Converting Between Timestamps and Dates

The CAST function can be used to convert a TIMESTAMP to a DATE and vice versa. First let’s convert a TIMESTAMP to a DATE. SELECT CAST(SYSTIMESTAMP AS DATE) ts_to_date FROM   dual; TS_TO_DAT ——— 31-MAR-15 To convert a DATE to a TIMESTAMP do the following. SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts FROM   dual; DATE_TO_TS ————————————————————————— 31-MAR-15 09.21.45.000000

[Read More]
Dec 022014
 

Difference Between CASE and DECODE Statements Both CASE and DECODE are conditional statement with IF-THEN-ELSE logic within a SQL statement. CASE statement can perform additional to what DECODE can do, but DECODE is cleaner then CASE if logic is equally well for both because DECODE is shorter and easier to understand. CASE can work with

[Read More]
Dec 012014
 

Performance Tuning on SQL and PL/SQL Performance Tuning can be performed by following few guidelines: 1. Database must be normalized which will help to decrease the storage requirement and faster search performance. Normalization Normalization is the process of efficiently organizing data in a database. It deals with elimination of redundant i.e. duplicate data and ensures

[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]
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]
Oct 082014
 

Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for

[Read More]
Oct 072014
 

Many times it is required in SQL to calculate number of days, number of hours, number of minutes and number of seconds from data time data field.Here, it is discussed one by one. SQL query to fetch number of days from dates in Oracle [Suppose start date is ’01-Jan-2014’ and end date is ’31-Jul-2014’] Query

[Read More]