Introduction to Advanced SQL: Window Functions & CTEs
Explore the basics of window functions and common table expressions in SQL.
Questions in this quiz
15 questions on Introduction to Advanced SQL: Window Functions & CTEs. Work through them here, then start the quiz to answer them and see your score.
1.What is the primary purpose of a window function in SQL?
- ATo delete duplicate rows
- BTo join multiple tables
- CTo perform calculations across a set of table rows related to the current row
- DTo create temporary tables
2.Which SQL clause is used to define a window for window functions?
- AWINDOW
- BGROUP BY
- CORDER BY
- DPARTITION BY
3.What does CTE stand for in SQL?
- AConsolidated Table Entity
- BComputed Table Element
- CCommon Table Expression
- DConditional Table Entry
4.Which keyword is used to define a CTE in SQL?
- AWITH
- BDEFINE
- CCREATE
- DAS
5.How does a window function differ from an aggregate function?
- AAggregate functions can return multiple rows, window functions cannot
- BBoth return multiple rows
- CWindow functions can return multiple rows, aggregate functions cannot
- DBoth return a single row
6.Which of the following is a valid window function?
- ASUM()
- BROW_NUMBER()
- CAVG()
- DCOUNT()
7.What does the OVER() clause do in a window function?
- ADefines the window frame
- BSpecifies the partitioning of rows
- CAll of the above
- DSpecifies the order of rows
8.Which of the following is NOT a window function?
- ARANK()
- BGROUP BY
- CDENSE_RANK()
- DLEAD()
9.What is the main advantage of using CTEs?
- AImproves query readability
- BIncreases query execution speed
- CReduces database size
- DAutomatically indexes tables
10.Which of the following can be achieved using window functions?
- AUser authentication
- BData encryption
- CSchema creation
- DRunning totals
11.Can a CTE be recursive?
- AOnly in specific SQL dialects
- BYes, by default
- CYes, using the RECURSIVE keyword
- DNo, CTEs cannot be recursive
12.Which SQL function can be used to calculate a moving average with window functions?
- ACOUNT()
- BAVG()
- CMAX()
- DSUM()
13.What is the purpose of the ROW_NUMBER() function?
- ATo assign a unique sequential integer to rows within a partition
- BTo count the number of rows
- CTo calculate the sum of a column
- DTo filter rows based on a condition
14.Can multiple CTEs be defined in a single query?
- AYes, by separating them with commas
- BYes, but they must be nested
- CYes, but only in subqueries
- DNo, only one CTE per query
15.What does the LAG() function do in SQL?
- AFilters out duplicate rows
- BAccesses data from a previous row in the same result set
- CReturns the last row in a partition
- DCalculates the difference between two rows