Database Design & Development Final Exam

Ace your homework & exams now with Quizwiz!

True

MySQL's REPLACE() function lets you replace part or all of a string. TRUE OR FALSE

True

MySQL's conditional logic CASE expression is part of the SQL standard (SQL92). TRUE OR FALSE

False

MySQL's conditional logic IF() function is part of the SQL standard (SQL92). TRUE OR FALSE

False

Primary key constraints guarantee not null behaviors but not uniqueness. TRUE OR FALSE

Purging all data from a table used to stage new data warehouse feeds. Modifying all rows in a table after a new column has been added. Retrieving all rows from a message queue table.

Sometimes you will want to work with every row in a table to accomplish which of the following? (Multiple answers are possible.)

True

Although you interact with data one row at a time, relational databases are really all about sets.

False

At the time of writing, MySQL allows parentheses in compound queries.

True

Even with an index, queries may scan all rows in a table. TRUE OR FALSE

False

Foreign key constraints always restrict the allowable values in other tables. TRUE OR FALSE

Start position, Number of characters to replace, Original string, Replacement string

MySQL's INSERT() function takes which of the following four arguments? (Multiple answers are possible.)

True

Multicolumn indexes perform better than single column indexes. TRUE OR FALSE

True

The RIGHT JOIN returns all rows from the table on the right side of the join and only the rows that meet the join condition on the left side of the join. TRUE OR FALSE

True

The following statement lets you display definition and constraints of a table (the guillemet or angle brackets are only to illustrate that any table, column, or constraint name are placeholders for valid tables, columns, or constraints of a database). mysql> SHOW CREATE TABLE <table_name>; TRUE OR FALSE

True

Unlike tables, views do not involve data storage. TRUE OR FALSE

integer

What data type is returned by an ascii() function?

True

You need to tell the MySQL Server to convert a string to a datetime when the string doesn't conform to the default datetime format.

on delete restrict on delete set null on update restrict on update cascade

Which of the following are valid options for foreign key constraints? (Multiple answers are possible.)

MySQL Shell, MySQL

Which of the following can request and hold a MySQL "database connection"? (Multiple answers are possible.)

=, IN

Which of the following comparison operators work inside the WHEN clause of a CASE expression to resolve equality? (Multiple answers are possible.)

True

While the ANSI SQL specification includes the except operator for performing the except operation, it is not implemented in MySQL 8.

True

While the ANSI SQL specification includes the intersect operator for performing the intersect operation, it is not implemented in MySQL 8.

True

B-tree indexes work best with high cardinality data. TRUE OR FALSE

How many columns are returned by the following query? SELECT * FROM (SELECT 'Yes' AS reply UNION ALL SELECT 'No' AS reply) decided CROSS JOIN (SELECT 'Maybe' AS reply) undecided;

True

MySQL's CONCAT() function is available in Microsoft SQL Server and performs exactly the same way as it does in MySQL.

False

MySQL's CONCAT() function is available in Oracle and performs exactly the same way as it does in MySQL.

True

SELECT statements retrieves data.

Generating data, Manipulating data, Retrieving data

SQL was initially created to be the language for which of the following? (Multiple answers are possible.)

True

The MySQL server always commits all pending transactions when you issue a SQL schema statement, like ALTER TABLE. TRUE OR FALSE

True

The MySQL server always commits all pending transactions when you issue another START TRANSACTION; command. TRUE OR FALSE

False

The chr function lets you access special characters, like accented vowels in the latin1 character set. TRUE OR FALSE

False

The conditional logic IF() function is built into the SQL grammar and works in the SELECT, INSERT, UPDATE, and DELETE statements. TRUE OR FASLE

False

The describe command only works for tables not views. TRUE OR FALSE

True

The roots of SQL go all the way back to the 1970s.

False

The union of two sets (A union B) includes all the rows in both sets that aren't in the other set and two copies of the intersection.

>ANY, >=

Which of the following comparison operators work inside the WHEN clause of a CASE expression to resolve inequality? (Multiple answers are possible.)

Microsoft SQL Server, MySQL

Which of the following servers requires that you explicitly begin a transaction according to the textbook? (Multiple answers are possible.)

False

You can not join a table and a view. TRUE OR FALSE

True

A single query returns the same result as two queries joined by the intersect operator.

True

Conditional logic is simply the ability to take one of several paths during program execution. TRUE OR FALSE

True

The INNER JOIN returns rows from both tables that meet the join criteria. TRUE OR FALSE

True

The INSERT() function in MySQL lets you insert or replace parts of a string: SELECT INSERT('Goodbye world!', 9, 0,'cruel ') AS farewell; It returns the following: +----------------------+ | farewell | +----------------------+ | Goodbye cruel world! | +----------------------+

True

A except B result leaves you with the rows in set A not found in set B, which is known as the complement of B.

Table, Documents, Flat files

SQL has been evolving to retrieve data from which of the following? (Multiple answers are possible.)

False

The following statement lets you create a unique bitmap index of a table (the guillemet or angle brackets are only to illustrate that any table, column, or constraint name are placeholders for valid tables, indexes, columns, or constraints of a database). mysql> CREATE UNIQUE INDEX <index_name> -> ON <table_name> (<column_name1>, <column_name2>); TRUE OR FALSE

True

The intersection of two sets (A intersect B) is the area of overlap where the two Venn diagram circles contain the same rows of data.

Table

What type of lock granularity keeps multiple users from modifying data in the same table simultaneously?

True

When you execute the CREATE VIEW statement, the database server stores the definition but does not executes it. TRUE OR FALSE

True

With an index, queries scan an index before inspecting rows. TRUE OR FALSE

True

You can access the errata at this page: https://www.oreilly.com/catalog/errata.csp?isbn=0636920274803

True

You can combine LEFT JOIN operations with other LEFT JOIN operations. TRUE OR FASLE

True

A "condition" in the WHERE clause may compare equality between literal, column, or expression values with the REGEXP regular expression operator.

True

A "network database system" can work as a multi-parent hierarchy.

True

B except A result leaves you with the rows in set B not found in set A, which is known as the complement of A.

True

Relational databases always report how many rows you insert, update, or delete. TRUE OR FALSE

False

The MySQL proprietary conditional logic function is the COALESCE() function. TRUE OR FALSE

False

The MySQL proprietary conditional logic function is the DECODE() function. TRUE OR FALSE

True

A "condition" in the WHERE clause may compare inequality between literal, column, or expression values.

False

A "condition" in the WHERE clause may compare inequality, like >, >=, <, or <=, between literal, column, or expression values and a list of values or a result set from a subquery.

%, _

A "condition" in the WHERE clause that may compare equality between literal, column, or expression values and use which of the following wildcards? (Multiple answers are possible.)

True

A database lock holds access to data resources for one user while others may wait for access to the same resource. TRUE OR FALSE

False

Relational databases always report how many rows you change in the data dictionary when you CREATE a table.

True

Resetting the default sql_mode to a value of ansi suppresses the raised error when the string is too long. It then truncates the original string to let it fit in the original column. SET sql_mode='ansi';

Derived table

1986, 1992, 2016

The American National Standards Institute (ANSI) established SQL standards in which of the following years? (Multiple answers are possible.)

True

The B-tree index works best with columns that have unique data. TRUE OR FALSE

True

The MySQL database server manages how multiple users interact with data by implementing locking mechanisms. TRUE OR FALSE

True

The MySQL proprietary conditional logic function is the IF() function. TRUE OR FASLE

False

The following creates a view with an obfuscated column. mysql> CREATE VIEW customer_vw -> ( customer_id -> , first_name -> , last_name -> , email ) AS -> ( SELECT customer_id -> , first_name -> , last_name -> , CONCAT(SUBSTR(email,1,2),'*****',SUBSTR(email,-4)) email -> FROM customer; TRUE OR FALSE

Simple

The following is what type of CASE expression? mysql> SELECT CASE 'B' -> WHEN 'A' THEN 'Alpha' -> WHEN 'B' THEN 'Beta' -> WHEN 'C' THEN 'Charlie' -> ELSE 'Zulu' -> END answer; It would produce the following results: +--------+ | answer | +--------+ | Beta | +--------+

False

The following mysql client command lets you connect to the MySQL studentdb database: [username@localhost ~] $ mysql -ustudent -p

False

The following statement lets you create a unique bitmap index of a table (the guillemet or angle brackets are only to illustrate that any table, column, or constraint name are placeholders for valid tables, indexes, columns, or constraints of a database). mysql> CREATE BITMAP UNIQUE INDEX <index_name>-> ON <table_name> (<column_name>); TRUE OR FALSE

Page

What type of lock granularity keeps multiple users from modifying data on the same page of a table simultaneously?

True

When an active transaction is always associated with a database session, there is no need or method to explicitly begin a transaction. TRUE OR FALSE

True

When an active transaction is not automatically associated with a database session, all SQL statements are committed independently of each other unless you begin a transaction. TRUE OR FALSE

True

You create a view by assigning a name to a SELECT statement and then storing the query for others to use. TRUE OR FALSE

True

Bitmap indexes work best with low cardinality data. TRUE OR FALSE

True

Views are useful when you want to join partitioned tables with set operators. TRUE OR FALSE

False

Views introduce design complexity by hiding joins. TRUE OR FALSE

False

A COUNT(*) function in the SELECT-list of a LEFT JOIN returns 1 for any row not found in the intersection of the LEFT JOIN results. TRUE OR FALSE

True

A COUNT(*) function in the SELECT-list of a LEFT JOIN returns 1 for any row not found in the intersection of the LEFT JOIN results. TRUE OR FALSE

True

A CROSS JOIN is lazy somewhat like the NATURAL JOIN because it doesn't require a join condition.

False

A CROSS JOIN multiplies columns and adds rows to create a result set. TRUE OR FALSE

False

A Cartesian product has a join like other joins on one or more columns from each table. TRUE OR FALSE

True

A FROM clause with two tables requires a link between the tables inside an ON clause.

True

A NATURAL JOIN relies on identical column names across multiple tables to infer the proper join conditions. TRUE OR FALSE

False

A WHERE clause can only filter in data.

False

A WHERE clause can only filter out data.

True

A versioning locking strategy is where writers must request and receive a write lock to modify data while queries do not require a read lock, which means a reader may see data in one state while it is changing to another. TRUE OR FALSE

True

A view can obfuscate part of a column to protect sensitive display of data. TRUE OR FALSE

True

A view is simply a mechanism for querying data. TRUE OR FALSE

True

From the user's standpoint, a view looks exactly like a table. TRUE OR FALSE

True

Multiuser databases provide bookkeeping, or rules, to prevent conflict between multiple users' manipulation statements, like inserts, updates, and deletes. TRUE OR FALSE

False

MySQL implements the MINUS set operator in lieu of the ANSI-compliant EXCEPT operator.

False

MySQL lets you disable auto commit with the following command: SET IMPLICIT_TRANSACTIONS ON TRUE OR FALSE

CEIL(), CEILING(), FLOOR(), ROUND(), TRUNCATE()

MySQL supports which of the following functions? (Multiple answers are possible.)

True

Queries using set operators can only have one ORDER BY clause.

True

SQL transaction statements determine where to begin, end, and roll back transactions.

True

The A except B result plus the B except A result leaves you with the rows in set A and B not found in both sets. This is the addition of the complement of A and complement of B and is known as the symmetrical difference (or mirrored difference) between the two sets.

False

The B-tree index works best with columns that have non-unique data. TRUE OR FALSE

True

The CAST() function lets you convert a varchar to a date data type.

True

The CEIL() function rounds up to the nearest integer. TRUE OR FALSE

False

The EXTRACT() function returns a string from a date value. TRUE OR FALSE

False

The MySQL server automatically commits all pending transactions when a SQL statement incurs a deadlock error. TRUE OR FALSE

True

The MySQL server rolls back automatically any transactions explicitly not committed when the server shuts down abruptly. TRUE OR FALSE

False

The NATURAL JOIN statement in the sakila database: SELECT DISTINCT f.film_id left_id , i.film_id right_id , f.title FROM film f NATURAL JOIN inventory i ORDER BY 1; It returns the following data set from the sakila database: +---------+----------+-----------------------------+ | left_id | right_id | title | +---------+----------+-----------------------------+ | 1 | 1 | ACADEMY DINOSAUR | | 2 | 2 | ACE GOLDFINGER | | 3 | 3 | ADAPTATION HOLES | | 4 | 4 | AFFAIR PREJUDICE | ... | 997 | 997 | YOUTH KICK | | 998 | 998 | ZHIVAGO CORE | | 999 | 999 | ZOOLANDER FICTION | | 1000 | 1000 | ZORRO ARK | +---------+----------+-----------------------------+ TRUE OR FALSE

True

The different MySQL engines govern how lock guarantees work. TRUE OR FALSE

True

The following statement lets you display indexes of a table (the guillemet or angle brackets are only to illustrate that any table, column, or constraint name are placeholders for valid tables, columns, or constraints of a database). mysql> ALTER TABLE <table_name> -> ADD CONSTRAINT <constraint_name>-> FOREIGN KEY (<column_name>)-> REFERENCES <reference_table> (<reference_column>); TRUE OR FALSE

False

The possible_keys column tells you which columns it must use. TRUE OR FALSE

False

Updatable view definitions may use the GROUP BY clause but not the clause. TRUE OR FALSE

False

Views are useful when you want to join partitioned tables with inner joins. TRUE OR FALSE

False

Views can store aggregated data columns only when the constructing query uses column aliases. TRUE OR FALSE

IN, =ANY

Which of the following "membership conditions" may replace a set of comparisons made against string literal values with the OR operator in the WHERE clause? (Multiple answers are possible.) mysql> SELECT title -> , rating -> FROM film -> WHERE rating = 'G' OR rating = 'PG';

True

You can join a table and a view. TRUE OR FALSE

False

You can use only some of the clauses of the SELECT statement when querying through a view. TRUE OR FALSE

True

You define table aliases by appending <table_alias> after the table name in the FROM clause.

True

You should ensure primary key columns are indexed. TRUE OR FALSE

False

You should index all foreign key columns with unique indexes. TRUE OR FALSE

True

You should only index what's needed because indexes are resource expensive. TRUE OR FALSE

True

"Column aliases" let you rename columns in the SELECT clause.

False

"Primary keys" are examples of redundant data in relational database systems.

Foreign keys in a relational database system are like lines that connect entities in a hierarchical or network database management system.

How many columns are returned by the following query? SELECT * FROM (SELECT 'Yes' AS reply , 'Decided' AS answer UNION ALL SELECT 'No' AS reply , 'Decided' AS answer UNION ALL SELECT 'Maybe' AS reply , 'Undecided' AS answer) a LEFT JOIN (SELECT 'Yes' AS reply UNION ALL SELECT 'No' AS reply) b ON a.reply = b.reply;

True

The following SELECT clause is the first clause of a SELECT statement but the last clause evaluated by the SQL engine.

JDBC, ADO.NET, Ruby DBI, Python DBI, Package Database/SQL

Which of the following are SQL integration toolkits? (Multiple answers are possible.)

Both data sets must have the same number of columns. The data types of each column across the two data sets must be the same.

Which of the following are guidelines that qualify how set operators work? (Multiple answers are possible.)

A table created by using the CREATE TABLE statement.

Which of the following best describes a permanent table?

True

A "condition" in the WHERE clause may compare a literal, column, or expression values to see if it is a null value by using the IS NULL operator.

True

A "condition" in the WHERE clause may compare equality between literal, column, or expression values.

True

A "database connection" gets generated when the server verifies your username and password are correct.

extensions False

A "database system" is nothing more than a set of related information. TRUE OR FALSE

True

A "network database system" exposes sets of records and links, where the links define relationships between different records.

True

A "primary key" consisting of two or more columns is a compound key.

False

A "range condition" in the WHERE clause may compare whether a literal, column, or expression values is between a set of literal, column, or expression values that include a NULL value. mysql> SELECT first_name -> , last_name -> FROM customer -> WHERE last_name BETWEEN 'FA' AND NULL;

False

A "range condition" in the WHERE clause may compare whether a literal, column, or expression values of a NULL value, like an optional middle_name column value, is between a set of literal, column, or expression values that exclude a NULL value. mysql> SELECT first_name -> , last_name -> FROM customer -> WHERE middle_name BETWEEN 'FA' AND 'G';

True

A "range condition" in the WHERE clause may compare whether a literal, column, or expression values of a NULL value, like an optional middle_name column value, is not between a set of literal, column, or expression values that exclude a NULL value. mysql> SELECT first_name -> , last_name -> FROM customer -> WHERE NOT middle_name BETWEEN 'FA' AND 'G';

True

A "surrogate key" is generated by database management systems as a unique set of numbers.

True

A WHERE clause can only filter in some data and filter out other data.

True

A WHERE clause supports one or more "conditions."

True

The DAYNAME() function in MySQL lets you display the day of the week for a date value: SELECT DAYNAME('1941-12-07') AS what_day; It returns the following: +----------+ | what_day | +----------+ | Sunday | +----------+

True

The LEFT JOIN returns all rows from the table on the left side of the join and only the rows that meet the join condition on the right side of the join. TRUE OR FALSE

False

True

The STR_TO_DATE() function in MySQL lets you convert a string that does not comply with the default date format: SELECT STR_TO_DATE('07-DEC-2016','%d-%M-%Y') AS newdate; It returns the following: +------------+ | newdate | +------------+ | 2016-12-07 | +------------+

True

Searched

Temporary table

True

The following GROUP BY clause lists the raw columns returned with the result of an aggregating function, like the COUNT(), SUM() or others. mysql> SELECT CONCAT(c.first_name,' ',c.last_name) AS full_name -> , COUNT(*) AS number_of_customer -> FROM customer c INNER JOIN rental r -> ON c.customer_id = r.customer_id -> GROUP BY c.first_name -> , c.last_name;

True

The following SELECT statement returns a string literal in a its result set: mysql> SELECT 222 AS room_number -> , 'Kotter' AS teacher; +-------------+---------+ | room_number | teacher | +-------------+---------+ | 222 | Kotter | +-------------+---------+

True

The following WHERE clause filters on the combination of one or the other of two criteria. mysql> SELECT title -> FROM film -> WHERE (rating = 'G' OR rental_duration >= 7) -> AND (rating = 'PG' OR rental_duration <= 3);

True

The following WHERE clause filters on the combination of two criteria. mysql> SELECT title -> FROM film -> WHERE rating = 'G' AND rental_duration >= 7;

True

The following mysql client command lets you connect to the MySQL studentdb database: [username@localhost ~] $ mysql -ustudent -p -Dstudentdb

False

The following query id: mysql> SELECT 1 num, 'one' str -> UNION -> SELECT 2 num, 'two' str -> UNION -> SELECT 3 num, 'three' str -> UNION -> SELECT 2 num, 'two' str; It returns a result like the following: +-----+-------+ | num | str | +-----+-------+ | 1 | one | | 2 | two | | 2 | two | | 3 | three | +-----+-------+

True

The following query uses a LEFT JOIN of the film and inventory tables returns everything in the film table on the left of the join operation. The WHERE clause checks for film_id columns in the film table that are null values, which means only the rows outside of the intersection are returned. SELECT f.film_id left_id , i.film_id right_id , f.title FROM film f LEFT OUTER JOIN inventory i ON f.film_id = i.film_id WHERE i.film_id IS NULL ORDER BY 1; TRUE OR FASLE

True

The following query uses a LEFT JOIN of the inventory and film tables returns everything in the inventory table on the left of the join operation. The WHERE clause checks for film_id columns in the inventory table that are null values, which means only the rows outside of the intersection are returned. SELECT f.film_id left_id , i.film_id right_id , f.title FROM film f RIGHT OUTER JOIN inventory i ON f.film_id = i.film_id WHERE i.film_id IS NULL ORDER BY 1; TRUE OR FALSE

True

The following query: mysql> SELECT 1 num, 'one' str -> UNION ALL -> SELECT 2 num, 'two' str -> UNION ALL -> SELECT 3 num, 'three' str -> UNION ALL -> SELECT 2 num, 'two' str -> ORDER BY 1, 2; It returns a result like the following: +-----+-------+ | num | str | +-----+-------+ | 1 | one | | 2 | two | | 2 | two | | 3 | three | +-----+-------+

True

To make the server ignore an apostrophe in a string, you need to add an escape character (like a \) before the apostrophe. TRUE OR FALSE

True

To make the server ignore an apostrophe in a string, you need to add an escape character, like another apostrophe before the apostrophe, which allows the second apostrophe to be treated as ordinary text. TRUE OR FALSE

having, select, from, where, order by

Which of the following are valid clauses in an SQL query? (Multiple answers are possible.)

A view created by using the CREATE VIEW statement.

Which of the following best describes a virtual table?

IN, >=, LIKE

Which of the following operators are supported by the searched CASE expression? (Multiple answers are possible.)

Oracle

Which of the following servers always has active transactions associated with a database session according to the textbook? (Multiple answers are possible.)

False

You should index every column in a table because indexes reduce query response time. TRUE OR FALSE

Database Design & Development Final Exam

Related study sets

anthropology midterm

Biology Quiz 2 Flash Cards

ITSP 135 chapter 7 review

Income Tax Chapter 1 Project

World History: The Industrial Revolution

Vulnerable Subjects - Research Involving Workers/Employees (ID 483)

A and P Chapter 15

Bio 1260 exam 3 - clicker question

Special ed Praxis

Citation words

arch 2

Micro 1

MA Casualty Insurance

APUSH Chapter 16 and 17

الدروز

Corporate Finance Test 1 Review

Pediatric Exam 1

Soil Science Final Review

Aging

Test 1