top of page
    • Aug 16, 2021
    • 5 min read

Most Commonly Asked SQL Interview Topics - Part 3


Hi All!! Hope you all had a great weekend!! In continuation of the SQL series, we are focusing on topics as string formating, substrings, WINDOW functions & subqueries. Previously we looked in other most commononly askd SQL interview 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. String formatting, subformatting:


FORMAT is one of the new built-in String Function introduced as a part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value.

SYNTAX: FORMAT ( value, format [, culture ] )

RETURNS: Return value type is nvarchar.


Examples: 

1. FORMAT(123456789,’##-##-#####’) --12-34-56789
2. FORMAT(454545.389, 'N','en-US') AS 'Number Format in US'454,545.39
3. FORMAT(454545.389, 'N','en-IN')  AS 'Number Format in INDIA' 4,54,545.39
4. FORMAT(454545.389, '#.0')     AS 'With 1 Decimal', 454545.4
5. FORMAT(454545.389, '#.00')    AS 'With 2 Decimal' 454545.39
6. FORMAT(454545.389, '#,##.00') AS 'With Comma and 2 Decimal', 454,545.39
7. FORMAT(454545.389, '##.00')   AS 'Without Comma and 2 Decimal' 454545.39

Note: Use format() function to format date/time values and number values. For general data type conversions, use cast() or convert()


SUBSTRING() function extracts the substring from the specified string based on the specified location.

SYNTAX: SUBSTRING(expression, starting_position, length)

RETURNS: Return value type is nvarchar.


Examples:
1. Substring in SELECT statement:
SELECT SUBSTRING('Hi, You are on MYSITE.COM', 16, 10) result;
Result: MYSITE.COM
2. Substring in where clause
Select firstname ,lastname,len(lastname) as LastNameLength
FROM 
  tb1
WHERE 
SUBSTRING(FirstName, LEN(FirstName)-1,2) = 'el'
Result:
firstname lastname  LastNameLength
Angel     Adams     5
Gabriel   Adams     5
Miguel    Adams     5
Samuel    Adams     5
3. Substring with Charindex:
SELECT 
a.firstname,
  a.emailaddress, 
  Substring (a.emailaddress, Charindex( '@', emailaddress ) + 1,  
Len(emailaddress)) AS [Domain Name] 
FROM   tb1 a
Result : 
N1@mysite.com

2. WINDOW FUNCTIONS:

We perform calculations on data using various aggregated functions such as Max, Min, and AVG. We get a single output row using these functions. SQL Server provides SQL RANK functions to specify rank for individual fields as per the categorizations. It returns an aggregated value for each participating row. SQL RANK functions also knows as Window Functions.


We have the following rank functions.

• ROW_NUMBER()

• RANK()

• DENSE_RANK()


In the SQL RANK functions, we use the OVER() clause to define a set of rows in the result set. We can also use SQL PARTITION BY clause to define a subset of data in a partition. You can also use Order by clause to sort the results in a descending or ascending order.


We use ROW_Number() function to get a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.

SELECT Studentname, 
       Subject, 
       Marks, 
       ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
FROM ExamResult;

RANK() Function:

We use RANK() SQL Rank function to specify rank for each row in the result set.


The following query to get this result set. In this query, you can note the following things:

• We use PARTITION BY colname1 clause to perform calculations on each group

• Each subset should get rank as per their colname2 in descending order

• The result set uses Order By clause to sort results on colname1 and their rank

SELECT colname1, colname2, 
       RANK() OVER(PARTITION BY colname1 ORDER BY colname2 DESC) Rank
FROM Tb1
ORDER BY colname1, Rank;

Let’s execute the following query of SQL Rank function and look at the result set. In this query, we did not specify SQL PARTITION By clause to divide the data into a smaller subset. We use SQL Rank function with over clause on Marks clause ( in descending order) to get ranks for respective rows.


SELECT Studentname, Subject, Marks, 
       RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;

Studetname   Subject  Marks   Rank
Isabella     english   90       1
Olivia       english   89       2
Lily         Science   80       3
Lily         english   70       4
Isabella     Science   70       4
Lily         Maths     65       6
Olivia       Science   60       7
Olivia       Maths     55       8
Isabella     Maths     50       9

In the output, we can see each student get rank as per their marks irrespective of the specific subject. For example, the highest and lowest marks in the complete result set are 90 and 50 respectively. In the result set, the highest mark gets RANK 1, and the lowest mark gets RANK 9.

If two students get the same marks (in our example, ROW numbers 4 and 5), their ranks are also the same.

DENSE_RANK() SQL RANK function

We use DENSE_RANK() function to specify a unique rank number within the partition as per the specified column value. It is similar to the Rank function with a small difference.

In the SQL RANK function DENSE_RANK(), if we have duplicate values, SQL assigns different ranks to those rows as well. Ideally, we should get the same rank for duplicate or similar values.

Let’s execute the following query with the DENSE_RANK() function.

SELECT Studentname, Subject, Marks, 
       DENSE_RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;
Studetname   Subject  Marks   Rank
Isabella     english   90       1
Olivia       english   89       2
Lily         Science   80       3
Lily         english   70       4
Isabella     Science   70       4
Lily         Maths     65       5
Olivia       Science   60       6
Olivia       Maths     55       7
Isabella     Maths     50       8

In the output, you can see we have the same rank for both Lily and Isabella who scored 70 marks.

Let’s use DENSE_RANK function in combination with the SQL PARTITION BY clause.


SELECT Studentname, Subject, Marks, 
       DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Studentname, Rank;
Studetname   Subject  Marks   Rank
Isabella     english   90       1
Olivia       english   89       2
Lily         english   70       3
Lily         Maths     65       1
Olivia       Maths     55       2
Isabella     Maths     50       3
Lily         Science   80       1
Isabella     Science   70       2
Olivia       Science   60       3

We do not have two students with similar marks; therefore result set similar to RANK Function in this case.

Let’s update the student mark with the following query and rerun the query.

Update Examresult set Marks=70 where Studentname='Isabella' and Subject='Maths'

We can see that in the student group, Isabella got similar marks in Maths and Science subjects. Rank is also the same for both subjects in this case.

Let’s see the difference between RANK() and DENSE_RANK() SQL Rank function with the following query.


Query 1:
SELECT Studentname, Subject, Marks, 
RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) Rank
FROM ExamResult
ORDER BY Studentname, Rank;
         
Studetname   Subject  Marks   Rank
Isabella     Maths     70       1
Isabella     Science   70       1
Isabella     english   90       2
Lily         Maths     65       1
Lily         english   70       2
Lily         Science   80       3
Olivia       Maths     55       1
Olivia       Science   60       2
Olivia       english   89       3
Query 2:
	SELECT Studentname, 
       Subject, 
       Marks, 
       DENSE_RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) Rank
FROM ExamResult
ORDER BY Studentname, 
         Rank;

Studetname   Subject  Marks   Rank
Isabella     Maths     70       1
Isabella     Science   70       1
Isabella     english   90       3
Lily         Maths     65       1
Lily         english   70       2
Lily         Science   80       3
Olivia       Maths     55       1
Olivia       Science   60       2
Olivia       english   89       3

A rank function assigns rank 1 for similar values however, internally ignores rank two, and the next row gets rank three. In the Dense_Rank function, it maintains the rank and does not give any gap for the values.

3. Subqueries:

A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery

FROM:

SELECT sub.*
  FROM (
        SELECT *
          FROM tbname1
         WHERE col2 = 'Friday'
       ) sub
 WHERE sub.col4 = 'NONE'

Where:

SELECT *
  FROM tbname1
 WHERE col1= (SELECT MIN(col1)
                 FROM tbname1

JOIN:

SELECT *
  FROM tbname1 tb1
  JOIN ( SELECT col1
           FROM tbname1
          ORDER BY col1
          LIMIT 5
       ) sub
    ON tb1.col1= sub.col1

Insert:

INSERT INTO tbnme_bkp
   SELECT * FROM tbname
   WHERE col3 IN (SELECT col3 
   FROM tbname) ;

Update:

UPDATE tbname
   SET col2 = col2 * 0.25
   WHERE col1 IN (SELECT col1 FROM tbnme_bkp
      WHERE col1 >= 27 );

Delete:

DELETE FROM tbnme
   WHERE col1 IN (SELECT AGE FROM tbnme_bkp
      WHERE col1 >= 27 );

Resources Used:


Part 4 will be out next week. If you like this content, please like, share and subscribe!!

Part 1 & Part 2 of the SQL series

ความคิดเห็น

bottom of page