SQL Test Questions

Mod #2: Which of the following commands will drop any columns marked as unused from the TABLEA table?

ALTER TABLE tablea DROP UNUSED COLUMNS;

Mod #2: Which of the following keywords is used to mark a column for deletion at a later time?​

ALTER TABLE...SET UNUSED

Mod #2: Which of the following statements about a PRIMARY KEY is incorrect?​ A. Each data value must be unique B. It identifies which column(s) uniquely identify each record. C. It can be NULL, as long as the FOREIGN KEY contains a value D. None of the above

C. It can be NULL, as long as the FOREIGN KEY contains a value

Mod #2: Explain the similarities and differences between the UNIQUE and PRIMARY KEY constraints.​

A PRIMARY KEY identifies which columns contain unique records and helps ensure referential integrity through the use of FOREIGN KEYS. The UNIQUE constraint simply requires that a column not have duplicate values.

Mod #2: Which of the following statements about the FOREIGN KEY constraint is incorrect?​ A. ​When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table. B. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted. C. ​The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table. D. The constraint exists between two tables, called the parent table and the child table.

C. ​The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.

Mod #2: What is the problem associated with changing the name of a table?

Changing the name of a table can cause confusion when maintaining a database

Mod #2: What is the difference between how a NOT NULL constraint is added to an existing column and how other types of constraints are added?​

Constraints must have their conditions defined whereas NOT NULL simply means the column cannot have a null value.

Mod #2: What is the difference between dropping a column with the DROP COLUMN clause and setting a column as unused with the SET UNUSED clause?​

DROP COLUMN removes a column immediately and SET UNUSED marks it for deletion at a later time.

Mod #2: Which of the following keywords is used to remove a database table in Oracle 12c?​

DROP TABLE

Mod #2: [T/F] A CHAR column cannot be resized to a width that is smaller than the data it already contains.

True

Mod #2: [T/F] A CHECK constraint requires that a data value meet a certain condition before the record is added to the database table.

True

Mod #2: [T/F] A FOREIGN KEY constraint can be added to the column of a table to ensure that the referenced data value actually exists in the other table.​

True

Mod #2: [T/F] A PRIMARY KEY constraint will make certain the column designated as the primary key does not contain a NULL value.

True

Mod #2: [T/F] A UNIQUE constraint is the same as a PRIMARY KEY constraint, except that it will accept NULL values.

True

Mod #2: [T/F] A table that has been dropped without the PURGE option can be retrieved using the FLASHBACK TABLE command.​

True

Mod #2: [T/F] DDL commands are used to create or modify database objects. _________________________​

True

Mod #2: Which of the following types of constraints is used to enforce referential integrity?​

FOREIGN KEY

Mod #2: [T/F] A FOREIGN KEY constraint will not allow a row containing a NULL value in the foreign key column to be added to the table.

False

Mod #2: [T/F] A column name can consist of up to 225 characters.​

False

Mod #2: [T/F] DML commands are used to create or modify database tables.​

False

Mod #2: [T/F] Data manipulation language commands are used to create or modify database tables. _________________________​

False

​Mod #2: [T/F] The DELETE TABLE command can be used to delete all the data stored in a table and release the storage space, yet retain the structure of the table. _________________________

False

Mod #2: Which of the following is a valid column name?​

NEW_COLUMN

Mod #2: What is the relationship between a NOT NULL constraint and a CHECK constraint?

NOT NULL requires that a column not have a null value and a CHECK constraint checks the column to verify it meets certain conditions.

Mod #2: Which of the following is a valid statement?​ A. Constraints are rules used to enforce business rules, practices, and policies B. Constraints ensure the accuracy and integrity of data. C. Constraints prevent errors by not allowing data to be added to tables if the data violates specific rules. D. All of the above

D. All of the above

Mod #2: Which of the following is a valid SQL statement?​ A. CREATE TABLE newname (colA, colB); B. CREATE TABLE newname (colA DATE, colB VARCHAR2(3); C. ​CREATE TABLE newname as SELECT * FROM books; D. CREATE TABLE newname (colA NUMBER, colB DATE);

D. CREATE TABLE newname (colA NUMBER, colB DATE);

Mod #2: Which of the following is displayed by the DESCRIBE command?​ A. column names B. not null requirements C. datatypes D. all of the above

D. all of the above

Mod #2: Which of the following datatypes refers to variable-length character data, where n represents the maximum length of the column?​

VARCHAR2(n)