Window Functions (79–88)

 

Window Functions (79–88)

79. What is a window function?
    It performs calculations across rows without collapsing them.
80. What is ROW_NUMBER()?
    Assigns unique row number.
81. What is RANK()?
    Assigns rank with gaps for ties.
82. What is DENSE_RANK()?
    Assigns rank without gaps.
83. Difference between RANK and DENSE_RANK?
    RANK skips numbers after tie; DENSE_RANK does not.
84. What is PARTITION BY?
    Divides result set into groups for window functions.
85. Find highest salary in each department.
    Use RANK() with PARTITION BY dept.
86. What is LEAD()?
    Fetches next row value.
87. What is LAG()?
    Fetches previous row value.
88. Scenario: Compare current month sales with previous month.
    Use LAG(sales).

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516 

Subqueries & CTEs (66–78)

 

66. What is a subquery?
    A query inside another query.
67. Types of subqueries?
    Single-row, multi-row, correlated.
68. What is correlated subquery?
    A subquery that depends on outer query.
69. What is CTE?
    Common Table Expression is a temporary result set.
70. Why use CTE?
    Improves readability and simplifies complex queries.
71. Difference between CTE and subquery?
    CTE is reusable in same query; subquery is not.
72. Can CTE be recursive?
    Yes.
73. Find 2nd highest salary using subquery.
    SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
74. Find employees earning above average salary.
    Use subquery with AVG(salary).
75. What is EXISTS?
    Checks if subquery returns rows.
76. What is NOT EXISTS?
    Checks if subquery returns no rows.
77. Difference between IN and EXISTS?
    IN compares values; EXISTS checks row existence.
78. Which performs better: IN or EXISTS?
    EXISTS is better for large datasets.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516