The SQL ALTER TABLE statement allows you to rename an existing table. It
can also be used to add, modify, or drop a column from an existing
table.
To rename a table, the SQL ALTER TABLE syntax is:
To add a column to an existing table, the SQL ALTER TABLE syntax is:
Syntax #2
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:
To modify a column in an existing table, the SQL ALTER TABLE syntax is:
Syntax #2
To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:
To drop a column in an existing table, the SQL ALTER TABLE syntax is:
SQL ALTER TABLE - Rename column(s) in a table
Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the SQL ALTER TABLE syntax is:
----------------------------------------------------------------------------------------------------\
Example For Alter Multiple Coloumn in sql server
dfdsfd
To rename a table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name RENAME TO new_table_name;For example:
ALTER TABLE suppliers RENAME TO vendors;This will rename the suppliers table to vendors.
SQL ALTER TABLE - Adding column(s) to a table
Syntax #1To add a column to an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name ADD column_name column-definition;For example:
ALTER TABLE supplier ADD supplier_name varchar2(50);This will add a column called supplier_name to the supplier table.
Syntax #2
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name ADD (column_1 column-definition, column_2 column-definition, ... column_n column_definition);For example:
ALTER TABLE supplier ADD (supplier_name varchar2(50), city varchar2(45));This will add two columns (supplier_name and city) to the supplier table.
SQL ALTER TABLE - Modifying column(s) in a table
Syntax #1To modify a column in an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name MODIFY column_name column_type;For example:
ALTER TABLE supplier MODIFY supplier_name varchar2(100) not null;This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
Syntax #2
To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type, ... column_n column_type);For example:
ALTER TABLE supplier MODIFY (supplier_name varchar2(100) not null, city varchar2(75));This will modify both the supplier_name and city columns.
SQL ALTER TABLE - Drop column(s) in a table
Syntax #1To drop a column in an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name DROP COLUMN column_name;For example:
ALTER TABLE supplier DROP COLUMN supplier_name;This will drop the column called supplier_name from the table called supplier.
SQL ALTER TABLE - Rename column(s) in a table
(NEW in Oracle 9i Release 2)
Syntax #1Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;For example:
ALTER TABLE supplier RENAME COLUMN supplier_name to sname;This will rename the column called supplier_name to sname.
Practice Exercise #1:
Based on the departments table below, rename the departments table to depts.CREATE TABLE departments ( department_id number(10) not null, department_name varchar2(50) not null, CONSTRAINT departments_pk PRIMARY KEY (department_id) );
Solution:
The following SQL ALTER TABLE statement would rename the departments table to depts:ALTER TABLE departments RENAME TO depts;
Practice Exercise #2:
Based on the employees table below, add a column called salary that is a number(6) datatype.CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
Solution:
The following SQL ALTER TABLE statement would add a salary column to the employees table:ALTER TABLE employees ADD salary number(6);
Practice Exercise #3:
Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
Solution:
The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:ALTER TABLE customers ADD (contact_name varchar2(50), last_contacted date);
Practice Exercise #4:
Based on the employees table below, change the employee_name column to a varchar2(75) datatype.CREATE TABLE employees ( employee_number number(10) not null, employee_name >varchar2(50) not null, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
Solution:
The following SQL ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):ALTER TABLE employees MODIFY employee_name varchar2(75);
Practice Exercise #5:
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50), address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
Solution:
The following SQL ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:ALTER TABLE customers MODIFY (customer_name varchar2(50) not null, state varchar2(2));
Practice Exercise #6:
Based on the employees table below, drop the salary column.CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), salary number(6), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
Solution:
The following SQL ALTER TABLE statement would drop the salary column from the employees table:ALTER TABLE employees DROP COLUMN salary;
Practice Exercise #7:
Based on the departments table below, rename the department_name column to dept_name.CREATE TABLE departments ( department_id number(10) not null, department_name varchar2(50) not null, CONSTRAINT departments_pk PRIMARY KEY (department_id) );
Solution:
The following SQL ALTER TABLE statement would rename the department_name column to dept_name in the departments table:ALTER TABLE departments RENAME COLUMN department_name to dept_name;
----------------------------------------------------------------------------------------------------\
Example For Alter Multiple Coloumn in sql server
dfdsfd
I think SQL has the best tools and solutions for the most complex database operations.Apart from this there is a need to focus more on some database operations.
ReplyDeleteSQL Server Load Rest Api