Database Part2 M4
Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will list all orders contained in the ORDERS table that have been shipped based upon the customer# and order#?
Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will list all orders contained in the ORDERS table that have been shipped based upon the customer# and order#?
SELECT * FROM orders WHERE shipdate-orderdate <= 3;
Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will return all orders shipped within three days after the order was received?
SELECT order# FROM orders WHERE SUBSTR(shipzip, 1, 3) = 323;
Based upon the contents of the ORDERS table, which of the following SQL statements will display only those orders shipped to the zip code zone that begins with 323?
SELECT order#, TO_CHAR(shipdate-orderdate, '99') FROM ordersWHERE shipdate IS NOT NULL;
Based upon the contents of the ORDERS table, which of the following SQL statements will display the number of days between the date an order was placed and the date it was shipped?
SELECT shipstate, COUNT(*)FROM orders GROUP BY shipstate;
Based upon the contents of the ORDERS table, which of the following will display how many orders were shipped to each state?
SELECT shipstate, COUNT(*)FROM ordersGROUP BY shipstate;
Based upon the contents of the ORDERS table, which of the following will display how many orders were shipped to each state?
SELECT INITCAP(CONCAT(shipcity, CONCAT(', ', CONCAT(shipstate, CONCAT(' ', shipzip))))) FROM orders;
Based upon the contents of the ORDERS table, which of the following will display the shipping location as: Shipcity, Shipstate Shipzip
none of the above
Based upon the contents of the PUBLISHER table, which of the following SELECT statements will display the publisher's name first in the results?
SELECT contact person, name FROM publisher;
Based upon the contents of the PUBLISHER table, which of the following SQL statements will display only two columns in the results?
SELECT pubid name FROM publisher;
Based upon the contents of the PUBLISHER table, which of the following SQL statements will execute without returning an error message?
SELECT pubid name FROM publisher;
Based upon the contents of the PUBLISHER table, which of the following SQL statements will execute without returning an error message?
SELECT contact, nameFROM publisher;
Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?
SELECT name, contact FROM publisher/
Based upon the contents of the PUBLISHER table, which of the following is not a valid SQL statement?
SELECT order#, "order number" FROM orders;
Based upon the structure of the ORDERS table, which of the following SQL statements will return an error message upon execution?
SELECT order#, "order number" FROM orders;
Based upon the structure of the ORDERS table, which of the following SQL statements will return an error message upon execution?
SELECT order#, orderdate DateOrdered FROM orders;
Based upon the structure of the ORDERS table, which of the following is a valid SQL statement?
primary
Because a partial dependency can exist only if a table's primary key is composed of several attributes, a table whose ____________________ key consists of only a single attribute is automatically in 2NF if it is in 1NF.
False
By default, a column alias is displayed in lower case characters in the results.
False
By default, data within a character or data column is right-aligned when it is displayed.
True
By default, data within a numeric column is right-aligned when it is displayed.
True
By default, data within a numeric column is right-aligned when it is displayed. _________________________
True
By default, query results are sorted in ascending order based upon the column specified in the ORDER BY clause.
right
By default, the data for a numeric column is ____________________ aligned when it is displayed.
true
By default, use of the JOIN keyword creates an inner join.
False
Case conversion functions can only be used in the SELECT clause of a SELECT statement.
False
Case conversion functions can only be used in the SELECT clause of a SELECT statement.
projection
Choosing specific columns in a SELECT statement is known as ____________________.
True
Choosing specific columns to be displayed by a SELECT statement is known as projection.
False
Column aliases can be used in the GROUP BY clause.
True
Column aliases can be used in the subquery to assign new column names to the columns contained in a view.
True
Column headings may be truncated in the results of a SELECT statement.
True
Columns used to group data in the GROUP BY clause do not have to be listed in the SELECT clause.
Set operators
Combine the results of two (or more) SELECT statements. Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS.
concatenation
Combining the contents of two or more columns is known as ____________________.
False, concatenation
Combining the contents of two or more columns is known as projection.
data manipulation language (DML)
Commands used to modify data are called ____ commands.
data manipulation language (DML)
Commands used to modify data are called ____ commands.
data manipulation language (DML)
Commands used to modify data are called ____ commands. Selected Answer:d. data control language (DCL)Correct Answer:c. data manipulation language (DML)
Which of the following are used to enforce business rules?
Constraints
True
Constraints are rules used to enforce business rules, practices, and policies.
Constraints are used to ensure the accuracy and integrity of the data contained in the database.
Constraints are used to ensure the accuracy and integrity of the data contained in the database.
True
Constraints are used to ensure the accuracy and integrity of the data contained in the database.
derived
From a strictly database point of view, ____ attribute values can be calculated when they are needed to write reports or invoices.
True
Group functions are also known as aggregate functions.
True
Group functions can be nested inside single-row functions.
True
Group functions can only be nested to a depth of two.
1
How many joining conditions will be required in an SQL statement that is used to determine the gift that corresponds to each book in the BOOKS table?
True
IN is a valid multiple-row subquery operator.
False
If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be 13 rows in the results.
True
If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
R
If a FOREIGN KEY constraint is displayed in the USER_CONSTRAINTS view, the constraint type will have the letter ____________________ displayed.
Where
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed first.
Having
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
index
If a column contains a large number of NULL values, a(n) ____________________ can quickly help determine the rows that contain NULL values and those that do not.
truncated
If a column heading is longer than the assigned width of a character column, the column heading will be ____________________.
SAL_PCT DEFAULT ON NULL 100.
If a column named SAL_PCT is to be defined to ensure a value of 100 is input if a NULL value is provided when a new row is inserted, which column definition should be used?
table
If a constraint applies to more than one column, the constraint must be created at the ____________________ level.
False
If a group function is used in the SELECT clause, then any individual columns listed in the SELECT clause must also be listed in the ORDER BY clause.
Commit
If a new row is added to the PROMOTION table, which of the following will make the change permanent?
commit
If a new row is added to the PROMOTION table, which of the following will make the change permanent?
False
If a numeric column is being used for comparison against the search condition, the search condition is enclosed in single quotation marks.
True
If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.
correlated
If a subquery references a column from the outer query, then it is a(n) ____________________ subquery.
False
If a table alias is assigned in the SELECT clause, it must be used any time the table is referenced in that SQL statement.
false
If a table alias is assigned in the SELECT clause, it must be used any time the table is referenced in that SQL statement.
partial dependencies
If a table has multiple candidate keys and one of those candidate keys is a composite key, the table can have ____ based on this composite candidate key, even when the primary key chosen is a single attribute.
True
If a user has a shared lock on a table, this will prevent any other user from obtaining a shared or exclusive lock on the same table.
CONSTRAINT
If a user is going to assign a name to a constraint, the ____________________ keyword must be included in the command.
constraint
If a user is going to assign a name to a constraint, the ____________________ keyword must be included in the command.
OR REPLACE
If a view needs to be modified, it must be re-created using the ____________________ keywords in the CREATE VIEW command.
re-create the view without the option
If a view was created with the WITH CHECK OPTION constraint, to remove the constraint you will need to ____.
re-create the view without the option
If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____.
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. _________________________
where
If a(n) ____________________ clause is not included in the UPDATE command, then all rows in the table will be changed.
UNIQUE
If a(n) ____________________ constraint is being dropped, then only the type of constraint and the column name need to be included in the DROP clause of the ALTER TABLE command.
primary key
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.
Group by
If an individual column is listed in a SELECT clause, along with a group function, the column must also be included in a(n) ____________________ clause.
False
If data is only being added to some of the columns in a table, the names of the columns must be listed in the VALUES clause.
10
If more than ____________________ percent of a table is expected to be returned in the results of a query, then an index will probably not speed up the process.
False
If more than one data value is being added to a table, the values must be separated by parentheses.
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.
ALL
If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed.
All
If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed.
All
If the DISTINCT keyword is not included in the VARIANCE function, the ____ keyword will be assumed.
ALTER TABLE promotion DROP UNUSED COLUMNS;
If the MAXRETAIL column of the PROMOTION table has been set as unused, which of the following commands is valid?
private
If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.
False
If the SET clause of the UPDATE command is omitted, then all rows in the table will be updated with the new data value.
False
If the SET clause of the UPDATE command is omitted, then all rows in the table will be updated with the new data value. _________________________
False
If the WHERE clause is omitted from the DELETE command, then all columns from the database table will be dropped. _________________________
False
If the WHERE clause is omitted from the UPDATE command, then all columns in the table will be changed.
Where
If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
ALTER TABLE orderitems DROP PRIMARY KEY;
If the constraints on the ORDER# and ITEM# columns of the ORDERITEMS table were created as a PRIMARY constraint, and the actual constraint name is not known, which of the following commands can be used to delete the constraint?
ORDER BY
If the data returned by the GROUP BY clause needs to be sorted in descending order, you must include a(n) ____________________ clause in the SELECT statement.
15
If the first table in a Cartesian join has five rows and the second table has three rows, the results will consist of ____________________ rows.
MAXVALUE 200
If the last values generated by a sequence were 277 and 278, which of the following changes cannot be initiated by the ALTER SEQUENCE command?
MAXVALUE 200
If the last values generated by a sequence were 277 and 278, which of the following changes cannot be initiated by the ALTER SEQUENCE command?
True
If the minimum value for a sequence is not specified, then NOMINVALUE will be assumed as the default.
HAVING
If the result returned from a subquery must be compared to a group function, then the inner query must be nested in the outer query's ____ clause.
Having
If the result returned from a subquery must be compared to a group function, then the inner query must be nested in the outer query's ____ clause.
HAVING
If the result returned from a subquery must be compared to a group function, then the inner query must be nested in the outer query's ____ clause.
TRUE
If the value of the EXISTS operator is ____________________, then the rows meeting the condition are displayed.
Join...USING
If you are attempting to join two tables that have multiple common columns, which of the following JOIN keywords should be used to specify how the tables should be linked?
True
If you are joining four tables in a SELECT statement, three joining conditions will be required.
False
If you are joining two tables in a SELECT statement, three joining conditions will be required.
False
If you assign a column alias to a column, it must be enclosed in double quotation marks (" ").
double quotation
If you do not want a column alias to appear in all upper case characters, the column alias must be enclosed in ____________________ marks.
true
In Oracle12c, a MERGE statement compares data between two tables and can perform a series of DML actions to assist in synchronizing the data of the two tables.
True
In Oracle12c, subqueries can be nested to a depth of 255 in a WHERE clause.
True
In Oracle12c, there is no depth limit on the number of subqueries that can be nested in a(n) FROM clause.
4NF
In _________________________ no row may contain two or more multivalued facts about an entity.
90
In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output.
False
In a WHERE clause, logical operators are evaluated before comparison operators.
Flexibility
In a real-world environment, we must strike a balance between design integrity and ____.
division
In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle 12c.
performance
In order to meet ___________________ requirements, you may have to denormalize some portion of the database.
only columns in the SELECT clause can be used for sorting
In regards to sorting, if a DISTINCT option is used in the SELECT clause of a query then ____.
False
In the USER_CONSTRAINTS view, the constraint type for a NOT NULL constraint will be listed as N. _________________________
False
In the USER_CONSTRAINTS view, the constraint type for a PRIMARY KEY constraint will be listed as PK.
C
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a CHECK constraint.
systems deployment
In which step of the Systems Development Life Cycle (SDLC) is the system actually used by the end-user on a regular basis?
True
Including MONTH in the format argument of the TO_DATE function indicates that the month is spelled out in the specified date.
True
Including MONTH in the format argument of the TO_DATE function indicates that the month is spelled out in the specified date. _________________________
True
Indexes are usually created for frequently referenced or searched columns.
DML
Indexes can speed up row retrieval, but can slow down ____________________ operations because the index must also be updated.
Column qualifier
Indicates the table containing a referenced column.
False
Insignificant zeros are displayed for numeric columns by default to show two decimal places.
False
Insignificant zeros are displayed for numeric columns by default to show two decimal places. _________________________
inner
Joins are classified as ____________________ joins if the results can only contain the rows that had matching values in each table, rather than rows being matched with NULL values.
Inner joins
Joins that display data if there were a corresponding record in each table queried. Equality joins, non-equality joins, and self-joins are all classified as inner joins.
False
LOWER, UPPER, and INITCAP functions are all character manipulation functions.
Non-equality join
Links data in two tables that do not have equivalent rows of data.
Equality joins
Links table data in two (or more) tables having equivalent data stored in a common column. These joins might also be called equijoins, inner joins, or simple joins
Cartesian join
Links table data so each record in the first table is matched with each individual record in the second table. Also called a Cartesian product or cross join.
NULL value
Means no value has been stored in that particular field. A NULL value indicates the absence of data, not a blank space.
True
More than one column can be changed at a time with the ALTER TABLE...MODIFY command.
True
More than one column can be changed at a time with the ALTER TABLE...MODIFY command. _________________________
3NF
Most designers consider the BCNF as a special case of the ____.
True
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
True
N is a valid multiple-row subquery operator.
False
Normalization is a process that is used for changing attributes to entities.
False
Normalization is a very important database design ingredient and the highest level is always the most desirable.
False
Normalization produces a lower normal form.
Normalization produces a lower normal form.
Normalization produces a lower normal form.
False
Normalization purity is easy to sustain in the modern database environment.
True
Normalization works through a series of stages called normal forms.
True
Once a column as been set as UNUSED, it cannot be recovered.
False
Only a DATE function can be nested inside another DATE function.
True
Only one PRIMARY KEY constraint can exist for each table.
Data dictionary
Oracle 11g stores all information about database objects in this "dictionary." Stored information includes objects' names, types, structures, owners, and the identity of users who have access to each object
Start with
Oracle12c will begin each sequence with the value of one, unless another value is specified in the ____ clause.
True
Parentheses can be used to override the order of operations in an arithmetic expression.
True
Parentheses can be used to override the order of operations in an arithmetic expression. _________________________
True
Partial dependency can only exist if the data is uniquely identified by a composite primary key.
a column is dependent only on a portion of a composite primary key
Partial dependency exists if what conditions exist?
True
Pre-generated sequence numbers that have not been used when the database is shut down will no longer be available when the database is restarted.
False
Rows can be added through a complex view that is based upon grouped data.
delete
Rows can be deleted from a table using the ____________________ command.
false
Rows can be deleted through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
false
Rows cannot be added to a simple view.
False
Rows cannot be added to a table through a complex view that was created with the ORDER BY clause.
True
Rows cannot be deleted from a table through a complex view that is based on a group function.
True
Rows cannot be deleted if the complex view was created with a(n) GROUP BY clause.
USING
SELECT customer# FROM customers MINUS SELECT customer# FROM orders;
True
SELECT9 is a valid table name.
SQL*Plus
SQL commands can be issued in Oracle 12c through ____________________ which is an interface that allows users to interact with the database.
False
SQL*Plus can only be accessed via an internet interface.
True
SQL*Plus is an Oracle tool used to enter and edit SQL statements.
False
SYSTEMDATE can be used to include the computer's date in a SQL statement.
False
Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
False
Single-row functions return one row of results for each group or category of rows processed.
False
Single-row functions return one row of results for each group or category of rows processed.
False
Single-row operators can be used with multiple-row subqueries that return only one column of results.
Wildcard characters
Symbols used to represent one or more alphanumeric characters. The wildcard characters in Oracle 11g are the percent sign (%) and the underscore symbol ( _ ). The percent sign is used to represent any number of characters; the underscore represents one character.
True
Tables can be joined in the FROM clause or the WHERE clause of a SELECT statement.
true
Tables can be joined in the FROM clause or the WHERE clause of a SELECT statement.
False
The "equal to" comparison operator is used to search for NULL values. _________________________
False
The < symbol is used to search for records containing a value that is less than or equal to the stated search condition.
single-row
The <> operator is referred to as a(n) ____ operator.
single-row
The = operator is referred to as a(n) ____ operator.
True
The =ANY operator yields the same results as using the IN multiple-row operator.
more than the highest
The >ALL operator indicates that a value must be ____ value returned by the subquery. query?
more than the highest
The >ALL operator indicates that a value must be ____ value returned by the subquery. query?
true
The ADD clause of the ALTER TABLE command is used to add a PRIMARY KEY constraint to an existing table.
True
The ADD clause of the ALTER TABLE command is used to add a PRIMARY KEY constraint to an existing table. _________________________
False
The ADD_MONTHS function is used to add one month to a specified date.
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
The ALTER TABLE command with the ENABLE clause can be used to enable a constraint.
False
The ALTER TABLE command with the MODIFY clause can be used to disable a constraint.
numeric
The AVG function can be used with ____ values.
False
The CHAR datatype is used to store variable-length data that can consist of letters and numbers.
False
The COMMIT command is used to add new rows to a table.
False
The CONCAT function is used to store the contents of two columns into one column.
True
The CONSTRAINT keyword is required if the user is going to assign a name to a constraint.
False
The COUNT function can only be used for numeric columns.
True
The COUNT(*) function can be used to count the number of rows containing NULL and non-NULL values.
False
The COUNT(*) function is only used to count the number of records containing NULL values.
False
The CREATE TABLE cannot contain a subquery.
True
The CREATE TABLE command is used to create a table in Oracle 12c.
False
The CURRVAL pseudocolumn is used to generate the next value in a sequence.
False
The CYCLE option prevents a sequence from regenerating previous values.
True
The DECODE function allows the user to specify different actions to be taken, depending on the value being used for the comparison.
True
The DELETE operation can be performed through a complex view that contains an arithmetic operation.
False
The DESCRIBE command is used to view data in a table.
False
The DIST or UNIQ keywords can be used to suppress duplicate data in the results of a SELECT statement.
True
The DROP TABLE command can be used to remove a table and all its data from a database.
ALTER TABLE
The DROP UNUSED COLUMNS clause can be used with the ____________________ command to delete any column previously set as unused.
False
The DROP command can be used to remove rows from an existing table.
False
The EXISTED operator is used to determine whether a condition is present in a subquery.
True
The EXISTS operator can be used with multiple-row subqueries.
none of the above
The EXISTS operator must be listed ____
False
The GENERATE option can be used to have a sequence pre-generate a set of numbers before they are requested by a user.
False
The GROUP BY clause can only be used when a SELECT statement includes a group function.
False
The GROUP BY clause is used to restrict the groups returned by a query.
True
The HAVING clause specifies which groups will be displayed in the results, while the WHERE clause restricts the records that are retrieved from the table for processing.
True
The IN operator is valid for multiple-row subqueries.
False
The INCREMENT BY setting for a sequence cannot be changed with the ALTER SEQUENCE command.
False
The INSERT command is used to modify existing data in a row.
False
The INSTR function is used to return a portion of a character string.
FROM
The JOIN keyword is included in which of the following clauses?
from
The JOIN keyword is included in which of the following clauses?
False
The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked.
False
The JOIN keyword must be used in the WHERE clause of a SELECT statement.
false
The JOIN...USING keywords are used to join two tables that do not have a commonly named and defined column.
False
The LENGTH function is used to determine the number of rows in a table.
True
The LPAD or RPAD function can be used to add a character to extend the current contents of a column to a specified width.
False
The LTRIM function can be used to remove a specific number of characters from the left side of a set of data.
True
The MAX function can be used with character data.
False
The MAX function can only be used with numeric or date columns.
True
The MIN function can be used with numeric, date, and character columns.
True
The MIN function can be used with the DISTINCT or ALL keywords.
True
The MIN function is used to determine the smallest value in a specified column.
True
The MINUS set operator is used to display rows that were uniquely returned by the first query in the set.
False
The MODIFY command can be used to change existing data stored in a table.
True
The MONTHS_BETWEEN function can be used to determine the number of months between two dates
False
The NATURAL JOIN keywords can be used to create non-equality joins.
false
The NATURAL JOIN keywords can be used to create non-equality joins.
True
The NEXTVAL pseudocolumn can be referenced in an INSERT command to add the value to a database table.
True
The NOCACHE option specifies that the sequence can generate a number only after a request has been made by a user.
False
The NOFORCE keyword can be used to create a view based upon a table that has not yet been created.
False
The NOFORCE keyword can be used to create a view based upon a table that has not yet been created. _________________________
column
The NOT NULL constraint can only be created at the ____ level.
True
The NVL function can be used in a subquery.
True
The NVL function can be used to include records containing null values in calculations.
True
The NVL2 function allows different options to be selected, based upon whether a NULL value exists.
True
The OR REPLACE clause is only necessary if another view may already exist with the same name.
False
The REFERENCE constraint is used to ensure that a data value meets a specified condition before a record is added to a table.
False
The ROLLBACK command is used to permanently save changed data.
True
The ROUND function can be used to round numeric data to the left or right of the decimal point.
False
The ROUND function can be used to truncate numeric data.
False
The SELECT clause identifies the table containing the data to be queried. _
False
The SELECT clause identifies the table containing the data to be queried. _________________________
True
The SELECT...FOR UPDATE can be used to create a shared lock.
share
The SELECT...FOR UPDATE command will automatically obtain a(n) ____________________ lock on the row to be changed.
False
The SORT BY clause is used to present query results in a specific order.
False
The SOUND function is used to determine the phonetic representation of a character string. _________________________
False
The START WITH clause is used to identify the starting numeric value for a synonym.
True
The SUBSTR function is used to return a portion of a character string. _________________________
False
The SUBSTR function is used to substitute one character string for another in a set of data.
numeric
The SUM function can only be used with ____________________ columns.
True
The SUM function is used to calculate the total amount stored in a numeric field for a group of records.
False
The SYSDATE can be used as a condition in a CHECK constraint.
True
The SYSDATE keyword can be used in the INSERT command to enter the computer's system date when adding a row to a table.
False
The SYSDATE keyword cannot be included in the INSERT command, only the UPDATE command.
False
The SYSDATE keyword cannot be included in the INSERT command, only the UPDATE command.
True
The TO CHAR function can be used to display insignificant zeros.
True
The TO_CHAR function can be used to add a dollar sign ($) to a numeric value.
True
The UPDATE command can be used to add data to existing rows in a table.
False
The UPDATE command cannot be used on a view created with the WITH CHECK OPTION.
False
The UPPER function can be used to display upper-case characters in lower-case.
False
The USER_ALL data dictionary view displays information about all the relevant objects owned by the user.
True
The USER_INDEXES data dictionary view can be used to verify existing indexes.
True
The USER_SEQUENCES view of the data dictionary can be used to determine the current values and settings for a sequence.
false
The USING clause must be used with the JOIN keyword when linking tables that do not contain a commonly named column.
True
The VALUES clause is not included with the INSERT command if the data is being retrieved from another table.
False
The VALUES clause of the UPDATE command is used to specify the columns being updated and the new data values.
False
The VAR function is used to determine the variance contained within a set of data.
numeric
The VARIANCE function can be used with ____ columns.
True
The VARIANCE function is used to determine the variance in a group of numeric data.
False
The WHERE clause can contain group functions.
True
The WHERE clause filters the data before grouping, while the HAVING clause filters the groups after grouping is performed.
True
The WHERE clause of the UPDATE command is used to specify exactly which rows should be changed.
False
The YR element can be included in a format argument of the TO_CHAR function to spell out the year of the specified date.
GROUP BY
The ____ clause is used to indicate that groups should be created.
Group
The ____ clause is used to indicate that groups should be created.
SELECT...FOR UPDATE
The ____ command can be used to view the contents of a record when it is anticipated that the record will need to be modified. It places a shared lock on the record(s) to be changed and prevents any other user from acquiring a lock on the same record(s).
LOCK TABLE
The ____ command will prevent two users from trying to make changes to the same table at the same time.
CHECK
The ____ constraint requires that a specific condition be met before a record can be added to a table.
AVG
The ____ function calculates the average of the numeric values in a specified column.
TO_CHAR
The ____ function can be used to convert dates and numbers to a formatted character string.
MAX
The ____ function can be used to determine the largest value stored in a specified column.
Max
The ____ function can be used to determine the largest value stored in a specified column.
NVL
The ____ function can be used to include NULL values in a calculation.
MIN
The ____ function returns the smallest value in a specified column.
EXISTS
The ____ operator is used to determine whether a condition is present in a subquery.
Ccle/NoCycle
The ____ options are used to determine whether Oracle12c should begin reissuing values from the sequence once the minimum or maximum value has been reached.
cross
The ____________________ JOIN keyword can be used to create a Cartesian join.
drop
The ____________________ SEQUENCE command can be used to permanently delete a sequence from a database.
drop
The ____________________ SEQUENCE command can be used to permanently delete a sequence from a database.
drop
The ____________________ SYNONYM command is used to permanently remove a synonym from a database
Alter
The ____________________ TABLE command is used to add a PRIMARY KEY constraint to an existing table.
lock
The ____________________ TABLE command is used to prevent other users from making changes to a portion of a table.
WHERE
The ____________________ clause can be used to restrict rows, but not groups.
MINVALUE
The ____________________ clause identifies the lowest value a sequence can generate
MINVALUE
The ____________________ clause identifies the lowest value a sequence can generate.
Group By
The ____________________ clause is used to group data based upon a specified column or columns.
increment by
The ____________________ clause is used to indicate the interval that must exist between two values generated in a sequence.
Having
The ____________________ clause is used to restrict the groups returned by a query.
having
The ____________________ clause is used to restrict the groups returned by a query.
WHERE
The ____________________ clause is used to restrict the rows returned by a query.
Order by
The ____________________ clause is used to specify the order in which the data should be presented in the results of a query.
ADD
The ____________________ clause of the ALTER TABLE command is used to add a PRIMARY KEY constraint to an existing table.
START WITH
The ____________________ clause of the CREATE SEQUENCE command is used to identify the starting value for a sequence.
DROP VIEW
The ____________________ command can be used to permanently remove a view from a database.
rollback
The ____________________ command is used to "undo" changes that have not yet been committed.
UPDATE
The ____________________ command is used to add data to an existing row in a table.
insert
The ____________________ command is used to add new rows to a table.
Between
The ____________________ comparison operator is used to indicate that a data value must fall within a range of values to be included in the query results
To_Date
The ____________________ function allows a user to enter a date in a variety of formats, and then changes the value to the default Oracle12c internal storage format.
length
The ____________________ function can be used to determine the number of characters in a string.
Count
The ____________________ function can be used to determine the number of rows that have non-NULL values in a specified column.
count
The ____________________ function can be used to determine the number of rows that have non-NULL values in a specified column.
COUNT
The ____________________ function can be used to determine the number of rows that have non-NULL values in a specified column.A(n) ____________________ should be included as the argument for the COUNT function to include NULL values in the results.
Min
The ____________________ function can be used to determine the smallest value in a specified column.
INTCAP
The ____________________ function converts character strings to mixed-case, with each word beginning with a capital letter.
STDDEV
The ____________________ function is used to calculate the standard deviation for a column.
sum
The ____________________ function is used to calculate the total amount stored in a numeric field for a group of records.
Variance
The ____________________ function is used to determine how widely the numeric data contained within a specified column is spread out.
max
The ____________________ function is used to determine the largest value stored in a specified column.
NEXT_DAY
The ____________________ function is used to determine the next occurrence of a specific day of the week after a given date.
Replace
The ____________________ function is used to replace a specific set of characters found within a character string with another set of characters.
NVL
The ____________________ function is used to substitute a specified value for a NULL value.
LTRIM
The ____________________ function removes a specific string of characters from the left side of a set of data values.
decode
The ____________________ function takes a specified value and returns results based on a set of values provided in a list.
BITMAP
The ____________________ index structure can be particularly beneficial in queries involving compound conditions (AND and OR operator usage).
UNIQUE, DISTINCT
The ____________________ keyword can be included in a SELECT statement to suppress the display of duplicate data.
sysdate
The ____________________ keyword can be included in the INSERT command to enter the computer's date into the row when it is added to the table.
FORCE
The ____________________ keyword can be used to create a view based upon a table that does not yet exist.
distinct
The ____________________ keyword can be used with the COUNT function to specify that only unique occurrences should be included in the results.
Distinct
The ____________________ keyword is used to indicate that only unique values should be included in the calculation performed by the SUM function.
sysdate
The ____________________ keyword is used to return the value of the date according to the computer.
with Check Option
The ____________________ keywords are used to make certain any DML operations do not prevent a row from being accessible to the view after the DML operation is executed.
ON DELETE CASCADE
The ____________________ keywords can be added to the end of the command that creates a FOREIGN KEY constraint to indicate that when a row is deleted from the parent table that is referenced by entries in the child table, the rows in the child table should also be deleted.
NATURAL JOIN
The ____________________ keywords create a join automatically between two tables, based on columns with matching names.
natural join
The ____________________ keywords create a join automatically between two tables, based on columns with matching names.
NOT
The ____________________ logical operator is evaluated first in a sequence of logical operators.
OR
The ____________________ logical operator is evaluated last in a sequence of logical operators.
OR
The ____________________ logical operator is similar to the IN comparison operator.
>ALL
The ____________________ operator indicates that a value must be more than the highest value returned by the subquery.
outer join, +
The ____________________ operator is used to create an outer join in the WHERE clause of a SQL statement.
Existed
The ____________________ operator is used to determine whether a condition is present in a subquery.
exists
The ____________________ operator is used to determine whether a condition is present in a subquery.
CACHE
The ____________________ option of a sequence indicates that a set of values are to be pre-generated by the sequence rather than wait for a user request.
UNION ALL
The ____________________ set operator is used to display the combined results returned by multiple SELECT statements.
Union all
The ____________________ set operator is used to display the combined results returned by multiple SELECT statements.
union all
The ____________________ set operator is used to display the combined results returned by multiple SELECT statements.
>=
The ____________________ symbol is used for determining whether or not a value is greater than or equal to a specified search condition.
percent
The ____________________ symbol is used to represent one or more characters in a search pattern.
USER_INDEXES
The ____________________ view can be used to verify the existence of an index owned by a user.
False
The abbreviation for the DESCRIBE command is DES.
False
The abbreviation for the DESCRIBE command is DES. _________________________
False
The asterisk symbol (*) can be used in a SELECT statement to indicate that all rows in the table should be displayed in the results.
( )
The column list of the CREATE TABLE command must be enclosed in
False
The column names used in the view must be the same names as the column names in the underlying table(s).
SET
The column to be updated by the UPDATE command is specified in the ____ clause.
False
The columns displayed in the results of a SELECT statement will be in the same order as they are stored in the database table.
normalization
The combination of ____________________ and ER modeling yields a useful ERD, whose entities may now be translated into appropriate table structures.
False
The data warehouse contains information about objects included in the database.
True
The default beginning value for a sequence is one.
DD-MON-YY
The default format for a DATE column is ____________________.
numeric
The entire column heading for a column will be displayed, regardless of the assigned width of the column, if the column is defined as a(n) ____ column.
True
The equal sign, =, is a valid single-row operator.
many-to-many
The fact that a person can wear different size clothes and that different people can wear the same size clothes is best characterized as a what type of relationship?
True
The first column listed in an ORDER BY clause is considered the primary sort.
inline view
The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;
inline view
The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;
single-row
The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;
multiple-row
The following SQL statement contains which type of subquery? SELECT title, retail, category FROM books WHERE retail IN (SELECT MAX(retail) FROM books GROUP BY category);
multiple-column
The following SQL statement contains which type of subquery? SELECT title, retail, category, cataverage FROM books NATURAL JOIN (SELECT category, AVG(retail) cataverage FROM books GROUP BY
False
The format argument in the TO_CHAR function must be enclosed in double quotation marks.
True
The lowest possible value for an increasing sequence is 1 .
255
The maximum number of columns that can be specified for sorting data is ____.
True
The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column.
the Systems Development Life Cycle
The multi-step process used when creating a new system is referred to as ____.
true
The number of joining conditions required to join tables is always one less than the number of tables being joined.
False
The only group function that includes NULL values is the NVL function.
False
The only required clauses for a SELECT statement are the WHERE and FROM clauses. ________________________
single-row
The only type of subquery that can be used in a SELECT clause is a(n) ____________________ subquery.
False
The optional UNIQUE keyword instructs Oracle 11g to include only unique numeric values in its calculation.
where
The outer join operator can only be used in the ____________________ clause.
where Correct Answer:
The outer join operator can only be used in the ____________________ clause.
both a and b
The outer join operator in the WHERE clause cannot be used with which of the following operators?
True
The outer join operator is placed on the side of the comparison that is deficient or is missing the matching rows.
True
The outer join operator is placed on the side of the joining condition that references the table containing the deficient rows.
False
The outer join operator is used to combine the results of multiple SELECT statements.
Subquery
The outer query receives its input from the ____.
subquery
The outer query receives its input from the ____.
False
The percent sign (%) is used in a search pattern to indicate "exactly one character in this position".
Outer join operator
The plus (+) symbol enclosed in parentheses, used in an outer join operation.
anomalies
The problem with transitive dependencies is that they still yield data ____________________.
outer
The results of a subquery are passed back as input to the ____ query.
Outer
The results of a subquery are passed back to the ____________________ query.
False
The results of the outer query are passed to the inner query.
WHERE
The rows to be deleted from a table are identified by the ____________________ clause of the DELETE command.
True
The simplest approach to remove a partial dependency is to use each portion of the primary key to create separate tables.
alter
The starting value for a sequence cannot be changed using the ____________________ SEQUENCE command.
Systems Development Life Cycle (SDLC)
The steps used to design and develop a database are commonly referred to as the ______________________________.
inline
The subquery used to create a(n) ____ view can contain an ORDER BY clause.
True
The syntax for a SQL statement gives the basic structure required to execute the statement.
True
The syntax of the CREATE TABLE command requires that the column list be enclosed in parentheses. _
False
The table called DUMMY consists of a blank holding area that can be referenced by a user.
False
The table level approach can be used to create any constraint, except a CHECK constraint.
materialized
The type of view that actually replicates data is called a(n) ____ view.
False
The use of a column alias in the SELECT clause can be designated by the keyword ALIAS. _________________________
False
The variable name of a substitution variable is preceded by a(n) percent sign.
True
To add a series of asterisks (*) to a column with the intent of extending it to a specific length, you can use the LPAD or RPAD function
False
To calculate the difference between two date columns, you must use the TO_DATE function.
True
To change the starting value for a sequence, the sequence must be dropped and then re-created.
True
To create a PUBLIC synonym, the PUBLIC keyword must be used in the CREATE SYNONYM command.
TRUNCATE TABLE
To delete all the rows in a table and free up the storage space that was occupied by those rows, the ____ command should be used.
outer
To display rows from one table that do not have a corresponding row in the other table, you must create a(n) ____________________ join.
3
To display the name of each customer and the title of each book purchased by the customers would require how many join conditions?
True
To indicate that data should be sorted in descending order, use the DESC keyword.
MI
To indicate that minutes should be displayed, include ____________________ in the time element format argument.
DY
To indicate that the three-letter abbreviation for day of the week should be displayed, include ____________________ in the date element format argument.
USER_SEQUENCES
To verify individual settings for the clauses of a sequence, you can query the ____ table in the data dictionary.
True
Transaction control statements are used to either save modified data or to undo changes before they are committed.
False
Transitive dependency can only exist if the data is uniquely identified by a composite primary key.
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
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.
False
Update operations cannot be performed through a complex view that contains an arithmetic expression.
SYSDATE
Use the ____ keyword to enter the computer's date as a data value in the INSERT command.
Dynamic view
Used to access statistics relating to the database's performance
Logical operators
Used to combine two or more search conditions. The logical operators include AND and OR. The NOT operator reverses the meaning of search conditions.
ON
Using the JOIN...____________________ keywords to join two tables requires that a condition be specified to indicate how the tables are related.
On
Using the JOIN...____________________ keywords to join two tables requires that a condition be specified to indicate how the tables are related.
on
Using the JOIN...____________________ keywords to join two tables requires that a condition be specified to indicate how the tables are related.
False
Valid multiple-row operators include =, >, <, >=, <=.
True
Values cannot be inserted through a view into columns that are based on arithmetic expressions.
False
Views are not database objects.
7
What is the correct answer for the arithmetic expression 9+2*(3-4) using the order of operations employed by Oracle 12c when solving equations?
13
What is the correct answer for the arithmetic expression 9+2*3-2 using the order of operations employed by Oracle 12c when solving equations?
14
What is the correct solution for the arithmetic expression 2+8/2*9/3 using the order of operations employed by Oracle 12c when solving equations?
It is a temporary data source that exists only while a command is being executed.
What is the definition of an inline view?
two
What is the maximum number of columns or character strings that can be combined through a single CONCAT function?
foreign key
What name is used to denote a common field that exists between two tables, but is also the primary key for one of the tables?
False
When DDL operations are performed, Oracle 12c will automatically place a(n) shared lock on the table
True
When Oracle 12c encounters a substitution variable, the user will be prompted for the actual value to be assigned to the variable.
False
When a FOREIGN KEY constraint is being created, the REFERENTIAL keyword is used to indicate the table being referenced.
Where
When a SELECT statement contains WHERE, GROUP BY, and HAVING clauses, the ____________________ clause is always evaluated first.
Having
When a SELECT statement contains WHERE, GROUP BY, and HAVING clauses, the ____________________ clause is always evaluated last.
True
When a column is deleted, the deletion is permanent.
after
When a constraint is created at the table level, the constraint definition is provided ____ the column definition list.
single quotation marks
When a format argument is included in a function, it must be enclosed in ____.
True
When a multiple-column subquery is included in the outer query's WHERE clause, the column names listed in the WHERE clause must be in the same order as they're listed in the subquery's SELECT clause.
False
When a multiple-column subquery is used in the WHERE clause of the outer query, the column names listed on the left side of the comparison operator must be enclosed in double-quotation marks.
False
When a multiple-column subquery is used in the WHERE clause of the outer query, the column names listed on the left side of the comparison operator must be enclosed in single-quotation marks.
True
When a multiple-column subquery is used in the outer query's FROM clause, it creates a temporary table, called an inline view, that can be referenced by other clauses of the outer query.
BCNF
When a nonkey attribute is the determinant of a key attribute the table is in 3NF but not _________________________.
True
When a positive value is assigned to the INCREMENT BY clause of the CREATE SEQUENCE command, numeric values are generated in ascending order.
False
When a query references a view, the query in the view is processed, and the results are treated as a(n) permanent table.
True
When a self-join is created, each copy of the table must be assigned a table alias.
True
When a sequence is created for internal control purposes, the values should not be cached otherwise gaps may appear in the sequence.
True
When a subquery is used in the INSERT command, the subquery does not have to be enclosed in parentheses.
True
When a view includes columns from more than one table, updates can only be applied to the table that includes the primary key for the view.
True
When combining the results of two SELECT statements with the MINUS keyword, duplicate rows are suppressed in the results.
True
When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results.
the same case used in the INSERT command
When data is being entered into a VARCHAR2 or CHAR column using the INSERT command, the data will be stored in ____.
True
When data is being entered into a VARCHAR2 or CHAR column using the INSERT command, the data will be stored in ____.
False
When data is sorted in ascending order, NULL values appear first in the list.
( )
When defining columns for a table in the CREATE TABLE command, the column list must be enclosed in which of the following symbols?
When the user executes COMMIT;
When does a COMMIT command explicitly occur?
When the user issues a DDL command such as CREATE or ALTER TABLE.
When does a COMMIT command implicitly occur?
When the user issues a DDL command such as CREATE or ALTER TABLE.
When does a COMMIT command implicitly occur?
True
When functions are nested, the inner function is solved first. _
True
When group functions are nested, the inner function is always resolved first.
False
When listing more than one column name in the SELECT clause of a SELECT statement, the column names must be separated by semicolons . _________________________
VALUES
When new rows are being added to a table, the actual data being added are listed in the ____ clause.
Values
When new rows are being added to a table, the actual data being added are listed in the ____ clause.
single
When non-numeric data is being added to a table, the data must be enclosed in ____________________ quotation marks.
Primary sort
When only one column is specified in the ORDER BY clause, data is ordered, or sorted, based on the data organization within the specified column.
False
When solving arithmetic expressions, Oracle 12c always resolves addition and subtraction operations first from left to right in the expression. _________________________
numeric
When sorting the results in descending order, which of the following values will be presented last in the output?
False
When the SELECT statement includes both WHERE and GROUP BY clauses, the GROUP BY clause is always evaluated first.
arithmetic operations
When the WHERE clause contains multiple types of operators, which of the following is resolved first?
arithmetic operations
When the WHERE clause contains multiple types of operators, which of the following is resolved first?
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
When two conditions are joined by the AND logical operator, both of the conditions must be evaluated as FALSE to be included in the query results.
True
When two conditions are joined by the AND logical operator, both of the conditions must be evaluated as TRUE to be included in the query results.
False
When two conditions are joined by the AND logical operator, only one of the conditions must be TRUE to be included in the query results.
False
When two conditions are joined by the AND logical operator, only one of the conditions must be TRUE to be included in the query results.
False
When two conditions are joined by the OR keyword, both conditions must be TRUE for a record to be included in the results.
Secondary sort
When two or more columns are specified in the ORDER BY clause, data in the second column (or additional columns) provide an alternative field on which to order data if an exact match occurs between two or more rows in the first, or primary, sort.
True
When used with a multiple-row subquery, the IN operator indicates that the records processed by the outer query must match one of the values returned by the subquery.
False
When using a search pattern, a(n) ? symbol is used to indicate exactly one character in that position. _________________________
in the data dictionary
Where does the Oracle 12c server store information about objects in the database, including information about constraints?
INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);
Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
SELECT * FROM orders CROSS JOIN customers;
Which SQL statement will return the same results as the following SQL statement? SELECT * FROM orders, customers;
Having
Which clause is used when the group results of a subquery need to be restricted, based on some condition?
DISABLE
Which clause will allow you to disable a constraint?
DESCRIBE
Which command can be used to determine whether or not a column is allowed to contain a NULL value?
CREATE SYNONYM synonymname FOR tablename;
Which command will create a synonym for a table?
CREATE INDEX indexname ON tablename (columnname)
Which command will create an index?
DROP SEQUENCE sequencename
Which command will delete a sequence?
DROP VIEW
Which command will delete a view?
Is NULL
Which comparison operator allows you to search for NULL values in a subquery?
WITH READ ONLY
Which constraint ensures that the data in a view cannot be changed?
Commit
Which keyword permanently saves changed data in a table?
DROP UNUSED COLUMNS
Which keywords are used to complete the deletion of a column previously marked with SET UNUSED?
SELECT...FOR UPDATE
Which keywords create a shared lock on a table to prevent another user from making changes to data in specified columns?
SELECT...FOR UPDATE
Which keywords create a shared lock on a table to prevent another user from making changes to data in specified columns?
Foreign Key
Which keywords identify a column that, if it contains a value, it must match data contained in another table?
FOREIGN KEY
Which keywords identify a column that, if it contains a value, it must match data contained in another table?
CREATE TABLE promotion (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2));
Which of the following SQL statements was most likely used to create the PROMOTION table?
DROP SYNONYM mysynon;
Which of the following SQL statements will delete the private synonym named MYSYNON?
SELECT customer# FROM customers MINUS SELECT customer# FROM orders;
Which of the following SQL statements will display all customers who have not recently placed an order?
SELECT title FROM books MINUS SELECT title FROM books NATURAL JOIN orderitems;
Which of the following SQL statements will display books that have not been ordered recently?
SELECT gift FROM promotion, orderitems oi, books b WHERE retail BETWEEN minretail AND maxretail AND oi.isbn = b.isbn AND order# = 1003;
Which of the following SQL statements will display the gift or gifts that should be sent with order# 1003?
SELECT gift FROM promotion, books WHERE retail BETWEEN minretail AND maxretail AND title = 'SHORTEST POEMS';
Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled SHORTEST POEMS?
SELECT UNIQUE name FROM books NATURAL JOIN publisher WHERE category = 'COMPUTER';
Which of the following SQL statements will display the name of each publisher that publishes a book classified in the COMPUTER category?
SELECT lastname, firstname FROM customers c, books b, orders o, orderitems oi WHERE c.customer# = o.customer# AND o.order# = oi.order# AND oi.isbn = b.isbn AND title LIKE '%BUSI%';
Which of the following SQL statements will display the names of all customers who have purchased a copy of E-BUSINESS THE EASY WAY?
SELECT title, retail-cost FROM books;
Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table?
SELECT title FROM books NATURAL JOIN orderitems WHERE qty > 1;
Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?
SELECT title FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books USING (isbn) WHERE customer# = 1003;
Which of the following SQL statements will display the title of the books ordered by customer# 1003?
SELECT lastname, firstname, order# FROM customers c LEFT OUTER JOIN orders o ON c.customer# = o.customer#;
Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?
SELECT lastname, firstname, order# FROM customers, orders WHERE orders.customer# (+) = customers.customer#;
Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?
none of the above
Which of the following SQL statements will return an error message, based upon the contents of the CUSTOMERS table?
SELECT lastname, firstname FROM customers NATURAL JOIN orders WHERE orderdate = '12-APR-03';
Which of the following SQL statements will return the names of all customers who placed an order on April 12, 2003?
synonym
Which of the following can be used to provide an alternative, simplified name, to identify database objects?
DROP INDEX
Which of the following can be used to remove an index that currently exists on the column of a database table?
all of the above
Which of the following can be used with date columns?
AVG
Which of the following cannot be used with date columns?
Order by
Which of the following clauses is used to indicate a particular sort sequence for presenting query results?
CREATE SEQUENCE customers_customer#;
Which of the following commands can be used to create a sequence for the CUSTOMERS table?
none of the above
Which of the following commands can be used to modify an index?
UPDATE
Which of the following commands is used to add data to an existing row in a table?
Insert
Which of the following commands is used to add rows to a table?
insert
Which of the following commands is used to add rows to a table?
UPDATE
Which of the following commands is used to modify existing data in a table?
ALTER TABLE promotion ADD CONSTRAINT orderitems_minretail_uk UNIQUE (minretail);
Which of the following commands will add a UNIQUE constraint to the MINRETAIL column of the PROMOTION table?
DELETE FROM publisher WHERE pubid = 4;
Which of the following commands will delete only publisher 4 from the PUBLISHER table?
ALTER TABLE tablea DROP UNUSED COLUMNS;
Which of the following commands will drop any columns marked as unused from the TABLEA table?
INCREMENT BY
Which of the following contains the ROWID of referenced rows?
inline view
Which of the following describes a subquery used in a FROM clause to create a "temporary" table that can be referenced by the SELECT and WHERE clauses of the outer query?
inline view
Which of the following describes a subquery used in a FROM clause to create a "temporary" table that can be referenced by the SELECT and WHERE clauses of the outer query?
DAY
Which of the following format argument elements indicates that the name of the day of the week for the specified date should be displayed?
SSSS
Which of the following format argument elements will display the number of seconds past midnight?
0009999
Which of the following format elements will display insignificant leading zeros?
D
Which of the following format elements will display the value of Friday in a specified date as a 6?
ADD_MONTHS
Which of the following functions can be used to determine the date one month after a specified date?
NEXT_DAY
Which of the following functions can be used to determine the next occurrence of a specific day of the week after a given date?
Decode
Which of the following functions can be used to specify a list of alternative actions that should be taken depending upon a stated value?
NVL
Which of the following functions can be used to substitute another value for a NULL value during calculations?
CONCAT
Which of the following functions is similar to using the (| |) symbol to combine the contents of two character strings?
MONTHS_BETWEEN
Which of the following functions is used to determine the number of months between two date values?
none of the above
Which of the following functions will convert the first letter of each word to an upper-case letter and the remaining letters of the word to lower-case letters?
ROUND(34.21, 0)
Which of the following functions will round the numeric data to no decimal places?
A PUBLIC synonym can be referenced by any user.
Which of the following is a correct statement?
From
Which of the following is a mandatory clause in a SELECT statement?
none of the above
Which of the following is a valid SELECT statement?
SELECT order#, orderdate, shipdate FROM orders;
Which of the following is a valid SQL statement to retrieve data from the ORDERS table?
none of the above
Which of the following is a valid SQL statement when referencing a sequence?
SELECT SUM(quantity*retail) "Total Sales" FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems.ISBN = books.ISBN WHERE orderdate = '02-APR-03';
Which of the following is a valid SQL statement?
SELECT c.customer#, order#, orderdate, shipdate FROM customers c, orders o WHERE c.customer# = o.customer#;
Which of the following is a valid SQL statement?
all of the above
Which of the following is a valid comparison operator?
all of the above
Which of the following is a valid logical operator?
Column aliases cannot be used in a GROUP BY clause.
Which of the following is a valid statement?
When rows are added to a table, the column names can be omitted if the values are listed in the same order as the columns are listed in the table.
Which of the following is a valid statement?
When the LOWER function is used in a SELECT clause, the function only stays in effect for the duration of that SQL statement.
Which of the following is an accurate statement?
When the LOWER function is used in a SELECT clause, the function only stays in effect for the duration of that SQL statement.
Which of the following is an accurate statement?
a person's hair color
Which of the following is an example of an attribute?
FROM orders o, customers c
Which of the following is an example of assigning "o" as a table alias for the ORDERS table in the FROM clause?
object
Which of the following is defined in Oracle12 c as simply anything that has a name and a defined structure?
reference
Which of the following is not a constraint type in Oracle 12c?
INSERT INTO acctmantable AS (SELECT amid, amname, amedate, region FROM acctmanager WHERE amedate <= SYSDATE);
Which of the following is not a valid SQL command?
INSERT INTO acctmantable AS (SELECT amid, amname, amedate, region FROM acctmanager WHERE amedate <= SYSDATE);
Which of the following is not a valid SQL command?
fk
Which of the following is the standard abbreviation for the constraint FOREIGN KEY?
nn
Which of the following is the standard abbreviation for the constraint NOT NULL?
outer join operator (+)
Which of the following is used to create an outer join in a WHERE clause?
SUBSTR
Which of the following is used to return a portion of a character string?
Substr
Which of the following is used to return a portion of a character string?
NEXTVAL
Which of the following keywords is used to actually generate a sequence value?
NEXTVAL .
Which of the following keywords is used to actually generate a sequence value?
all of the above
Which of the following keywords is used to create an equality join?
ON DELETE CASCADE
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?
ON DELETE CASCADE
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?
Line 5
Which of the following lines of the SQL statement contains an error? 1 SELECT title, MAX(retail) 2 FROM books 3 WHERE retail > 30 4 AND pubid = 1 5 GROUP BY retail;
Line 2
Which of the following lines of the SQL statement contains an error? 1 SELECT title, MAX(retail) 2 FROM books 3 WHERE retail > 30 4 AND pubid = 1 5 GROUP BY retail;
unnormalized data
Which of the following may contain repeating groups of data?
( )
Which of the following must be used to separate a subquery from the outer query?
AND
Which of the following operators can be used to combine search conditions?
Like
Which of the following operators is used when the criteria is based upon a search pattern?
all of the above
Which of the following operators is used with a multiple-row subquery?
WITH READ ONLY
Which of the following options will prevent any DML operations from being performed on the underlying table of a view?
SELECT which specifically references the hidden column.
Which of the following queries will allow hidden columns to be displayed?
all of the above
Which of the following queries will display data from both the ORDERS and CUSTOMERS tables?
both a and b
Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?
SELECT customer#, lastname, firstname, order# FROM customers JOIN orders USING (customer#);
Which of the following queries will return the same results as the following SQL statement? SELECT c.customer#, lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#;
both a and b
Which of the following queries will return the same results as the following SQL statement? SELECT c.customer#, lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#;
single-row function
Which of the following returns one row of results for each record processed?
Column names can contain an ampersand (&)
Which of the following rules does not apply to column names in Oracle 12c?
ColB IS NOT NULL
Which of the following search conditions can be used to identify records that have data stored in a column named ColB?
%03
Which of the following search patterns would find dates occurring only in the year 2003?
Start with
Which of the following sequence settings cannot be changed with the ALTER SEQUENCE command?
INTERSECT
Which of the following set operators can be used to make certain that only the rows returned by both queries are displayed in the results?
It can be NULL, as long as the FOREIGN KEY contains a value.
Which of the following statements about a PRIMARY KEY is incorrect?
All DML operations can be performed on complex views, just like simple views.
Which of the following statements about complex views is incorrect?
If you do not provide a name for a constraint, the Oracle 12c server will issue an error message.
Which of the following statements about creating constraints is incorrect?
Values cannot be inserted into columns that are based on arithmetic expressions.
Which of the following statements about performing DML operations on complex views is correct?
DML operations cannot be performed on a non key-preserved table.
Which of the following statements about performing DML operations on complex views is correct?
The command SET VERIFY OFF will delete all values stored in substitution variables.
Which of the following statements about substitution variables is incorrect?
The command SET VERIFY OFF will delete all values stored in substitution variables.
Which of the following statements about substitution variables is incorrect?
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.
Which of the following statements about the FOREIGN KEY constraint is incorrect?
Views are database objects that actually store data.
Which of the following statements about views is incorrect?
DESCRIBE ORDERS
Which of the following statements can be used to view the structure of the ORDERS table?
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 statements is correct?
Views can be modified by using the ALTER VIEW...MODIFY command.
Which of the following statements is incorrect?
SELECT customer#, city, REPLACE(state, 'FL', 'FLORIDA') FROM customersWHERE state = 'FL';
Which of the following statements will display the value of FL assigned to the state column as FLORIDA?
SELECT customer#, city, SUBSTR(state, 'FL', 'FLORIDA') FROM customersWHERE state = 'FL';
Which of the following statements will display the value of FL assigned to the state column as FLORIDA?
multiple-row subquery
Which of the following subqueries returns more than one row of results to the outer query?
#
Which of the following symbols can be used in a column name?
percent sign (%)
Which of the following symbols is a wildcard character that can represent any number of characters in a WHERE clause?
*
Which of the following symbols represents all the columns contained in a table?
character
Which of the following terms is considered the basic unit of data in a database?
common column
Which of the following terms refers to a column with equivalent data that exists in two or more tables?
correlated subquery
Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?
Character, data column, numeric date
Which of the following types of columns will automatically left-align the column heading when it is displayed?
numeric
Which of the following types of columns will automatically right-align the data when it is displayed?
FOREIGN KEY
Which of the following types of constraints is used to enforce referential integrity?
equality join
Which of the following types of joins is created by matching equivalent values in each table?
self-join
Which of the following types of joins refers to joining a table to itself?
Cartesian join
Which of the following types of joins refers to results consisting of each row from the first table being replicated from every row in the second table?
shared lcok
Which of the following types of locks permits other users access to unlocked portions of a table?
simple view
Which of the following types of views cannot include an arithmetic expression?
simple view
Which of the following types of views cannot include an arithmetic expression?
foreign key
Which of the following usually correlates to a primary key in another table?
all of the above
Which of the following will allow a user to enter a NULL value using the INSERT command?
SELECT title, cost*1.20 "New Retail Price" FROM books;
Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
SELECT title, pubdate, name FROM publisher JOIN books USING (pubid) WHERE category = 'BUSINESS';
Which of the following will display the title, publication date, and publisher name of each book in the BUSINESS category?
<ANY
Which operator will instruct Oracle12c to list all records with a value that is less than the highest value returned by the subquery?
>ALL
Which operator will instruct Oracle12c to list all records with a value that is more than the highest value returned by the subquery?
ORDER | NOORDER
Which options are used in application cluster environments where multiple users may be requesting sequence values at the same time?
DML operations can only be performed on the columns belonging to the BOOKS table.
Which statement about the view created from the following SQL command is correct, assuming ISBN from the BOOKS table is the primary key used by the view? CREATE OR REPLACE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit, name FROM books NATURAL JOIN publisher;
No DML operations can be performed through the view.
Which statement about the view created from the following SQL statement is correct? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
For every occurrence of A, there can only be one occurrence of B.
Which statement best describes the relationship shown in Figure 1?
all of the above
Which statement is true about the view created with the following command?
A database object named INVENTORY may already exist.
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
A database object named INVENTORY may already exist.
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
The shipping date of an order cannot be changed through the view.
Which statement is true about the view created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
simple
Which type of view is created by the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
complex
Which type of view is created from the following command? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
simple
Which type of view is created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
simple
Which type of view is created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
USER_CONSTRAINTS
Which view will display the names of all the constraints that you own?
DML
With a MERGE statement, a series of ____ actions can occur with a single SQL statement.
With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.
With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.
uncorrelated
With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.
correlated
With a(n) ____________________ subquery, the subquery is executed once for each row in the outer query.
False
You can determine the number of characters in a column by using the COUNT function.
False
You can determine the number of characters in a column by using the COUNT function. _________________________
parentheses
You can include ____________________ to override the order of operations when solving arithmetic expressions.
True
You can include multiple subqueries in a SELECT statement
True
You can include multiple subqueries in a SELECT statement.
False
You cannot create a view if the underlying table does not exist at the time the view is created.
True
You cannot delete the last column in a table.
True
You must include the MI element in the format argument of a TO_CHAR function to include the minutes when displaying time.
True
You must include the MI element in the format argument of a TO_CHAR function to include the minutes when displaying time.
Data warehouse
____ databases reflect the ever-growing demand for greater scope and depth in the data on which decision support systems increasingly rely.
Denormalization
____ yields better performance.
Wildcard
___________________ characters are used to represent unknown characters in a search pattern.
Top-N
____________________ analysis could be used to find the top five values stored in a column.
Set
____________________ operators are used to combine the results of multiple queries.
Having
f a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
inner
had matching values in each table, rather than rows being matched with NULL values.
MONTHS_BETWEEN
he ____________________ function is used to determine the number of months between two dates.
False
out of 10 points The variable name of a substitution variable is preceded by a(n) percent sign.
True
A view can be created to simplify issuing complex SQL queries.
sequence
A(n) ____________________ can be used for internal control purposes by generating a series of numbers.
multiple-row
A(n) ____________________ subquery is a nested query that returns more than one row of results.
non key-preserved
A(n) ____________________ table is a table that does not contain the primary key being used by a complex view to uniquely identify each row displayed by the view.
inline
A(n) ____________________ view only exists while the SELECT command is being executed.
ORDER BY
An inline view for "TOP-N" analysis is created by sorting data in descending order through the use of the ____________________ clause in the subquery.
Order by
An inline view for "TOP-N" analysis is created by sorting data in descending order through the use of the ____________________ clause in the subquery.
Line 3
Based on the contents of the BOOKS table, which line in the following SQL statement contains an error? 1 SELECT title 2 FROM books 3 WHERE pubid EXISTS IN 4 (SELECT pubid 5 FROM books 6 WHERE retail > 41.95);
SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);
Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?
SELECT customer# FROM customers WHERE NVL(referred, 0) = (SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
Based on the contents of the CUSTOMERS table, which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?
SELECT title, retail+retail FROM books;
Based upon the contents of the BOOKS table, which of the following SQL statements will display the retail price for two copies of each book currently in inventory?
When searching for all customers who live in the same state as customer# 1007. >
Based upon the contents of the CUSTOMERS table, which of the following would be the most appropriate use of a subquery?
SELECT order#, TO_CHAR(shipdate, 'Month DD, YYYY')FROM orders;
Based upon the contents of the ORDERS table, which of the following SQL statements will display the ship date for order 1006 as April 2, 2002?
SELECT contact, nameFROM publisher;
Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?
False
By default, a column alias is displayed in lower case characters in the results.
True
Choosing specific columns from a database through a SELECT statement is known as projection .
False
Choosing specific columns to be displayed by a SELECT statement is known as selection.
ROWNUM
DML operations are not allowed on views that include the pseudo column ____________________
False
Data stored in multiple tables can be combined through the use of an ORDER BY clause.
false
Data stored in multiple tables can be combined through the use of an ORDER BY clause.
True
If a column alias contains a blank space, it must be enclosed in double quotation marks. _________________________
True
If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.
Having
If a subquery's result must be compared with a group function, you must nest the inner query in the ____________________ clause of the outer query.
True
If multiple column names are listed in the SELECT clause of a SELECT statement, the columns must be separated by a comma (,).
True
Parentheses can be used to override the order of operations in an arithmetic expression.
group
Rows cannot be added through a complex view that is based on ____________________ data
numeric and date
The TO_CHAR function can be used to format what types of data?
numeric and date
The TO_CHAR function can be used to format what types of data?
False
The USER_ALL data dictionary view displays information about all the relevant objects owned by the user.
cross
The ____________________ JOIN keyword can be used to create a Cartesian join.
Describe
The ____________________ command is used to display the structure of a table.
WITH CHECK OPTION
The ____________________ keywords are used to make certain any DML operations do not prevent a row from being accessible to the view after the DML operation is executed.
WITH READ ONLY
The ____________________ keywords can be used to ensure data cannot be changed through the view.
Minus
The ____________________ set operator is used to display the results that were returned by the first query that were not also returned by the second query.
INTERSECT
The ____________________ set operator is used to display the rows returned by both SELECT statements.
intersect
The ____________________ set operator is used to display the rows returned by both SELECT statements.
False
The columns displayed in the results of a SELECT statement will be in the same order as they are stored in the database table.
uncorrelated
The following SQL statement contains which type of subquery? SELECT title, retail, category, cataverage FROM books NATURAL JOIN (SELECT category, AVG(retail) cataverage FROM books GROUP BY category);
True
The highest possible value for a sequence can be specified through the MAXVALUE clause.
False
The only required clauses for a SELECT statement are the WHERE and FROM clauses. _________________________
outer
The results of a subquery are passed back to the ____________________ query.
False
To find rows containing a NULL value in a specified column, you must use the search condition of = NULL.
IN
Valid multiple-row operators include ANY, ALL, and ____________________.
none of the above
What is the maximum width that can be assigned to a DATE column?
single quotation marks
When a format argument is included in a function, it must be enclosed in ____.
primary key
When a view includes columns from more than one table, updates can only be applied to the table that contains the ____________________ for the view.
False, separated by a comma
When listing more than one column name in the SELECT clause of a SELECT statement, the column names must be separated by semicolons
SELECT name, title, retail FROM books NATURAL JOIN publisher WHERE cost > 35.95;
Which of the following SQL statements will display the publisher name, book title, and retail price of all books that cost more than $35.95?
SELECT title, nameFROM publisher NATURAL JOIN books;
Which of the following SQL statements will display the title of each book in the BOOKS table and the name of its publisher?
SELECT lastname, firstname FROM customers NATURAL JOIN orders WHERE orderdate = '12-APR-03';
Which of the following SQL statements will return the names of all customers who placed an order on April 12, 2003?
CHAR(n)
Which of the following datatypes refers to fixed-length character data, where n represents the maximum length of the column?
DY
Which of the following format elements can be used to display the three-letter abbreviation for Wednesday?
SP
Which of the following format model elements can be used to spell out a number?
NVL
Which of the following functions can be used to substitute another value for a NULL value during calculations?
correlated subquery
Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?
CACHE
Which option is used to have Oracle12c pre-generate a set of values and store those values in the server's memory?
merge
With a(n) ____________________ statement, a series of DML actions can occur with a single SQL statement.
Top-N Correct Answer:
____________________ analysis could be used to find the top five values stored in a column.
Function-based index
— Can be used when a query is based on a calculated value or a function.
Outer join
— Links data in tables that do not have equivalent rows. An outer join can be created in either the WHERE clause with an outer join operator (+) or by using the OUTER JOIN keywords.
Self-join
— Links data within a table to other data within the same table. A selfjoin can be created with a WHERE clause or by using the JOIN keyword with the ON clause
Private synonym
—An alias used by an individual to reference objects owned by that individual.
False
"TOP-N" analysis can be used to find the highest values in a column by sorting the data in ascending order.
True
"TOP-N" analysis is performed by sorting values in ascending or descending order in an inline view.
C
0 out of 10 points In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a NOT NULL constraint.
False
10 points LOWER, UPPER, and INITCAP functions are all character manipulation functions.
True
A Bitmap index is useful for improving queries on columns that have low cardinality.
True
A Cartesian join can be created by not including a joining condition in the WHERE clause of a SELECT statement. _
True
A Cartesian join usually results from the user omitting the joining condition that links two or more tables together.
Seven
A DATE column consists of ____________________ bytes.
False
A DEFAULT sequence value will override any value provided for the column in an INSERT statement.
True
A Julian date is the number of days that have passed between a specific date and January 1, 4712 B.C.
False
A Julian date represents the number of hours that have passed since January 1, 4712 B.C.
upsert
A MERGE statement containing an UPDATE and an INSERT clause is also called a(n) ____________________ statement.
MODIFY
A NOT NULL constraint can be added to an existing table using the ALTER TABLE command with the ____________________ clause.
False
A NOT NULL constraint can only be created at the table level.
False
A NOT NULL constraint is a special FOREIGN KEY constraint.
True
A NULL value can be included in the data being added to a table by explicitly entering the word NULL.
Add
A PRIMARY KEY constraint can be added to an existing table by using the ____ clause of the ALTER TABLE command.
A SELECT statement cannot include both a GROUP BY and an ORDER BY clause.
A SELECT statement cannot include both a GROUP BY and an ORDER BY clause.
query
A SELECT statement is also referred to as a ____________________.
True
A UNIQUE constraint is the same as a PRIMARY KEY constraint, except that it will accept NULL values
repeating group
A ____ derives its name from the fact that a group of multiple entries of the same type can exist for any single key attribute occurrence.
transitive dependency
A ____ exists when there are functional dependencies such that Y is functionally dependent on X and Z is functionally dependent on Y, and X is the primary key.
database
A ____ is a storage structure designed to hold a collection of data.
semicolon
A ____________________ is used to end a SQL statement.
True
A basic CREATE INDEX command will create a B-Tree index.
True
A clause is a section within a SQL statement that begins with a keyword.
True
A collection of records is a file.
qualifier
A column ____________________ indicates the table containing the column being referenced.
False
A column alias that has been defined in the SELECT clause of a SELECT statement cannot be referenced in an ORDER BY clause.
False
A column heading that is longer than the width of a column will not be truncated for a column that is defined to store character data.
False
A column heading that is longer than the width of a column will not be truncated for a column that is defined to store character data. _________________________
False
A column name can consist of up to 225 characters.
False
A column qualifier indicates the column containing the data being referenced.
False
A column qualifier is separated from the column name with a colon.
period
A column qualifier is separated from the column using which symbol?
True
A column qualifier is used to indicate the table containing the column being referenced.
true
A column qualifier is used to indicate the table containing the column being referenced.
True
A column represents a field in the physical database table.
Common column
A column that exists in two or more tables and contains equivalent data.
data type
A column's ____________________ identifies the type of data that can be stored in a column.
subquery
A complete query nested inside another query is called a(n) ____
True
A complex view can retrieve data from more than one table.
False
A constraint can be added to a table after the table has been populated with data even if the existing data violates the constraint.
False
A constraint can only be created as part of the CREATE TABLE command
column
A constraint is created at the ____________________ level if it is included as part of the column definition.
False
A constraint name can consist of up to 10 characters.
False
A constraint name can consist of up to 25 characters and numbers.
True
A correlated subquery is a subquery that is executed once for each row in the outer query.
True
A correlated subquery references one or more columns in the outer query, and the EXISTS operator is used to test whether the relationship or link is present.
True
A database index allows users and application programs to quickly locate specific records.
True
A database index allows users and application programs to quickly locate specific records.
False
A database must be shut down before the ALTER TABLE command can be executed.
True
A database object is a defined, self-contained structure in Oracle 12c.
Sequence
A database object that generates sequential integers that can be used for an organization's internal controls. A sequence can also serve as a primary key for a table
True
A deadlock occurs when two users hold shared locks on portions of a table that are needed to complete the transaction of the other user.
Database object
A defined, self-contained structure in Oracle 11g. Database objects include tables, sequences, indexes, and synonyms.
partial dependency
A dependency based on only a part of a composite primary key is called a(n) ____________________.
False
A dependency of one nonprime attribute on another nonprime attribute is a partial dependency.
column
A field in the logical design of a database corresponds to a ____ in the physical database.
False
A field in the logical design of a database corresponds to a row in the physical table of a relational database.
False
A field is a basic unit of data also referred to as a record.
characters
A field is a group of related ____________________.
column
A field is referred to as a(n) ____________________ in the physical database.
records
A file is composed of a group of related ____________________.
table
A file is referred to as a(n) ____________________ in an Oracle 12c database.
False
A foreign key constraint can only be created at the column level.
False
A foreign key uniquely identifies each row in a table.
False
A full outer join can be created by including an outer join operator on both sides of the linking condition stated in the WHERE clause.
false
A full outer join can be created by including an outer join operator on both sides of the linking condition stated in the WHERE clause.
Where
A full outer join cannot be created in the ____________________ clause.
all of the above
A function-based index can be created based on which of the following?
True
A function-based index can be used for queries that include searches based upon arithmetic expressions or functions.
False
A good relational DBMS excels at managing denormalized relations.
A group function can be nested inside a(n)____.
A group function can be nested inside a(n)____.
True
A group function can be used in a(n) inline view.
Application cluster environment
A high-volume work environment in which multiple users simultaneously request data from a database
inner
A join that is based upon data having equivalent data in common columns is known as a(n) ____________________ join.
True
A many-to-many relationship cannot exist in a relational database.
one
A maximum of ____________________ PRIMARY KEY constraints can exist for each database table.
False
A multiple-column subquery cannot be nested in a WHERE clause.
True
A multiple-row subquery can be nested in a HAVING clause.
INCREMENT BY
A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.
False
A one-to-many relationship cannot be included in a relational database.
Condition
A portion of an SQL statement that identifies what must exist, or a requirement that must be met. When a query is executed, any record meeting the given condition will be returned in query results.
True
A primary key is usually given the abbreviation _pk in the constraint name if the name is assigned by the user.
true
A primary key is usually given the abbreviation _pk in the constraint name if the name is assigned by the user.
True
A query can be executed in the SQL*Plus tool.
True
A query is accomplished by issuing a SELECT statement.
row
A record in the logical design of a database corresponds to a ____ in the physical database.
many
A referential integrity constraint is usually placed on the ____________________ side of a one-to-many relationship.
Equality operator
A search condition that evaluates data for exact, or equal, values. The equality operator symbol is the equal sign (=)
Comparison operator
A search condition that indicates how data should relate to a given search value (equal to, greater than, less than, etc.). Common comparison operators include >, <, >=, and <= .
ON
A self-join can only be specified in the FROM clause with the use of the JOIN...____________________ keywords.
Index
A separate database object that stores frequently referenced values so they can be quickly located. An index can either be created implicitly by Oracle 11g or explicitly by a user.
True
A sequence can be deleted from the database using the DROP SEQUENCE command.
False
A sequence can be used to speed up row retrieval.
False
A sequence can be used to speed up row retrieval.
False
A sequence serves as a nickname for a database object.
True
A sequence value may be set as a DEFAULT value for a column.
True
A shared lock is automatically obtained when the SELECT...FOR UPDATE command is executed.
False
A shared lock prevents another user from performing DDL or DML operations on the table.
True
A single-row function can be used with the DUAL table.
False
A single-row subquery can return several columns, but only one row, of results to the outer query.
one to many
A straight line with a crow's foot at one end depicts a(n) ____________________ relationship in an E-R Model.
Single quotation
A string literal must be enclosed in ____________________ marks.
False
A subquery can only be nested in the WHERE or HAVING clause of the outer query.
nested
A subquery is a(n) ____________________ query — one complete query inside another query
False
A subquery is required when the condition for the inner query is based upon an unknown.
True
A subquery is required when the condition for the outer query is based upon an unknown.
parentheses
A subquery must be enclosed in a set of ____________________.
both a and b
A subquery must include a(n) ____ clause.
True
A subquery nested in a SELECT clause cannot contain an ORDER BY clause.
False
A subquery nested in a WHERE clause can only be on the left side of the comparison operator.
True
A subquery, except one in the FROM clause, can't have an ORDER BY clause.
Order by
A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.
ampersand
A substitution variable can be identified by the ____________________ symbol that precedes the variable name.
False
A synonym is an alternate name assigned to database instances .
False
A table alias can be assigned in the FROM clause, even when tables are being joined using the NATURAL JOIN keywords.
Fasle
A table alias can be assigned in the FROM clause, even when tables are being joined using the NATURAL JOIN keywords.
false
A table alias can be assigned in the FROM clause, even when tables are being joined using the NATURAL JOIN keywords.
30
A table alias can consist of a maximum of ____ characters.
has no multivalued dependencies
A table is in 4NF if it is in 3NF and ____.
False
A table is in BCNF if every determinant in the table is a foreign key.
True
A table is in fourth normal form if it is in third normal form and has no independent multivalued dependencies.
True
A table name can be up to 30 characters in length.
underscore
A table name can contain the number sign and ____________________ symbols.
True
A table that has been dropped without the PURGE option can be retrieved using the FLASHBACK TABLE command.
2NF
A table that is in 2NF and contains no transitive dependencies is said to be in ____.
3NF
A table that is in 2NF and contains no transitive dependencies is said to be in ____.
4NF
A table where all attributes are dependent on the primary key and are independent of each other, and no row contains two or more multivalued facts about an entity, is said to be in ____.
BCNF
A table where every determinant is a candidate key is said to be in ____.
Table alias
A temporary name for a table, given in the FROM clause. Table aliases are used to reduce memory requirements or the number of keystrokes needed when specifying a table throughout the SQL statement.
True
A transaction is defined as the set of statements that are committed at one time.
all of the above
A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?
transaction controls
A user who is issuing DML commands can save modified data or undo uncommitted changes by issuing ____ statements.
False
A value must be provided in an INSERT statement for a virtual column.
True
A view can be created to simplify issuing complex SQL queries.
False
A view can be dropped or deleted using the DELETE VIEW command.
True
A view can be thought of as the result of a(n) stored query.
False
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. _________________________
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
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
A(n) UNIQUE constraiToolsnt will allow NULL values to be stored in the designated column.
True
A(n) UPDATE operation can be performed on a complex view that contains an arithmetic operation.
False
A(n) WHERE statement allows a user to retrieve data from a database table.
sequence
A(n) ____ generates sequential integers that can be used by organizations to assist with internal controls or simply to serve as a primary key for a table.
keyword
A(n) ____ has a predefined meaning in Oracle 12c.
substitution variable
A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.
substitution variable
A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.
set operator
A(n) ____ is used to combine the results of two queries.
view
A(n) ____ stores a query and is used to access data in the underlying tables.
multiple-row
A(n) ____ subquery is one that can return several rows of results
multiple-row
A(n) ____ subquery is one that can return several rows of results.
private
A(n) ____ synonym is used by an individual to reference objects owned by that person.
non key-preserved
A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view.
key-preserved
A(n) ____ table is the table that contains the primary key the view uses to uniquely identify each record being displayed by the view.
view
A(n) ____________________ can be used to restrict a user's access to sensitive data.
string literal
A(n) ____________________ can consist of numbers, characters, or special symbols that should be included in the output exactly as entered by the user.
NOT NULL
A(n) ____________________ constraint is a special CHECK constraint with the condition of IS NOT NULL.
unique
A(n) ____________________ constraint requires that, if an entry is made into the column, the data value must be unique.
Julian
A(n) ____________________ date represents the number of days that have passed between a specified date and January 1, 4712, B.C.
foreign key
A(n) ____________________ is a common field between two tables and is also a primary key for one of the tables.
view
A(n) ____________________ is a database object that can be used to access data, but it does not actually store data itself.
database
A(n) ____________________ is a group of interrelated files.
function
A(n) ____________________ is a predefined block of code that accepts one or more arguments and returns a single value as output.
database
A(n) ____________________ is a storage structure that contains data.
alias
A(n) ____________________ is an alternate name temporarily assigned to a table.
Column alias
A(n) ____________________ is an alternative column heading displayed in the output of a query.
self
A(n) ____________________ join is required when a table must be joined to itself.
non-equality
A(n) ____________________ join is used when the related columns between two tables cannot be joined through an equal sign.
shared
A(n) ____________________ lock will allow other users to view the data stored in the table, but it prevents anyone from altering the structure of the table.
shared
A(n) ____________________ lock will allow other users to view the data stored in the table, but it prevents anyone from altering the structure of the table.
negative
A(n) ____________________ number is used in the ROUND function to indicate that the data should be rounded to a position on the left side of the decimal point.
full
A(n) ____________________ outer join is necessary when you need rows returned from either table that do not have a matching record in the other table.
many-to-many
A(n) ____________________ relationship means data can have multiple occurrences in both entities.
asterisk (*)
A(n) ____________________ should be included as the argument for the COUNT function to include NULL values in the results.
secondary
A(n) ____________________ sort can be specified in the ORDER BY clause to indicate that if an exact match occurs during the primary sort, the alternate column listed should be used to determine the presentation sequence.
multiple-row
A(n) ____________________ subquery is a nested query that returns more than one row of results.
multiple-column
A(n) ____________________ subquery returns more than one column to the outer query.
public
A(n) ____________________ synonym is an alternate name that can be referenced by all users.
public
A(n) ____________________ synonym is an alternate name that can be referenced by all users.
key preserved
A(n) ____________________ table is a table that contains the primary key being used by the view to uniquely identify each row displayed by the view.
complex
A(n) ____________________ view can contain group functions.
complex
A(n) ____________________ view can contain grouped data.
simple
A(n) ____________________ view cannot be created using a GROUP BY clause.
simple
A(n) ____________________ view cannot contain grouped data.
True
A(n) constraint can be created during the creation of a database table or added to a table afterwards.
True
A(n) correlated subquery references one or more columns from the outer query.
False
A(n) exclusive lock is automatically obtained when the user executes the SELECT...FOR UPDATE command.
True
A(n) existing view cannot be modified.
False
A(n) implicit commit is issued when the user enters and executes COMMIT; in SQL*Plus. _________________________
True
A(n) index can be used to quickly determine whether a value already exists in a specific column.
True
A(n) inner join will only include rows that have matching rows in the other table.
false
A(n) non-equality join is also known as an equijoin, inner join, or simple join.
True
A(n) non-equality join is used when there are no equivalent rows of data in the tables that are being joined.
False
A(n) outer join can be created by not including a joining condition in a SELECT statement.
False
A(n) outer join can be created by not including a joining condition in a SELECT statement. _________________________
False
A(n) outer query is also referred to as a parent query.
True
A(n) outer query is also referred to as a parent query.
True
A(n) sequence can be used to generate a series of numeric values.
False
A(n) shared lock prevents other users from obtaining another shared lock on the same table. _________________________
False
A(n) uncorrelated subquery is when the outer query is executed first, then the inner query is executed.
False
A(n) uncorrelated subquery is when the outer query is executed first, then the inner query is executed. _
True
ALTER TABLE promotion DROP UNUSED COLUMNS;
True
After a COMMIT command is executed, the ROLLBACK command will have no affect on the changed data.
False
All synonyms are PUBLIC.
DUAL
Although the ____________________ table is rarely used in the industry, it can be valuable for someone learning how to work with functions or testing new functions.
True
An ON NULL clause may be used in conjunction with a column's DEFAULT option.
inline
An ORDER BY clause can be included in a(n) ____________________ view to perform "TOP-N" analysis.
True
An ORDER BY clause can be used to perform "TOP-N" analysis.
true
An ORDER BY clause can reference a column to be used for sorting based upon its position in the SELECT clause.
False
An ORDER BY clause can reference a column to be used for sorting based upon its position in the database table
Index Organized Table
An _________________________ is a variant of the B-tree index structure and is used as an alternative to the conventional heap-organized table.
Public synonym
An alias that can be used by others to access an individual's database objects
Synonym
An alternative name given to a database object with a complex name. Synonyms can be either private or public.
atomicity atomicity Correct Answer:
An attribute that cannot be further divided is said to display ____________________.
False
An automatic column will generate a value at runtime and may reference values from other columns.
False
An entity is represented by a column in the Entity-Relationship Model.
True
An explicit commit is issued when the user executes a COMMIT; command.
CREATE
An index can be created with the ____________________ INDEX command.
public
An index is implicitly created when a NOT NULL constraint is added to a table.
NOT NULL
An index is not implicitly created when which type of constraint is created?
True
An inline analysis can be used to find the "Top-N" values.
True
An inline view is a temporary table that only lasts for the duration of the SELECT statement used to create it.
subquery
An inline view is created by a SELECT statement that contains a(n) ____________________ in the FROM clause.
False
An inline view is used to create a(n) permanent table.
False
An outer join operator can be included in a FROM clause to list all rows from one table that do not have a corresponding row in the other table.
false
An outer join operator can be included in a FROM clause to list all rows from one table that do not have a corresponding row in the other table.
False
An outer join operator consists of a minus sign enclosed in parentheses, (-).
Column alias
Another name substituted for a column name. A column alias is created in a query and displayed in the results.
False
Any DDL operation can be performed on a simple view that was not created with the WITH READ ONLY option, unless it violates an existing constraint.
false
Any DDL operation can be performed on a simple view that was not created with the WITH READ ONLY option, unless it violates an existing constraint.
True
Any DML operation can be performed on a simple view that was not created with the WITH READ ONLY option, unless it violates an existing constraint.
simple
Any DML operation can be performed on a(n) ____________________ view as long as the view was not created with the WITH READ ONLY option and existing constraints are not violated.
determinant
Any attribute whose value determines other values within a row is called a(n) ____________________.
False
Any constraint can be created at the table or the column level.
Fasle
Any constraint can be created at the table or the column level.
True
Any of the single-row functions covered in this chapter can be used with the DUAL table.
True
Any of the single-row functions covered in this chapter can be used with the DUAL table. _
column
Any type of constraint can be created at the ____________________ level, unless the constraint is being defined for more than one column.
Having
Any type of subquery can be used in the FROM, WHERE, or ____________________ clause of a SELECT statement.
False
Atomic attributes are attributes that can be further divided.
none of the above
Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 WHERE title = 'SHORTEST POEMS') 7 AND retail-cost > 8 (SELECT AVG(retail-cost) 9 FROM books);
none of the above
Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?
SELECT COUNT(*)FROM ordersWHERE shipdate IS NULL;
Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
CREATE INDEX customers_name_idx ON customers (lastname, firstname);
Based on the structure of the CUSTOMERS table, which of the following is a valid SQL statement?
CREATE INDEX customers_name_idx ON customers (lastname, firstname);
Based on the structure of the CUSTOMERS table, which of the following is a valid SQL statement?
ADD FOREIGN KEY (isbn) REFERENCES books(isbn);
Based on the structure of the ORDERITEMS table, which of the following commands will make certain that the ISBN entered actually exists in the ISBN column of the BOOKS table?
ALTER TABLE promotion ADD PRIMARY KEY(gift);
Based on the structure of the PROMOTION table, which of the following commands will add a PRIMARY KEY constraint to the GIFT column?
ALTER TABLE promotion ADD PRIMARY KEY(gift);
Based on the structure of the PROMOTION table, which of the following commands will add a PRIMARY KEY constraint to the GIFT column?
ALTER TABLE promotion ADD CHECK ( minretail < maxretail);
Based on the structure of the PROMOTION table, which of the following commands will ensure that the value entered for the MINRETAIL column is less than the value entered for the MAXRETAIL column?
SELECT * FROM books WHERE pubid = 1;
Based upon the contents of the BOOKS table in the accompanying figure, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?
SELECT DISTINCT pubid FROM books;
Based upon the contents of the BOOKS table, which of the following SQL statements will display five rows in its results?
pubid
Based upon the contents of the BOOKS table, which of the following columns will be displayed with a right-aligned column heading?
SELECT COUNT(pubid)FROM booksWHERE pubid = 3;
Based upon the contents of the BOOKS table, which of the following will determine the number of books provided by publisher 3?
SELECT COUNT(DISTINCT pubid) FROM books;
Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?
SELECT INITCAP(CONCAT(city, CONCAT(', ', CONCAT(state, CONCAT(' ', zip))))) FROM customers;
Based upon the contents of the CUSTOMERS table, which of the following will display the shipping location as: City, State Zip
False
Each section of a statement that begins with a keyword is known as a(n) buffer .
True
Equality, non-equality, and self-joins are all categorized as inner joins.
false
Equality, non-equality, and self-joins are broadly categorized as outer joins.
True
Every table name within a specific schema must be unique.
False
FORCE is the default mode for the CREATE VIEW command.
False
Each column can only be included in one constraint.
False
Each section of a SQL command that begins with a keyword is known as a statement.
False
DML commands are used to create or modify database tables.
ROWNUM
DML operations are not allowed on a view that includes the pseudo column ____.
distinct
DML operations are not allowed on a view that is created with the ____ keyword
False
DML operations can be performed through a simple view even if the operation will violate constraints on the underlying table.
False
DML operations cannot be performed on a key-preserved table.
True
DML operations cannot be performed on non key-preserved tables through a complex view.
transitive dependencies
Data in second normal form (2NF) may contain which of the following?
none of the above
Data in third normal form (3NF) contains which of the following?
transitive dependencies
Data is in third normal form if it is in second normal form and has no _________________________.
False
Data manipulation language commands are used to create or modify database tables.
analyzing data already stored in a database
Data mining refers to ____.
False
Data redundancy is created through a process known as normalization.
data integrity problems
Data redundancy produces ____.
join conditions
Data stored in separate tables can be reconstructed through the use of ____________________.
True
Data that is being added to a table is specified in the VALUES clause of the INSERT command. _________________________
False
Dependencies that are based on only a part of a composite primary key are called transitive dependencies.
False
Each clause of a SELECT statement must be entered on a new line in SQL*Plus.