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