SQL Create Table | SQL Alter and Drop Table

FREE Online Courses: Click, Learn, Succeed, Start Now!

1. SQL Create, Alter, and Drop Table

In this SQL tutorial, we are going to learn about the SQL Create, Alter, and Drop Table Statements. Moreover, we will see commands in SQL Create, Alter, and Drop Table. Also, we discuss the SQL Create Table, SQL Drop Table, SQL Alter Table with example and syntax. Along with this, SQL Create, Alter, and Drop Table Tutorial contain add, drop and modify a column in SQL. 

Now, for understanding SQL Create, Alter and Drop Table in detail, let’s start with the SQL Create Table.

SQL Create Table | SQL Alter and Drop Table

SQL Create Table | SQL Alter and Drop Table

2. SQL CREATE TABLE

This table is used to create a table in SQL.
The Syntax of SQL Create Table

CREATE TABLE table_name(
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
  columnN datatype,
  PRIMARY KEY( one or more columns )
);

For this situation, you need to create another table. The one of a kind name or identifier for the table takes after the CREATE TABLE command.
Do you know about SQL RDBMS Concept
A duplicate of a current table can be created utilizing a blend of the CREATE TABLE explanation and the SELECT proclamation. You can check the total points of interest at Create Table using another Table.
a. SQL Create Table Example
The accompanying code square is an illustration, which creates a CUSTOMERS table with an ID as an essential key and NOT NULL are the requirements demonstrating that these fields can’t be NULL while making records in this table −

SQL> CREATE TABLE CUSTOMERS(
  ID  INT            NOT NULL,
  NAME VARCHAR (20)     NOT NULL,
  AGE  INT            NOT NULL,
  ADDRESS  CHAR (25) ,
  SALARY   DECIMAL (18, 2),
  PRIMARY KEY (ID)
);
SQL Create Table

SQL Create Table Statement

3. SQL ALTER Table

An SQL ALTER table statement is used in SQL to delete, add or even modify the columns of an existing table. It is also used to introduce constraints in the table.

SQL Create Table

SQL Alter Table Statement

Have a look at SQL Alter Commands

a. SQL ADD Column

This command is used to add a column in a table.
The syntax for SQL Add Column in SQL ALTER table –

ALTER TABLE table_name
ADD column_name datatype;

b. SQL DROP Column

This command is used to delete a column in a table.
The syntax for SQL Drop Column in SQL ALTER Table –

ALTER TABLE table_name
DROP COLUMN column_name;

c. SQL ALTER/MODIFY Column

We use this command to change the data type of a column in a table.
The syntax of Alter Table Modify Column SQL Server –

  • SQL Server / MS Access
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
  • My SQL / Oracle (prior version 10G)

Have a look at SQL Null Values

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
  • Oracle 10G and later
ALTER TABLE table_name
MODIFY column_name datatype;

d. SQL ALTER TABLE Example

Look at the “Persons” table:

IDLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger

When we want to add an extra column “date of birth”, we use the following command.

ALTER TABLE Persons
ADD DateOfBirth date;

The “Persons” table will now look like this:
Let’s discuss SQL Wildcard characters

IDLastNameFirstNameAddressCityDateOfBirth
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger
  • Now if we want to change the data type of the column named “DateOfBirth” in the “Persons” table.

The Syntax for SQL ALTER Table –

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

DROP COLUMN Example
To delete the column named “DateOfBirth” in the “Persons” table.
The syntax of Drop Column –

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

4. SQL DROP TABLE

The DROP TABLE statement is used to drop an existing table in a database.
The Syntax SQL DROP Table–

DROP TABLE table_name;

You must read SQL Server Transactions
a. SQL DROP Table Example

DROP TABLE CUSTOMERS;

So, this was all in SQL Create, Alter, and Drop table. Hope you like our explanation.

5. Conclusion: SQL Create, Alter, and Drop Table

Hence, in this SQL Create Tutorial, we learned about the Create, drop and alter command in SQL. Also, we discussed the example of SQL Create Table, SQL Alter table, and SQL Drop Table. If you like the article, then let us know through the comment. You can ask your query also.
See also –
SQL Constraint
For reference

Did you know we work 24x7 to provide you best tutorials
Please encourage us - write a review on Google

follow dataflair on YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *