SQL Create Table, DROP Table Syntax and SQL Default Command

    7 Votes

In this article, we will have a look at the commonly used SQL scripts to create, drop and alter a database table. 

SQL Create Table Command

To create a table in database, “Create Table” SQL command is used. To create a table, following information need to be specified :
  • Command should start with "Create Table"
  • After CREATE TABLE SQL command, table name should be specified
  • Specify column name with data type and size
  • Additional info like table location can also be specified

 

To create employee table using SQL, create table syntax is as shown below. 
create table EMPLOYEE
(
 EMPLOYEE_ID int not NULL ,
 EMPLOYEE_NAME VARCHAR2(40) not NULL ,
 AGE int
)

By specifying not NULL for EMPLOYEE_NAME and EMPLOYEE_ID column, we are specifying that there should be value for these two columns when a row is inserted. Since AGE column does not have NULL restriction, data can be inserted in table with no value for age field.

Read DBMS Interview Questions for frequently asked questions on SQL.

SQL Default Keyword Syntax

Default keyword is used to insert a value into a column when no value is supplied to that column. Let us modify employee table with SQL DEFAULT keyword for AGE field.

create table EMPLOYEE
(
 EMPLOYEE_ID int not NULL ,
 EMPLOYEE_NAME VARCHAR2(40) not NULL ,
 AGE int default 25
)

When a row is inserted into employee table, if age is null 25 will be inserted. In case value is supplied for that field, the value provided by the user will get stored in the age column.

SQL ALTER Table Command

To update a table, “SQL ALTER Table ” statement is used. To alter a table, following information need to be specified :

  • After ALTER TABLE keywords, table name should be specified
  • To add a column, specify "ADD" followed by column name data type and size after "Alter table" table name
  • To Drop a column, specify "DROP" followed by column name after "Alter table" table name

SQL Command for Adding / deleting column example is given below

ALTER Table employee add second_name varchar(50);
ALTER Table employee drop column second_name varchar(50);

SQL DROP Table Command

To delete a table, use “DROP Table” statement followed by table name. SQL syntax for drop table is given below

DROP Table employee;

Popular Videos

communication

How to improve your Interview, Salary Negotiation, Communication & Presentation Skills.

Got a tip or Question?
Let us know

Related Articles

RDBMS and DBMS Introduction, Concepts and Basics
Primary Key, Foreign Key and Unique Key
SQL Select with Order By, Insert, Update and Delete Command
SQL Operators - Where, IN, AND, OR and Between
SQL Like and Distinct Command
SQL Join Command - Inner, Outer and Left Join
SQL Mathematical Functions, Concat and Trim Commands
SQL Group By and Having Command
Difference between having and where clause
Difference between Inner join and Left join
Clustered Index vs Non Clustered Index