1. What is the difference between HAVING and WHERE?
WHERE: is used to check conditions before the aggregation takes place.

SELECT state, COUNT(city) as city_count
FROM Address
WHERE country = 'IN'
GROUP BY state

This gives you a table of all states in 'IN' and the number of cities in each state.

HAVING: is used to check conditions after the aggregation takes place.

SELECT state, COUNT(city) as city_count
FROM Address
WHERE country = 'IN'
GROUP BY state
HAVING COUNT(city)>5

Gives you a table of states in 'IN' with more than 5 cities and the number of cities in each state.

2a) SELECT COUNT(DISTINCT name) vs SELECT DISTINCT COUNT(name).
2b) When are COUNT(name) and COUNT DISTINCT(name) equal?
a) SELECT COUNT(DISTINCT name) returns the number of rows that have unique names. For e.g. For a table with names : [Lakshmi, Karthik, Karthik, Rahul, Rahul, Rahul], the response is 3. SELECT DISTINCT COUNT(name) returns the number of unique counts of names present. For the same set of names as the previous example, the result would be [1, 2, 3]
b) If the table column does not have duplicate data, then the response would be the same for both SELECT COUNT(name) and SELECT COUNT(DISTINCT name)

3. Can left and inner join return the same results?
LEFT JOIN and INNER JOIN might provide the same result when, all the records of left table has at least one match on the right table. The main difference between INNER JOIN and LEFT JOIN is that LEFT JOIN still displays the records on the LEFT side even if they have no match on the RIGHT side table.

4. What is the difference between RANK and DENSE RANK?
RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.
DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

SELECT employee_name, department, salary,
RANK() OVER (partition by department ORDER BY salary nulls first) r,
DENSE_RANK() OVER (partition by department ORDER BY salary nulls first) dr,
DENSE_RANK() OVER (partition by department ORDER BY salary nulls last) dr2
FROM Employees

employee_name  department  salary   r    dr    dr2  
-------------- ------------ ------ ---- ---- ----
Rahul         10           null    1    1    4  
Ajay          10           10000   2    2    1  
Siva          10           40000   3    3    2
Akshaya       10           40000   3    3    2
Shruti        10           50000   5    4    3
Sabari        10           50000   5    4    3
Vignesh       11           5000    1    1    1
Karthi        11           20000   2    2    2
Ravi          12           30000   1    1    1

5. Can UNION and UNION ALL return the same results?
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. Both these commands result the same results when there are no duplicate rows present (both data sets combined).

6. What are the best scenarios to use a self join?
You use a self join when a table references data in itself.

E.g., an Employee table of an Employee Portal app, may have a supervisor_id column that points to the employee that is the boss of the current employee. Or a Catogory table of an e-commerce website may have a sub_category_id column that points to a category present in the same table.

7. How can you use SQL to dedupe rows?
Dedupe here refers to removal of duplicate rows based on a certain number of columns.
Solution 1: One can use CTE's(Common Table Expressions) to delete using analytic function ROW_NUMBER().

WITH CTE (col1, col2, dupcnt)
AS
(
SELECT col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1) AS dupcnt
FROM MyTable
)
DELETE
FROM CTE
WHERE dupcnt > 1
GO

Solution 2: Use SELF JOINs

SELECT emp_name, emp_address, sex, marital_status
from MyTable a
WHERE NOT EXISTS (select 1 
         from MYTable b
         where b.emp_name = a.emp_name and
               b.emp_address = a.emp_address and
               b.sex = a.sex and
               b.create_date >= a.create_date)

Solution 3: Use GROUP BY

SELECT first_name, last_name, mobile_no
FROM  Customer
GROUP BY first_name, last_name, mobile_no;

8. In what scenarios are the LAG function useful? SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. Syntax:

LAG (scalar_expression [, offset] [, default])  
OVER ( [ partition_by ] order_by )

For Example

SELECT organisation, year, revenue,
LAG (revenue, 1, 0) 
OVER (PARTITION BY organisation ORDER BY year) AS PrevYearRevenue  
FROM OrgTable 
ORDER BY organisation, year;

9. Use a window function to calculate a running total.
As simple as

WITH data AS (
  SELECT
    date(rental_date) as day,
    count(rental_id) as rental_count
  FROM Rental
  GROUP BY day
)
SELECT  day, rental_count,
   SUM(rental_count) OVER (ORDER BY day) as cumulative_sum
from data;

Note: The sum(), lead(), lag() functions are supported only in MySQL version >= 8

10. Why would you use GROUP BY ROLLUP?
The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets. For e.g. when we run the following query, we get:

SELECT
    warehouse, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse;

warehouse      qty
------------------
Chennai         11 
Mumbai          10

whereas, the same query with ROLLUP gives an extra row with the sum of the individual counts.

SELECT 
    warehouse, SUM(quantity) qty
FROM
    inventory
GROUP BY ROLLUP (warehouse);

warehouse      qty
------------------
Chennai         11 
Mumbai          10  
null            21

If you learned something new from the post, leave a 👍. நன்றி.

~Rahul