IT 314 Final

When adding a column to an existing table, the ____________________ clause must be used with the ALTER TABLE command.

ADD

The ____________________ command can be used to modify the structure of a table.

ALTER TABLE

Functions cannot be used in a(n) ____________________ constraint or as a default value of a table column.

CHECK

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?

Cartesian join

The ____________________ data dictionary view allows us to view the source code of packages.

USER_SOURCE

When two tables that share more than one common column are being joined, the JOIN...____________________ keywords are normally used in the FROM clause to join the tables.

USING

Which of the following column data type changes is permitted?

VARCHAR2 to CHAR

The outer join operator can only be used in the ____________________ clause.

WHERE

The ____ packaged function purity level does not modify packaged variables.

WNPS

A(n) ____________________ is a declaration of a program unit in a package body by placing the header code at the top of the package body code.

forward declaration

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.

full

A join that is based upon data having equivalent data in common columns is known as a(n) ____________________ join.

inner; equality; equijoin; simple

Forcing the user to use his or her own privileges for a program unit, and not that of the owner is called ____.

invoker-rights

Data stored in separate tables can be reconstructed through the use of ____________________.

joins; joins conditions

The DROP UNUSED COLUMNS clause can be used with the ____________________ command to delete any column previously set as unused.

one

To display rows from one table that do not have a corresponding row in the other table, you must create a(n) ____________________ join.

outer

The ____________________ operator is used to create an outer join in the WHERE clause of a SQL statement.

outer join, (+), +

Using the same name on multiple program units within the same package is referred to as ____.

overloading

The ____ is referred to as the package header.

package specification

A(n) ____ is used to combine the results of two queries.

set operator

Deleting columns from a table is a(n) ____ action.

unrestricted

Once a table is ____________________, any associated indexes will be removed from the database.

Dropped

A database must be shut down before the ALTER TABLE command can be executed.

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(n) outer join can be created by not including a joining condition in a SELECT statement.

False

A(n) program declaration is a declaration of a program unit in a package body by placing the header code at the top of the package body code.

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, (-).

False

DML commands are used to create or modify database tables.

False

Data manipulation language commands are used to create or modify database tables.

False

If you are joining five tables in a SELECT statement, five joining conditions will be required.

False

Package elements are considered public if they can be called only from other program units within the same package.

False

The ALTER TABLE name can be used to rename a table.

False

The CHANGE TABLE command can be used to modify an existing table.

False

The DEL command can be used to remove the package specification and body.

False

The DELETE TABLE command can be used to delete all the data stored in a table and release the storage space, yet retain the structure of the table.

False

The DELETE TABLE command can be used to remove a table from a database.

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 MODIFY TABLE command can be used to change the size of a table.

False

The MODIFY clause can be used with the ALTER TABLE command to add a column to an existing table.

False

The UNION set operator will not suppress rows that are returned by both queries.

False

The outer join operator is used to combine the results of multiple SELECT statements.

False

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

False

When combining the results of two SELECT statements with the MINUS keyword, duplicate rows are suppressed in the results.

False

The ____________________ set operator is used to display the rows returned by both SELECT statements.

INTERSECT

Which of the following keywords can be used to change the size, datatype, and/or default value of an existing column?

MODIFY

A self-join can only be specified in the FROM clause with the use of the JOIN...____________________ keywords.

ON

The ____ directive needs to be included in the package specification to clearly state the purity levels applicable to the function.

PRAGMA RESTRICT_REFERENCES

The ____________________ directive needs to be included in the package specification to clearly state the purity levels applicable to the function.

PRAGMA RESTRICT_REFERENCES

Which of the following correctly sets the default purity level for a packaged function?

PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, WNPS);

Group functions can be nested to a depth of ____.

2

The column definition of NUMBER(7, 4) indicates a precision of ____.

7

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.

90

The ____________________ operator indicates that a value must be less than the highest value returned by a subquery.

<ANY

Which operator will instruct Oracle10 g to list all records with a value that is less than the highest value returned by the subquery?

<ANY

Which operator will instruct Oracle11 g to list all records with a value that is more than the highest value returned by the subquery?

>ALL

Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store the date that the customer first placed an order with the company?

ALTER TABLE customers ADD (firstorderdate DATE);

Which command is used to disable the constraint named faculty_loc_id_fk in the faculty table?

ALTER TABLE faculty DISABLE CONSTRAINT faculty_loc_id_fk;

Which of the following keywords cannot be used to modify an existing table?

ALTER TABLE...AS

The ____________________ keyword must be used with the CREATE TABLE command if the table is being created from a subquery.

AS

Oracle10g stores character data in VARCHAR2 and CHAR columns using ____ encoding.

ASCII

The ____________________ function is used to calculate the average of the numeric values in a specified column.

AVG

Which of the following statements is correct?

An exception that has been declared must be referred to in the RAISE statement, or a PL/SQL error will occur.

The ____________________ function can be used to determine the number of rows that have non-NULL values in a specified column.

COUNT

Which command instructs Oracle11 g to create a new table from existing data?

CREATE TABLE...AS

____________________ are restrictions on the data values that a column can store.

Constraints

Which of the following format argument elements indicates that the name of the day of the week for the specified date should be displayed?

DAY

Rows can be deleted from a table using the ____________________ command.

DELETE

Which of the following commands is used to remove rows from a table?

DELETE

Based on the contents of the PROMOTION table, which of the following commands will delete only the row for the Free Bookmark from the table?

DELETE FROM promotion WHERE gift = 'BOOKMARKER';

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

DELETE FROM publisher WHERE pubid = 4;

______________________________ commands are used to modify data.

DML

The statement ____ would successfully remove the package specification and body.

DROP PACKAGE package_name;

The ____ operator is used to determine whether a condition is present in a subquery.

EXISTS

A(n) ____ is a mechanism to trap an error that occurs in processing.

Exception handler

Which of the following is an example of assigning "o" as a table alias for the ORDERS table in the FROM clause?

FROM orders o, customers c

A NOT NULL constraint is an example of a table constraint.

False

A composite key is created using a value constraint.

False

A correlated subquery is one in which the inner query is executed first, and then the outer query is executed.

False

A cursor variable assigns a name to a work area holding a specific result set, whereas an explicit cursor is simply a pointer to a work area in which a query can be processed.

False

A single-row subquery can return several columns, but only one row, of results to the outer query.

False

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

False

According to the Oracle Naming Standard, '-' (hyphen) is a legal character.

False

Adding the program unit name to the END statement that closes the procedure or function statements in the package body is mandatory.

False

An outer join only lists rows that contain a match in both tables.

False

Explicit cursors are declared automatically for all DML and SELECT statements issued within a PL/SQL block.

False

For overloading to be successful, the formal parameters in the procedures or functions must be the same.

False

Functions are used in CHECK constraints or as a default values of table columns.

False

Functions that have not been declared in a package specification are considered public.

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.

False

If a group function is used in the WHERE clause, then any individual columns listed in the SELECT clause must also be listed in the GROUP BY clause.

False

If more than one data value is being added to a table, the values must be separated by parentheses.

False

If the WHERE clause is omitted from the DELETE command, then all columns from the database table will be dropped.

False

If the column S_ID is the primary key of the STUDENT table, the constraint name would be pk_STUDENT_S_ID according to the constraint naming convention.

False

In Oracle11 g, subqueries in a WHERE clause can be nested to a depth of 2.

False

Packages can be created in local libraries, as can procedures and functions.

False

Results returned from a SELECT statement that include a GROUP BY clause will present the results in descending order of the column(s) listed in the GROUP BY clause.

False

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

False

The SOUND function is used to determine the phonetic representation of a character string.

False

The TRUNCATE TABLE command can be used to delete a table from a database.

False

The USER_SOURCE data dictionary view is useful to identify what packages exist on the system.

False

The VALUES clause of the UPDATE command is used to specify the columns being updated and the new data values.

False

The greater than operator, >, is a valid operator for multiple-row subqueries.

False

The only group function that includes NULL values is the NVL function.

False

The pragma level defines what type of data structures the function reads or modifies.

False

To indicate that the numeric value of the day of the year for a date is being specified, include DAY in format argument of the TO_DATE function.

False

Use MON in the format argument to spell out the specified month.

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 including a subquery in an INSERT command, the syntax of enclosing the subquery in parentheses is mandatory.

False

When the INSERT command is used to add rows to a table, the column names of the table must be specified.

False

When the SELECT statement includes both WHERE and GROUP BY clauses, the GROUP BY clause is always evaluated first.

False

You cannot include data type information when declaring a formal parameter.

False

The default size of the CHAR column is 2.

False - Default is 1

The maximum width of a NUMBER column is 25 digits.

False -Max is 38

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.

HAVING

If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement.

HAVING

If a multiple-column subquery is contained in the WHERE or HAVING clause of the outer query, the ____________________ operator must be used.

IN

The ____ operator indicates that the records processed by the outer query must match one of the values returned by the subquery.

IN

To add data to the database, use the ____ command.

INSERT

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?

INTERSECT

If the ____________________ clause of a SELECT statement contains either scalar or record variables and if more than one row is selected, the TOO_MANY_ROWS error is raised.

INTO

Which comparison operator allows you to search for NULL values in a subquery?

IS NULL

Which of the following functions can be used to convert a character string to lower-case letters?

LOWER

To indicate that minutes should be displayed, include ____________________ in the time element format argument.

MI

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.

MINUS

Which of the following functions is used to determine the number of months between two date values?

MONTHS_BETWEEN

The ____________________ keywords create a join automatically between two tables, based on columns with matching names.

NATIRAL JOIN

Which of the following can only be used to link tables that have a common column?

NATURAL JOIN

The ____ constraint specifies whether the user must enter a column for a specific record, or whether the value can be NULL (indeterminate or unknown).

NOT NULL

____ refers to a SELECT statement in a PL/SQL block that retrieves no rows.

NO_DATA_FOUND

If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.

NVL

Which of the following functions allows for different options, depending upon whether a NULL value exists?

NVL2

A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.

ORDER BY

____ can be created only as stored program units.

Packages

____ are values passed into the cursor when opened and used in the SELECT statement of the cursor to determine what value it will contain.

Parameters

The ____________________ function is used to replace a specific set of characters found within a character string with another set of characters.

REPLACE

All of the following represent packaged function purity levels, except ____.

RIPS

The ____________________ function fills the area to the right of a character string with a specific character.

RPD

The ____________________ function returns the phonetic representation for a specified value.

SOUNDEX

Predefined exceptions are declared in the ____________________ package, which makes these globally available on the system.

STANDARD

The ____________________ function is used to calculate the standard deviation for a column.

STDDEV

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.

SYSDATE

____ refers to a SELECT statement in a PL/SQL block that retrieves more than one row.

TOO_MANY_ROWS

Which of the following functions will truncate a numeric value to a specific position?

TRUNC

The ____________________ TABLE command can be used to delete and release the storage space of all data contained in a database table, while still retaining the table's structure.

TRUNCATE

Regarding the INSERT statement, which of the following is correct?

The data values to be inserted are enclosed in parentheses ( ) after the VALUES keyword.

When creating a package body for the package specification above, which of the following statements is incorrect?

The package body cannot include declarations of variables, cursors, types, and program units not found in the corresponding specification.

Which of the following statements is correct?

The value of the SQLCODE is assigned to a variable.

A CHAR column cannot be resized to a width that is smaller than the data it already contains.

True

A Cartesian join can be created by not including a joining condition in the WHERE clause of a SELECT statement.

True

A NULL value can be included in the data being added to a table by explicitly entering the word NULL.

True

A SELECT statement within a PL/SQL block has the possibility of raising the NO_DATA_FOUND exception.

True

A column qualifier is used to indicate the table containing the column being referenced.

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 datatype identifies the type of data to be stored in a column.

True

A package specification can contain declarations for procedures, functions, variables, exceptions, cursors, and types.

True

A primary key is defined using an integrity constraint.

True

A user schema contains all database objects created by a user.

True

A(n) correlated subquery is when the outer query is executed first, and then the inner query is executed.

True

A(n) correlated subquery references one or more columns from the outer query.

True

A(n) package body is the program unit that contains the code for any procedures and functions declared in the specification.

True

By default, the SUM function assumes the ALL keyword and the result will include multiple occurrences of numeric values.

True

Column qualifiers must be included in the WHERE clause if the columns used to join the tables have the same column names.

True

Commands used to modify data are known as DML commands.

True

DDL commands are used to create or modify database objects.

True

Data that is being added to a table is specified in the VALUES clause of the INSERT command.

True

Functions written in other languages such as JAVA can also be called from Oracle programs.

True

If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.

True

If you are joining four tables in a SELECT statement, three joining conditions will be required.

True

In Oracle11 g, 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 PL/SQL, comment text is not executed.

True

It is possible to temporarily disable constraints in an Oracle database.

True

Items declared in the package body and not in the specification can be used only by other procedures and functions within this same package body.

True

More than one column can be changed at a time with the ALTER TABLE...MODIFY command.

True

Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.

True

Packaged variable values are persistent throughout a user session and each user of the package has his or her own copy of the packaged variable.

True

Some cursor variables are references or pointers to a specific work area.

True

Tables can be joined in the FROM clause or the WHERE clause of a SELECT statement.

True

The <ALL operator indicates that for a value to be included in the results, it must be less than the lowest value returned by the subquery.

True

The =ANY operator yields the same results as using the IN multiple-row operator.

True

The AUTHID CURRENT_USER clause is used to override definer-rights behavior.

True

The AVG function only includes non-NULL values in its calculations.

True

The CREATE TABLE command is used to create a table in Oracle11g.

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 DROP TABLE command can be used to remove a table and all its data from a database.

True

The GROUP BY clause is used to group data.

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 can be used with single-row, multiple-row, or multiple-column subqueries.

True

The INTERSECT set operator only displays the rows returned by both queries.

True

The MAX function can be used with character data.

True

The MINUS set operator is used to display rows that were uniquely returned by the first query in the set.

True

The RAISE_APPLICATION_ERROR is an Oracle built-in procedure that allows the developer to associate their own error number and message to an error.

True

The VARCHAR2 data type can store up to 4,000 characters.

True

The WHERE clause of the UPDATE command is used to specify exactly which rows should be changed.

True

The optional DISTINCT keyword instructs Oracle11g to include only unique numeric values in the calculation.

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.

True

The precision of a NUMBER does not include the decimal point itself in the total number of digits that can be stored in a column.

True

The scale of a NUMBER column indicates the number of digits that can be displayed to the right of the decimal point.

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.

True

To check that the value entered into a CHAR column is either 'M' or 'F', you would use a check condition constraint.

True

Using the CHAR data type causes a column value to be padded to the maximum declared size of the column.

True

When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results.

True

When compiling objects that call packaged program units, only the package specification or program unit header is used for verification.

True

When group functions are nested, the inner function is always resolved first.

True

When the subquery is executed first and the value is passed back as input to the outer query, the subquery is known as an uncorrelated subquery.

True

You can process multiple rows of data from a database by creating explicit cursors.

True

You cannot delete the last column in a table.

True

Which of the following set operators will display only the unique results of the combined SQL statements?

UNION

The ____________________ set operator is used to display the combined results returned by multiple SELECT statements.

UNION ALL

Which of the following set operators will display the results of the combined SQL statements without suppressing duplicate rows?

UNION ALL

Because Oracle does not raise an error if a(n) ____________________ does not affect any rows, you must include specific instructions in the block.

UPDATE

The ____________________ command is used to add data to an existing row in a table.

UPDATE

The ____________________ command is used to change data stored in a table.

UPDATE

Which of the following functions can be used to convert a character string to upper-case letters?

UPPER

____ constraints define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL.

Value

The ____ handler should always be the last handler listed in the EXCEPTION section of a block.

WHEN OTHER

____ is used to trap errors not specifically addressed in one of the exception handlers.

WHEN OTHERS

A search condition begins with the keyword ____.

WHERE

The row(s) to be updated by the UPDATE command is specified by the ____ clause.

WHERE

The ____ purity level does not modify database tables.

WNDS

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.

A(n) ____________________ enables you to specify that a column value must be a specific value or fall within a range of values.

check constraints

A(n) ____________________ constraint limits the value that can be placed in a specific column, irrespective of values that exist in other table rows.

column

Each ____________________ name within a table must be unique.

column

If more than one column is listed in the INSERT INTO clause, the column names must be separated by ____.

commas

Which of the following terms refers to a column with equivalent data that exists in two or more tables?

common column

With a(n) ____________________ subquery, the subquery is executed once for each row in the outer query.

correlated

A column's ____________________ identifies the type of data that can be stored in a column.

data type; datatype

A(n) ____ is a statement used to define a data construct, such as a variable, name, and data type.

declaration

The outer join operator is placed on the side of the joining condition that has ____________________ rows.

deficient, missing, NULL

When a user assumes the rights of the program unit owner just during the processing of that program unit, this is referred to as ____.

definer-rights

Which command would be used to delete table x and all foreign key constraints to x?

drop x cascade constraints

A join based upon a column from each table containing equivalent data is known as a(n) ____.

equality join

Which of the following types of joins is created by matching equivalent values in each table?

equality join

A(n) ____ is a declaration of a program unit in a package body by placing the header code at the top of the package body code.

forward declaration

One advantage that packages provide is that constructs, such as variables, cursors, types, and exceptions, declared in the specification are ____________________.

global

If a group function is used in the SELECT clause, any ____ listed in the SELECT clause must also be listed in the GROUP BY clause.

individual columns

A(n) ____________________ constraint defines a primary or foreign key.

integrity

A(n) ____________________ subquery returns more than one column to the outer query.

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 category);

multiple-columns

A(n) ____ subquery is one that can return several rows of results.

multipule-row

A subquery is a(n) ____________________ query — one complete query inside another query.

nested

The results of a subquery are passed back as input to the ____ query.

outer

A column qualifier is separated from the column using which symbol?

period (.)

The ____________________ of a NUMBER column indicates the total number of digits that can be stored in the column.

precision

Package elements are considered ____ if they can be called only from other program units within the same package.

private

Package elements that can be referenced from outside the package are considered ____.

public

The ____ defines the type of data structures the function reads or modifies.

purity level

SQL command words are also known as ____.

reserved words

Adding a check condition constraint to a table is a(n) ____________________ action.

restricted

Which of the following is not part of a DDL command?

select table_name from ...

A(n) ____________________ join is required when a table must be joined to itself.

self

____________________ operators are used to combine the results of multiple queries.

set

When the INSERT command is being used to enter data into a non-numeric column, the data must be enclosed in ____.

single quotation marks

The <= operator is referred to as a(n) ____ operator.

single-row

The = operator is referred to as a(n) ____ operator.

single-row

The > operator is referred to as a(n) ____ operator.

single-row

The operators =, <, >, <=, >=, and <> are referred to as ____ operators.

single-row

A complete query nested inside another query is called a(n) ____.

subquery

A(n) ____________________ is a nested query - one complete query inside another query.

subquery

Which of the following pieces of information is not displayed by SQL*Plus when an error occurs?

suggested correction

A(n) ____________________ constraint restricts the data value with respect to all other values in the table.

table

Which of the following is not required when creating a table?

table size

With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.

uncorrelated

Adding a new column to a database table is a(n) ____________________ action.

unrestricted

If a(n) ____________________ clause is not included in the UPDATE command, then all rows in the table will be changed.

where