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)
The CREATE TABLE Command –
The CREATE TABLE Command is used to create or define the tables in a database.
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.
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.
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
To Add a Column
ALTER TABLE Table_Name ADD <Column_specification>
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;
ALTER TABLE Employee DROP COLUMN Last_Name;
Syntax To MODIFY a Column
ALTER TABLE table_name MODIFY column_name data_type;
ALTER TABLE Employee MODIFY Salary float;
Syntax to delete a Constraint
ALTER TABLE table_name DROP [CONSTRAINT|INDEX] Constraint_Name;
Rename Command is used to change the name of the table or the database object.
RENAME old_table_name TO new_table_name;
RENAME Employee TO WIPRO_Employees;
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.
DROP TABLE table_name;
DROP TABLE Employee;
|Introduction to SQL : Structured Query Language||DML Commands : Data Manipulation Language Commands|
Incoming search terms:
- data definition language commands
- explain DDL command in detail ? create table employee display the name of emp from employee table whose salary is between 500to1000