Oracle

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 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]
Dec 162014
 

Solved 51 SQL Queries 37) SQL query to create a role and grant it to a user Create role <role_name>; //Create Role Grant create table, create view to <role_name>; // Grant privileges to role Grant <role_name> to <user_name1>, <user_name2>; // Grant Role to Users 38) SQL query to revoke select and insert privileges from a

[Read More]
Dec 152014
 

Solved 51 SQL Queries 25) SQL query to get the first day of a month Select trunc(to_date(’30-Nov-2014′,’DD-MON-YYYY’),’month’)from dual; Output: 01-Nov-2014 26) SQL query to get the first day of a Year Select trunc(to_date(’30-Nov-2014′,’DD-MON-YYYY’),’Year’)from dual; Output: 01-Jan-2014 27) SQL query to spell a day Select To_char(to_date(’20-Nov-2014′,’DD-MON-YYYY’),’ddspth’)from dual; Output: twentieth [day 20 is spelled as twentieth] 28)

[Read More]
Dec 112014
 

Solved 51 SQL Queries 13) SQL query to copy structure of a table Create table employee_new as select * from employee where 1>2; [Where”employee_new” is a copied table to “employee” table without any data] 14) SQL query using MERGE statement MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80)

[Read More]
Dec 102014
 

Solved 51 SQL Queries 1) SQL query to create partition by range on a table create table product (product_id number, product_name varchar2(30), Manufactured_Date date) partition by range (Manufactured_Date) ( partition part1 values less than (TO_DATE(‘2013-01-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)), partition part2 values less than (TO_DATE(‘2014-01-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)) ); 2) SQL query to create database link

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