CIS 388 Exam 3 Practice Questions

What is the correct sequence to create a database connection? 1.Import JDBC packages. 2.Open a connection to the database. 3.Load and register the JDBC driver. 4.Execute the statement object and return a query resultset. 5.Create a statement object to perform a query. 6.Close the resultset and statement objects. 7.Process the resultset. 8.Close the connection. 1, 3, 2, 5, 4, 7, 6, 8 1, 3, 2, 4, 5, 6, 7, 8 1, 2, 3, 5, 4, 7, 8, 6 2, 1, 3, 4, 8, 7, 5, 6

1, 3, 2, 5, 4, 7, 6, 8

A credit-card sized plastic card with an embedded microprocessor chip with the ability to store, process and output electronic data in a secure manner is called a(n): 25) A) smart card. B) secure card. C) e-credit card. D) smart chip.

25) A) smart card

All of the following values can be stored in a column that's defined as SQL DECIMAL(6,2), except -246 2479.95 0 32492.05

32492.05

Which of the following is not an advantage of using subqueries over using joins? You can use a subquery to pass an aggregate value to the main query. A subquery tends to be more intuitive when it uses an existing relationship between the two tables. Long, complex queries can sometimes be easier to code using subqueries

A subquery tends to be more intuitive when it uses an existing relationship between the two tables.

The W3C standard for Web privacy is called: 12) A) Platform for Privacy Preferences. B) the Magna Carta. C) Freedom of Web Information Act. D) the Web privacy act

A) Platform for Privacy Preferences.

A(n) ________ prevents another transaction from reading and therefore updating a record until it is unlocked. 7) A) exclusive lock B) authorization rule C) record controller D) shared lock

A) exclusive lock

Most data outages in organizations are caused by: 23) A) human error. B) hardware failures. C) electrical outages. D) software failures.

A) human error.

An audit trail of database changes is kept by a: A) journalizing facility. B) before image. C) subschema. D) change control device

A) journalizing facility.

Sarbanes-Oxley Act was enacted to ensure the integrity of: 13) A) public companies' financial statements. B) Entity-Relationship Diagrams. C) SQL. D) private companies shareholders meetings

A) public companies' financial statements.

When incorrect data have been introduced, the database is best recovered by: 18) A) restarting from the most recent checkpoint and processing subsequent transactions. B) starting with a new ERD. C) reloading RDMS software. D) formatting server.

A) restarting from the most recent checkpoint and processing subsequent transactions.

Security measures for dynamic Web pages are different from static HTML pages because: 11) A) the connection requires full access to the database for dynamic pages. B) dynamic Web pages are built "on the fly." C) HTML is more complex than dynamic Web pages. D) static Web pages contain more sensitive data.

A) the connection requires full access to the database for dynamic pages.

Which statement is used to modify the structure of an existing table? ALTER TABLE CREATE TABLE CHANGE TABLE UPDATE

ALTER TABLE

Which of the following attributes do you use to generate the values for a column as unique numbers in sequence? AUTO_NUMBER UNIQUE_NUMBER AUTO_INCREMENT UNIQUE_INCREMENT

AUTO_INCREMENT

Which of the following is true about creating indexes? MySQL automatically creates indexes for primary keys. MySQL automatically creates indexes for foreign keys. You can create an index that's based on more than one column. All of the above

All of the above

Which of the following is true about the MySQL database server? It reads settings from a file when it starts It reads from and writes to data files when it's running It can be configured to write data to log files when it's running All of the above None of the above

All of the above

Which is true when you define a column as the primary key? The column is forced to be NOT NULL. The column is forced to contain a unique value for each row. An index is automatically created based on the column. All of the above.

All of the above.

________ is used to undo unwanted database changes. 17) A) Restart B) Rollback C) Rollforward D) Encryption

B) Rollback

A(n) ________ is submitted by a DBA to test the current performance of a database or predict the response time for queries. 21) A) elusive query B) heartbeat query C) HTML D) performance test

B) heartbeat query

A discrete unit of work that must be processed completely or not at all within a computer system is called a: 16) A) journalizing facility. B) transaction. C) before image. D) change control device.

B) transaction.

An optimistic approach to concurrency control is called: 8) A) denormalization. B) versioning. C) HappyControl. D) deadlock resolution.

B) versioning.

JDBC API supports____________ architecture model for accessing the database. Two-tier client/server Three-tier client/server Both a and b

Both a and b

Which method can be used to load a database driver in JDBC (Note these methods may be from different Java classes)? getConnection() registerDriver() forName() Both b and c

Both b and c

In JDBC, how can you execute DML statements (i.e. insert, delete, update) in the database? By invoking the execute(...) or executeUpdate(...) method of a normal Statement object or a sub-interface object thereof By invoking the executeInsert(...), executeDelete(...) or executeUpdate(...) methods of the DataModificationStatement object By making use of the execute(...) statement of the DataModificationStatement object By making use of the InsertStatement, DeleteStatement or UpdateStatement classes

By invoking the execute(...) or executeUpdate(...) method of a normal Statement object or a sub-interface object thereof

________ is a technical function responsible for database design, security, and disaster recovery. A) Tech support B) Data administration C) Database administration D) Operations

C) Database administration

Which of the following is NOT an area of concern when trying to maintain a well-tuned database? 20) A) Memory usage B) Input/output contention C) User interface design D) CPU usage

C) User interface design

A device to measure or detect fingerprints or signatures is called a(n) ________ device. 24) A) laser B) view C) biometric D) ink

C) biometric

A DBMS periodically suspends all processing and synchronizes its files and journals through the use of a: 15) A) database change log. B) backup facility. C) checkpoint facility. D) recovery manager.

C) checkpoint facility.

The actions that must be taken to ensure data integrity is maintained during multiple simultaneous transactions are called ________ actions. 6) A) logging B) multiple management C) concurrency control D) transaction authorization

C) concurrency control

Research shows that if an online customer does not get the service he or she expects within a few ________, the customer will switch to a competitor. 22) A) seconds B) hours C) minutes D) days

C) minutes

Which of the following expressions would you use to display the invoice_total column with a dollar sign, one or more commas, a decimal point, and two decimal digits? CONCAT('$', invoice_total) CONCAT('$', CONVERT(invoice_total, DECIMAL(9, 2))) CONCAT('$', FORMAT(invoice_total, 2)) CONCAT('$', CAST(invoice_total AS CHAR(11)))

CONCAT('$', FORMAT(invoice_total, 2))

A transaction that terminates abnormally is called a(n) ________ transaction. 19) A) deleted B) completed C) terminated D) aborted

D) aborted

The ________ occurs when one user reads data that have been partially updated by another user. 9) A) honest dirty data B) data quality problem C) distant read problem D) inconsistent read problem

D) inconsistent read problem

A(n) ________ stores metadata about an organization's data and data processing resources. 5) A) data dictionary B) DBA C) organizational system catalog D) information repository

D) information repository

Guidelines for server security should include all of the following EXCEPT: 10) A) guidelines for password lengths. B) password naming conventions. C) guidelines for frequency of password changes. D) securing the network between client and server.

D) securing the network between client and server.

Which of the following types of real numbers is considered an exact numeric type? DOUBLE FLOAT DECIMAL all of the above

DECIMAL

Which of the following keywords does not define a column constraint? UNIQUE DEFAULT PRIMARY KEY NOT NULL

DEFAULT

Which of the following is not typically the responsibility of a database administrator? Maintain log files Back up the database regularly Develop applications that access the database Maintain user accounts

Develop applications that access the database

What are the major components of the JDBC? DriverManager, Driver, Connection, Statement, and ResultSet DriverManager, Connection, Statement, and ResultSet DriverManager, Statement, and ResultSet DriverManager, Driver, Connection, and Statemen

DriverManager, Driver, Connection, Statement, and ResultSet

Which of the following keywords would you use to introduce a subquery if you want to determine if the subquery returns one or more rows? ANY SOME EXISTS ALL

EXISTS

Which of the following is not true if you use a string in a numeric expression? If the string contains only numbers, MySQL returns those numbers. If the string starts with a number, MySQL returns that number and each successive number until it encounters a letter or special character. If the string starts with a letter or special character, MySQL returns a value of zero. If the string doesn't contain any numbers, an error occurs.

If the string doesn't contain any numbers, an error occurs.

In which of the following ways can you not use a subquery in a SELECT statement? In a SELECT clause as a column specification In a FROM clause as a table specification In a WHERE clause as part of a search condition In an ORDER BY clause as a column specification

In an ORDER BY clause as a column specification

Which of the following engines supports foreign keys and transactions and is the default storage engine for MySQL 5.5 and later. InnoDB MyISAM BLACKHOLE none of the above

InnoDB

Which of the following statements about a correlated subquery is not true? It is executed once for each row in the main query. It can't use the same table as the main query. It must refer to a value that's provided by a column in the main query. It can use a table name or alias to refer to a table in the main query

It can't use the same table as the main query.

What does the following code do? SELECT @@autocommit It gets the global value of the autocommit variable It gets the session value of the autocommit variable It gets the session value of the autocommit variable if it exists. Otherwise, it gets the session value. It gets the global value of the autocommit variable if it exists. Otherwise, it gets the session value.

It gets the session value of the autocommit variable if it exists. Otherwise, it gets the session value.

Which of the following statements is not true about the CHAR type? It uses the same amount of storage for each value. It includes one byte that indicates the actual length of the string. When used with the utf8mb4 character set, four bytes must be reserved for each character. It can be used to store numbers that aren't used in numeric operations.

It includes one byte that indicates the actual length of the string.

In JDBC, what is true about getConnection() method? It is a non static method from DriverManager class It is a static method from DriverManager interface It is a non static method from DriverManager interface It is a static method from DriverManager class

It is a static method from DriverManager class

Which of the following statements about a TIMESTAMP type is NOT true? It stores a date and time. MySQL automatically sets the value of a TIMESTAMP column when it inserts a row. MySQL automatically changes the value of a TIMESTAMP column when it updates a row. It requires more storage than a DATETIME type.

It requires more storage than a DATETIME type.

1) Which of the following is NOT true of poor data and/or database administration? A) Multiple entity definitions B) Unknown meanings of stored data C) Maintaining a secure server D) Data timing problems

Maintaining a secure server

The MySQL server process that runs in the background is called MySQL db MySQL database MySQL daemon MySQL engine

MySQL daemon

If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting? NULL NOT NULL This will cause an error none of the above

NULL

What is the return type of executeQuery() method? int array list ResultSet

ResultSet

What should be the correct order to close the database resource in JDBC? ResultSet, Statements, and then Connection Statements, ResultSet, and then Connection ResultSet, Connection, and then Statements Connection, Statements, and then ResultSet

ResultSet, Statements, and then Connection

Which of the following statements do you use to delete all data from a table without deleting the definition for the table? RENAME TABLE TRUNCATE TABLE CREATE TABLE DROP TABLE

TRUNCATE TABLE

Which of the following is not true about indexes? They can help speed up insert, update and delete operations on columns that are updated frequently. MySQL automatically creates an index for unique constraints. They can improve performance when MySQL searches for rows in a table. They can be deleted by using the DROP INDEX statement.

They can help speed up insert, update and delete operations on columns that are updated frequently.

An open-source DBMS is: 4) A) a beta release of a commercial RDBMS. B) an object-oriented database management system. C) source code for a commercial RDBMS. D) a free source-code RBMS that provides the functionality of an SQL-compliant DBMS

a free source-code RBMS that provides the functionality of an SQL-compliant DBMS

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery) a single value a list of values a table of values a subquery can't be introduced in this way

a list of values

If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery) a single value a list of values a table of values a subquery can't be introduced in this way

a list of values

If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery) a single value a list of values a table of values a subquery can't be introduced in this way

a single value

If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) a single value a list of values a table of values a subquery can't be introduced in this way

a single value

A subquery can return a single value only a list of values only a single value or a list of values only a single value, a list of values, or a table of values

a single value, a list of values, or a table of values

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) a single value a list of values a table of values a subquery can't be introduced in this way

a subquery can't be introduced in this way

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) a single value a list of values a table of values a subquery can't be introduced in this way

a table of values

In the collation, utf8mb4_0900_as_cs, the as stands for: alphanumeric-special alphanumeric-sensitive accent-special accent-sensitive

accent-sensitive

To manage binary logs, you can use a strategy known as: log rotation age-based expiration replication configuration

age-based expiration

Which of the following techniques can you use to make permanent changes to the configuration file for a MySQL server? use the SET statement use a text editor b and c only all of the above

b and c only

(Refer to code example 7-1.) When this query is executed, the rows will be sorted by Code example 7-1 SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC invoice_id vendor_id balance_due in descending sequence vendor_id and then by balance_due in descending sequence

balance_due in descending sequence

Which of the following logs contains a record of all changes that have been made to the database? binary error relay startup

binary

In the collation, utf8_spanish_ci, the ci stands for: case-insertion case-insensitive collation-insensitive collation-insertion

case-insensitive

When you code a table-level constraint, the constraint can refer to data in more than one database table column value

column

In which of the following types of files are the system variables for a MySQL server stored? data file log file configuration file system file

configuration file

The CREATE TABLE statement creates a new table in the specified database creates a new table in the current database specifies attributes for an existing table all of the above

creates a new table in the current database

As the database server runs, it can write information to data files data files and log files data files and the configuration file data files, log files, and the configuration file

data files and log files

The role of a ________ emphasizes integration and coordination of metadata across many data sources. 3) A) data architect B) data administrator C) database administrator D) data warehouse administrator

data warehouse administrator

You use data definition language (DDL) to create, modify, and delete tables only databases and tables only tables and indexes only databases, tables, and indexes

databases, tables, and indexes

The DATE data type can store dates only dates and times times only all of the above

dates only

(Refer to code example 7-2.) When this query is executed, the result table will contain one row for Code example 7-2 SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC each vendor each vendor in the table named ia each invoice each invoice in the table named ia

each vendor in the table named ia

Which of the following logs contains messages about server startup and shutdown? binary error relay startup

error

(Refer to code example 7-2.) When this query is executed, there will be one row Code example 7-2 SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC for each vendor for each vendor with an average invoice total that's greater than 100 for each vendor with a maximum invoice total that's greater than 100 for each invoice with an invoice total that's greater than the average invoice total for the vendor and also greater than 100

for each vendor with an average invoice total that's greater than 100

What kind of constraint enforces referential integrity between tables? primary key foreign key unique key all of the above

foreign key

Which feature does the MyISAM storage engine not support? full-text searches foreign keys spatial data types

foreign keys

Which of the following can you not code as part of a CREATE TABLE statement? not null constraints column-level constraints table-level constraints function-based indexes

function-based indexes

What is the return type of executeUpdate() method? byte short int long

int

Numbers that don't include a decimal point are known as integers exact numbers real numbers precise numbers

integers

In JDBC, we use Class.forName() to load a JDBC driver. In which Java package is Class located? java.util java.lang java.io java.sql

java.lang

Which Java package contains JDBC classes and interfaces? java.sql java.jdbc java.util java.lang

java.sql

What must be the first characters of a database URL in JDBC? db: sql: database: jdbc:

jdbc:

To manage text-based logs, you can use a strategy known as: log rotation age-based expiration replication configuration

log rotation

Which of the following system variables controls whether the binary log is enabled or disabled? log_bin log_binary bin_log binary_log

log_bin

The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type? small, medium, large yes, no, maybe mushrooms, sausage, peppers cash, credit, debit

mushrooms, sausage, peppers

Which method on the result set tests whether or not there remains at least one unfetched row in the result set? fetch() next() more() current()

next()

(Refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number Code example 7-1 SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC 1 of invoices in the Invoices table of invoices for each vendor of invoices for each vendor that has a larger balance due than the average balance due for all invoices

of invoices for each vendor that has a larger balance due than the average balance due for all invoices

The latin1 character set uses how many bytes per character? one two three up to four

one

(Refer to code example 7-1.) When this query is executed, the result set will contain Code example 7-1 SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC one row for each invoice that has a larger balance due than the average balance due for all invoices one row for the invoice with the largest balance due for each vendor one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if the balance due is larger than the average balance due for all invoices

one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if the balance due is larger than the average balance due for all invoices

Which of the following indicates the total number of digits that can be stored in a real number? size precision scale significance

precision

For SQL data types, numbers that include a decimal point are known as integers exact numbers real numbers precise numbers

real numbers

Variables that store information about the state of the database server are known as system variables server variables status variables configuration variables

status variables

You can use database replication to synchronize two or more databases that are running on different machines copy a database from one server to another backup a database copy log files from one server to another

synchronize two or more databases that are running on different machines

Variables that control how the server is currently configured are known as system variables server variables status variables configuration variables

system variables

Which of the following types of data would you not store in a binary large object (BLOB) type? image files text files sound files video files

text files

When you define a foreign key constraint, which of the following can you not specify? that no action should be taken and an error should be returned when referential integrity is going to be violated that the deletion of a row in a primary key table should be cascaded down to related rows in the foreign key table that the deletion of a row in a primary key table should cause the foreign keys in related rows in the foreign key table to be set to nulls that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

A character set determines the characters that are used to represent the binary types in a database the character types in a database the numeric types in a database all of the above

the character types in a database

When you use MySQL Workbench to view the column definitions for a table, you can see the data types only the attributes only the data types and attributes only the data types, attributes, and indexes

the data types and attributes only

When you define a column with the DEFAULT attribute a null value is used if another value isn't specified when a row is added to the database. the default value you specify does not need to correspond with the data type for the column. the default value is used if another value isn't specified when a row is added to the database.

the default value is used if another value isn't specified when a row is added to the database.

(Refer to code example 7-2.) When this query is executed, each row in the result table will show Code example 7-2 SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC the largest invoice total related to that row the average invoice total related to that row the largest invoice total related to that row, but only if it's larger than the average for all invoices the average invoice total related to that row, but only if it's greater than 100

the largest invoice total related to that row

When the following query is executed, the result set will include one row for each invoice with an invoice total that's greater than SELECT vendor_name, invoice_number, invoice_total FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id WHERE invoice_total > ANY (SELECT invoice_total FROM invoices WHERE vendor_state = 'CA') the minimum invoice total for vendors in California the maximum invoice total for vendors in California the minimum invoice total for vendors in states other than California the maximum invoice total for vendors in states other than California

the minimum invoice total for vendors in California

Which uses the least amount of storage? 'example' stored in a column of type CHAR(20) 'exam' stored in a column of type CHAR(20) 'ex' stored in a column of type CHAR(20) they all use the same amount of storage

they all use the same amount of storage

The utf8mb4 character set uses how many bytes per character? one two three up to four

up to four

How can you change a system variable without restarting the database server? use MySQL Workbench to change the options file use a text editor to edit the configuration file use a SET statement to set the system variable all of the above

use a SET statement to set the system variable

Which of the following can you not specify for a column when you create a table using the CREATE TABLE statement? data type value column name attributes

value

When you code a script for creating a database you can create the tables in whatever sequence you prefer you need to create the referred to tables before you create the tables that refer to them you need to create the indexes before you create the tables that they apply to you need to code the primary key column first in each table

you need to create the referred to tables before you create the tables that refer to them

Which of the following is the default date format for MySQL? mm/dd/yy mon-dd-yyyy yyyy-mm-dd yy/dd/mm

yyyy-mm-dd