CPT 242
To sort the rows that are retrieved by a SELECT statement in descending sequence by invoice_total, you code this ORDER BY clause:
(/DESC/)
A subquery is a/an ____________________ statement that's coded within another SQL statement.
(/SELECT/)
Write an aggregate expression for the number of unique values in the vendor_id column:
(COUNT(DISTINCT vendor_id))
To return all of the columns from the base table, you can code the ________________ operator in the SELECT clause.
*
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.
What are the three hardware components of a client/server system?
1. Clients 2. Network 3. Server
___________________________ is a command-line tool that you can use for running SQL statements.
MySQL Command Line Client
You can use the SQL Editor tab to enter and ______________________ one or more SQL statements.
execute
To override the order of precedence in an arithmetic expression, you can use __________________.
parentheses
BLANK uniquely identifies each row in a table
primary key
To retrieve or update the client sends _________ to database
query
The result of a SELECT statement is
result set
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
Which of the following types of statements does not modify the data in a table?
select
You can combine inner and outer joins within a single _______________ statement.
select
When you code a SELECT statement, you must code the four main clauses in the following order
select, from, where, order by
Code example 4-1 SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id
table alias
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
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
Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id
the number of rows in the Invoices table
Which of the following isn't a valid column alias?
total sales
A full outer join returns
unmatched rows from both the left and right tables
A subquery can return a list of values, a table of values, or a single __________.
value
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$'
If you want to filter the rows that are returned by a SELECT statement, you must include a/an ___________________ clause.
where
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 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
Code example 4-1 SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id
inner
To search for null values, use the _________________ clause.
IS NULL
Code example 6-1 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 CONTAINS LOGICAL ERROR
The column name for the fifth column in the result set doesn't match the data.
You can simulate a full outer join by using
a union
Write an aggregate expression to calculate the average value of the invoice_total column, excluding null values:
AVG(invoice_total) Correct Response
Write an aggregate expression for the number of entries in the vendor_name column:
COUNT(vendor_name)
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.
DISTINCT
The ________________ clause of the SELECT statement specifies the table that contains the data.
FROM
To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.
LIKE
Which of the following would return a maximum of 7 rows, starting with the 4th row?
LIMIT 3, 7
Write an aggregate expression to find the vendor_name column that's last in alphabetical order:
MAX(vendor_name)
The MySQL Reference Manual can be found at the _______________ web site.
MySQL
When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500? Answer a b c or d
PaymentDate IS NOT NULL AND InvoiceTotal >= 500
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
A SQL script is a file that contains one or more ___________________.
SQL statements
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 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
Which of the following WHERE clauses will return vendor names from A to C?
WHERE vendor_name < 'D'
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
If introduced as follows, the subquery can return which of the values listed below?
a single value
Question 9 Unsaved If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)
a table
Unless you assign a/an _____________________________, the column name in the result set is the same as the column name in the base table.
alias
the processing that's done by the DBMS is typically referred to as
back-end-processing
Question 8 Unsaved Code example 7-1 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
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
To concatenate character strings, you use the ___________________ function in a string expression.
concat
In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it?
create a diagram database
Which of the following types of SQL statements is not a DML statement?
create table
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
Code example 7-2 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
Which of the following is not a common error when entering and executing SQL statements?
forgetting to connect to a database
When you code a subquery in a FROM clause, it returns a result set that can be referred to as an _____________________________ view.
inline
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
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) TRYING
In MySQL Workbench, you can start or stop the MySQL database server by clicking the Start Server button in the _______________________ window.
navigator
Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id
none of the above
The order of precedence for the logical operators in a WHERE clause is
not, and, or
Each database includes tables, views, and routines. These are called database ____________________.
objects
Question 7 Unsaved Code example 7-1 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
