Chapter 05 slides

Quiz

The six clauses of a SELECT statement must be coded in which order?

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

Expressions coded in the WHERE clause____

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

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 > 0

GROUP BY VendorID;

the total unpaid balance due for each VendorID

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

ROLLUP

Expressions coded in the HAVING clause____

can use either aggregate search conditions or non-aggregate search conditions

Which aggregate expression finds the VendorName column that’s last in alphabetical order?

MAX(VendorName)

Which of the statements below best describes the result set returned by this SELECT statement?

SELECT VendorState, COUNT(*) AS Column2

FROM Vendors

GROUP BY VendorState

HAVING COUNT(*) > 1;

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

By default, all duplicate values are included in an aggregation calculation unless you specify which keyword?

DISTINCT

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

Which aggregate expression gets the number of unique values in the VendorID column?

COUNT(DISTINCT VendorID)

Scalar function

operate on a single value and return a single value

Aggregate function

operate on a series of values and returns a single summary value

column function

AKA aggregate function

summary query

a query that contains one or more aggregate functions

scalar aggregate

aggregate function that return a single value for all the rows in a result set

vector aggregate

aggregate function that returns a set of values for each group of rows in a result

cumulative total

an aggregate function that calculates the running total of a set of values over a specified range or period.

moving average

a calculation that analyzes data points by creating averages of different subsets of the complete data set over time.