SQL Create Table | SQL Alter and Drop Table
Keeping you updated with latest technology trends, Join DataFlair on Telegram
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.
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) );
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.
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)
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:
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
- 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 –