Pivoting

Question 1

T/F Aggregate functions ignore NULL values.

Answer 1

True

Question 2

COUNT( * ) is functionally equivalent to COUNT( column ) where column is a column that…

Answer 2

always returns a value (no NULLs).

Question 3

Does the COUNT function ignore NULL values?

Answer 3

Yes

Question 4

When pivoting non-aggregate functions and using MAX ( CASE … what else is necessary in your query?

Answer 4

the GROUP BY clause

Question 5

If you wanted your query to return results whether a value was recorded on the flowsheet or not how might you accomplish this?

Answer 5

Use a CTE with an inner join and MAX( CASE to return your recorded values. Then left join to the CTE in your main query.

Question 6

What are the requirements for using the UNION set operator?

Answer 6

same # columns, in the same order, same data types

Question 7

When using the UNION set operator which query decides the names of the columns?

Answer 7

The first SELECT clause

Question 8

How can you make a UNION set operator query more efficient?

Answer 8

Use UNION ALL which allows duplicate values. UNION returns only distinct values.

Question 9

What does the UNION set operator do?

Answer 9

appends two tables: returns a table that has all distinct rows of both of the original two tables.