Oracle Database Interview Question:

How To Create a New Table by Selecting Rows from Another Table?

Submitted by: Administrator

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