6 Quiz

Write an aggregate expression to calculate the average value of the invoice_total column, excluding null values: BLANK

AVG(invoice_total)

All of the aggregate functions ignore null values, except for the BLANK function.

COUNT(*)

Write an aggregate expression for the number of unique values in the vendor_id column: BLANK

COUNT(DISTINCT vendor_id)

By default, all duplicate values are included in the aggregate calculation, unless you specify the BLANK keyword.

DISTINCT

Write an aggregate expression to find the vendor_name column that's last in alphabetical order: BLANK

MAX(vendor_name)

Write an aggregate expression to find the oldest date in the invoice_date column: BLANK

MIN(invoice_date)

The six clauses of the SELECT statement must be coded in the following order: SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

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 (Please refer to code example 6-1.) Although this query runs as coded, it contains this logical error: The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. The column name for the fifth column in the result set doesn't match the data. The condition in the WHERE clause should be coded in the HAVING clause. The condition in the HAVING clause should be coded in the WHERE clause.

The column name for the fifth column in the result set doesn't match the data.

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 names of the vendors in each state The duplicate vendors from each state The number of vendors in each state The number of vendors in each state that has more than one vendor

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 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 amount invoiced by each vendor_id The unpaid balance for each invoice The total of paid invoices for each vendor_id The total unpaid balance due for each vendor_i

The total unpaid balance due for each vendor_i

When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the BLANK operator.

WITH ROLLUP

Expressions coded in the HAVING clause can use non-aggregate search conditions but can't use aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions can refer to any column in the base tables can use either aggregate search conditions or non-aggregate search conditions

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 must refer to columns in the SELECT clause can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions

can use non-aggregate search conditions but can't use aggregate search conditions

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 (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 average over $500 each city with invoice average over $500 each city with invoice totals over $500 each vendor with invoice totals over $500

each vendor with invoice totals over $500

A SELECT statement that includes aggregate functions is often called a/an BLANK query.

summary