CIT 225 Final Exam

Ace your homework & exams now with Quizwiz!

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

% _

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

** NOT 2

Which of the following are reasons for adopting the ANSI SQL92 syntax?

** NOT The join conditions for each pair are organized apart from filters. ** NOT Queries are portable across database servers.

Examining the following query, which type of ANSI SQL92 join is it using? mysql> SELECT c.first_name -> , c.last_name -> , a.address -> FROM customer c JOIN addrees a;

** not a full join

A subquery may consist of which of the following? (Multiple answers are possible.)

*A single row with a single column A single row with multiple columns *Multiple rows with a single column *Multiple rows having multiple columns None of the above ** only partially correct

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

1986, 1992, 2016

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;

How many rows 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 WHERE b.reply IS NOT NULL;

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;

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

== = IN

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';

=ANY IN

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

>= >ANY

Examining the following query, which type of ANSI SQL92 join is it using? mysql> SELECT c.first_name -> , c.last_name -> , a.address -> FROM customer c LEFT JOIN address a -> ON c.address_id = a.address_id;

A left join

Examining the following query, which type of ANSI SQL92 join is it using: mysql> SELECT c.first_name -> , c.last_name -> , a.address -> FROM customer c RIGHT JOIN address a -> ON c.address_id = a.address_id;

A right join

Which of the following are guidelines that qualify how set operators work? (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.

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

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

The EXTRACT() function returns which of the following from a date value? (Multiple answers are possible.)

DAY WEEK MONTH QUARTER YEAR

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

False

A "condition" in the WHERE clause may compare inequality, like > ANY, >= ANY, < ANY, or <= ANY, between literal, column, or expression values and a lists of values. mysql> SELECT 'True' AS answer -> WHERE 'a' < ANY('a', 'b', 'c');

False

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

False

A "database" is much more than a set of related information.

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 Cartesian product has a join like other joins on one or more columns from each table.

False

A WHERE clause can only filter out data.

False

A WHERE clause supports only one "condition."

False

A versioning locking strategy is where writers must request and receive a write lock to modify data and a query lock to read data, which enables one user to write a locked element of data while many users may read the same element of data.

False

Aggregation functions take only columns as arguments.

False

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

False

Data is seldom stored at the lowest level of granularity.

False

Does the NATURAL JOIN statement in the sakila database: mysql> SELECT f.film_id left_id , i.film_id right_id , f.title FROM (SELECT film_id , 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 | +---------+----------+----------------------

False

Examining the following query, it returns films with prequels and films without prequels in the Harry Potter series. mysql> SELECT f.title AS film -> , fp.title AS prequel -> FROM film f LEFT JOIN film fp -> ON f.prequel_id = fp.film_id -> WHERE f.series_name = 'Harry Potter' -> AND fp.series_name = 'Harry Potter' -> ORDER BY f.series_number;

False

Examining the following query, it returns only films with prequels in the Harry Potter series. mysql> SELECT DISTINCT -> f.title AS film -> , CASE -> WHEN NOT f.film_id = fp.film_id -> AND f.prequel_id = fp.film_id THEN -> fp.title -> END AS prequel_id -> FROM film f RIGHT JOIN film fp -> ON f.prequel_id = fp.film_id -> OR f.prequel_id IS NULL -> WHERE f.series_name = 'Harry Potter' -> AND fp.series_name = 'Harry Potter' -> ORDER BY f.series_number;

False

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

False

Foreign key constraints may restrict the allowable values in other tables.

False

If you switch the table on the left with the table on the right of a RIGHT JOIN, you always return null values on the right of the join operation.

False

Microsoft's "Active Directory" is a network database system.

False

Multi-column grouping is accomplished by a list of two or more columns in the ORDER BY clause.

False

MySQL does not support self-joins in version 8.

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

False

MySQL supports joins between two copies of the same table without table aliases.

False

MySQL's conditional logic DECODE() function is part of the SQL standard (SQL92).

False

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

False

The CAST function lets you convert a hexadecimal value to an extended character defined in the character set: SELECT CAST('0x86' AS CHAR) AS extended; It returns the following: +----------+ | extended | +----------+ | å | +----------+ 1 row in set (0.00 sec)

False

The CAST() function in MySQL lets you convert a string that does not comply with the default date format: SELECT CAST('07-DEC-2016' AS DATE) AS newdate; It returns the following: +------------+ | newdate | +------------+ | 2016-12-07 | +------------+ 1 row in set (0.03 sec)

False

The DATE_DIFF() function in MySQL lets you find the days between two dates: SELECT DATE_DIFF('2021-02-14','2020-12-25') AS days_between; It returns the following: +--------------+ | days_between | +--------------+ | 51 | +--------------+ 1 row in set (0.00 sec)

False

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

False

The MySQL proprietary conditional logic function is the DECODE() function offers only equality comparison.

False

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

False

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

False

The SHOW PLAN statement lets you ask the server to display a query's execution plan in MySQL.

False

The ^ operator in MySQL lets you raise the first value to the power of the second value: SELECT 2^16 AS result; It returns the following: +--------+ | result | +--------+ | 65536 | +--------+ 1 row in set (0.00 sec)

False

The avg() function returns the mode value within a set.

False

The char function by itself lets you display accented characters, like å in the latin1 character set: SELECT char(134) AS extended; It will display the following: +----------+ | extended | +----------+ | å | +----------+ 1 row in set (0.00 sec)

False

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

False

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

False

The count() function creates an explicit group when it is the only thing returned in the SELECT-list.

False

The following SELECT statement: mysql> SELECT database() AS database; It displays the following result set: +----------+ | database | +----------+ | sakila | +----------+ 1 row in set (0.00 sec)

False

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

False

False

The following correlated subquery and operator lets you find rows in the film_actor table that no longer support valid relationships between the actor and film tables. mysql> SELECT fa.actor_id -> , fa.film_id -> FROM film_actor fa -> WHERE (actor_id, film_id) NOT EXISTS -> (SELECT a.actor_id -> , f.film_id -> FROM actor a CROSS JOIN film f -> WHERE a.actor_id = fa.actor_id -> AND fa.film_id = f.film_id);

False

The following multicolumn subquery and operator lets you find rows in the film_actor table that no longer support valid relationships between the actor and film tables. mysql> SELECT actor_id -> , film_id -> FROM film_actor -> WHERE (actor_id, film_id) IN -> (SELECT a.actor_id -> , f.film_id -> FROM actor a INNER JOIN film_actor fa -> ON a.actor_id = fa.actor_id INNER JOIN film f -> ON fa.film_id = f.film_id -> WHERE a.last_name = 'MONROE' -> AND f.rating = 'PG');

False

The following multicolumn subquery and operator lets you find rows in the film_actor table that no longer support valid relationships between the actor and film tables. mysql> SELECT fa.actor_id -> , fa.film_id -> FROM film_actor fa -> WHERE EXISTS -> (SELECT NULL -> FROM actor a CROSS JOIN film f -> WHERE a.actor_id = fa.actor_id

False

The following multicolumn subquery and operator lets you find rows in the film_actor table that no longer support valid relationships between the actor and film tables. mysql> SELECT fa.actor_id -> , fa.film_id -> FROM film_actor fa -> WHERE EXISTS -> (SELECT NULL -> FROM actor a CROSS JOIN film f -> WHERE a.actor_id = fa.actor_id -> AND f.film_id = fa.film_id);

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 | +-----+-------+ 4 rows in set (0.11 sec)

False

The following query uses the COUNT function to return the first letter of the last_name column values from the customer table where the last_name column values that start with the letter 'M'. mysql> SELECT SUBSTR(last_name,1,1) AS first_letter -> , COUNT(last_name) -> FROM customer -> WHERE substr(last_name,1,1) = 'M' -> GROUP BY SUBSTR(last_name,1,1);

False

The following query uses the COUNT function to return the same number of first_name and middle_name column values from the customer table where only 25% of customers have a middle name. mysql> SELECT COUNT(first_name) -> , COUNT(middle_name) -> FROM customer;

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

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

False

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

False

The following subquery qualifies as a scalar subquery based on what it returns. mysql> SELECT film_id -> FROM film -> WHERE title LIKE '%HARRY'; It should return values like the following: +---------+ | film_id | +---------+ | 147 | | 478 | | 534 | +---------+ 3 rows in set (0.00 sec)

False

The following subquery qualifies as a working multiple-row subquery based on what it returns and the comparison operator used by the containing query. mysql> SELECT city_id -> , city -> FROM city -> WHERE country_id IN -> (SELECT country_id -> FROM country -> WHERE country = ('Canada','Mexico')); It should return values like the following: +---------+----------------------------+ | city_id | city | +---------+----------------------------+ | 179 | Gatineau | | 196 | Halifax | | 300 | Lethbridge | ... | 452 | San Juan Bautista Tuxtepec | | 541 | Torren | | 556 | Uruapan | | 563 | Valle de Santiago | | 595 | Zapopan | +---------+----------------------------+ 37 rows in set (0.21 sec)

False

The last table in the FROM clause is the driving table in a join of two or more tables.

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.

False

Updatable view definitions may use the DISTINCT clause to return a unique result set.

False

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

False

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

False

Views introduce design complexity by hiding joins.

False

You can not join a table and a view.

False

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

False

You may choose to include or exclude a table alias when you use a subquery as a data source in the FROM clause.

False

You see the following when connecting to the mysql client: MySQL Shell 8.0.21 Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. MySQL JS >

False

You should index all foreign key columns with unique indexes.

False

Examining the following query, which description best describes the following join? mysql> SELECT c.first_name -> , c.last_name -> , a.address -> FROM customer c INNER JOIN address a -> ON c.address_id = a.address_id;

INNER

Which of the following is the implementation of an ANSI SQL cross join in MySQL? (Multiple answers are possible.)

INNER JOIN CROSS JOIN JOIN LEFT JOIN RIGHT JOIN

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

MySQL Shell, MySQL

The following locking strategy is used by which of the following databases? (Multiple answers are possible.) A locking strategy 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.

Oracle MySQL

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

Page

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

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.

Searched

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 | +--------+ 1 row in set (0.00 sec)

Simple

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

Table

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

Table, Documents, Flat files

Temporary table

False

The following command inserts data to a TEST table: mysql> CREATE TABLE test -> ( test_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT=1001 -> , test_name VARCHAR(20)); Query OK, 0 rows affected (0.85 sec)

"Apache Directory Server" is a hierarchical database system.

True

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

True

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

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 with the REGEXP regular expression operator.

True

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

True

A "condition" in the WHERE clause may compare equality, like IN, between literal, column, or expression values and a lists of values. mysql> SELECT 'True' AS answer -> WHERE 'a' IN ('a', 'b', 'c');

True

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

True

A "condition" in the WHERE clause may compare the first character of a literal, column, or expression values with the LEFT() built-in function.

True

A "database" is nothing more than a set of related information.

True

A "hierarchical database" system configures data in a single-parent hierarchy.

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.

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 COUNT(*) function in the SELECT-list of a LEFT JOIN returns 0 for any row not found in the intersection of the LEFT JOIN results.

True

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

True

A CROSS JOIN yields a Cartesian result set.

True

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

True

A NATURAL JOIN lets you name the tables to be joined without specifying the join condition.

True

A SAVEPOINT sets a roll back place in the scope of a transaction.

True

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

True

A except B result is equivalent to subtracting the rows that are in both A and B from the A set.

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.

True

A modern "database system" can manage more than gigabytes of data.

True

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

True

A straight_join key word in MySQL requests that the tables are joined in a specific order.

True

A subquery is a query contained within another SQL statement.

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

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

True

A view is simply a mechanism for querying data.

True

Aggregate functions can work with columns and expressions as arguments.

True

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

True

Bitmap indexes work best with low cardinality data.

True

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

True

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

True

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

True

If you switch the table on the left with the table on the right of a LEFT JOIN, you always return null values on the right of the join operation.

True

Indexes are generally used by the server to quickly locate rows in a table.

True

Multi-column grouping is accomplished by a list of two or more columns in the GROUP BY clause.

True

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

True

MySQL supports joins between a table and the result from query because the query's result becomes a temporary table.

True

MySQL supports joins between two tables defined in the data dictionary.

True

MySQL uses the concat() function glues string and number data together.

True

MySQL uses the length() function to find the length of string data.

True

Primary key constraints guarantee uniqueness.

True

Prior to ANSI SQL92 syntax join conditions and filters were both put in the WHERE clause.

True

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

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';

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.

True

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

True

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

True

The DATE_ADD() function in MySQL lets you add a number of days to a date: SELECT DATE_ADD('2020-12-20', INTERVAL 5 DAY) AS christmas_day; It returns the following: +---------------+ | christmas_day | +---------------+ | 2020-12-25 | +---------------+ 1 row in set (0.00 sec)

True

The DISTINCT keyword lets you remove duplicates from the queries result set.

True

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

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! | +----------------------+ 1 row in set (0.00 sec)

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

The MOD() function provides a "modulo" operator equivalent in MySQL's implementation of SQL.

True

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

True

The MySQL database throws an exception when the string data is too large for the string data type.

True

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

True

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

True

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

True

The SELECT statements are database queries.

True

The SEQUEL language was shortened to SQL.

True

The VARCHAR holds a variable-length string of up to 65,535 characters in length.

True

The collection of the data in the data dictionary is also known as metadata.

True

The conditional logic CASE expression is built into the SQL grammar and works in the SELECT, INSERT, UPDATE, and DELETE statements.

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 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 CONCAT(c.first_name,' ',c.last_name);

True

The following ORDER BY clause sorts result set by column name in descending order for last_name and ascending order for first_name. 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 -> WHERE SUBSTRING(c.last_name,1,1) IN ('J','K','L','M') -> GROUP BY c.first_name -> , c.last_name -> HAVING COUNT(*) > 30 -> ORDER BY c.last_name DESC, first_name;

True

The following ORDER BY clause sorts the result set by column name in ascending order. 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 CONCAT(c.first_name,' ',c.last_name) -> HAVING COUNT(*) > 30 -> ORDER BY c.first_name;

True

The following command lets you discover a table's MySQL engine (the guillemet or angle brackets are only to illustrate that table, column, or constraint name is a placeholder for a valid table of a database). SHOW TABLE STATUS LIKE '<table_name>' \G

True

True

The following correlated subquery and operator lets you find rows in the film_actor table that no longer support valid relationships between the actor and film tables. mysql> SELECT fa.actor_id -> , fa.film_id -> FROM film_actor fa -> WHERE NOT EXISTS -> (SELECT NULL -> FROM actor a INNER JOIN film f -> WHERE a.actor_id = fa.actor_id -> AND f.film_id = fa.film_id);

True

The following multicolumn subquery qualifies as a working multiple-row subquery based on what it returns and the comparison operator used by the containing query. mysql> SELECT actor_id -> , film_id -> FROM film_actor -> WHERE (actor_id, film_id) IN -> (SELECT a.actor_id -> , f.film_id -> FROM actor a CROSS JOIN film f -> WHERE a.last_name = 'MONROE' -> AND f.rating = 'PG'); It should return values like the following: +----------+---------+ | actor_id | film_id | +----------+---------+ | 120 | 63 | | 120 | 144 | | 120 | 414 | | 120 | 590 | | 120 | 715 | | 120 | 894 | | 178 | 164 | | 178 | 194 | | 178 | 273 | | 178 | 311 | | 178 | 983 | +----------+---------+ 11 rows in set (0.05 sec)

True

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

True

The following query returns the maximum number of days between the return and rental dates. mysql> SELECT MAX(datediff(return_date, rental_date)) AS maximum_period -> FROM rental;

True

The following query uses the COUNT function to return the number of first_name column values from the customer table. mysql> SELECT COUNT(first_name) -> FROM customer;

True

The following query uses the COUNT function to return the number of rows in the customer table. mysql> SELECT COUNT(*) -> FROM customer;

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 | +-----+-------+ 4 rows in set (0.11 sec)

True

The following subquery qualifies as a working multiple-row subquery based on what it returns and the comparison operator used by the containing query. mysql> SELECT city_id -> , city -> FROM city -> WHERE country_id IN -> (SELECT country_id -> FROM country -> WHERE country IN ('Canada','Mexico')); It should return values like the following: +---------+----------------------------+ | city_id | city | +---------+----------------------------+ | 179 | Gatineau | | 196 | Halifax | | 300 | Lethbridge | ... | 452 | San Juan Bautista Tuxtepec | | 541 | Torren | | 556 | Uruapan | | 563 | Valle de Santiago | | 595 | Zapopan | +---------+----------------------------+ 37 rows in set (0.21 sec)

True

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.

True

The max() function returns the maximum value within a set.

True

The query demonstrates result set transformations with existence checking. mysql> SELECT a.first_name -> , a.last_name -> , CASE -> WHEN EXISTS (SELECT 1 -> FROM film_actor fa INNER JOIN film f -> ON fa.film_id = f.film_id -> WHERE fa.actor_id = a.actor_id -> AND f.rating = 'G') THEN 'Y' -> ELSE 'N' -> END AS g_actor -> FROM actor a -> WHERE a.last_name LIKE 'ki%';

True

The query demonstrates result set transformations. mysql> SELECT SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1 -> ELSE 0 -> END) may_rentals -> , SUM(CASE WHEN monthname(rental_date) = 'Jun' THEN 1 -> ELSE 0 -> END) may_rentals -> , SUM(CASE WHEN monthname(rental_date) = 'Jul' THEN 1 -> ELSE 0 -> END) may_rentals -> FROM rental -> WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01';

True

The sum() function returns the sum across a set.

True

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

True

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

True

Views can appear to store data that preaggregated because of the view definition that is stored in the database.

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

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

True

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

True

With a three table join, there are three tables and two join types in the FROM clause, and two on subclauses.

True

Without an index, queries scan all rows in a table.

True

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

True

You can execute a noncorrelated subquery inside or outside of the containing query.

True

You can join a table and a view.

True

You can use any clauses of the SELECT statement when querying through a view.

True

You can use subqueries in all four SQL data statements.

True

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

True

You must define <table_alias> when you have two or more copies of the same table in the FROM clause.

True

You should ensure primary key columns are indexed. Some database servers, including MySQL, automatically index primary key columns upon the creation of the primary key.

True

You should make sure that all foreign key columns are indexed with non-unique indexes. Some database servers, like MySQL, do this automatically.

True

Which of the following best describes a temporary table?

Volatile data held in memory.

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

CIT 225 Final Exam

Related study sets

Intermediate Accounting exam 2

ALL CH 1

Exam One leadership study set

Pediatrics

Introduction to Health Assessment Test

Quest 6: Coagulation and Hemostasis

QUIZ 3: HOW DO PLANTS WORK?

unit 2 progress check: mcq

Parallel Lines & Transversal Theorems

Exam answers

Human Growth and Development test #1

OC1 Quiz Questions

CareerSafe - EMERGENCY ACTION PLANS AND FIRE PROTECTION

Art Chp 7-9

Module 3: INTRO

Government Section 1 HW ?s

A&P Test 3

Chapter 4: Learning & Transfer or Training

CITI Social and Behavioral Research- Basic/Refresher

AP World History Unit 6 Quizzes