Create and Drop Table

For any database, the basic element to be constructed and defined are the tables. In RDBMS the connection among the various tables, using the relations will completely define a database. In this tutorial, we shall look into the creation of the table and deleting the tables that are not required accordingly. Create and drop table

Application to execute the SQL commands

Before moving on to the creation of the tables we shall look on to the application, which is the basic requirement to execute the set of commands which perform the creation, deletion and manipulation on the databases.

You can work on any application as per your requirement, if you are advanced in SQL else it is better to follow this tutorial if you are a beginner.
You can open this link https://livesql.oracle.com/apex/f?p=590:1000:15883965669361:::::

Then you can create your account and start executing the SQL commands in livesql developed by Oracle.

Create Table

To create a new table in the database using the SQL in the database, the CREATE TABLE command is used.

Syntax

CREATE TABLE TNAME
(
		Col 1 datatype,
		Col 2 datatype,
		….
		….
		….
		Col N datatype,
		PRIMARY KEY(can be one or more)
);

The name of the table must be unique and cannot be duplicated and the names or the commands used in the SQL are not case sensitive. 

The above syntax contains the CREATE TABLE which is the keyword to create a table, followed by the name of the table. Inside the table, different columns can be defined accordingly, with different data types, with the primary key which is necessary for RDBMS and the attributes for a primary key can be single or many.

Let us look on to the example of the creation of the table.

CREATE TABLE STUDENTS 
( 
	ID		INT,			 
	NAME	VARCHAR(20),	 
	AGE		INT,			 
	SECTION	VARCHAR(5), 
	ADDRESS	CHAR(25),		 
	PRIMARY KEY(ID) 
)

This, the above table created, can be used to store the different attributes that are required for the student. 

Let us look on to the another example with the default value specifying in the table.

CREATE TABLE ORDERITEMS 
( 
	ID		INT,			 
	NAME	VARCHAR(20),	 
	PROD_NUM		INT,			 
	SECTION	VARCHAR(5), 
	QUANTITY	INT DEFAULT 1,		 
	PRIMARY KEY(ID) 
)

In this table, there are different attributes. Out of the mentioned attributes with different datatype, everything is similar to the above example excepting the default1. The default keyword states that in the column of the quantity of any of the value is not mentioned or missing, it is by default set as the value of 1.

In SQL, using the CREATE and SELECT commands, you can create a new table by considering the few columns in the existing table or can copy the existing table and modify the values accordingly. 

The command SELECT will be discussed briefly in the further upcoming tutorials, till then consider it as known command and move on to the below syntax explanation.

Syntax

CREATE TABLE newtable AS
    SELECT col1, col2,...
    FROM existingtable
    WHERE ....;

You must have the table created previously, to create a new table from the existing table.

Delete Table

Till now, we have seen the creation of the table using the SQL commands, but in the database, it is important to destruct or delete the tables that are not much required or useful, so that space can be saved for the storage of necessary information.

Syntax

DROP TABLE TABLENAME;

Let us look on to the example by deleting the above table which we have created in the database.

DROP TABLE STUDENTS;

The above syntax with example shows that DROP TABLE is the keyword that deletes the table which is mentioned.

This completes the tutorial about the table creation and deletion and these are like pillars which are the base to work further on the databases. So, make sure that you understand this tutorial perfectly and for any sort of doubts or the questions, you can reach out through the comment box and if you like the explanation of this tutorial hit on the like button located on the top left corner at the start of this tutorial. 

Read related topics:

Introduction to SQL