Class 2 Statements in Oracle DDL

Statements in Oracle

Statements in Oracle is very much important topic to understand rest other topics better, so there will be lot of important interview questions will be there in this chapter.

This statements in Oracle is also called as Subset of oracle statements.

Before entering in to the statements in oracle, everyone should know the basic understanding about Data types in Oracle with that clear content will be much used to understand this subset concept will be easy to learn.

Overview of Statements in Oracle

• At the end of this chapter you will get clear Understanding about the statements of oracle

• With the help of this statements, you can easily create database objects like Tables

• Able to create tables, columns, filling the data and merging and other privileges related parts will be seen here.

• DDL, DML, DCL, TCL are explained very well in this post.

  

POINTS TO REMEMBER: Always use underscore while naming any table or columns. Example: Instead of using customer name, use customer_name. Can't be able to change the data type from VARCHAR2 to NUMBER, if data is present inside the column. [In MODIFY command of alter command in DDL ] Don’t forget to mention the keyword (“Column” for RENAME and DROP alter command) No need to mention the keyword (“Table” for RENAME command). Don’t confuse ALTER column level command and DDL table level command.

DATA DEFINE LANGUAGE

• It is Auto commit Statement, so no need to give commit in this transaction, this commit can be detailed explanation in TCL (Transaction Control Language)
• This DDL (Data Define Language) is used to give a structure to the particular table and as well as creating a table and doing any changes inside the table.

CREATE ALTER [Command Level] RENAME DROP TRUNCATE

Shortcut to remember this

CREATE COMMAND

• It is used to create a table

• Syntax: CREATE TABLE table_name (Column_name datatype(size))

Example

CREATE TABLE orders (id NUMBER);

Orders -> Table name

Id -> column name

Number -> data type.

ALTER COMMAND

• It is used to create a structure for a table

• By using this we can do any changes in the columns (Alter command)

• This alter command is subdivided into 4 categories.

ADD REMOVE MODIFY DROP

NOTE: The above command add, remove, modify and drop can be used only for column level only. If we use without an alter command it will act in the table.

ADD	Used to add a new column in a existing table
Syntax: ALTER TABLE table_name ADD column_name DATATYPE

RENAME	Used to rename a existing column in a table
Syntax: ALTER TABLE table_name RENAME column old_name TO new_name

MODIFY	Used to change the data type of particular column
Syntax: ALTER TABLE table_name MODIFY column_name new DATATYPE

DROP	Used to drop the column in a existing table
Syntax: ALTER TABLE table_name DROP column column_name

RENAME COMMAND

• It is used to change the name of the table

DROP COMMAND

• It is used to drop the table, and as well as there won’t be any records

• After dropping the table, if you try to view the table it will throw an error like table or view does not exist.

TRUNCATE COMMAND

• It is used to delete the records in the table.

• Here the structure is not disturbed.

• The table name, column name and everything will remain the same.

PRACTICAL SESSION FOR ORACLE STATMENTS 

CREATE TABLE reli (id NUMBER);

Table Created.

ALTER TABLE reli ADD name VARCHAR2(20);

Table altered.

ALTER TABLE reli RENAME COLUMN name TO customer_name;

Table altered.

ALTER TABLE reli MODIFY customer_name VARCHAR2(80);

Table altered.

ALTER TABLE reli DROP COLUMN customer_name;

Table altered.

IMPORTANT INTERVIEW QUESTION

1, What is DDL in oracle Statements ?

2, What are the commands in DDL of oracle statements ?

3, What are the command that are used for column level in oracle statements ?

4, Syntax to remove the records from the table ?

5, Syntax to change the data type of the particular columns

6, Syntax to add new columns to the existing table ?

7, Difference between TRUNCATE and DELETE ?    Most Important

NOTE: Must read the points to remember, which is given beginning of the chapter.

CONCLUSION

I hope now you got some clear understanding about the data define languages, so if you found any error or doubts in the above content means kindly feel free to ask us in the contact form or else through official mail.