Which tab within MySQL Workbench will display error messages for a failed SQL statement?
Front
Output
Back
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?
Front
invoice_total - credit_total - payment_total / 10
Back
A relational database consists of one or more __________________.
Front
Tables
Back
The __________________ clause of the SELECT statement specifies the table that contains the data.
Front
FROM
Back
Which of the following isn't a valid column alias?
Front
total sales
Back
In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.
Front
equal
Back
To return all of the columns from the base table, you can code the _______________ operator in the SELECT clause.
Front
*
Back
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?
Front
invoice_total IN (0, 1000)
Back
A database driver is software that lets the
Front
data access model communicate with the database
Back
The processing that's done by the DBMS is typically referred to as
Front
back-end processing
Back
You can simulate a full outer join by using
Front
a union
Back
When you use the USING keyword for a join,
Front
the join must be based on a column or columns that have the same name in both tables
Back
You can combine inner and outer joins within a single ________________ statement.
Front
select
Back
To concatenate character strings, you use the _________________ function in a string expression.
Front
CONCAT
Back
When you code an ORDER BY clause, you can specify a
Front
column name, alias, expression, or column number
Back
Code example 4-2
SELECT vendor_name, invoice_number
FROM invoices LEFT JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
(Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal
Front
none of the above
Back
Which of the following recommendations won't improve the readability of your SQL statements?
Front
Use comments to describe what each statement does.
Back
Code example 4-2
SELECT vendor_name, invoice_number
FROM invoices LEFT JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
(Refer to code example 4-2.) The total number of rows returned by this query must equal
Front
the number of rows in the Invoices table
Back
If you want to filter the rows that are returned by a SELECT statement, you must include a/an ______________ clause.
Front
WHERE
Back
When you code a SELECT statement, you must code the four main clauses in the following order
Front
SELECT, FROM, WHERE, ORDER BY
Back
A table _________________ can be used when you want to assign a temporary name to a table.
Front
alias
Back
What is the default port number the MySQL server listens on for client connections?
Front
3306
Back
Which of the following WHERE clauses will return vendor names from A to C?
Front
WHERE vendor_name < 'D'
Back
A full outer join returns
Front
unmatched rows from both the left and right tables
Back
In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it?
Front
create a database diagram
Back
In a join, column names need to be qualified only
Front
when the same column names exist in both tables
Back
The result of a SELECT statement is
Front
a result set
Back
When you run a SQL statement that contains a coding error, MySQL Workbench displays an error message that does not include
Front
the likely cause of the error
Back
When you code a union that combines two result sets, which of the following is not true?
Front
The result sets must be derived from different tables.
Back
Which is not true about the USING keyword?
Front
you code a USING clause in addition to the ON clause
Back
If you define a column with a default value, that value is used whenever a row
Front
that doesn't include a value for that column is added to the table
Back
The order of precedence for the logical operators in a WHERE clause is
Front
Not, And, Or
Back
A union combines the rows from two or more _______________________.
Front
result tables
Back
A/An ____________________________ diagram can be used to show how the tables in a database are defined and related..
Front
Entity Relationship
Back
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?
Front
vendor_city REGEXP 'NA$'
Back
Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union
Front
combines the result sets of two or more SELECT statements
Back
Which of the following types of SQL statements is not a DML statement?
Front
CREATE TABLE
Back
To prevent duplicate rows from being returned by a SELECT statement, you can code the _____________ keyword in the SELECT clause.
Front
DISTINCT
Back
What symbols are used to form a block comment within a SQL program?
Front
//
Back
Which of the following is not a common error when entering and executing SQL statements?
Front
Forgetting to connect to a database
Back
The interface between an application program and the DBMS is usually provided by the
Front
data access API
Back
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax
Front
is an older syntax that works with legacy code
Back
If you assign an alias to one table in a join, you have to
Front
use that alias to refer to the table throughout your query
Back
In a cross join, all of the rows in the first table are joined with all of the
Front
rows from the second table
Back
The three main hardware components of a client/server system are the clients, the server, and the ___________________________.
Front
Network
Back
A/An _________________ statement is used to retrieve selected columns and rows from a base table.
Front
SELECT
Back
Which of the following types of statements does not modify the data in a table?
Front
SELECT
Back
Which of the following would return a maximum of 7 rows, starting with the 5th row?
Front
LIMIT 4, 7
Back
Which of the following is not true when you're using MySQL Workbench to test a script that contains more than one SQL statement?
Front
You cannot run all of the statements in the script at once.
Back
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?
Front
payment_date IS NOT NULL AND invoice_total >= 500
Back
Section 2
(50 cards)
The ________________ clause of the SELECT statement specifies the table that contains the data.
Front
From
Back
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
Front
The number of vendors in each state that has more than one vendor
Back
If introduced as follows, the subquery can return which of the values listed below?
WHERE vendor_id NOT IN (subquery)
Front
a column of one or more rows
Back
Code example
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
(Please refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number
Front
of invoices for each vendor that have a larger balance due than the average balance due for all invoices
Back
A table ___________________ can be used when you want to assign a temporary name to a table.
Front
alias
Back
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, the result table will contain one row for
Front
each vendor in the table named ia
Back
To concatenate character strings, you use the ___________________ function in a string expression.
Front
CONCAT
Back
By default, all duplicate values are included in the aggregate calculation, unless you specify the __________________ keyword.
Front
DISTINCT
Back
If introduced as follows, the subquery can return which of the values listed below?
FROM (subquery)
Front
a table
Back
A subquery can be coded in a WHERE, FROM, SELECT or ______________________ clause.
Front
HAVING
Back
All of the following values can be stored in a column that's defined as DECIMAL(6,2), except
Front
32492.05
Back
Code example
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.) When this summary query is executed, the result set will contain one summary row for
Front
each vendor with invoice totals over $500
Back
The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type?
Front
mushrooms, sausage, peppers
Back
If introduced as follows, the subquery can return which of the values listed below?
WHERE (subquery)
Front
a subquery can't be introduced in this way
Back
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')
Front
The number of items in the column list doesn't match the number in the VALUES list.
Back
A SELECT statement that includes aggregate functions is often called a/an ____________________ query.
Front
summary
Back
Which statement from the MySQL command line client is used to select the database you want to work on?
Front
use
Back
The default date format for MySQL is _________________.
Front
yyyy-mm-dd
Back
The DATE data type can store
Front
dates only
Back
If introduced as follows, the subquery can return which of the values listed below?
WHERE 2 < (subquery)
Front
a single value
Back
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.
Front
null
Back
Which flag option is available to specify the username when using the MySQL command line client?
Front
-u
Back
SQL statements in MySQL are case-sensitive.
Front
False
Back
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
Front
foreign
Back
When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the ___________________ operator.
Front
with rollup
Back
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.
Front
WHERE
Back
You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement.
Front
SELECT
Back
Write the code for a DELETE statement that deletes every row in the Invoices_Copy table:
Front
DELETE FROM Invoices_Copy
Back
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
Front
for each vendor with an average invoice total that's greater than 100
Back
The six clauses of the SELECT statement must be coded in the following order:
Front
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Back
SELECT vendor_name, invoice_date
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id;
Refer to code example. This type of join is called a/an __________________ join.
Front
inner
Back
If introduced as follows, the subquery can return which of the values listed below?
SELECT (subquery)
Front
a single value
Back
All of the aggregate functions ignore null values, except for the _____________________ function.
Front
count(*)
Back
What symbol is placed at the end of a SQL statement before it will be executed?
Front
;
Back
MySQL Workbench has both an Apply and Revert button.
Front
True
Back
Code example
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
(Please refer to code example 7-1.) When this query is executed, the result set will contain
Front
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
Back
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
Front
The total unpaid balance due for each vendor_id
Back
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.
Front
DISTINCT
Back
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.
Front
subquery select
Back
You can combine inner and outer joins within a single _______________ statement.
Front
select
Back
By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue.
Front
DELETE
Back
Code example
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:
Front
The column name for the fifth column in the result set doesn't match the data.
Back
Expressions coded in the HAVING clause
Front
can use either aggregate search conditions or non-aggregate search conditions
Back
Which SQL statement creates a complete copy of the users table call users_copy.
Front
CREATE TABLE users_copy AS SELECT * FROM users;
Back
Expressions coded in the WHERE clause
Front
can use non-aggregate search conditions but can't use aggregate search conditions
Back
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
Front
the largest invoice total related to that row
Back
To sort the rows that are retrieved by a SELECT statement in descending sequence by invoice_total, you code this ORDER BY clause:
ORDER BY invoice_total ________________
Front
DESC
Back
Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements?
Front
By default, the changes are automatically committed to the database.
Back
If introduced as follows, the subquery can return which of the values listed below?
WHERE invoice_total > ALL (subquery)
Front
a column of one or more rows
Back
Code example
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
(Please refer to code example 7-1.) When this query is executed, the rows will be sorted by
Front
balance_due in descending sequence
Back
Section 3
(50 cards)
When you code a subquery in a FROM clause, it returns a result set that can be referred to as an ____________________ view.
Front
inline
Back
Numbers that include a decimal point are knows as ____________________.
Front
real numbers
Back
When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion.
Front
implicit
Back
To return all of the columns from the base table, you can code the ________________ operator in the SELECT clause.
Front
*
Back
Numbers that don't include a decimal point are known as ____________________.
Front
integers
Back
When you identify the data elements in a new database, you typically subdivide data elements into
Front
the smallest practical components
Back
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
Front
ewilliam
Back
All of the aggregate functions ignore null values, except for the ______________________________ function.
Front
COUNT(*)
Back
The ______________ function lets you test an expression and return one value if the expression is true and another value if the expression is false.
Front
IF
Back
The _________________ data type is used for fixed-length strings, which use the same amount of storage for each value regardless of the actual length of the string.
Front
CHAR
Back
Which of the following is not a good guideline for deciding when to create an index for a column?
Front
The column is frequently updated.
Back
To enforce referential integrity for a delete operation, a MySQL database can
Front
a, b, or c
Back
The _________ function concatenates a string with the specified separator string added in between.
Front
CONCAT_WS
Back
The __________ function returns the smallest integer that is greater than or equal to the number.
Front
CEILING
Back
The DATETIME data type includes not only the date, but also a ________________________.
Front
time
Back
You can use the __________________ keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery.
Front
Any
Back
Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit.
Front
ROUND(payment_total, 1)
Back
The _____________________ of a real number indicates the total number of digits that can be stored in the data type.
Front
precision
Back
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
Front
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.
Back
Typically, most database designers consider a database structure normalized if it's in the ________________________ normal form.
Front
Third
Back
Which code returns the date in the format Friday, April 20th, 2015?
Front
DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
Back
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?
Front
use the DATE_ADD function to remove the time values from the dates in each column
Back
Write a SQL statement that creates a complete copy of the vendors table call vendors_backup.
Front
CREATE TABLE vendors_backup AS
SELECT *
FROM vendors
Back
If two tables have a one-to-many relationship, you need to add a _______________________ column to the table on the "many" side.
Front
Foreign Key
Back
The COALESCE function provides one way to substitute other values for __________________________ values.
Front
Null Values
Back
Code example
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
Front
each vendor with invoice totals over $500
Back
Use the _______________ function to find a character in a string.
Front
LOCATE
Back
When you code a subquery in a FROM clause, you must assign a/an ___________________ to it.
Front
alias
Back
Write an aggregate expression for the number of entries in the vendor_name column.
Front
SELECT COUNT(vendor_name) as vendor_name_entries
FROM vendors
Back
To normalize a data structure, you apply the ______________________ in sequence.
Front
Forms
Back
The ____________________ data types can store large, unstructured data such as text, images, sound, and video.
Front
large object
Back
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
Front
-45
Back
A subquery can return a list of values, a table of values or a single ____________________.
Front
Value
Back
You should create an _______________ when a column is used frequently in search conditions or joins.
Front
index
Back
Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type:_______________________________
Front
SELECT invoice_date
CONVERT(DATE, DATETIME) as invoice date
FROM Invoice
Back
The vendor ASC Signs moved from Fresno to Los Angeles. Use an UPDATE statement to change the vendor_city to Los Angeles and vendor_zip_code to 90025 for this vendor.
Front
UPDATE vendors
SET vendor_city = "Los Angeles",
vendor_zip_code = "90025"
WHERE vendor_name = "ASC Signs"
Back
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
Front
reduces redundancy and makes maintenance easier
Back
Each table in a database should have a _____________________________ that uniquely identifies each row.
Front
Primary Key
Back
A/An ______________model is a representation of the entities, or objects, of the database including the tables, views, and stored programs.
Front
EER
Back
Write the code for a DELETE statement that deletes every row in the Invoices_Copy table.
Front
DELETE FROM Invoices_Copy
Back
Write an INSERT statement to insert a row in the general_ledger_accounts table using these values
account_number: 82915
account_description: Main Street Books
Front
INSERT INTO invoices
(account_number, account_description)
VALUES
(82915, Main Street Books)
Back
The ______________ function returns the string with any leading spaces removed.
Front
LTRIM
Back
You can't code which one of the following as part of a CREATE TABLE statement:
Front
function-based indexes
Back
The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion.
Front
CAST
Back
When you create a script for creating a database:
Front
you need to create the referred to tables before you create the tables that refer to them
Back
If you TRUNCATE the number 17.99, you get a value of:
Front
17
Back
You use the _________________ to add a specified number of date parts to a date.
Front
DATE_ADD
Back
Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause
Front
all of the above
Back
To be in the third normal form,
Front
each non-key column must depend only on the primary key
Back
To be in the first normal form, each cell in a table must contain
Front
a single, scalar value
Back
Section 4
(13 cards)
To generate unique numbers in sequence, you use the ________________ attribute.
Front
AUTO_INCREMENT
Back
To delete all data from a table without deleting the definition for the table, you use the ______________ statement.
Front
TRUNCATE TABLE
Back
Which of the following is true about creating indexes?
Front
All of the above
Back
If you want to create an index that doesn't allow duplicate values, you use the ____________________________ keyword in the CREATE INDEX statement.
Front
UNIQUE
Back
The latin1 character set uses how many bytes per character?
Front
one
Back
When you define a column with the DEFAULT attribute:
Front
the default value is used if another value isn't specified when a row is added to the database.
Back
Which is true when you define a column as the primary key?
Front
All of the above.
Back
Which feature does the MyISAM storage engine not support?
Front
foreign keys
Back
The default storage engine for MySQL 5.5 and later. This engine supports foreign keys and transactions.
Front
InnoDB
Back
To modify the structure of an existing table, you use the _______________________ statement.
Front
ALTER TABLE
Back
If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, the default setting for null values is __________________________.
Front
NULL
Back
When you use a script to create all of the tables for a database, you must start with the tables that don't have _____________________________.
Front
Foreign Keys
Back
In the collation, utf8_spanish_ci, the ci stands for: