Top 10 SQL Queries from Technical Interviews to Learn Window Functions like ROW_NUMBER

Hello guys, If you're preparing for SQL interviews or working on real-world reporting tasks, mastering window functions is no longer optional. They are one of the most powerful tools in SQL which allows you to perform calculations across rows related to the current row without collapsing your result set like GROUP BY does. Window functions like ROW_NUMBER(), RANK(), LEAD(), LAG(), and SUM() OVER help you write cleaner, faster, and more expressive queries—ideal for performance tuning and analytical processing.

Let’s dive into the Top 10 SQL window function queries that you must know.

How to replace NULL with Empty String in SQL Server? ISNULL() vs COALESCE() Examples

We often need to replace NULL values with empty String or blank in SQL e.g. while concatenating String. In SQL Server, when you concatenate a NULL String with another non-null String the result is NULL, which means you lose the information you already have. To prevent this, you can replace NULL with empty String while concatenating. There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.

Difference between char, varchar, nchar and nvarchar data types in SQL Server? Example

What is the difference between char and varchar in SQL, followed by nchar and nvarchar, is one of the popular SQL interview questions, and surprisingly not every programmer knows this basic difference. If you go with the name, which you should, then you can figure out that char is a fixed-length data type while varchar should be a variable-length data type. Though all char, varchar, nchar, and nvarchar are used to store text or String data there are subtle differences between them. As I said char is fixed length, which means a variable or a column like Zipcode char(10) will take only 10 bytes to store data, including space.

How to Find Duplicate values in a Table? SQL GROUP BY and Having Example| Leetcode Solution

Hello guys, if you are wondering how to find duplicate values in a table then don't worry, there are many ways to find duplicate rows or values from a given table. For example, you can use the GROUP BY and HAVING clause in SQL with count function to find all the rows which has same values for a particular column and then filter out rows where count is just one, I mean unique values. This way you can find all the duplicate from a given table. Using group by you can create groups and if your group has more than 1 element it means it's kind of duplicate.

How to use EXISTS and NOT Exists in SQL? Example Query and Tutorial

Hello Guys, you might have heard about how useful the EXISTS clause is helpful in writing sophisticated queries. Still, at the same time, I have also seen that many programmers struggle to understand and use EXISTS and NOT EXISTS clauses while writing SQL queries.  If you are one of them, then you have come to the right place. Today you will learn how to use the EXISTS clause in SQL by picking up a real-world example and an excellent SQL exercise from the LeetCode. Suppose that a website contains two tables, the Customers table, and the Orders table. Can you write an SQL query to find all customers who have never ordered anything?

Difference between LEFT and RIGHT OUTER Joins in SQL - MySQL Join example

There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. The main difference between RIGHT OUTER joins and LEFT OUTER join, as their name suggests, is the inclusion of non-matched rows. Sine INNER join only include matching rows, where the value of the joining column is the same, in the final result set, but OUTER join extends that functionality and also include unmatched rows in the final result. LEFT outer join includes unmatched rows from the table written on the left of the join predicate. 

Top 12 SQL Query Problems for Coding Interviews (with Solutions)

Hello guys, if you are looking for SQL query examples from interviews or SQL Query Practice questions to improve your SQL skill or just to prepare for tech interviews  then you have come to the right place. Earlier, I have shared best websites to learn SQL and Practice Query online and in this article, I am going to share 12 popular SQL query questions from interviews. SQL is an important skills for both programmers and data scientist, even people from QA and BA stream also need to know SQL to do their job well in this era or data driven world. That's why SQL queries are also quite popular on interviews. 

4 Ways to find Nth highest salary in SQL - Oracle, MSSQL and MySQL

One of the most common SQL interview questions is to find the Nth highest salary of employees, where N could be 2, 3, 4 or anything e.g. find the second highest salary in SQL. Sometimes this question is also twisted as to find the nth minimum salary in SQL. Since many Programmers only know the easy way to solve this problem e.g. by using SQL IN clause, which doesn't scale well, they struggle to write the SQL query when the Interviewer keeps asking about the 4th highest, 5th highest and so on. In order to solve this problem effectively, you need to know about some key concepts like a correlated subquery, window functions like ROW_NUMER(), RANK(), and DENSE_RANK(), etc. Once you know the generic logic to solve this problem, you can tackle all those variations by yourself.

SQL Self Join Example - SQL Query to Find Employees Earning More Than Managers - LeetCode Solution

Hello guys, are you looking for a simple example of how to use SELF JOIN in SQL? If yes, then you have come to the right place. This article will show you how to use Self join in solving interesting SQL problems from LeetCode. Along the way, you will also learn this useful SQL concept. So, what are you waiting for? Let's first check the problem, and then we'll write an SQL query using SELF Join to solve this problem.

Write an SQL Query to Find Employees Earning More Than Managers
The Employee table holds all employees, including their managers. Every employee has an Id, and there is also a column for the manager Id, as shown below:

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager because Henry's Manager is Max, who earns 90000, which is more than Henry's salary of 80000.

Difference between row_number(), rank() and dense_rank() window functions in SQL

Though all three are ranking functions in SQL, also known as a window function in Microsoft SQL Server, the difference between rank(), dense_rank(), and row_number() comes when you have ties on ranking i.e. duplicate records. For example, if you are ranking employees by their salaries then what would be the rank of two employees of the same salaries? It depends on which ranking function you are using like row_number, rank, or dense_rank.

How to Compare Date in SQL Server Query? Finding All Rows Between Two Dates

It's tricky to use dates in the SQL server query, especially if you don't have good knowledge of how DateTime type works in the SQL server. For example, one of the frequently asked SQL queries on the interview is to "select all rows where the date is 20151007?" How would you do that? Does the following SQL Query will work correctly

select * from table where date = '20151007'

It may or may not, it entirely depends on upon data in your table. When you only provide date part of a DateTime variable, it uses '00:00:00.000' for the time part.

How to find duplicate records in a table on database - SQL tips

How to find duplicate records in a table is a popular SQL interview questions which have been asked as many times as difference between truncate and delete in SQL or finding second highest salary of employee. Both of these SQL queries are must know for any one who is appearing on any programming an interview where some questions on database and SQL are expected. In order to find duplicate records in the database table you need to confirm the definition of duplicates, for example in below contact table which is suppose to store name and phone number of the contact, a record is considered to be duplicate if both name and phone number is the same but unique if either of them varies.

How to find second highest or maximum salary of Employee in SQL - Interview question

How to find the second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview questions similar to finding duplicate records in table and when to use truncate vs delete. There are many ways to find second highest salary based upon which database you are using as different database provides different feature which can be used to find the second maximum or Nth maximum salary of employee. Well this question can also be generalized with other scenario like finding second maximum age etc. In this SQL tutorial we will see different example of SELECT SQL query to find second highest salary independent of databases or you may call in ANSI SQL and other SQL queries which uses database specific feature to find second maximum salary.

SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database - SELECT * Example

Many times we need to create backup or copy of tables in databases like MySQL, Oracle, or PostgreSQL while modifying table schema like adding new columns, modifying columns, or dropping columns. Since it's always best to have a backup of a table that can be used in any event. I was looking for an easy way to create an exact copy or duplicate tables which must be the same in the schema as well as in data, similar to creating a copy of the folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create an exact copy of the table by executing just one SQL query. Yes, you read it correctly, no tool is required to create a backup of the table you just need to execute an SQL query.

How to find Length of String in SQL Server? LEN() Function Example

One of the most common task while writing SQL queries or stored procedure is to find the length of String. Since most of the columns are VARCHAR, you often need to find the length before taking any action. In Java, you can find the length of String by using the length() method but how about SQL Server? How will you find the length of String in Microsoft SQL Server in general and Microsoft SQL Server 2016 in particular? Well, you can use the LEN() function to find the length of a String value in SQL Server, for example, LEN(emp_name) will give you the length of values stored in the column emp_name. This method exists from SQL Server 2008 onwards which means you can use this function in SQL Server 2012, 2014, 2016 and latest version of Microsoft SQL Server i.e. SQL Server 2017.

How to do Pagination in Oracle Database - SQL Query With Example

Many times we need an SQL query that returns data page by page i.e. 30 or 40 records at a time, which can be specified as the page size. In fact, Database pagination is a common requirement of Java web developers, especially dealing with the largest data sets.  In this article, we will see how to query Oracle 10g database for pagination or how to retrieve data using paging from Oracle. Many Java programmer also uses display tag for paging in JSP which supports both internal and external paging. In the case of internal paging, all data is loaded into memory in one shot and the display tag handles pagination based upon page size but it is only suitable for small data where you can afford those many objects in memory.

Difference between Clustered Index and Non Clustered Index in SQL - Example

In the SQL Server database, there are mainly two types of indexes, Clustered index, and the Non-Clustered index and difference between Clustered and Non-Clustered index are very important from an SQL performance perspective. It is also one of the most common SQL Interview questions, similar to the difference between truncate and delete,  primary key or unique key, or correlated vs non-correlated subquery. For those, who are not aware of the benefits of Index or why we use an index in the database, they help in making your SELECT query faster. 

Difference between Primary key vs Foreign key in table – SQL Tutorial Example

The main difference between the Primary key and the Foreign key in a table is that it’s the same column that behaves as the primary key in the parent table and as a foreign key in a child table. For example in the Customer and Order relationship, customer_id is the primary key in the Customer table but a foreign key in the Order table. By the way, what is a foreign key in a table and the difference between Primary and Foreign key are some of the popular SQL interview questions, much like truncate vs delete in SQL or difference between correlated and noncorrelated subquery? We have been learning key SQL concepts along with these frequently asked SQL questions and in this SQL tutorial, we will discuss what is a foreign key in SQL and the purpose of the foreign key in any table. 

What is Referential Integrity in Database or SQL - MySQL Example Tutorial

Referential Integrity is a set of constraints applied to foreign keys which prevents entering a row in the child table (where you have the foreign key) for which you don't have any corresponding row in the parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having incorrect or incomplete relationships e.g. If you have two tables Order and Customer where Customer is parent table with primary key customer_id and Order is child table with foreign key customer_id. Since as per business rules you can not have an Order without a Customer and this business rule can be implemented using referential integrity in SQL on a relational database.

Difference between SQL, T-SQL and PL/SQL?

Hello guys, if you are preparing for SQL and Database Interviews or any Software engineering interview and looking for difference between T-SQL, SQL, and PL/SQL then you have come to the right place. Earlier, I have shared 50 SQL Interview questions and 12 SQL query Examples from interviews and today, we are going to see another common and interesting SQL interview question, what is the difference between SQL, T-SQL, and PL/SQL? It is also one of the most common doubts among SQL beginners. It's common for programmers to think that why there are many types of SQL languages, why not just single SQL across DB? etc. Well, let's first understand the difference between SQL, T-SQL, and PL/SQL, and then we will understand the need for these dialects.