How To Add a New Column to an Existing Table

Q

How To Add a New Column to an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements

✍: FYIcenter.com

A

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_110 
  2  AS SELECT * FROM employees WHERE department_id=110;
Table created.

SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.

SQL> SELECT first_name, last_name, vacation 
  2  FROM emp_dept_110;
FIRST_NAME           LAST_NAME                   VACATION
-------------------- ------------------------- ----------
Shelley              Higgins
William              Gietz

This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.

2007-04-22, 5195👍, 0💬

How To Create a New Table by Selecting Rows from Another Table
How To Create a New Table by Selecting Rows from Another Table? - Oracle DBA FAQ - Understanding SQL DDL Statements Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement t... 2007-04-22, 5336👍, 0💬

How To Use Regular Expression in Pattern Match Conditions
How To Use Regular Expression in Pattern Match Conditions? - Oracle DBA FAQ - Understanding SQL Basics If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE(). The following script provides you some good examples: SELECT ... 2007-04-22, 5272👍, 0💬

How To Add a New Column to an Existing Table
How To Add a New Column to an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script: SQL> connect HR/fy... 2007-04-22, 5196👍, 0💬

How To Delete a Column in an Existing Table
How To Delete a Column in an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script: SQL> CREATE TABLE emp_dept_90 2 A... 2007-04-22, 5086👍, 0💬

How To Drop an Index
How To Drop an Index? - Oracle DBA FAQ - Understanding SQL DDL Statements If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script: CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHA... 2007-04-22, 4996👍, 0💬

How To Create a New Table
How To Create a New Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table: >.\bin\sqlplus /nolog SQL> con... 2007-04-22, 4980👍, 0💬

How To Create a Table Index
How To Create a Table Index? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use... 2007-04-22, 4934👍, 0💬

How To Use LIKE Conditions
How To Use LIKE Conditions? - Oracle DBA FAQ - Understanding SQL Basics LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition: '_' is used in the pattern to match any one character. '%' is used in the pattern to match any zero or more characters. ESCAPE clause is us... 2007-04-23, 4869👍, 0💬

How To Drop an Existing Table
How To Drop an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script: SQL> connect HR/fyicenter Connected. SQL> CREATE TABLE emp_dept_10 2 AS SELECT * FROM employees ... 2007-04-22, 4860👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.