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