Live Lecure Materials Week 11

Question 1

You can sort results using the

Answer 1

ORDER BY clause

Question 2

You can sort in __________ (___) or __________ (____) order

Answer 2

Ascending (ASC) or descending (DSC)

Question 3

Count(*), COUNT({Name}), SUM, AVG, MIN, MAX are examples of

Answer 3

Aggregate functions

Question 4

Count(*)

Answer 4

Counts the number of rows in a table

Question 5

Count({Name})

Answer 5

Counts rows where the column name is not null

Question 6

Min

Answer 6

Calculates the minimum value of all values

Question 7

Max

Answer 7

Calculates the maximum of all values

Question 8

Aggregate functions allow you to view a ______ piece of data from ________ pieces of data

Answer 8

single, multiple

Question 9

Most normal functions operate on a ______ ___, most aggregate functions operate on ________ ____

Answer 9

single row, multiple rows

Question 10

Put these in order for a SUM function

Sum( OrderTotal )
FROM
AS OrderSum
RETAIL_ORDER
SELECT

Answer 10

SELECT SUM( OrderTotal ) AS OrderSum FROM RETAIL_ORDER

Question 11

Clause that specifies which rows will be used to determine the groups

Answer 11

WHERE

Question 12

Clause that specifies which groups will be used in the final result

Answer 12

HAVING

Question 13

T/F - The WHERE clause should be placed before the GROUP BY clause

Answer 13

True

Question 14

T/F - HAVING applies before WHERE

Answer 14

False

Question 15

T/F - Rows can be grouped by more than one column

Answer 15

True

Question 16

Groups rows that have the same values into summary rows

Answer 16

GROUP BY

Question 17

If there is a column in the SELECT clause that is not used by any other functions in a statement, it must

Answer 17

be used in the group by clause. Otherwise the query will return an error.

Question 18

1 of the 2 limits to built-in aggregate functions

Answer 18

You cannot combine table column names with aggregate functions

Question 19

2 of the 2 limits to SQL built in functions

Answer 19

You cannot use aggregate functions in the WHERE clause

Question 20

SELECT OrderNumber, SKU
FROM ORDER_ITEM
WHERE (Quantity * Price) <> ExtendedPrice
ORDER BY OrderNumber, SKU;

This returns no results, why?

Answer 20

There are no rows where Quantity * Price does not equal ExtendedPrice. It is working correctly.