ITSS 4300 FINAL EXAM

The order of precedence for the logical operators in a WHERE clause is A. Not, And, Or B. And, Or, Not C. Or, And, Not D. Not, Or, And

A. Not, And, Or

______ minimizes the number of disk reads necessary to retrieve a row of data. A. Row-centric storage B. Column-family database C. Column-centric storage D. Column-oriented database

A. Row-centric storage

Suppose that you want to get a count of the number of vendors in the state that's stored in the variable named "vendor_state_var". Which of the following statements would you use to get the count and set a variable named "vendor_count" to the selected value? A. SELECT COUNT(vendor_id) INTO vendor_countFROM vendors WHERE vendor_state = vendor_state_var B. SET vendor_count = SELECT COUNT(vendor_id) INTO vendor_count FROM vendors WHERE vendor_state = vendor_state_var C. SELECT COUNT(vendor_id) AS vendor_countFROM vendors WHERE vendor_state = vendor_state_var D. vendor_count = SELECT COUNT(vendor_id) INTO vendor_count FROM vendors WHERE vendor_state = vendor_state_var

A. SELECT COUNT(vendor_id) INTO vendor_countFROM vendors WHERE vendor_state = vendor_state_var

Which of the following statements would you use to assign the value "Test" to a variable named "message"? A. SET message = 'Test' B. VARCHAR(message, 'Test') C. message = 'Test' D. SET(message, 'Test')

A. SET message = 'Test'

SQL view instances are retrieved using the ________. A. SQL SELECT statement B. SQL CREATE statement C. SQL INSERT statement D. SQL DELETE statement E. SQL UPDATE statement

A. SQL SELECT statement

The person responsible for the control of the centralized and shared database is the database _____ A. administrator B. analyst C. user D. programmer

A. administrator

By default, when you execute an INSERT, UPDATE, or DELETE statement outside of a transaction, MySQL A. automatically commits changes B. commits the changes when the end of a stored procedure is reached C. commits the changes when a save point is reached D. commits the changes when the COMMIT statement is executed

A. automatically commits changes

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

A. binary

As the database server runs, it can write information to A. data files and log files B. data files and the configuration file C. data files, log files, and the configuration file D. data files

A. data files and log files

In MongoDB, ______ method retrieves objects from a collection that match the restrictions provided. A. find() B. review C. count D. read

A. find()

Modeling and storing data about relationships is the focus of ______ databases. A. graph B. document C. key-value D. column-oriented

A. graph

By default, MySQL prevents all but one of the following types of concurrency problems. A. phantom reads B. nonrepeatable reads C. dirty reads D. lost updates

A. phantom reads

EXISTS will take a value of ________ if the subquery returns an intermediate results table that contains one or more rows A. true B. false C. undefined D. 1

A. true

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

A. use a SET statement to set the system variable

Which of the following is a request that can be answered using the HAVING clause? A. Which agents booked more than $3,000 worth of business in December, 2018? B. Show me how many recipes exist for each class of ingredient. C. List for each customer and order date the customer full name and the total cost of items ordered on each date. D. Display the highest raw score for each bowler.

A. Which agents booked more than $3,000 worth of business in December, 2018?

The SQL statement used to modify a view is ________.| A. DROP VIEW ViewName B. ALTER VIEW ViewName AS C. MODIFY VIEW ViewName AS D. ALTER EXISTING VIEW ViewName AS E. DROP EXISTING VIEW ViewName

B. ALTER VIEW ViewName AS

Which of the following do you need to do when you use a cursor to work with the data in a result set one row at a time? A. Declare an error handler that's executed when no more rows are found in the result set. B. All of the above C. Use a loop to process each row in the result set for the cursor. D. Get the column values in each row of the result set and store them in variables.

B. All of the above

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

B. All of the above

Which of the following statements creates a user named Jennifer with a password of jen3021 that can connect only from the computer where MySQL is running? A. CREATE USER jennifer@localhost PASSWORD 'jen3021' B. CREATE USER jennifer@localhost IDENTIFIED BY 'jen3021'; C. CREATE USER jennifer PASSWORD BY 'jen3021'; D. CREATE USER jennifer IDENTIFIED BY 'jen3021';

B. CREATE USER jennifer@localhost IDENTIFIED BY 'jen3021';

Which of the following clauses would you code on a SELECT statement if you wanted to lock the selected rows so other transactions can read those rows but can't modify them until the transaction that contains the SELECT statement is committed? A. FOR UPDATE B. FOR SHARE C. READ ONLY D. NO UPDATE

B. FOR SHARE

To grant privileges to other users, a user requires this privilege: A. SHOW GRANTS B. GRANT OPTION C. ALL D. USAGE

B. GRANT OPTION

Although it cannot be used in creating an SQL VIEW, the SQL SELECT statements retrieving view instances can include ________. A. SQL keyword NULL B. SQL keyword ORDER BY C. SQL keyword BETWEEN D. SQL keyword NOT NULL E. SQL keyword LIKE

B. SQL keyword ORDER BY

Each of the following is a valid reason to use a transaction except for one. Which one is it? A. When you code two or more INSERT, UPDATE, or DELETE statements that affect related data. B. The results of a SELECT query will be used as a subquery. C. When you move rows from one table to another table by using INSERT and DELETE statements. D. The failure of one statement in a set of INSERT, UPDATE, or DELETE statements will violate data integrity.

B. The results of a SELECT query will be used as a subquery.

Which of the following statements is not true about variables? A. They must have names that are different from the names of any columns used in any SELECT statement within the stored program. B. They can be declared with an expression that's used as the default value. C. They store values that can change as a program executes. D. They are declared with the DECLARE statement.

B. They can be declared with an expression that's used as the default value.

Which two clauses are used to filter the result set? A. GROUP BY and HAVING B. WHERE and HAVING C. FROM and WHERE D. WHERE AND GROUP BY

B. WHERE and HAVING

Which of the following occurs when neither of two transactions can be committed because they each have a lock on a resource needed by the other? A. rollback B. deadlock C. lost update D. common lock

B. deadlock

Neo4j is a ______ database. A. row-centric B. graph C. key-value D. column family

B. graph

The _____ process analyzes SQL queries and finds the most efficient way to access data. A. scheduler B. optimizer C. user D. listener

B. optimizer

A password must have a minimum of 8 characters, one numeric and special character" is an example of a _____. A. rule B. standard C. procedure D. policy

B. standard

SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query. A. grouping B. subquery C. joining D. union

B. subquery

A type of query that is placed within a WHERE or HAVING clause of another query is called a: A. superquery. B. subquery. C. master query. D. multi-query.

B. subquery.

A user that has access to all columns on a specified table is said to have: A. column privileges B. table privileges C. global privileges D. database privileges

B. table privileges

Suppose you declare three condition handlers: one that handles errors for a SQLSTATE code, one that handles errors for a MySQL error code, and one that handles any error condition other than NOT FOUND. If an error occurs that could be handled by any of the three condition handlers, which condition handler will MySQL use to handle the error? A. the one for the SQLSTATE code B. the one for the MySQL error code C. All of the above D. the one that will catch any error condition other than NOT FOUND

B. the one for the MySQL error code

Which of the following types of stored programs execute in response to an INSERT, UPDATE, or DELETE statement? A. event B. trigger C. stored function. D. stored procedure

B. trigger

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

B. use a SET statement to set the system variable

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

C. All of the above

Which of the following types of handlers would you use if you want MySQL to skip the rest of the code in a block of code when it encounters an error? A. SQLEXCEPTION B. END C. EXIT D. CONTINUE

C. EXIT

Which of the following statements do you use to get column values from a row in a cursor and store them in a series of variables? A. WHILE B. UPDATE C. FETCH D. OPEN

C. FETCH

If you need to check a Boolean expression and execute statements depending on whether that expresison is true or false, which of the following statements would you use? A. REPEAT B. simple CASE C. IF D. WHILE

C. IF

Which of the following statements do you use to turn off autocommit mode until the statements in a transaction are committed or rolled back? A. CREATE PROCEDURE B. NO COMMIT C. START TRANSACTION D. SET AUTOCOMMIT OFF

C. START TRANSACTION

______ is NOT one of the "3 Vs" of Big Data. A. Variety B. Velocity C. Validation D. Volume

C. Validation

Save points allow you to roll back a transaction A. None of the above B. to a particular save point C. both a and b D. to the beginning of the transaction

C. both b and d

Big Data ______. A. relies on the use of unstructured data B. imposes a structure on data when it is captured C. captures data in whatever format it naturally exists D. relies on the use of structured data

C. captures data in whatever format it naturally exists

Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union A. combines columns from the same table B. by default, does not eliminate duplicate rows C. combines the result sets of two or more SELECT statements D. is coded in the ORDER BY clause

C. combines the result sets of two or more SELECT statements

If there is no index, the DBMS will perform a _____ scan. A. range B. row ID table access C. full table D. loop

C. full table

To work with data, a DBMS must retrieve the data from _____ and place them in _____. A. data files; procedure cache B. temporary files; procedure cache C. permanent storage; RAM D. RAM; data cache

C. permanent storage; RAM

To work with data, a DBMS must retrieve the data from _____ and place them in _____. A. temporary files; procedure cache B. data files; procedure cache C. permanent storage; RAM D. RAM; data cache

C. permanent storage; RAM

In the context of Big Data, ______ relates to changes in meaning. A. variety B. viability C. veracity D. variability

D. variability

What are ACID Properties? . A. ATOMIC, CONSTANT, INLINE, DATA B. ATOMIC, CONSISTENCY, INLINE, DURABILITY C. ATOMIC, CONSISTENT, INLINE, DATA D. ATOMIC, CONSISTENCY, ISOLATION, DURABILITY

D. ATOMIC, CONSISTENCY, ISOLATION, DURABILITY

Which of the following types of handlers would you use if you want MySQL to execute the rest of the code in a block of code when it encounters an error? A. SQLEXCEPTION B. EXIT C. END D. CONTINUE

D. CONTINUE

SQL views are constructed from ________. A. VIEW statements B. SELECT statements C. UPDATE statements D. INSERT statements E. CREATE statements

D. INSERT statements

The MySQL server process that runs in the background is called A. MySQL engine B. MySQL db C. MySQL database D. MySQL daemon

D. MySQL daemon

Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1 ORDER BY vendor_state A. The names of the vendors in each state B. The duplicate vendors from each state C. The number of vendors in each state D. The number of vendors in each state that has more than one vendor

D. The number of vendors in each state that has more than one vendor

Each of the following techniques can help to prevent deadlocks except for one. Which one is it? A. Don't leave transactions open any longer than necessary. B. If you code two transactions that update the same resources, code the updates in the same order in each transaction C. Schedule transactions that modify a large number of rows so they run when no other transactions will be running. D. Use the highest isolation level possible.

D. Use the highest isolation level possible.

Document databases group documents into logical groups called ______. A. buckets B. blocks C. sets D. collections

D. collections

When this summary query is executed, the result set will contain one summary row for SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name A. each city with invoice average over $500 B. each city with invoice totals over $500 C. each vendor with invoice average over $500 D. each vendor with invoice totals over $500

D. each vendor with invoice totals over $500

During the ____ phase, the DBMS retrieves the data and sends the result set back to the client. A. execute B. process C. parse D. fetch

D. fetch

______ processing occurs when a program runs from beginning to end without any user interaction. A. batch B. Hive C. Block D. hadoop

D. hadoop

Which of the following does MySQL use by default to prevent concurrency problems? . all of the above B. save points C. transactions D. locks

D. locks

The _____ is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions. A. permanent storage B. data cache C. buffer cache D. procedure cache

D. procedure cache

SQL views are used for all except ________. A. to display the results of computations B. to layer built-in functions C. to hide columns or rows D. to backup preexisting tables E. to hide complicated SQL syntax

D. to backup preexisting tables

An SQL virtual table is called a(n) ________. A. embedded SQL statement B. CHECK constraint C. trigger D. view E. stored procedure

D. view

The SQL statement used to delete a view from a database is ________. A. DROP EXISTING VIEW ViewName B. ALTER EXISTING VIEW ViewName AS C. DELETE VIEW ViewName D. ALTER VIEW ViewName AS E. DROP VIEW ViewName

E. DROP VIEW ViewName

An index scan is less efficient than a full table scan. True False

False

SQL statements used to construct views cannot contain the WHERE clause. True False

False

A full backup, or database dump, produces a complete copy of the entire database. True False

True

An SQL virtual table is called a view. True False

True

Missing or Unknown value is treated as Null. True False

True

ROLLUP Clause used with GROUP BY Clause. True False

True

SQL triggers are used for providing default values, validity checking, updating views, and performing referential integrity actions. True False

True

SQL views are constructed from SELECT statements. True False

True

SQL views can be used to hide columns. True False

True

SQL views can be used to hide complicated SQL syntax. True False

True

SQL views can be used to layer built-in SQL functions. True False

True

SQL views contain their own data. True False

True

The SQL command CREATE VIEW is used to create view structures. True False

True

The SQL command SELECT is used to retrieve data from view instances. True False

True

The SQL keyword ORDER BY can be used with SELECT statements that are retrieving data from view instances to sort the results of the SELECT. True False

True

Velocity is the Big Data 3 V that relates to the speed at which data is entering the system. True False

True