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