Chapter 5

Write an aggregate expression to calculate the average value of the InvocieTotal column, excluding null values

AVG(InvoiceTotal)

Which functions perform a calculation on the values of a column from selected rows?

Aggregate

Write an aggregate expression for the number of entries in the VendorName column, including null values

COUNT (*)

Write an aggregate expression for the number of unique values in the VendorID column

COUNT(DISTINCT VendorID)

Write an aggregate expression for the number of unique values in the VendorID column

COUNT(DISTINVT VendorID)

All of the aggregate functions ignore null values, except for which function?

COUTN(*)

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 condtions

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

DISTINCT

Consider the following code example SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM( InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState <'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName;

Each vendor with invoice totals over $500

You can use the OVER clause with an aggregate function to

Include the rows used to calculate the summary in the result set

The CUBE operator is similar to the ROLLUP operator except that

It adds summary rows for every combination of groups

Write an aggregate expression to find the latest date in the InvoiceDate column

MAX(InvoiceDate)

Write an aggregate expression to find the VendorName column that's last is alphabetical order

MAX(VendorName)

When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding with operator?

ROLLUP

The six clauses of the SELECT statement must be coded in the following oder:

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

A SELECT Statement that includes aggregate functions is often called a/an ________ query

Summary

Consider the following code example: SELECT VendorState, VendorCity, VendorName, COUNT (*) AS InvoiceQty, SUM( InvoiceTotal) AS InvoiceAVg FROM Invoices JOIN Vendors ON INvoices.VendorID= Vendors.VendorID WHERE VendorState < 'e' GROUP By VendorState, VendorCity, VendorName Having SUM (InvoiceTotal)>500 ORDER By VendorState, VendorCity, VendorName;

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 returend by this SELECT statement? SELECT VendorState, COUNT(*) AS Column2 FROM Vendors GROUPS BY VendorState HAVING COUNT(*) >1;

The number of vendors in each state having more than one vendor

Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID, SUM( Invoice - PaymentTotal - CreditTotal) as Column2 FROM Invocies WHERE InvoicesTotal - PaymentTotal - CreditTotal > 0 Group BY VendorID

The total unpaid balance due for each VendorID

Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal- PaymentTotal- CreditTotal > Group BY VendorID;

The total unpaid balance due for each VendorID

A search condition in the _______ clause is applied before the rows are grouped while a search condition in the ______ clause isn't applied until after the grouping.

WHERE, HAVING

The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it

all of the above Includes summary rows adds summary rows for specified groups allows you to use addtional sets or parenthesesw to create compostie groups