AIT 524 - Week 8 (Table Creation and Management)
If a(n) ____________________ constraint is being dropped, then only the type of constraint needs to be identified in the ALTER TABLE because there can only be one such constraint for each table.
PRIMARY KEY
If the Oracle 12c server assigns a name to a constraint, it will use the format ____________________ for the constraint name.
SYS_Cn
What is the syntax for the default constraint name provided by the Oracle 12c server? a. PK_Cn b. SYS_Cn c. ORA_Cn d. DEF_Cn
SYS_Cn
When creating a table, which of the following statements is correct? a. Default values cannot be assigned to all columns in a table. b. A user can have two tables with the same name, as long as the column names are different. c. The column list must be enclosed in parentheses ( ). d. The schema of the table must be explicitly stated.
The column list must be enclosed in parentheses ( ).
A primary key is usually given the abbreviation _pk in the constraint name if the name is assigned by the user. True False
True
A(n) FOREIGN KEY constraint can only reference a column in the parent table that has been designated as the primary key for that table. _________________________ True False
True
A(n) FOREIGN KEY constraint is used to ensure that referential integrity exists between tables. _________________________ True False
True
A(n) UNIQUE constraint will allow NULL values to be stored in the designated column. _________________________ True False
True
Constraints are rules used to enforce business rules, practices, and policies. True False
True
DDL commands are used to create or modify database objects. _________________________ True False
True
If a data value violates a(n) constraint, the entire row is prevented from being added to the table. _________________________ True False
True
Once a column as been set as UNUSED, it cannot be recovered. _________________________ True False
True
SELECT9 is a valid table name. _________________________ True False
True
Seven bytes are allocated in a DATE column to store the century, year, month, day, hour, minute, and second of a date. _________________________ True False
True
The ALTER TABLE command can be used to disable a constraint. True False
True
The syntax of the CREATE TABLE command requires that the column list be enclosed in parentheses. _________________________ True False
True
When dropping a(n) PRIMARY KEY constraint, the name of the column does not need to be included in the ALTER TABLE command. _________________________ True False
True
A(n) ____________________ constraint requires that, if an entry is made into the column, the data value must be unique.
UNIQUE
Unless a PRIMARY KEY or a(n) ____________________ constraint is being dropped, the actual name assigned to the constraint must be included in the ALTER TABLE command.
UNIQUE
Once a table is ____________________, any associated indexes will be removed from the database.
deleted
Which of the following commands will change the name of the LAST NAME column to LAST_NAME in the CUSTOMERS table? a. ALTER TABLE customers MODIFY lastname to last_name; b. ALTER TABLE customers CHANGE lastname to last_name; c. ALTER TABLE customers MODIFY (lastname, last_name); d. none of the above
none of the above
A database ____________________ is a defined, self-contained structure in Oracle 12c.
object
The ____________________ of a NUMBER column indicates the total number of digits that can be stored in the column.
precision
If a constraint applies to more than one column, the constraint must be created at the ____________________ level.
table
Which of the following symbols can be used in a column name?
#
Which of the following SQL statements was most likely used to create the PROMOTION table? a. CREATE TABLE promotion AS (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2)); b. CREATE TABLE promotion ADD (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2)); c. CREATE TABLE promotion (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2)); d. CREATE TABLE promotion (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2);
(gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2));
A maximum of ____________________ PRIMARY KEY constraints can exist for each database table.
1
The column definition of NUMBER(7, 4) indicates a precision of ____. a. 4 b. 11 c. 7 d. 3
7
The DROP UNUSED COLUMNS clause can be used with the ____________________ command to delete any column previously set as unused.
ALTER TABLE
The ____________________ command can be used to enable or disable a constraint.
ALTER TABLE
Which of the following commands can be used to make structural changes to an existing table? a. FIX TABLE b. MODIFY TABLE c. CHANGE TABLE d. ALTER TABLE
ALTER TABLE
If a PRIMARY KEY constraint, named ORDERITEMS_PK, exists for the ORDER# and ITEM# columns of the ORDERITEMS table, which of the following commands will disable the constraint? a. ALTER TABLE orderitems REMOVE PRIMARY KEY CONSTRAINT; b. ALTER TABLE orderitems MODIFY PRIMARY KEY CONSTRAINT DISABLE; c. ALTER TABLE orderitems DISABLE CONSTRAINT orderitems_pk; d. ALTER TABLE orderitems DISABLE PRIMARY KEY CONSTRAINT;
ALTER TABLE orderitems DISABLE CONSTRAINT orderitems_pk;
Which of the following commands will add a UNIQUE constraint to the MINRETAIL column of the PROMOTION table? a. ALTER TABLE promotion ADD CONSTRAINT orderitems_minretail_uk UNIQUE (minretail); b. ALTER TABLE promotion MODIFY UNIQUE (minretail); c. ALTER TABLE promotion MODIFY minretail; d. ALTER TABLE promotion ADD UNIQUE minretail;
ALTER TABLE promotion ADD CONSTRAINT orderitems_minretail_uk UNIQUE (minretail);
Which of the following commands will drop any columns marked as unused from the TABLEA table? a. DROP UNUSED COLUMNS: b. ALTER TABLE tablea DROP UNUSED COLUMNS; c. DROP COLUMN FROM tablea WHERE column_status = UNUSED; d. ALTER TABLE tablea DROP (unused);
ALTER TABLE tablea DROP UNUSED COLUMNS;
Which of the following keywords cannot be used to modify an existing table? a. ALTER TABLE...ADD b. ALTER TABLE...MODIFY c. ALTER TABLE...AS d. ALTER TABLE...DROP COLUMN
ALTER TABLE...AS
Which of the following keywords is used to mark a column for deletion at a later time?
ALTER TABLE...SET UNUSED
If a user is going to assign a name to a constraint, the ____________________ keyword must be included in the command.
CONSTRAINT
Which command instructs Oracle 12c to create a new table? a. CREATE TABLE...FROM b. ALTER TABLE c. CREATE NEW TABLE d. CREATE TABLE
CREATE TABLE
Which of the following is a valid SQL statement? a. CREATE TABLE newname (colA DATE, colB VARCHAR2(3); b. CREATE TABLE newname as SELECT * FROM books; c. CREATE TABLE newname (colA NUMBER, colB DATE); d. CREATE TABLE newname (colA, colB);
CREATE TABLE newname (colA NUMBER, colB DATE);
Which command instructs Oracle 12c to create a new table from existing data? a. CREATE TABLE b. CREATE TABLE...AS c. CREATE TABLE...FROM d. CREATE NEW TABLE
CREATE TABLE...AS
In which format does Oracle 12c display a date value? a. DD-MM-YY b. DD-MON-YY c. DAY-MM-YY d. DD-MON-YEAR
DD-MON-YY
The default format for a DATE column is ____________________.
DD-MON-YY
_________________________ commands are used to create database objects.
DDL
A quick way to determine whether a column can contain a NULL value is to issue the ____________________ command, followed by the table name.
DESCRIBE
A FOREIGN KEY constraint will not allow a row containing a NULL value in the foreign key column to be added to the table. True False
False
A column name can consist of up to 225 characters. True False
False
A constraint name can consist of up to 25 characters and numbers. _________________________ True False
False
A database must be shut down before the ALTER TABLE command can be executed. True False
False
A foreign key constraint can only be created at the column level.
False
DML commands are used to create or modify database tables. True False
False
Each column can only be included in one constraint. True False
False
If a(n) FOREIGN KEY constraint has been created for a table, it means the data values in that column must be unique and cannot contain NULL values. _________________________ True False
False
In the USER_CONSTRAINTS view, the constraint type for a NOT NULL constraint will be listed as N. _________________________ True False
False
The ALPHANUMERIC datatype can be used to store characters and numbers to a maximum width of 2000. True False
False
The TRUNCATE TABLE command can be used to delete a table from a database. _________________________ True False
False
The default size of the CHAR column is 2. _________________________ True False
False
The table level approach can be used to create any constraint, except a CHECK constraint.
False
Which of the following keywords must have been included during the creation of a FOREIGN KEY constraint to allow a row from the parent table to be deleted, even if it is referenced by a row in the child table? a. CASCADE b. AUTO REMOVE c. ON DELETE CASCADE d. none of the above
ON DELETE CASCADE
A NOT NULL constraint can be added to an existing table using the ALTER TABLE command with the ____________________ clause.
MODIFY
Which of the following keywords can be used to change the size, datatype, and/or default value of an existing column? a. MODIFY b. RESET c. ADD d. CHANGE
MODIFY
Which of the following is a valid statement? a. Constraints are rules used to enforce business rules, practices, and policies. b. Constraints prevent errors by not allowing data to be added to tables if the data violates specific rules. c. Constraints ensure the accuracy and integrity of data. d. all of the above
all of the above
Each ____________________ name within a table must be unique.
column
