How To Create a New Table by Selecting Rows from Another Table?
Submitted by: AdministratorLet'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 to do this. Here is an example script:
>.insqlplus /nolog
SQL> connect HR/globalguideline
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary
2 FROM emp_dept_10;
<pre>FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400</pre>
As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.
This is really a quick and easy way to create a table.
Submitted by: Administrator
>.insqlplus /nolog
SQL> connect HR/globalguideline
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary
2 FROM emp_dept_10;
<pre>FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400</pre>
As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.
This is really a quick and easy way to create a table.
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How To Use Values from Other Tables in UPDATE Statements using Oracle? |
☺ | What Happens to Indexes If You Drop a Table? |
☺ | How To Add a New Column to an Existing Table in Oracle? |
☺ | How To Invoke the Data Pump Import Utility? |
☺ | How To Recover a Dropped Table in Oracle? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |