ch 18
All of the aggregate functions ignore null values, except for the ________________ function.
(/COUNT(*)/)
Write an aggregate expression that returns the number of non-null values in the vendorName column:________________.
(/COUNT(vendorName)/)
The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.
(/FROM/)
In most cases, the join condition of an inner join compares the primary key of one table to the ________________ key of another table.
foreign
Which of the following is NOT a comparison operator in a SQL statement?
!=
The WHERE clause in a SELECT statement is executed before the rows are grouped by a GROUP BY clause. In contrast, the ________________ clause is executed after the rows are grouped.
(/HAVING/)
Write an aggregate expression to find the oldest date in the invoiceDate column:________________.
(/MIN(invoiceDate)/)
If a database allows null values in a column, you can use the ________________ operator in a WHERE clause to retrieve only those rows that have a null value in that column.
(/NULL/)
A subquery can return a result set, a column of one or more rows, or ________________ value
(/one/, /a single/)
________________ functions perform calculations on the values in a set of selected rows in a SQL query.
Aggregate
If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ________________ keyword in the VALUES clause of the INSERT statement.
DEFAULT
To sort the records that are retrieved by a SELECT statement in descending sequence by invoice_total, you code ________________ at the end of the ORDER BY clause.
DESC
In a SQL statement, the________________ clause specifies the table or tables to retrieve data from.
FROM
The ________________ statement can be used in a SQL statement to add a new row to a table.
INSERT
To retrieve rows in which an expression matches a string pattern called a mask, you can use the ________________ operator followed by the mask.
LIKE
Which of the following is an operator that can be used in the WHERE clause of a SQL statement to use wildcards to match a pattern or portion of a string?
LIKE
Which clause in a SQL statement will only allow the retrieval of a maximum number of rows?
LIMIT
Which of the following LIMIT clauses will return a maximum of five rows starting with the eleventh row in the result set?
LIMIT 10, 5
In a SQL statement, the ________________ clause specifies how to sort the rows.
ORDER BY
A subquery is a/an ________________ statement that's coded within another SQL statement.
SELECT
In a SQL statement, the ________________ clause specifies the columns to retrieve.
SELECT
Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows?INSERT INTO invoices(vendorID, invoiceNumber, invoiceTotal, paymentTotal, creditTotal,termsID, invoiceDate, invoiceDueDate)VALUES(97, '456789', 8344.50, 0, 0, 1, '2017-08-29')
The number of items in the column list doesn't match the number in the VALUES list
Which of the statements below best describes the result set returned by this SELECT statement?SELECT vendorState, COUNT(*) AS column2FROM vendorsGROUP BY vendorStateHAVING COUNT(*) > 1
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 vendorID,SUM(invoiceTotal - paymentTotal - creditTotal) AS column2FROM invoicesWHERE invoiceTotal - paymentTotal - creditTotal > 0GROUP BY vendorID
The total unpaid balance due for each vendor ID
In a SQL statement, the ________________ clause specifies the rows to retrieve.
WHERE
When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the ________________ clause specifies which row or rows are to be updated.
WHERE
If introduced as follows, the subquery can return which of the values listed below?WHERE 2 < (subquery)
a single value
If introduced as follows, the subquery can return which of the values listed below?FROM (subquery)
a table
A table ________________ can be used when you want to assign a temporary name to a table and use it in the join condition of a SELECT statement.
alias
To specify the name of a column in the result set of a SQL query, you can specify a column ________________.
alias
Unless you assign a/an ________________, the column name in the result set is the same as the column name in the base table.
alias
Which of the following is used as a wildcard in a SQL statement to specify that all columns should be retrieved?
asterisk (*)
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
A ________________ is used in a SQL statement to combine columns from two or more tables into a result set.
join
Which type of operators can be used to create compound conditions in a SQL statement?
logical
Code example 18-1SELECT vendorName, COUNT(*) AS numberOfInvoices,MAX(invoiceTotal - paymentTotal - creditTotal) AS balanceBueFROM vendors vJOIN invoices iON v.vendorID = i.vendorID WHERE invoiceTotal - paymentTotal - creditTotal >(SELECT AVG(invoiceTotal - paymentTotal - creditTotal)FROM invoices)GROUP BY vendorNameORDER BY balanceBue DESC (Please refer to code example 18-1.) When this query is executed, the numberOfInvoices column 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
Code example 18-1SELECT vendorName, COUNT(*) AS numberOfInvoices,MAX(invoiceTotal - paymentTotal - creditTotal) AS balanceBueFROM vendors vJOIN invoices iON v.vendorID = i.vendorID WHERE invoiceTotal - paymentTotal - creditTotal >(SELECT AVG(invoiceTotal - paymentTotal - creditTotal)FROM invoices)GROUP BY vendorNameORDER BY balanceBue DESC (Please refer to code example 18-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
When coded in a WHERE clause, which search condition will return invoices when paymentDate isn't null and invoiceTotal is greater than or equal to $500?
paymentDate IS NOT NULL AND invoiceTotal >= 500
The LIMIT clause limits the number of ________________ that can be returned by a SELECT statement.
rows
Which of the following isn't a valid column alias?
total sales
If you assign an alias to one table in a join, you have to
use that name for the table throughout the query
In a join, column names need to be qualified only
use that name for the table throughout the query