ALTER command can be used to add/modify definition of various aspects of database and its objects.
SAMPLE SYNTAX FOR VARIOUS ALTER STATEMENTS ARE AS FOLLOWS:
Dropping, Adding or Repositioning a Column:
--drop existing column
alter table test_tbl drop column_name;
--add the same column at the end of the table
alter table test_tbl add column_name datatype;
--drop existing column
alter table test_tbl drop column_name;
--add the same column at top of the able
alter table test_tbl add column_name datatype first;
--drop existing column
alter table test_tbl drop column_name;
--add same column at after column c
alter table test_tbl add i datatype after c;
Changing a Column Definition or a Name:
--change datatype
alter table test_tbl modify col1 char(10);
--change column name
alter table test_tbl change col1 col1new bigint;
--change datatype
alter table test_tbl change col1new col1new int(1);
--setting the default value
alter table test_tbl modify i bigint not null default 100;
Changing a Column's Default Value:
--setting a default value of a column
alter table test_tbl column_name set default 1000;
--remove the default value of a column
alter table test_tbl alter column_name drop default;
Changing a Table Type:
--change the db engine type for a given table
alter table test_tbl type = myisam;
Renaming a Table:
--rename a table
alter table test_tbl rename to alter_tbl;
Let us add pincode column (which should have 6 digits) at the first of the table.
%%sql
alter table deptt_<<your lab username>> add pincode int(6) first ;
select * from deptt_<<your lab username>>;
Note - Having trouble with the assessment engine? Follow the steps listed here
Loading comments...