Table DDL commands
A table is basic entity in relational databases where actual data is stored. Table is also called as Entity. Table consists of rows and columns. Each row represents the instance of entity that table represents.
- CREATE TABLE
The CREATE TABLE command is used to create a table in the database. The syntax to create table is:
CREATE TABLE table_name
Here column1, column2, column3… are column names. Each column in the table should have a datatype. Data type specifies the types of column like salary can be decimal or float, roll no can be integer etc .
Let’s create Student table in our School database.
CREATE TABLE Student
Here Id, Name, MotherName, FatherName, Address are columns of the table Student. INT and VARCHAR is data type in SQL.
Note that CREATE TABLE only creates the structure of the table. The table is empty.
- ALTER TABLE
To modify the structure of table i.e. to add, remove or delete a column from a table; ALTER TABLE command is used. You can do following operations on table while altering it:
- Add Column
- Modify Column
- Rename Column
- Drop Column
- Add Index
- Drop Index
- Add Constraint
- Drop Constraint
We will not discuss all these, we will only see the add example. Suppose we want to add another column in Student Table namely Gender:
ALTER TABLE Student ADD Gender VARCHAR(6);
This statement will add column named Gender in Student table which of data type string having length as 6 characters.
- DROP TABLE
Sometime you want to delete a table from database. To delete the table, following command is used:
DROP TABLE table_name;
Let’s delete table School from database.
DROP TABLE School;
The above statement will completely remove School table from database.