QuizzesIntroduction to Advanced SQL: Window Functions & CTEs
beginner
databases

Introduction to Advanced SQL: Window Functions & CTEs

Explore the basics of window functions and common table expressions in SQL.

15 minutes 15 questions Pass at 70%
Time Limit
15 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

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. 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. 2.Which SQL clause is used to define a window for window functions?

    • AWINDOW
    • BGROUP BY
    • CORDER BY
    • DPARTITION BY
  3. 3.What does CTE stand for in SQL?

    • AConsolidated Table Entity
    • BComputed Table Element
    • CCommon Table Expression
    • DConditional Table Entry
  4. 4.Which keyword is used to define a CTE in SQL?

    • AWITH
    • BDEFINE
    • CCREATE
    • DAS
  5. 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. 6.Which of the following is a valid window function?

    • ASUM()
    • BROW_NUMBER()
    • CAVG()
    • DCOUNT()
  7. 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. 8.Which of the following is NOT a window function?

    • ARANK()
    • BGROUP BY
    • CDENSE_RANK()
    • DLEAD()
  9. 9.What is the main advantage of using CTEs?

    • AImproves query readability
    • BIncreases query execution speed
    • CReduces database size
    • DAutomatically indexes tables
  10. 10.Which of the following can be achieved using window functions?

    • AUser authentication
    • BData encryption
    • CSchema creation
    • DRunning totals
  11. 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. 12.Which SQL function can be used to calculate a moving average with window functions?

    • ACOUNT()
    • BAVG()
    • CMAX()
    • DSUM()
  13. 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. 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. 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