Scribd
Upload
13 views10 pages

SQL DDL: Create, Alter, Drop Tables

Uploaded by

jaggu011
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views10 pages

SQL DDL: Create, Alter, Drop Tables

Uploaded by

jaggu011
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

 Chapter 3: DDL

SQL Tutorial - Create Table


CREATE TABLE command is used to create a new table. ‘CREATE’ is part of Data Definition
Language (DDL).

While creating a table we provide the basic information for each column together with their data
type and sizes.

The Syntax for the CREATE TABLE Statement is:

 table_name - is the name of the table.


 column_name1, column_name2…. – is the name of the columns
 datatype – is the type of data that column holds.

For Example: If we want to create the employee table, the statement would be like

CREATE TABLE emp


( empno NUMBER(5),
ename CHAR(20),
job CHAR(10),
sal NUMBER(20),
comm NUMBER(10),
deptno NUMBER(10)
);
Output:
Once the table is created we will receive a message “Table Created”.
If a table of that name is already available then we will not be allowed to create that table\

Table Naming Conventions:


The name we choose for our table should follow these standard rules:
 The name must begin with a letter A-Z or a-z.
 Can contain numbers and underscores.
 Can be in UPPER or lower case.
 Can be up to 30 characters in length.
 Cannot use the same name of another existing object in our schema.

Examples of Object Names:

Name Valid?

Emp or emp Yes

1EMP or 1emp No – should not begin with a number

Employee_details Yes

Employee details No – should not contain a blank space in between the name

UPDATE No – ‘UPDATE’ is a SQL reserved word

Creating a Table from another Table/View:


Oracle provides another way for creating a table that is ‘copying another table’. This method is
useful when we wanted to select the data out of a table for temporary modifications or if we
wanted to create a table that is similar to the existing table or view using similar data.
The syntax is as follows:
CREATE TABLE na_emp AS
SELECT * FROM emp;
Here, na_emp table is created with the same number of columns and with datatypes as employee
table.

SQL Tutorial - Alter Table


Alter command is part of Data Definition Language (DDL). The SQL ALTER TABLE command is
used to modify the definition (structure) of a table by modifying the definition of its columns. The
ALTER command is used to perform the following functions.

1) Add, drop and modify table columns


2) Add and drop constraints
3) Enable and Disable constraints (will be discussed in constraints lesson)

Adding a column in table


Below is the syntax used to add a column in table.

Example:-
SQL>ALTER TABLE emp ADD na_column DATE;
Once the table is successfully altered then we will receive a message saying “Table altered”
Let’s check the “emp” table structure.
SQL>SELECT * FROM emp;

-- A new column is added in emp table.


Removing a column from table
Below is the syntax used to remove a column from a table

Let’s check the “emp” table structure.


SELECT * FROM emp;
-- ‘na_column’ is removed from emp table.

Renaming a Column in Table


Below is the syntax used to rename a column in table.
Example:-
ALTER TABLE emp RENAME COLUMN sal TO salary;
Let’s check the “emp” table structure.
SELECT * FROM emp;

-- We can observe that “sal” column is renamed as “salary”

Increasing or Decreasing Precision of a column


Below is the syntax used to increasing the precision of a column in table.

– Before we run the alter command lets first check the emp table description
SQL>DESC emp;

– Here ENAME datatype size is 10


Example:- Run alter command to increase the precision.
SQL>ALTER TABLE emp MODIFY ename VARCHAR(25);
Let’s check the “emp” table structure.

We can now observe that ENAME column data size is changed to 25.
Similarly we can decrease the precision of column but to decrease the precision, column should
be empty.

SQL Tutorial - Truncate Table

Truncate command is part of DDL (Data Definition Language).Truncate command is used to


delete the data permanently from the database table.

System does AUTO commit after the deletion and hence the data deleted cannot be rolled back.

Using Truncate we cannot delete partial records from the database. Once we run truncate
command, system will delete all the records permanently from the database.
Example:-

SQL> TRUNCATE TABLE emp;

Once the table is successfully truncated then we will receive a message saying ‘Table Truncated’
– Lets run the emp table after truncate
SELECT * FROM emp;

– We can check the count as well

SELECT COUNT(*) FROM emp;

SQL Tutorial - Drop Table

Drop command is used to remove the object from the database.


The DROP command not only removes the table but also removes table based indexes, privileges
and constraints.

At a later stage even if we wanted we will not be able to get the table or its data, as it is
permanently removed.

Example:-

SQL>DROP TABLE emp;


If table is successfully dropped then we will receive a message saying ‘Table dropped’
Even after dropping the emp table whenever we are try to select the emp table then system
throws an error saying ‘table or view does not exist’.

Error at line 1:
ORA-00942: table or view does not exist

From Oracle 10g version on wards we can get back the table which we dropped using FLASH
BACK , here is the following syntax for that:

Example:-

SQL>FLASH BACK emp TO BEFORE DROP;

Now if we try to use the SELECT command we can see the data from ‘emp’ table.

This process is similar like our recycle bin in our Operating System’s. It means from Oracle 10g
on wards the DROP command will not remove our table permanently it will store in Recycle bin.
So therefore if we want to delete the table permanently here is the following syntax which will
drop the table permanently and which cannot be retrieved back.

Example:-
SQL>DROP TABLE emp PURGE;

Now if we try to select the emp table then system throws an error like :
Error at line 1:
ORA-00942: table or view does not exist

SQL Tutorial - Rename Table


Rename command is used to rename the object in database. Rename command is part of DDL
(Data Definition Language).

Example:-

SQL>RENAME emp TO na_emp;

Once the table is successfully renamed then we will receive a message saying ‘Table renamed’.
– after rename if we try to access ‘emp’ table system will not recognize the table.

SELECT * FROM emp;

Output:-

ORA-00942: TABLE OR VIEW does NOT exit

SQL>SELECT * FROM na_emp;


Output:

Common questions

Powered by AI

The FLASHBACK command enhances database management by providing a safety net allowing the recovery of tables that have been dropped, analogous to a recycle bin in an operating system. This feature reduces downtime and data loss by letting administrators revert unintentional changes, a significant improvement over previous versions where dropped tables were permanently lost. However, this command's usefulness is diminished if the PURGE option is used, which removes the possibility of recovery .

Truncating a table permanently deletes all rows efficiently without affecting table structure, making it suitable for scenarios where table schemas are reused. However, dropping a table with the PURGE option fully removes the table and its schema elements, preventing any possibility of recovery, unlike simple DROP where FLASHBACK might retrieve it. The PURGE option is used when a clean slate is necessary, with no intent to recover structure or data .

The SQL ALTER TABLE command allows adding, dropping, and modifying columns or constraints, thus offering significant flexibility in tailoring table structures to evolving needs. Changing column precision generally requires columns to be empty if precision is decreased, ensuring no data loss. While increasing precision can be done at any time, care must be taken not to inadvertently alter the data type in a way that could truncate or invalidate existing data .

Data definition commands (DDL) such as CREATE, ALTER, and DROP are auto-commit operations that log changes immediately and permanently to the transaction log, often resulting in structural changes that cannot be rolled back. In contrast, data manipulation commands (DML) like INSERT, UPDATE, and DELETE allow for greater transaction control, supporting rollback because they record both the previous and new states of the data for potential undo operations .

The TRUNCATE command deletes all data from a table, performs an auto-commit, and therefore the data cannot be rolled back. It leaves the table structure in place for future data entry. The DROP command, on the other hand, completely removes the table and its associated objects such as indices, constraints, and privileges, making it irreversible in versions before Oracle 10g. Since Oracle 10g, however, DROP moves the table to a recycle bin, allowing recovery with the FLASHBACK command unless it is used with PURGE, which removes it permanently .

Table naming conventions in SQL are crucial for maintaining consistency, readability, and avoiding conflicts with existing database elements. Names must begin with a letter, can include numbers and underscores, and may not exceed 30 characters. They cannot duplicate existing object names or include spaces. Not adhering to these conventions can lead to errors, difficulty in managing database schema, and potential clashes with SQL reserved keywords, which can disrupt database operations .

Using the RENAME TABLE command changes the table name without altering its structure or contents, preserving existing data integrity. However, it can impact object dependencies, such as views, stored procedures, or foreign keys, which may reference the table by its previous name. Therefore, while renaming does not affect the table's internal integrity, external references must be updated to maintain database functionality .

TRUNCATE TABLE is preferred over DELETE when a complete table data wipe is needed because it is faster and uses significantly less transaction log, as it does not log individual row deletions. TRUNCATE also automatically commits the transaction and releases the space for new data more efficiently than DELETE, which is ideal for tables undergoing large volume data refreshes. DELETE is better reserved for partial data removal when roll-back capability is required .

Creating a new table by copying an existing table involves using the command 'CREATE TABLE new_table AS SELECT * FROM existing_table;'. This method is beneficial for temporary data manipulation or when a new table needs a similar structure and data set as an existing table or view. It streamlines the process of table creation by eliminating the need to redefine each column individually .

When using the ALTER TABLE command to modify data types of existing columns, special considerations include ensuring data compatibility to prevent truncation or conversion errors. For example, increasing column length is straightforward, but reducing length might clip existing entries, while changes to numeric types can alter precision and scale, affecting calculations. Data within modified columns must be reviewed to uphold data integrity, and backups are recommended before application .

You might also like