MySQL Final Study Guide - Chapters 1 - 11 Quizes

summary

A SELECT statement that includes aggregate functions is often called a/an ____________________ query. aggregate SELECT total summary

data access model communicate with the database

A database driver is software that lets the data access model communicate with the database application program communicate with the database data access model communicate with the application program application program communicate with the data access model

unmatched rows from both the left and right tables

A full outer join returns unmatched rows from both the left and right tables rows in the left table that don't satisfy the join condition rows in the right table that don't satisfy the join condition the Cartesian product of the two tables

alias

A table _________________ can be used when you want to assign a temporary name to a table. alias widget module label

result sets

A union combines the rows from two or more _______________________. SELECT statements result tables queries result sets

COUNT(*)

All of the aggregate functions ignore null values, except for the ______________________________ function. KEY HAVING ERR COUNT(*)

32492.05

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

The number of items in the column list doesn't match the number in the VALUES list.

Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows? INSERT INTO invoices (vendor_id, invoice_number, invoice_total, payment_total, credit_total, terms_id, invoice_date, invoice_due_date) VALUES (97, '456789', 8344.50, 0, 0, 1, '2012-08-31') The column names in the column list are in the wrong sequence. There are too many items in the column list. There are too few items in the VALUES list. The number of items in the column list doesn't match the number in the VALUES list.

DELETE

By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue. ON DELETE AS COMMIT

the largest invoice total related to that row

Code example 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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please refer to code example 7-2.) When this query is executed, each row in the result table will show 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

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

Code example 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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please refer to code example 7-2.) When this query is executed, there will be one row 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

can use either aggregate search conditions or non-aggregate search conditions

Expressions coded in the HAVING clause can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions can use non-aggregate search conditions but can't use aggregate search conditions can refer to any column in the base tables

can use non-aggregate search conditions but can't use aggregate search conditions

Expressions coded in the WHERE clause can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions can use non-aggregate search conditions but can't use aggregate search conditions must refer to columns in the SELECT clause

-45

If expiration_date contains a value that's equivalent to November 2, 2011 and the SYSDATE function returns a value that's equivalent to December 17, 2011, what will the exp_days column contain when this code is executed? expiration_date - SYSDATE() AS exp_days -45 -1.5 45 1.5

ewilliam

If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution EWilliams EWilliam ewilliams ewilliam

a table

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) a single value a column of one or more rows a table 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? SELECT (subquery) a single value a column of one or more rows a table 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? WHERE (subquery) a single value a column of one or more rows a table 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 column of one or more rows a table a subquery can't be introduced in this way

a column of one or more rows

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

a column of one or more rows

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

17

If you TRUNCATE the number 17.99, you get a value of: 18 17 20 17.5

use that alias to refer to the table throughout your query

If you assign an alias to one table in a join, you have to use that alias to refer to the table throughout your query qualify all of the column names for that table assign them to all of the tables qualify every column name in the query

that doesn't include a value for that column is added to the table

If you define a column with a default value, that value is used whenever a row that doesn't include a value for that column is added to the table is added to the table with a zero value for that column is added to the table in the table is updated

WHERE

If you want to filter the rows that are returned by a SELECT statement, you must include a/an ______________ clause. SELECT TABLE WHERE GOTO

create a database diagram

In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it? run SQL scripts navigate through database objects create a database diagram code SQL statements

rows from the second table

In a cross join, all of the rows in the first table are joined with all of the matched rows in the second table unmatched columns in the second table distinct rows in the second table rows from the second table

when the same column names exist in both tables

In a join, column names need to be qualified only in inner joins when the code is confusing in outer joins when the same column names exist in both tables

equal

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

combines the result sets of two or more SELECT statements

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

integers

Numbers that don't include a decimal point are known as ____________________. integers real abstract fraction

real numbers

Numbers that include a decimal point are knows as ____________________. real numbers UNSIGNED ENUM integers

each vendor in the table named ia

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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC When this query is executed, the result table will contain one row for each vendor each vendor in the table named ia each invoice each invoice in the table named ia

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

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 When this query is executed, the number_of_invoices for each row will show the number 1 of invoices in the Invoices table of invoices for each vendor of invoices for each vendor that have 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 that balance due is larger than the average balance due for all invoices

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 When this query is executed, the result set will contain 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 that balance due is larger than the average balance due for all invoices

balance_due in descending sequence

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 When this query is executed, the rows will be sorted by invoice_id vendor_id balance_due in descending sequence vendor_id and then by balance_due in descending sequence

the number of rows in the Invoices table

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above the number of rows in the Invoices table the number of rows in the Vendors table

none of the above

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal the number of rows in the Vendors table the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above the number of rows in the Invoices table

The column name for the fifth column in the result set doesn't match the data.

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 (Please refer to code example 6-1.) Although this query runs as coded, it contains this logical error: The condition in the WHERE clause should be coded in the HAVING clause. The column name for the fifth column in the result set doesn't match the data. The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. The condition in the HAVING clause should be coded in the WHERE clause.

each vendor with invoice totals over $500

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 (Please refer to code example) When this summary query is executed, the result set will contain one summary row for each city with invoice totals over $500 each vendor with invoice totals over $500 each city with invoice average over $500 each vendor with invoice average over $500

dates only

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

time

The DATETIME data type includes not only the date, but also a ________________________. string value whole number time

mushrooms, sausage, peppers

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

CONCAT_WS

The _________ function concatenates a string with the specified separator string added in between. LTRIM CONCAT CONCAT_WS SUBSTRING_INDEX

CEILING

The __________ function returns the smallest integer that is greater than or equal to the number. FLOOR TRUNCATE SIGN CEILING

FROM

The __________________ clause of the SELECT statement specifies the table that contains the data. FROM AND OR WHERE

CAST

The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion. BREAK FORMAT CAST CONVERT

yyyy-mm-dd

The default date format for MySQL is _________________. mm/dd/yy mon-dd-yyyy yyyy-mm-dd yy/dd/mm

Not, And, Or

The order of precedence for the logical operators in a WHERE clause is Or, And, Not And, Or, Not Not, And, Or Not, Or, And

a result set

The result of a SELECT statement is a stored procedure a calculated value a result set

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

The six clauses of the SELECT statement must be coded in the following order: SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING

reduces redundancy and makes maintenance easier

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This reduces redundancy but makes maintenance more difficult reduces redundancy and makes maintenance easier increases redundancy but makes maintenance easier increases redundancy but makes the data more consistent

a single, scalar value

To be in the first normal form, each cell in a table must contain a unique value a non-unique value a single, scalar value a non-redundant value

each non-key column must depend only on the primary key

To be in the third normal form, each non-key column must depend only on the primary key each non-key column must not depend on the primary key each non-key column must contain repeating values all of the above

CONCAT

To concatenate character strings, you use the _________________ function in a string expression. WHERE CONCAT &gt;&gt; STRING

a, b, or c

To enforce referential integrity for a delete operation, a MySQL database can return an error instead of deleting any rows set the foreign key values in the related rows to null delete the related rows in the foreign key table a, b, or c a or c

subquery select

To insert rows selected from one or more tables into another table with an INSERT statement, you code a/an ___________________________ in place of the VALUES clause. subquery select join close like

DISTINCT

To prevent duplicate rows from being returned by a SELECT statement, you can code the _____________ keyword in the SELECT clause. DUPLICATE UNIQUE LAST DISTINCT

*

To return all of the columns from the base table, you can code the _______________ operator in the SELECT clause. * TOTAL ALL ?

implicit

When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion. implicit complex transmutation switch

invoice_total IN (0, 1000)

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less? invoice_total IN (0, 1000) invoice_total <= 1000 NOT (invoice_total > 1000) invoice_total BETWEEN 0 AND 1000

vendor_city REGEXP 'NA$'

When coded in a WHERE clause, which of the following would not return rows for vendors in the cities of San Diego and Santa Ana? vendor_city REGEXP '^SA' vendor_city LIKE 'SAN%' vendor_city REGEXP 'NA$'

payment_date IS NOT NULL AND invoice_total >= 500

When coded in a WHERE clause, which search condition will return invoices when payment_date isn't null and invoice_total is greater than or equal to $500? NOT (payment_date IS NULL AND invoice_total <= 500) payment_date IS NOT NULL OR invoice_total >= 500 payment_date IS NULL AND invoice_total > 500 payment_date IS NOT NULL AND invoice_total >= 500

SELECT, FROM, WHERE, ORDER BY

When you code a SELECT statement, you must code the four main clauses in the following order SELECT, FROM, ORDER BY, WHERE SELECT, FROM, WHERE, ORDER BY SELECT, ORDER BY, FROM, WHERE SELECT, WHERE, ORDER BY, FROM

null

When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values. string open null quote

The result sets must be derived from different tables.

When you code a union that combines two result sets, which of the following is not true? Each result set must have the same number of columns. The corresponding columns in the result sets must have compatible data types. The result sets may or may not have any duplicate rows. The result sets must be derived from different tables.

column name, alias, expression, or column number

When you code an ORDER BY clause, you can specify a column name or alias only column name, alias, or expression only column name or expression only column name, alias, expression, or column number

WHERE

When you code an UPDATE statement for one or more rows, the SET clause names the columns to be updated and the values to be assigned to those columns, and the ______________________ clause specifies the conditions a row must meet to be updated. JOIN SELECT GET WHERE

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

When you create 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

the smallest practical components

When you identify the data elements in a new database, you typically subdivide data elements into the largest practical components the smallest practical components components that can be easily parsed each time you use them

the likely cause of the error

When you run a SQL statement that contains a coding error, MySQL Workbench displays an error message that does not include an error code brief description of the error the likely cause of the error

the join must be based on a column or columns that have the same name in both tables

When you use the USING keyword for a join, the join can't be an outer join the join can't be done on more than one column the join must be based on a column or columns that have the same name in both tables the join must be an inner join

CREATE TABLE users_copy AS SELECT * FROM users;

Which SQL statement creates a complete copy of the users table call users_copy. SELECT * FROM users INTO users_copy; CREATE COPY users_copy FROM users; CREATE TABLE users_copy AS SELECT * FROM users; DUPLICATE users INTO users_copy;

DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')

Which code returns the date in the format Friday, April 20th, 2015? DATE_FORMAT('2014-04-20', '%W, %M %D, %Y') DATE_FORMAT('2015-04-20', '%m/%d/%y') DATE_FORMAT('2015-04-20', '%W, %M %D, %Y') DATE_FORMAT('2015-04-20', '%e-%b-%y')

you code a USING clause in addition to the ON clause

Which is not true about the USING keyword? you code a USING clause in addition to the ON clause the join must be an equijoin, meaning the equals operator is used to compare the two columns it can be used with inner or outer joins you use it to simplify the syntax for joining tables

WHERE vendor_name < 'D'

Which of the following WHERE clauses will return vendor names from A to C? WHERE vendor_name < 'D' WHERE vendor_name = 'D' WHERE vendor_name < 'C' WHERE vendor_name = D

invoice_total - credit_total - payment_total / 10

Which of the following expressions does not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total? ((invoice_total - payment_total) - credit_total) / 10 (invoice_total - payment_total - credit_total) / 10 invoice_total - credit_total - payment_total / 10 (invoice_total - (payment_total + credit_total)) * 0.10

Forgetting to connect to a database

Which of the following is not a common error when entering and executing SQL statements? Misspelling a keyword Forgetting to connect to a database Selecting the wrong database Misspelling the name of a table

The column is frequently updated.

Which of the following is not a good guideline for deciding when to create an index for a column? The column is a foreign key. The column is frequently updated. The column is frequently used in search conditions or joins. The column contains a large number of distinct values.

is an older syntax that works with legacy code

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax lets you separate the join and search conditions lets you combine inner and outer joins is easier to read and understand is an older syntax that works with legacy code

use the DATE_ADD function to remove the time values from the dates in each column

Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the DATETIME data type? use the MONTH, DAYOFMONTH, and YEAR functions in the WHERE clause to search for just those components search for a range of dates that are greater than or equal to the date you're looking for, and less than the date that follows the date you're looking for use the DATE_FORMAT function in the WHERE clause to return a formatted string that only contains the month, day, and year use the DATE_ADD function to remove the time values from the dates in each column

You cannot run all of the statements in the script at once.

Which of the following is not true when you're using MySQL Workbench to test a script that contains more than one SQL statement? When you run a script, the results are displayed in the Results tab. You cannot run all of the statements in the script at once. You can run each statement in the script by itself.

all of the above

Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause use the CAST function to convert the column values to numbers add zeros to the column values using + 0 use the LPAD function to pad the numbers with leading zeros all of the above

total sales

Which of the following isn't a valid column alias? total total sales "Total Sales" total_sales

Use comments to describe what each statement does.

Which of the following recommendations won't improve the readability of your SQL statements? Indent continued lines. Break long clauses into multiple lines. Use comments to describe what each statement does. Start each clause on a new line.

Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date.

Which of the following statements best describes what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN (SYSDATE() - invoice_date) >= 30 AND (SYSDATE() - invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN (SYSDATE() - invoice_date) >= 60 AND (SYSDATE() - invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN (SYSDATE() - invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices Displays three columns for each invoice with a value in the third column that indicates how many days have elapsed between the invoice date and the current date. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date. Displays three columns for each invoice with one of these messages in the third column: 30-60, 60-90, or Over 90. Displays five columns for each invoice with a message in one of the last three columns based on how many days have elapsed between the invoice date and the current date.

By default, the changes are automatically committed to the database.

Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements? By default, the changes are automatically committed to the database. If autocommit mode is on you can rollback the changes by clicking on the Rollback button. If autocommit mode is on you can commit the changes by clicking on the Commit button. By default, the changes are rolled back when you exit MySQL Workbench unless you commit them.

CREATE TABLE

Which of the following types of SQL statements is not a DML statement? UPDATE SELECT CREATE TABLE INSERT

SELECT

Which of the following types of statements does not modify the data in a table? SELECT UPDATE INSERT DELETE

LIMIT 4, 7

Which of the following would return a maximum of 7 rows, starting with the 5th row? LIMIT 4 LIMIT 4, 6 LIMIT 4, 7 LIMIT 7, 4

The total unpaid balance due for each vendor_id

Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_id, SUM(invoice_total - payment_total - credit_total) AS column_2 FROM invoices WHERE invoice_total - payment_total - credit_total > 0 GROUP BY vendor_id The unpaid balance for each invoice The total unpaid balance due for each vendor_id The total amount invoiced by each vendor_id The total of paid invoices for each vendor_id

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

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 The names of the vendors in each state The duplicate vendors from each state The number of vendors in each state The number of vendors in each state that has more than one vendor

they all use the same amount of storage

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

'ex' stored in a column of type VARCHAR2(20)

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

ROUND(payment_total, 1)

Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit. ROUND(payment_total, 1) ROUND(payment) ROUND(decimal) ROUND(total, 1)

select

You can combine inner and outer joins within a single ________________ statement. select where using xor

a union

You can simulate a full outer join by using a left outer join a union a self join the ON clause

SELECT

You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement. SELECT DELETE WHERE LIKE

function-based indexes

You can't code which one of the following as part of a CREATE TABLE statement: not null constraints column-level constraints table-level constraints function-based indexes

index

You should create an _______________ when a column is used frequently in search conditions or joins. DKNF index EER entity