View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES
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 CREATE TABLE command:
Which statement is true regarding the above command?
A.
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the
column definition.
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 column names in the CREATE TABLE
command and the SELECT clause do not match.
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.