Oracle Basic 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]
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

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