Oracle 12c: SQL - Chapters 1 - 6 (Exam)

Based on the structure of the BOOKS table, which of the following is a valid SQL statement?

CREATE INDEX books_title_idx ON books (INITCAP(title));​

A *Datatype* is a rule used to ensure the accuracy of data stored in a database.

False

A collection of fields is a file.

False

A column has low cardinality if it contains many distinct values.

False

A constraint can be renamed using the ALTER TABLE command.

False

A constraint can only be created as part of the CREATE TABLE command.

False

A constraint name can consist of up to 10 characters.

False

A database is a physical storage device for data.

False

A one-to-many relationship cannot be included in a relational database.

False

A table name can consist of numbers, letters, and blank spaces.

False

A(n) synonym is a collection of objects.

False

An index is implicitly created when a NOT NULL constraint is added to a table.

False

Each clause of a SELECT statement *must* be entered on a new line in SQL*Plus.

False

Each section of a SQL command that begins with a keyword is known as a statement.

False

If the ON DELETE CASCADE keywords are included when a(n) *PRIMARY KEY* constraint is created, then if a row is deleted from the parent table, any corresponding records in the child table are also deleted.

False

If you assign a column alias to a column, it must be enclosed in double quotation marks (" ").

False

Only one column can be added to an existing table at one time.

False

Only one type of relationship can be represented in an Entity-Relationship Model.

False

Single-row functions cannot be included in the UPDATE command.

False

The *DROP* command can be used to remove rows from an existing table.

False

The ALPHANUMERIC datatype can be used to store characters and numbers to a maximum width of 2000.

False

The CREATE TABLE cannot contain a subquery.

False

The data *warehouse* contains information about objects included in the database.

False

To retain the structure of a table, while deleting all its data and releasing their storage space, execute the TRUNC command.

False

Transitive dependency can only exist if the data is uniquely identified by a composite primary key.

False

When DDL operations are performed, Oracle 12c will automatically place a(n) *shared* lock on the table.

False

When including a subquery in an INSERT command, the syntax of enclosing the subquery in parentheses is *mandatory*.

False

When the column names are listed in the INSERT INTO clause of the INSERT command, the order of the names must exactly match the order of the data values in the *UPDATE* clause.

False

A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.

INCREMENT BY

*Parentheses* can be used to override the order of operations in an arithmetic expression.

True

A bridging table can be used to eliminate a many-to-many relationship in a relational database.

True

A function-based index can be used for queries that include searches based upon arithmetic expressions or functions.

True

A many-to-many relationship cannot exist in a relational database.

True

If a data value violates a(n) *constraint*, the entire row is prevented from being added to the table.

True

The SET clause of the UPDATE command is used to identify the column(s) being updated.

True

The START WITH clause of a sequence cannot be reset with the ALTER SEQUENCE command.

True

The default format of a DATE column in Oracle 12c is DD-MON-YY.

True

The highest possible value for a descending sequence is -1.

True

The lowest possible value for an increasing sequence is 1 .

True

When a column is deleted, the deletion is permanent.

True

When a positive value is assigned to the INCREMENT BY clause of the CREATE SEQUENCE command, numeric values are generated in ascending order.

True

When a subquery is used in the INSERT command, the subquery does not have to be enclosed in parentheses.

True

When a user asks the database a question, the user is issuing a query.

True

Which of the following serves the same basic purpose as an index in a book, by allowing users to quickly locate specific records?

index

Which of the following symbols cannot be used in a table name?

​%

Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?

​ALTER TABLE customers MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));

What will happen if you try to use the INSERT command to insert a NULL value into a column that has been assigned a PRIMARY KEY constraint?

​An error message is returned and the row is not added to the table.

Which command will create an index?

​CREATE INDEX indexname ON tablename (columnname)

Which of the following commands can be used to create a sequence for the CUSTOMERS table?

​CREATE SEQUENCE customers_customer#;

Based on the structure of the ORDERITEMS table, which of the following commands was most likely used to create the table?

​CREATE TABLE orderitems (order# NUMBER (4), item# NUMBER(2), isbn VARCHAR2(10), qty NUMBER(3), PRIMARY KEY(order#, item#));

Which of the following commands will delete only publisher 4 from the PUBLISHER table?

​DELETE FROM publisher WHERE pubid = 4;

Which clause will allow you to disable a constraint?

​DISABLE

Which clause will allow you to delete a constraint?

​DROP

In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a FOREIGN KEY constraint.

​R

Which of the following commands allows a user to "undo" uncommitted changes to data?

​ROLLBACK

Which of the following commands is used to issue a query?

​SELECT

Which of the following is a valid SQL statement to retrieve data from the ORDERS table?

​SELECT order#, orderdate, shipdate FROM orders;

Based upon the contents of the BOOKS table, which of the following SQL statements will display two columns in its output?

​SELECT title, retail-cost "Profit" FROM books;

Which of the following queries will allow hidden columns to be displayed?

​SELECT which specifically references the hidden column.

Which of the following statements is correct?

​The columns will be listed in the results in the same order they are listed in the SELECT clause of the SELECT statement.

Which of the following commands is used to modify existing data in a table?

​UPDATE

Which of the following is not a valid SQL command?

​UPDATE acctmanager WHERE amid = 'J500';

Which data dictionary object contains a column named HIDDEN_COLUMN?

​USER_TAB_COLS.

Which of the following methods is not used in an INSERT statement to enable the use of a DEFAULT sequence value defined for a column?

​Use NEXTVAL to call the value

Which of the following keywords is omitted from the INSERT command if the data to be added to a table is already contained in another table?

​VALUES

If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.

​WHERE

A lock arising from a SELECT...FOR UPDATE command will be released when ____.

​a COMMIT command is executed

Partial dependency exists if what conditions exist?

​a column is dependent only on a portion of a composite primary key

When a constraint is created at the table level, the constraint definition is provided ____ the column definition list.

​after

If a new column is added to the PROMOTION table, where will the new column be listed?

​after the MAXRETAIL column

Which of the following terms is considered the basic unit of data in a database?

​character

Analyzing historical sales data stored in a database is commonly referred to as ____.

​data mining

If uncontrolled, what can lead to data anomalies?

​data redundancy

A(n) ____ simply identifies the type of data that Oracle 12c will be expected to store in a column.

​datatype

Which of the following does not contain repeating groups, but has a primary key and possibly partial dependencies?

​first normal form (1NF)

Which of the following are case sensitive in Oracle 12c?

​none of the above

Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?

​one-to-many

If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.

​private

Based upon the contents of the BOOKS table, which of the following columns will be displayed with a right-aligned column heading?

​pubid

A record in the logical design of a database corresponds to a ____ in the physical database.

​row

If the data has no partial dependencies, repeating groups, or transitive dependencies, and has a composite primary key, the data is in which form?

​third normal

Data in second normal form (2NF) may contain which of the following?

​transitive dependencies

Which of the following symbols can be used to combine data from different columns into one column of output?

​||

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 drop the constraint?

ALTER TABLE orderitems DROP PRIMARY KEY;

If a PRIMARY KEY constraint, named ORDERITEMS_PK, for the ORDER# and ITEM# columns of the ORDERITEMS table have been disabled, which of the following commands will enable the constraint?

ALTER TABLE orderitems ENABLE CONSTRAINT orderitems_pk;

A(n) CRBMDS is a software program that can be used to create a relational database and allows you to enter, manipulate, and retrieve data.

False

If one of the data values being entered into a table by the INSERT command violates an existing constraint, the remaining portion of the row will be added, but the data violating the constraint will need to be added at a later time using the UPDATE command.

False

The structure of a table can be viewed by issuing the STRUCTURE command.

False

A record in the logical design of a database corresponds to a row in the physical table of a relational database.

True

If multiple column names are listed in the SELECT clause of a SELECT statement, the columns must be separated by a comma (,).

True

Sequence settings can be altered using the ALTER SEQUENCE command.

True

The *syntax* for a SQL statement gives the basic structure required to execute the statement. _________________________

True

The ALTER TABLE command can be used to *delete* an existing constraint.

True

The ALTER TABLE command can be used to disable a constraint.

True

When modifying data in existing columns, which of the following is correct?

​A column must be as wide as the data it already contains.

Which command will modify a sequence?

​ALTER SEQUENCE sequencename

Which of the following is not a constraint type in Oracle 12c?

​REFERENCE

Which of the following commands will change the name of the PROMOTION table to GIFT?

​RENAME promotion TO gift;

Which command is used to rename a table you own?

​RENAME...TO

A column alias must be contained within double quotation marks (" ") if it contains which of the following?

​all of the above