Tableau Expert Info: DDL (Data Definition Language)

These statements will work on structure of the tables. Structure of the table means, name of the table, columns and their corresponding data types and constraints on the table etc. These statements will not affect the data present in the tables. Data definition involves, creating the new tables, modifying the existing tables like changing names of tables or columns, adding new columns to the table, removing existing columns from the table, removing tables from the database..... Etc.

DDL statements will carry implicit commit. So, these statements will not require any transaction control. Implicit commit means, whatever DDL statement issued against the database will be automatically committed. There is no rollback for the DDL statements. So, while working with the DDL statements we need to be very careful.

NOTE:

One important point to remember here is, the implicit commit which is carried by DDL statements will be applicable to total transactions happened in that session till that point. It means that if we have any unhandled (using TCL) DML statements issued against the database before issuing the DDL statements, they will also be committed to the database due to the implicit commit carried by DDL. So, we need to take care of this. If any pending DML transactions are there then first we have to handle them using commit or rollback before issuing any DDL operation in that session.

CREATE:
Create will be used to create new tables in the Database. We can also say that Create will be used to create new objects in the database. Objects mean Table, Views, Indexes, and Synonyms..... Etc. We can create any of these objects using Create statement. But now I will discuss only about tables.

Rules to create a Table:

Ø The user should have permission on create table command.

Ø The Table name should begin with a letter and can be 1 – 30 characters long.

Ø The table names can contain combination of A – Z (OR) a – z (OR) 0 – 9 (OR) _, $, #

Ø Names cannot be duplicated for another object name in the same oracle server.

Ø Table names are not case sensitive in oracle.

Ø Two column names in a table should not be same name.

The syntax for creating new table using CREATE statement is,

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
---------------
---------------
columnNdatatype

);

so, using this syntax we can create new tables in the database. We can use any of the data types which we have discussed to create columns of the database depending on type of data we want to store in those columns.

CREATE TABLE Employee(
                empno             number (10),
                ename varchar2 (20),
                hiredate          date,
                sal number (10, 2),
                deptno             number (10)

);
This statement will create Employee table with columns empno (numeric type), ename (character type), hiredate (date type), sal (numeric) and deptno (numeric).

If we want to see the structure of the tables without seeing the data present in the tables, we can use the describe command. Syntax for that is,

DESC table_name;
Ex: DESC Employee;
This statement will show us the structure of Employee table.

ALTER:
ALTER command will be used to modify the structure of the existing tables. Using ALTER we can do following things.

Ø Add new column to the table

Ø Remove existing column from the table

Ø Modify columns data type size of the table

Ø Change the name of a column of the table

The syntax for adding new column to the table is,

ALTER TABLE tab_name ADD col_name datatype;

Ex:

This statement will add new column gender of character type to Employee table.

ALTER TABLE Employee ADD gender CHAR(1);

The syntax for removing existing column from the table is,

ALTER TABLE tab_name DROP COLUMN col_name;

Ex:

This statement will remove gender column from the Employee table.

ALTER TABLE Employee DROP COLUMN gender;

The syntax for modifying size of character column from the table is,

ALTER TABLE tab_name MODIFY col_name datatype with new size;

Ex:

This statement will be used to change the size of ename column to 30.

ALTER TABLE Employee MODIFY ename VARCHAR2(30);

This statement can be used to change the size of any column to >= max(size) of the data already present in that column. For example if the max(size) of data already present in the column is 6 , then we can modify its size to >=6.

The syntax for changing the name of the column is,

ALTER TABLE tab_name RENAME COLUMN old_name TO new_name;

Ex:

This will change the name of sal column of Employee to salary.

ALTER TABLE Employee RENAME COLUMN sal TO salary;

So, these are the some basic things which we can do with ALTER statement.

TRUNCATE:
TRUNCATE will be used to remove the data from the tables. Truncate will be used to remove all the data present in the tables. We cannot remove specific rows of a table using TRUNCATE command. It means that we cannot write WHERE clause with this statement.

So, using truncate we can remove entire rows from the table only. The syntax for writing TRUNCATE is,

TRUNCATE TABLE tab_name;

Ex:

This will remove all the rows of Employee table.

TRUNCATE TABLE Employee;

DROP:

DROP will be used to remove tables from the database. This will remove entire table from the database along with data present in it. Syntax for DROP statement is,

DROP TABLE tab_name;

Ex:

This will remove Employee table from the database.

DROP TABLE Employee;

RENAME:

RENAME will be used to change the names of database objects like tables, views etc. Using this command we can change names of database objects only. We cannot change names of columns of the table.

Syntax for this statement is,

RENAME old_name TO new_name;

Ex:

RENAME Employee TO Employeedata;

This statement will change the name of Employee table to Employeedata.

Pages

ad

DDL (Data Definition Language)