DbSchema Tutorial | SQL Tables
Table of Contents:
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:
_Student_Name_ | AGE | _Gender_ |
---|---|---|
Sam |
32 | Male |
Bob |
45 | Male |
Anne |
23 | Female |
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.
- Creating a table which involves name of the
table. - Defining its columns which are part of the table
- 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 valueVarchar
stands for variable length character,
and the value passed is the maximum length of the characterPrimary 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 theMODIFY
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 columnNAME
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.