Tag Archives: SQL Tips and Tricks

Create a Table from another Table in Oracle SQL

create a table

In my previous article, you have seen how to create a Table and Global Temporary Table in Oracle. In this article, you will learn how to create a table from another table. 1) How to create a Table from an Existing Table? 2) How to create a Table from an Existing Table with filtered data?

Create Normal Table and Global Temporary Table in Oracle

create table

We can create two types of Table in Oracle. 1) Normal Table (CREATE TABLE Command) 2) Global Temporary Table (CREATE GLOBAL TEMPORARY TABLE Command) How to create a Normal Table in Oracle? The create table command allows you to create normal table in Oracle. The syntax of CREATE TABLE command is as follows: CREATE TABLE

Alter Table Command in Oracle SQL

alter table command

The ALTER TABLE command allows you to rename an existing Oracle table to a new table. The same command can also be used to add, modify, or drop a column from an existing Oracle table. How to Alter a Table in Oracle? Syntax to rename a table: ALTER TABLE <table-name> RENAME TO <new-table-name>; Example: Suppose

How to use LPAD and RPAD Function

LPAD and RPAD function

If you want to pad any character on the left side or right side of a string, you can use LPAD and RPAD functions. In this article you will see how to use LPAD and RPAD functions in Oracle. LPAD function: The LPAD function is used to pad the left side of a string with

How to Create Index in Oracle SQL with Example

Create index in Oracle SQL

What is an Index? Index is created on existing tables to retrieve data more quickly. This is a performance tuning method for faster retrieval of records from a table. By default oracle creates B-tree indexes when you create an index on a table. If you want to create an index on a table you can

NVL, DECODE, NVL2, COALESCE-NULL Functions in Oracle

This article will provide you a summary of the NULL functions available for handling null values in Oracle. All examples are described with the use of following table. CREATE TABLE TEST_NULL_VALUE ( test_id NUMBER(4), value1 VARCHAR2(12), value2 VARCHAR2(12), value3 VARCHAR2(12), value4 VARCHAR2(12), value5 VARCHAR2(12) ); INSERT INTO TEST_NULL_VALUE values (100, 'FIRST','SECOND','THIRD','FORTH','FIFTH'); INSERT INTO TEST_NULL_VALUE values

Difference in RANK, DENSE_RANK, FIRST and LAST Analytic Functions

Rank Dense_Rank

This article gives you an overview of the RANK, DENSE_RANK, FIRST and LAST analytic functions. First create a table STUDENT and insert some sample records to perform the task. CREATE TABLE STUDENT ( STUDENT_ID NUMBER(4), DEPT VARCHAR2(20), SCHOLARSHIP NUMBER(5)); INSERT INTO STUDENT VALUES(2001,'CS',800); INSERT INTO STUDENT VALUES(2002,'CS',950); INSERT INTO STUDENT VALUES(2003,'CS',1100); INSERT INTO STUDENT VALUES(2004,'EC',1250);

SQL Tips: LAG and LEAD Analytic Functions

LAG and LEAD functions

LAG and LEAD functions can be used in Oracle PL/SQL and apply on Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i versions. Let’s understand the difference between LAG and LEAD analytical functions in Oracle. First create a table called Student and insert some records in the table. CREATE TABLE STUDENT ( STUDENT_ID

How to Escape Special Characters in Oracle SQL queries

escape special characters in oracle sql

Whether you are working as PL/SQL developer or ETL developer or Data Warehousing Developer or using SQL and PL/SQL, you must know about how to escape special characters in SQL queries. In the article, you will learn: 1) How to escape ampersand (&) characters in SQL? 2) How to escape wildcard characters in SQL? 3)

Siteground SALE
60% OFF SiteGround WordPress Hosting – $3.95/month (Exclusive Offer !!!)