See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly,
the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS
and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Which statement is true regarding the above command?
A.
The NEW_SALES table would not get created because the column names in the CREATE TABLE
command and the SELECT clause do not match
B.
The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified
columns would be passed to the new table
C.
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the
column definition
D.
The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified
columns would be passed to the new table
Explanation:
Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE
TABLE statement and the AS subquery option.
CREATE TABLE table
[(column, column…)]
AS subquery;
Match the number of specified columns to the number of subquery columns.
Define columns with column names and default values.
Guidelines
The table is created with the specified column names, and the rows retrieved by the SELECT statement are
inserted into the table.
The column definition can contain only the column name and default value. If column specifications are given,
the number of columns must equal the number of columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the column names in the
subquery.
The column data type definitions and the NOT NULL constraint are passed to the new table. Note that only the
explicit NOT NULL constraint will be inherited. The PRIMARY KEY column will not pass the NOT NULL feature
to the new column. Any other constraint rules are not passed to the new table. However, you can add
constraints in the column definition.