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