SQL Tables Explained with Examples

Table of Contents:

1. What is table?
2. Create Table
3. Drop Table
4. Alter Table
5. Conclusion

What is table?

Tables are the fundamental way to store data in a relational database management system. A table is a database object which is composed of rows and columns in SQL. In simple terms, it’s a collection of related data held in a table format.

Let's see an example of a Student table:

In the above table, Student is the table name,Student_Name, Age and Gender are the column names. The combination of data of multiple columns forms a row e.g. Sam, 32 and Male are the data of one row.

Create Table

SQL CREATE TABLE statement is used to create table in a database.

1. Creating a table which involves name of the table.
2. Defining its columns which are part of the table
3. Assigning a data type to each column

Let's see the simple syntax to create the table.

    CREATE TABLE table_name (   column1 datatype,
                                column2 datatype,
                                ...
                                columnN datatype,
                                PRIMARY KEY
                                (one or more columns) );

where CREATE TABLE is the keyword, tablename is the name of the table name, columns1 to columnN are the set of columns, and PRIMARY KEY is a constraint followed by a semicolon Let’s create an employee table. Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.

SQL> CREATE TABLE STUDENTS (  
         ID INT NOT NULL,  
         NAME VARCHAR (20) NOT NULL,  
         AGE INT NOT NULL,           
         PRIMARY KEY (ID)  
);  

• Not null is the constraint used to signify that all cells of this column must have a value
• Varchar stands for variable length character, and the value passed is the maximum length of the character
• Primary key constraints help uniquely identify all records from the table, and a table cannot have more than one primary key

Now you have the STUDENTS table available in your database and you can use to store required information related to students.

Drop Table

A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

This is very important to know that once a table is deleted all the information available in the table is lost forever, so we have to be very careful when using this command.

Let's see the syntax to drop the table from the database.

DROP TABLE "table_name";

This shows that STUDENTS table is available in the database, so we can drop it as follows:

SQL>DROP TABLE STUDENTS;

Alter Table

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. This statement also allows database users to add and remove various SQL constraints on the existing tables.

Any user can also change the name of the table using this statement.

• Add a new column using the ADD clause.
• Modify attribute of a column such as constraint, default value, etc. using the MODIFY clause.
• Remove columns using the DROP clause.

ALTER TABLE ADD Column statement in SQL

To add the columns in the existing table. Instead of creating a whole table or database again you can easily add single and multiple columns using the ADD keyword. Let's see the syntax to add column to the table.

ALTER TABLE table_name ADD column_name column-definition;

Let’s look at example of adding new columns to the STUNDETS table that we created in the create table.

SQL>ALTER TABLE STUDENTS ADD Address Varchar
(100);  

ALTER TABLE MODIFY Column statement in SQL

The MODIFY clause allows you to change some attributes of the existing column e.g., NOT NULL ,UNIQUE, and data type. Let's see the syntax to modify column from the table.

ALTER TABLE table_name MODIFY column_definition ;

Let’s look at example of modifying column NAME to the STUNDETS table that we created in the create table.

SQL>ALTER TABLE STUDENTS MODIFY NAME VARCHAR 
(15) NULL ;

ALTER TABLE DROP Column statement in SQL

To delete the columns from the existing table. Instead of deleting the whole table or database you can use DROP keyword for deleting the columns. Let's see the syntax to delete column from the table.

ALTER TABLE table_name DROP column_name ;

Let’s look at example of deleting column to the STUNDETS table that we created in the create table.

SQL>ALTER TABLE STUDENTS DROP Address ;

Conclusion

In this tutorial, you have learned step by step how to CREATE, ALTER AND DROP table.