BCIS 4620 Exam 2 Review

What type of command does this SQL statement use? SELECT P_CODE, P_DESCRIPT, P_PRICE_V_NAME FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE=VENDOR. V_CODE

"old-style" join

_____ is a cursor attribute that returns TRUE if the last FETCH returned a row, and FALSE if not.

%FOUND

Which comparison operator indicates a value is not equal?

<>

The special operator used to check whether an attribute value is within a range of values ____

BETWEEN

The special operator used to check whether an attribute value is within a range of values is _____.

BETWEEN

The _____ function returns the current system date in MS Access.

DATE()

The PL/SQL block starts with the ____ section.

DECLARE

The PL/SQL block starts with the _____ section.

DECLARE

The Oracle ____ function compares an an attribute or expression with a series of values and returns an associated value or a default value if no match is found.

DECODE

The Oracle _____ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found.

DECODE

The ____ constraint assigns a value to an attribute when a new row is added to a table.

Default

Comparison operators cannot be used to place restrictions on character-based attributes.

False

Every PL/SQL block must be given a name.

False

Most SQL implementations yield case-insensitive searches.

False

Oracle users can use the Access QBE (query by example) query generator.

False

SQL supports the conditional execution of procedures (IF-THEN-ELSE statements) that are typically supported by a programming language.

False

Stored procedures must have at least one argument.

False

Triggers can only be used to update table values.

False

SQL requires the use of the _____ command to enter data into a table.

INSERT

The special operator used to check whether an attribute value matches a given string pattern is _____.

LIKE

The _____ pseudo-column is used to select the next value from a sequence.

NEXTVAL

In Oracle, _____ make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping.

Procedural Language SQL

A(n) _____ query specifies which data should be retrieved and how it should be filtered, aggregated, and displayed.

SELECT

No matter what language you use, if it contains embedded SQL statements, it is called the _____ language

host

No matter what language you use, if it contains embedded SQL statements, it is called the _____ language.

host

All changes in a table structure are made using the _____ TABLE command, followed by a keyword that produces the specific changes a user wants to make.

ALTER

The _____ constraint is used to validate data when an attribute value is entered.

CHECK

The SQL command that allows a user to permanently save data changes is ____

COMMIT

The SQL command that allows a user to permanently save data changes is _____.

COMMIT

The _____ command permanently saves all changes—such as rows added, attributes modified, and rows deleted—made to any table in the database.

COMMIT

In Oracle, _____ retrieves the current value of a sequence.

CURRVAL

The _____ command defines a default value for a column when no value is given.

DEFAULT

The _____ command restricts the selection of grouped rows based on a condition.

DEFAULT

The _____ constraint assigns a value to an attribute when a new row is added to a table.

DEFAULT

Which command would be used to delete the table row where the P_CODE is 'BRT-345'?

DELETE FROM PRODUCT WHERE P_CODE='BRIT-345'

Which statement describes a feature of Oracle sequences?

Dropping a sequence does not delete values assigned to table attributes; it deletes only the sequence object from the database.

The special operator used to check whether a subquery returns any rows is _____.

EXISTS

A persistent stored module is stored and executed on the database client machine.

False

A row-level trigger is assumed if we omit the FOR EACH ROW keywords and a statement-level trigger required the use of the FOR EACH ROW keyword

False

All SQL commands must be issued on a single line.

False

An alias cannot be used when a table is required to be joined to itself in a recursive query.

False

Automating business procedures and automatically maintaining data integrity and consistency are trivial in a modern business environment.

False

Comparison operators cannot be used to place restrictions on character-based attributes

False

The____ command restricts the selection of grouped rows based on a condition.

HAVING

When using a(n) _____ join, only rows from the tables that match on a common value are returned.

Inner

According to the rules of precedence, which of the following computations should be completed first?

Operations within parentheses

The _____ command is used to restore the database to its previous condition.

ROLLBACK

Which query is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another?

SELECT ONLY V_CODE FROM PRODUCT;

Which SQL format would be best used for a small, numeric data type?

SMALLINT

In Oracle, the _____ function converts a date to a character string.

TO_CHAR()

A sequence is not associated with a table and can be dropped from a database with a DROP SEQUENCE command.

True

A view is a virtual table based on a SELECT query.

True

ANSI-standard SQL allows the use of special operators in conjunction with the WHERE clause.

True

In Oracle, you can use the SQL*Plus command SHOW ERRORS to help you diagnose errors found in PL/SQL blocks.

True

Numeric functions take one numeric parameter and return one value.

True

One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions

True

One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions.

True

Some RDBMSs, such as Microsoft Access, automatically make the necessary conversions to eliminate case sensitivity.

True

String comparisons are made from left to right.

True

The ANSI SQL standards are also accepted by the ISO.

True

The COUNT function is designed to tally the number of non-null "values" of an attribute, and is often used in conjunction with the DISTINCT clause

True

The COUNT function is designed to tally the number of non-null "values" of an attribute, and is often used in conjunction with the DISTINCT clause.

True

The conditional LIKE must be used in conjunction with wildcard characters.

True

To remedy the lack of procedural functionality in SQL, and to provide some standardization within the many vendor offerings, the SQL-99 standard defined the use of persistent stored modules

True

To remedy the lack of procedural functionality in SQL, and to provide some standardization within the many vendor offerings, the SQL-99 standard defined the use of persistent stored modules.

True

The _____ specification creates an individual index on a respective attribute; use it to avoid having duplicated values in a column.

Unique

A(n) _____ is an alternate name given to a column or table in any SQL statement.

alias

The SQL data manipulation command HAVING:

b. restricts the selection of grouped rows based on a condition.

The tables on which a view, or a virtual table derived from a SELECT query, are based are called _____ tables.

base

A(n) _____ join performs a relational product (also known as the Cartesian product) of two tables.

cross

When you create a new database, the RDBMS automatically creates the data _____ tables in which to store the metadata and creates a default database administrator

dictionary

When you create a new database, the RDBMS automatically creates the data _____ tables in which to store the metadata and creates a default database administrator.

dictionary

A(n) _____ cursor is automatically created in procedural SQL when the SQL statement returns only one value.

implicit

A(n) _____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values.

outer

When writing SQL table-creating command sequences, the entire table definition is enclosed in _____.

parentheses

A _____ is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server.

persistent storage module (PSM)

You cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor. This is known as _____

referential integrity

You cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor. This is known as _____.

referential integrity

Words used by a system that cannot be used for any other purpose are called _____ words. For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns.

reserved

The SQL data manipulation command HAVING:

restricts the selection of grouped rows based on a condition

A(n) _____ is a query that is embedded (or nested) inside another query.

subquery

Oracle recommends _____ for creating audit logs.

triggers

Although SQL commands can be grouped together on a single line, complex command sequences are best shown on separate lines, with space between the SQL command and the command's components.

true

The Oracle string concatenation function is ____

||