Saturday, March 23, 2024

How to calculate Running Total and Running Average in SQL (with PARTITION BY Example)

Hello guys, calculating running totals and running averages is a common requirement in data analysis and reporting but I have interviewed many people who have no clue on how to do this. If you have also seen this question on interview and struggle to answer then you have come to the right place. SQL provides powerful features like window functions,which can be used to compute these running aggregates or running totals. In this article, we'll explore the concepts of running total and running average in SQL and see the real world examples and explanations to help you master these calculations but before that let's find out what is running total actually means?

Sunday, March 10, 2024

Does order of columns matter in composite index? Examples

Hello guys, if you have worked in SQL then you may know that indexes are very important and that's why they are also quite popular on SQL interviews. One of such question which I remember asked to me on interview was "does the order of columns matters in case of composite index?" I mean if you have an index which depends upon two columns the does the order mater like A, B or B,A are same or different. It's tricky question and not many people can answer but we will try in this article.  When it comes to composite indexes, the order of columns can significantly impact how efficiently a database executes queries. In this article, we'll explore the implications of column order in composite indexes and provide examples to illustrate its influence on SQL optimization.

Thursday, March 7, 2024

10 Examples of SELECT Queries in SQL

Hello guys, if you are new to SQL or learning SQL to become a programmer or data scientist then the first thing you should start with SELECT query. I know that many people will say that your first need to know how to create a database, a table, and learn about INSERT,  UPDATE, and DELETE but I suggest you to learn SELECT first. Why? because in 90% cases you will not create new tables, or database but you will be using SELECT queries to retrieve data from existing tables, and I can say from my 20 years of experience in programming and Software development. How to learn SELECT query? Well, the best way is to just execute queries and learn then create requirement like how to find employees whose salary greater than or less than certain amount etc. Once you reach that level, you are good to start with advanced SELECT concepts.

Thursday, February 1, 2024

What is NULL in SQL? How to use Nulls in Query? Example Tutorial

Hello guys, NULL is a very important concept in SQL as it indicate absence of values. A column can be Nullable or not Nullable, which means it can either contains null values or not. For a column which contains optional values like Car in Person table, you can use Null to indicate a person doesn't have car. 

While this part is easy, the real trouble comes when you need to query and deal with values which may have nulls. For an SQL developer it's important to find all rows where a certain column is Null, now how to compare Null as simply checking with = or != is not enough. 

This article will describe the complications that might arise when working with SQL NULL values, as well as some tips for resolving them.

Wednesday, January 31, 2024

How to remove duplicate values from a table in SQL? Example

In the world of data management, duplicate values can be a persistent nuisance. Duplicate records not only clutter your database but can also lead to erroneous results and performance issues when querying or analyzing data. Fortunately, SQL provides a powerful set of tools for identifying and removing duplicate values from a table. In this comprehensive guide, we will explore various techniques and best practices for deduplicating your data using SQL.