query menambahkan field

http://www.techonthenet.com/sql/tables/alter_table.php

SQL: ALTER TABLE Statement

Learn how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.

Description

The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

To add a column in a table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.
For example:

ALTER TABLE supplier
  ADD supplier_name varchar2(50);

This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.

Add multiple columns in table

Syntax

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);

Example

Let's look at SQL ALTER TABLE example that adds more than one column.
For example:

ALTER TABLE supplier
  ADD (supplier_name varchar2(50),
       city varchar2(45));

This SQL ALTER TABLE example will add two columns, supplier_name as a varchar2(50) field and city as a varchar2(45) field to the supplier table.

Modify column in table

Syntax

To modify a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY column_name column_type;

Example

Let's look at SQL ALTER TABLE example that modifies a column.
For example:

ALTER TABLE supplier
  MODIFY supplier_name varchar2(100) not null;

This SQL ALTER TABLE example will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Modify multiple columns in table

Syntax

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);

Example

Let's look at SQL ALTER TABLE example that modifies more than one column.
For example:

ALTER TABLE supplier
  MODIFY (supplier_name varchar2(100) not null,
          city varchar2(75));

This SQL ALTER TABLE example will modify both the supplier_name and city columns.

Drop column in table

Syntax

To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let's look at SQL ALTER TABLE example that drops (ie: deletes) a column from a table.
For example:

ALTER TABLE supplier
  DROP COLUMN supplier_name;

This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.

Rename column in table

Syntax

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;

Example

Let's look at SQL ALTER TABLE example that renames a column in a table.
For example:

ALTER TABLE supplier
  RENAME COLUMN supplier_name to sname;

This SQL ALTER TABLE example will rename the column called supplier_name to sname.

Rename table

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME TO new_table_name;

Example

Let's look at SQL ALTER TABLE example that renames a table.
For example:

ALTER TABLE suppliers
  RENAME TO vendors;

This SQL ALTER TABLE example will rename the suppliers table to vendors.

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 for Practice Exercise #1:

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 for Practice Exercise #2:

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 for Practice Exercise #3:

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 for Practice Exercise #4:

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 for Practice Exercise #5:

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 for Practice Exercise #6:

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 for Practice Exercise #7:

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;

Share on Facebook

Share on Twitter

Share on Google+

Share on LinkedIn

Subscribe to receive free email updates: