- Aug 23, 2021
- 4 min read

Most Commonly Asked SQL Interview Topics - Part 4

Hi All!! Hope you all had a great weekend!! In continuation of the SQL series, we are focusing on topics such as difference between HAVING and WHERE clause, LEAD & LAG. Previously we looked into topics related to string formating, substrings, WINDOW functions & subqueries in Part 3 and other topics in Part 1 & Part 2. Without further ado, let's get into this week's topics. Hope you like the content!

Most Commonly Asked SQL Interview Topics:

1. HAVING vs WHERE clause:

WHERE Clause is used to filter the records from the table or used while joining more than one table.Only those records will be extracted who are satisfying the specified condition in WHERE clause. It can be used with SELECT, UPDATE, DELETE statements.

SELECT colname1, colname2 FROM tablename
WHERE condition;

HAVING Clause is used to filter the records from the groups based on the given condition in the HAVING Clause. Those groups who will satisfy the given condition will appear in the final result. HAVING Clause can only be used

with SELECT statement.

SELECT colname1, COUNT(colname2) AS alias_name  
FROM tablename GROUP BY colname1
HAVING COUNT(colname2) condition

Differences:

1. WHERE is used to filter the records from the table based on the specified condition. whereas HAVING is used to filter record from the groups based on the specified condition.

2. WHERE can be used without GROUP BY whereas HAVING cannot.

3. WHERE implements in row operations whereas HAVING implements in column operation

4. WHERE cannot contain aggregate function whereas HAVING can contain aggregate function

5. WHERE can be used with SELECT, UPDATE, DELETE statement whereas HAVING Clause can only be used with SELECT statement

6. WHERE is used before GROUP BY Clause whereas HAVING is used after

7. WHERE is used with single row function like UPPER, LOWER etc. whereas HAVING is used with multiple row function like SUM, COUNT etc.

2. LEAD and LAG - What are they used for?

LAG() with and LEAD() are window functions specifically called "positional functions". They are most commonly used in creating reports as they can refer to data from rows above or below the current row.

The LAG() function allows access to a value stored in a different row above the current row.

Let’s look its syntax:

LAG(expression [,offset[,default_value]]) OVER(ORDER BY columns)

function LAG() takes 3 arguments: the name of the column or expression rom which value is obtained, offset (which is number of rows to skip), and the default value to be returned if the stored value is obtained from the row above is empty. Only the first argument is required. The third argument is allowed only if you specify the second argument.Similar to other window functions, LAG() requires the OVER clause and must specify an ORDER BY in the OVER clause, with a column or a list of columns by which the rows should be sorted.

Example:

id	seller_name		sale_value
3 	Stef                		7000
1 	Alice             		12000
2 	Mili                		25000

SELECT seller_name, sale_value,
  LAG(sale_value) OVER(ORDER BY sale_value) as previous_sale_value
FROM sale;

Result:
seller_name   sale_value   previous_sale_value
Ana			7000            NULL
Tpm		12000           7000
Mini                  25000          12000

the second record displays Ana's sale amount ($12,000) with Tom's ($7,000) from the row above, in columns sale_value and previous_sale_value, respectively. Notice that the first row does not have an adjacent row above, and consequently the previous_sale_value field is empty (NULL), since the row from which the value of sale_value should be obtained does not exist.

LEAD() is similar to LAG(). Whereas LAG() accesses a value stored in a row above, LEAD() accesses a value stored in a row below.

The syntax of LEAD() is just like that of LAG():

LEAD(expression [,offset[,default_value]]) OVER(ORDER BY columns)

Just like LAG(), the LEAD() function takes three arguments: the name of a column or an expression, the offset to be skipped below, and the default value to be returned if the stored value obtained from the row below is empty. Only the first argument is required.

Just like LAG(), LEAD() is a window function and requires an OVER clause and must be accompanied by an ORDER BY in the OVER clause.

Example:


id	seller_name		sale_value
3	Ana			7000
1	Tom			12000
2	Mini			25000

SELECT seller_name, sale_value,
  LEAD(sale_value) OVER(ORDER BY sale_value) as next_sale_value
FROM sale;

Result:
rep_name		sale_value              next_sale_value
Ana			7000			12000
Tom			12000			25000
Mini			25000			NULL

In the above snippet, the rows are sorted by the column specified in ORDER BY (sale_value). The LEAD() function takes the sale amount from the row below. For example, Ana's own sale amount is $7,000 in the column sale_value, and the column next_sale_value in the same record contains $12,000. The latter comes from the sale_value column for Tom, the seller in the next row. Note that the last row does not have a next row, so the next_sale_value field is empty (NULL) for the last row.

If you specify only the required argument, that is, only the name of the column or other expression, the offset defaults to 1 and the third argument defaults to NULL. In our example, the value for Alice’s next_sale_value is from the column sale_value of the adjacent row below, since the default offset is 1.

Note: Using LEAD() & LAG(), you can compare values across rows.