As discussed in previous articles, we are aware that the changes to a table or column can be done using ALTER TABLE command. The ALTER TABLE can be even used to add or drop the table or the columns or even it can be used to modify the constraints of the existing table or columns.
ALTER TABLE (ADD) –
The ADD command is used to add columns to the existing table. There is no need to build the entire table again if we can easily use the command to add columns to the existing one.
- Syntax of ALTER TABLE (ADD) –
ALTER TABLE table_name ADD
(Column1 datatype , column2 datatype, column3 datatype,..., columnN datatype);
Demo table –
Manager’s Table
| Region | Regional Manager |
| West | Sadie Pawthorne |
| East | Chuck Magee |
| Central | Roxanne Rodriguez |
| South | Fred Suzuki |
Example – Consider the Manager’s table of the Customer database, add a column as “Category ID” using ALTER TABLE (ADD).
ALTER TABLE [dbo].[Manager] ADD Manager_id integer ;
The output after implementing the above ALTER TABLE (ADD) operator –
| Region | Regional Manager | Manager_id |
| West | Sadie Pawthorne | NULL |
| East | Chuck Magee | NULL |
| Central | Roxanne Rodriguez | NULL |
| South | Fred Suzuki | NULL |
“Manager_id” is added to the manager table using ALTER ADD Command. But currently, the values are NULL in the new column. That is because we haven’t inserted any value into the column.
ALTER TABLE (DROP) –
The DROP command is used to drop any column in the table. It is used to delete the not needed columns.
- Syntax of ALTER TABLE (DROP) –
ALTER TABLE table_name DROP COLUMN column_name ;
Demo table –
| Region | Regional Manager | Manager_id |
| West | Sadie Pawthorne | NULL |
| East | Chuck Magee | NULL |
| Central | Roxanne Rodriguez | NULL |
| South | Fred Suzuki | NULL |
Example – Consider the Manager’s table of the Customer database, drop the created column “Manager_id ” using ALTER TABLE (DROP).
ALTER TABLE [dbo].[Manager] DROP COLUMN Manager_id;
The output after implementing the above ALTER TABLE (DROP) operator –
| Region | Regional Manager |
| West | Sadie Pawthorne |
| East | Chuck Magee |
| Central | Roxanne Rodriguez |
| South | Fred Suzuki |
“Manager_id” is dropped from the manager table using ALTER DROP Command.
ALTER TABLE (MODIFY) –
The MODIFY command is used to MODIFY any column or its constraint in the table. MODIFICATION can be done on multiple columns at once.
Syntax of ALTER TABLE (MODIFY) –
ALTER TABLE table_name
ALTER COLUMN column_name column_type ;
Demo table –
| Region | Regional Manager |
| West | Sadie Pawthorne |
| East | Chuck Magee |
| Central | Roxanne Rodriguez |
| South | Fred Suzuki |
Example – Consider the Manager’s table of the Customer database, and modify the datatype of the Regional manager.
ALTER TABLE [dbo].[Manager]
ALTER COLUMN [Regional Manager] varchar (20);
After executing the above query maximum size of the Regional manager Column is reduced to 20 from 30.
Other SQL topics to check out:
- How to write SQL Select Statements
- Introduction to SQL for Data Scientists
- What is RDBMS (Relational Database Management System)?
- SQL WHERE Clause
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #tableau #SQL #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz
