Introduction

As we all know that a table can be defined as a collection of rows and columns but in the Relational Database management system (RDBMS) tables are database objects which help in organizing the data into rows and columns, so it is like a data structure. The Oracle database may have multiple data structures. Each structure should be described in the database design so that it may be built during the database development build stage.

• Table: Data storage.
• View: A subset of data from one or more table is referred to as a view.
• Sequence: Generates numeric values.
• Index: Used to improve and to speed up the performance of some queries.
• Synonym: Gives an object a different name.

Oracle Tables

To access and to feed the data into the database, you execute a Structured Query Language (SQL) which is the American National Standards Institute (ANSI) standard language for operating relational databases. SQL has a set of statements that all users access the data from the Oracle database.

Different SQL statements are used for different tasks, including:

•     Querying data.
•     For insertion, updating & deletion of rows.
•     Creating, replacing, altering, and dropping objects.
•     Guarantee database consistency and integrity.

SQL combines all the preceding processes into a single language and allows you to work with data at a logical level.

Create Table

To create a table you should have an existing database/schema created, access that database from the oracle SQL developer tool or sqlplus keep in mind that you or the user must have create table system privilege.

Syntax of create table

             CREATE TABLE table_name

(column_name 1 Data-type(size)  constraints,

  column_name 2 Data-type(size)  constraints,

  ………

  column_name 1000 Data-type(size) constraints

);

Create Table As

Now let’s take an example of creating a table and understand how an oracle will create a table, CREATE TABLE is an oracle reserved word, or we can say an oracle keyword whereas table names are user-defined so you can specify whatever name you want to your table and its columns. Every column should be separated from each other using commas. Oracle has a maximum limit of 1000 columns in a table and no limits on rows.

CREATE TABLE Persons (

            Person_ID int,

            Last_Name varchar(255),

            First_Name varchar(255),

            Address varchar(255),

            City varchar(255)

);

Alter Table

It is used to add, delete, or modify columns or constraints to your table.

Syntax of ALTER TABLE

ALTER TABLE table_name

ADD column_name datatype;

Now let’s take an example of alter table

ALTER TABLE Persons 

ADD Email varchar(255);

Drop Table

A database table can be deleted using the DROP TABLE statement, remember that dropping a table will delete all the rows of the table and also the structure of the table.

Syntax of Drop table

DROP TABLE table_name;

Example:

DROP TABLE Persons;

Temp Tables

When temporary data is stored in SQL Server, the Temp Table is used.

You are the only one who can see the temporary table. No one else will be able to see or affect your temporary table, even if someone else creates one with the same name.

There are two types of temp tables.

1) Global Temp Tables

The ## symbol was used to create a global table. When the user disconnects, the global Temp Table is removed.

Here ##Global_Temp Table created using CREATE TABLE.

Example:

CREATE TABLE ##Global_Temp (

             ID int,

             Name varchar (255)

             );

2) Local Temp Tables

The # symbol was used to create a local table. When the user disconnects, the Local Temp Table is removed.

There are two techniques to make temporary tables.

1) CREATE TABLE

Here #Student Table created using CREATE TABLE.

Example:

CREATE TABLE #Student (

             Roll_ID int,

             Last_Name varchar(255),

             First_Name varchar(255)

             );

2) SELECT INTO

Selecting all the records from the temp table which was created earlier

SELECT * FROM #TEMPTABLE;