Most Commonly Asked SQL Interview Topics - Part 5

Photo by Scott Graham on Unsplash

Hi All!! Hope you all had a great weekend!! This post will be the final post related to SQL series. In today's post, we are focusing on topics such as Indexes, Running Totals, and MIN() and MAX(). Previously, we looked into the difference between HAVING and WHERE clause, LEAD & LAG in Part 4 and several other topics in Part 3, Part 2, and Part 1. Without further ado, let's get into this week's topics. Hope you like the content!

Most Commonly Asked SQL Interview Topics:

1. Indexes:

Indexes are lookup tables that the database search engine can use ti speed up data retrieval.

It helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements.

Create index:

CREATE INDEX index_name ON tablename;

Single column index:

CREATE INDEX index_name ON tablename (colnm name)

Unique indexes: these are used not only for the performance, but also for data integreity. It does not allow any duplicate values to be inserted into the table

CREATE UNIQUE INDEX_NAME index_name ON tablename(column_name)

Composite Index:

A composite index is an index on two or more columns of a table.

CREATE INDEX index_name ON tablename (column1, column2)

Drop index:

An index can be droped using the DROP INDEX command

DROP INDEX index_name

Clustered & Non clustered index:

Clustered Index:

Clustered index when applied in a table, it will perform sort in that table only. You cab create only one clustered index in a table like a primary key. It is similar to a dictionary whre the data is arranged by alphabetical order. In Clustered index, index contains pointer to block but not direct data. If you apply primary key to any column, then automatically it will become clustered index.

Non Clustered Index:

The data is stored in place, and index is stored in another place. Since the data and non-clustered index is stored separately, then you can have multiple non-clustered index in a table. In non-clustered index, index contains the pointer to data.

Syntax:

create nonclustered INDEX index_name ON tbname(colname ASC|DESC);

Note:

Implicit indexes are indexes that automatically created by the db server. They are also automatically created for primary key constraints & unique constrainyts.

When indexes are not required?

• Indexes should not be used on small tables

• Tables that have frequent, large batch updates or insert operations

• indexes should not be used on columns that contain a high number of NULL values

• columns that are frequently manipulated should not be indexed

2. Running Totals:

Calculate running totals is most commonly used in several scenarios. A running total refers to the sum of values in all cells of a column that preceeds the next cells in that particular column:

Syntax using window function:

select colnames, SUM(colntobecalculated) OVER (ORDER BY coln) AS alias name from tablename.

Syntax without window function:

Using self join

select colnmaes, sum(coln) as runningtot from tb1 a join tb1 b on a.colid = b.colid group by 1 order by 1

Usecase:

In the sample usecase, if we are to calculate the running total for the sales to know how much did the sales representatives make sales in total.

Id	RepName	Gender	Sales	
1	Sam	F	1000	
2 	Tom	M	1000	
3 	Eddy	M	1200	
4	Kai 	M  	900	
5	Ankita	F	1500	

Query:
SELECT Id, RepName, Gender, Sales, SUM(Sales) OVER(ORDER BY Id) AS RunningTotalSales FROM SaleTb;

Id	RepName	Gender	Sales	RunningTotalSales
1	Sam	F	1000	1000
2 	Tom	M	1000	2000
3 	Eddy	M	1200	3200
4	Kai 	M  	900	4100
5	Ankita	F	1500	5600

Using self join:

SELECT a.Id, a.RepName, a.Gender, a.Sales, SUM(a.Sales) as RunningTotalSales FROM Sales Tb a JOIN SalesTb b ON a.Id = b.Id GROUP BY a.Id, a.RepName, a.Gender, a.Sales ORDER BY a.Id, a.RepName, a.Gender, a.Sales;

--produces same result

3. MIN and MAX

MIN: returns the smallest value of the selected column

MAX: returns rge largest value of the selected column.

Single column:

Syntax:

select MIN(colnames) from tbname where condition;
select MAX(colnames) from tbname where condition;

Examples:

with addition or subtraction:

select MIN(col1 - col2) as aliasname from tb;
select MAX(col1 - col2) as aliasname from tb;

On date values: - first and latest date:

select MIN(datecol) as aliasname from tb;
select MAX(datecol) as aliasname from tb;

Bonus:

Calculate Running Totals

Calcuate Running Totals using OVER and PARTITION clause

SQL Series:

If you like this content, please like, share, and subscribe. Also don't miss out the rest of the series Part 1 , Part 2, Part 3 & Part 4