Databases Reference
In-Depth Information
schema is used to manage employee information. Therefore, Jan-
ice, the DBA and senior developer, must re-create the table to match the
datatypes and name of the
Now, the
HR
EMPLOYEES
table in the
HR
schema, as follows:
create table employees_hourly (
employee_id number(6) not null,
first_name varchar2(20),
last_name varchar2(25) not null,
email varchar2(25) not null,
phone_number varchar2(20),
job_id varchar2(10) not null,
manager_id number(6),
hire_date date not null,
hourly_rate number(5,2) default 6.50 not null,
department_id number(4),
ssn varchar2(11),
constraint pk_employees_hourly
primary key( employee_id ) ) ;
Because of the
PRIMARY
KEY
constraint on the
EMPLOYEE_ID
column, the val-
ues in the
EMPLOYEE_ID
column must be unique within the table.
Create Table As Select (CTAS)
If you want to base the contents of a new table on the results of a query of one or
more other tables, you can use the statement
CTAS
Also known as Create Table As Select, a
method for creating a table in the data-
base by using the results from a subquery
to both populate the data and specify the
datatypes of the columns in the new table.
CREATE TABLE ... AS SELECT
,
otherwise known as
CTAS
. It's shorthand for two or more individual statements: the
traditional
statements. Using
CTAS, you can create a table and populate it in one easy step.
The syntax for CTAS varies from the basic syntax of a
CREATE
TABLE
statement and one or more
INSERT
CREATE
TABLE
state-
ment as follows:
CREATE TABLE [
schema.
]
tablename
AS SELECT <
select_clauses
>;
Notice that with CTAS you cannot specify the datatypes of the new columns; the
column datatypes of the original columns, along with any
NOT
NULL
constraints, are
query. Any other constraints or indexes may
be added to the table later. Column aliases in the
derived from the columns in the
SELECT
SELECT
query are used as the col-
umn names in the new table.
At Scott's widget company, the Order Entry department frequently sends
out mailings to non-administrative staff, but the mailing list is becoming outdated.
The manager in the Order Entry department asks Janice to grant the developers
in the group the rights to access the
EMPLOYEES
table. However, the
EMPLOYEES
