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
    (
        column1 datatype,
        column2 datatype,
       column3 datatype,
       …..
    )
    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
    (
      Id INT,
      Name VARCHAR(50),
      MotherName VARCHAR(50),
      FatherName VARCHAR(50),
      Address VARCHAR(50),
    )
    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.

Download as PDF

Read next:  SQL – Data Manipulation Language ››

« Back to Course page