SQL Commands : Data Definition Language Commands

Before describe the SQL Commands, Let us discuss some rules  for the commands.

Rules for SQL Commands

  • SQL statements are not case sensitive. It can be typed  in lower case or upper case or mix of both.
  • The statements can be typed in a single line or multiple lines.
  • A semicolon is placed to terminate th SQL statements
  • The Keywords can’t be distributed across the line but statements may be.
  • A comma(,) is used to separate parameter without a clause.
  • Characters and Date Constants or literals must be enclosed in single quotes(‘A’).
  • A command can be type either full or first 4 characters generally.

SQL Commands : DDL Commands (Data Definition Language Commands)

  1. The CREATE TABLE Command –

    The CREATE TABLE Command is used to create or define the tables in a database.

    Syntax
    CREATE TABLE <table_name>
     (
      <Column1_specification>,
      <Column2 specification>,
      <Column3 specification>,
       ..........
     );

    The Column Specification includes –

    • column name
    • data type
    • [size or length of column]  //[] means optional
    • [constraints]  // [] means optional

    The table_name gives the name of the table you created, Column name gives the name of the column,
    Data Type specifies what type of data the column can hold and the size is optional,
    Constraints is a condition or check applicable that is apply on a field or set of fields.

    Example –
    CREATE TABLE Employee
     (
      Emp_ID INTEGER,
      Emp_First_Name CHARACTER(25),
      Emp_Last_Name CHARACTER(25),
      Address VARCHAR(255),
      Salary double
     );

    This will create Employee table with five columns namely, Emp_ID, Emp_First_Name, Emp_Last_Name, Address, Salary.

  2. The ALTER TABLE Command

    The ALTER Command is used to add, delete or modify columns and constraints in the existing table.

    The ALTER Command performs following functions :

    • Add, Drop(Delete), Modify table Columns
    • Add and Drop Constraints
    • Enable and Disable Constraints
    Syntax :
    • To Add a Column
             ALTER TABLE Table_Name
             ADD <Column_specification>

      For Example,

      ALTER TABLE Employee
      ADD Basic_Pay real;

      The above query will add a column Basic_Pay to the Employee table whose data type is real.

    • Syntax To DROP a Column
      ALTER TABLE table_name
      DROP COLUMN column_name;

      Example :

      ALTER TABLE Employee
      DROP COLUMN Last_Name;
    • Syntax To MODIFY a Column
      ALTER TABLE table_name
      MODIFY column_name data_type;

      Example :

      ALTER TABLE Employee
      MODIFY Salary float;
    • Syntax to delete a Constraint
      ALTER TABLE table_name
      DROP [CONSTRAINT|INDEX] Constraint_Name;
  3. Rename Command

    Rename Command is used to change the name of the table or the database object.

    Syntax :
    RENAME old_table_name
    TO new_table_name;
    Example :
    RENAME Employee TO
    WIPRO_Employees;
  4. The DROP TABLE Command

    The SQL DROP Command is used to remove the table definition and all data, indexes, triggers, constraints, and permission specified for the table.

    Once the DROP Command is executed, the table will be deleted and all the data/information available in the table would be lost forever. And we cannot even rollback after dropping a table.

    Syntax :
    DROP TABLE table_name;
    Example :
    DROP TABLE Employee;

Previous Home Next
Introduction to SQL : Structured Query Language DML Commands : Data Manipulation Language Commands

     

Leave a Reply