SQL P1

Question 1

Window Syntax

Answer 1

WDFn() over (partition by [col] order by [col]) as new_col_name

Question 2

WDFn: RANK()

Answer 2

same # => same Rank
leaves gaps after repeated ranks
nulls given same rank

Question 3

WDFn: DENSE_RANK()

Answer 3

same # => same Rank
no gap after repeated rank

Question 4

WDFn:ROW_NUMBER()

Answer 4

just a # to each tow. no repeated ranks

Question 5

PERCENT_RANK()

Answer 5

ordered % of data

Question 6

UNION

Answer 6

UNION combines the results of two result sets and removes duplicates. UNION ALL doesn’t remove duplicate rows.

Question 7

INTERSECT

Answer 7

INTERSECT returns only rows that appear in both result sets.

Question 8

EXCEPT

Answer 8

EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.

Question 9

ROWS BETWEEN lower_bound AND upper_bound

Answer 9

just remember it

Question 10

UNBOUNDED PRECEDING

Answer 10

All rows before the current row.

Question 11

n PRECEDING

Answer 11

n rows before the current row.

Question 12

CURRENT ROW

Answer 12

Just the current row.

Question 13

n FOLLOWING

Answer 13

n rows after the current row.

Question 14

UNBOUNDED FOLLOWING

Answer 14

All rows after the current row.