Tuesday, May 14, 2024

How to get rows between two dates in Microsoft SQL Server? MSSQL Example

Problem: You want all records between two dates e.g. start_date and end_date. For example, you have a Course table, which contains course_name, price and start_date. If want all the course which are starting next month, how will you do that?

Solution : You can get the records between two dates in SQL SERVER by using dates in WHERE clause, for example, below query will return all the courses for which start date is greater than 2025/01/01 and less than 2025/01/31

Tuesday, May 7, 2024

How to Remove duplicate rows from table in SQL Server using temp table? Example

One of the easiest way to remove duplicate rows from a table in SQL is copy unique data into temp table, delete the original table and create a new table from the temp table without any duplicates. Sometime when you don't have a unique key or primary key in table, you end up with duplicate rows i.e. more than one rows where all columns are identical. You can easily find those duplicate rows by using GRPUP BY, as seen in our previous SQL interview questions. You can also print just unique rows by using distinct clause, but how do you go about removing duplicate rows from table altogether? That's what you will learn in this article. Actually there are multiple ways to remove duplicates and you will learn couple of them in this article. 

Wednesday, May 1, 2024

Difference between Primary key Unique key in Database SQL

Hello friends, both primary key and unique key are two important concepts in a relational database which are used to uniquely identify a row in a table. For example, EmployeeId is often primary key in Employee table which can uniquely identify any employee which is denoted by a row in Employee table. Similarly, a employee can also be identify with EmployeeName if there is no duplicate but if its not primary key then we call it a unique key. While both primary key and unique keys can identify a row uniquely but there is some subtle difference between them which we will see in this article. 

Sunday, April 14, 2024

6 Examples of Correlated Subqueries in SQL

The correlated subquery is one of the interesting but tricky concept to master in SQL. It's like recursion in computer science, which many developer struggle to understand and apply, but it's a such nice technique that I believe every programmer should learn and master. It took me long time to understand and master this technique and solving SQL queries which I am going to share with you today, played a huge role in learning correlated subqueries better. Sometime, you can solve a problem easily by using correlated subquery like the Nth highest salary problem, which is quite easy to solve using correlated subquery but difficult otherwise.

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.

Saturday, January 27, 2024

What is Window Functions in SQL? Rank, Row_Number, and Dense_Rank? How to use it and Performance Analysis

T-SQL (Transact-SQL) window functions are a category of functions in Microsoft SQL Server that perform calculations across a specified range of rows related to the current row within a result set. These functions operate on a set of rows that are defined by an OVER clause in the query, allowing for more advanced and flexible analytical processing compared to traditional aggregate functions.

The 2024 Database Administrator RoadMap

As the digital realm continues to advance at a rapid pace, the role of a Database Administrator (DBA) becomes increasingly vital in ensuring the seamless management, security, and efficiency of data systems. This Database Administrator roadmap is tailored to equip both aspiring and seasoned DBAs with the knowledge, skills, and strategies needed to thrive in the dynamic world of database management in 2024. From fundamental concepts to emerging technologies, we will explore the key areas that define the modern database administration landscape. Whether you are seeking to enhance your existing expertise or embark on a new career path, this roadmap will be your trusted companion in mastering the latest tools, best practices, and industry trends shaping the field of database administration in 2024 and beyond.

2 Ways to remove duplicate rows in SQL? Example Tutorial

Hello guys, if you are wondering how to remove duplicate rows in SQL and looking for solutions on different databases like SQL Server, MySQL, and Oracle then you have come to the right place. In this article, I am going to share 2 ways to remove duplicate rows from SQL, first by using GROUP BY and HAVING clause and second by using the RANK function which works on most of the databases.  You can use any of the approaches to remove duplicates from tables using SQL. This is also one of the popular SQL Interview Questions so knowing how to solve this problem will also give you an edge on interviews. 

Wednesday, January 24, 2024

4 Examples of CASE expression in SQL

What is CASE and where is it used?
When a condition is met, the CASE statement evaluates different conditions and returns a scalar value. The phrase from the ELSE block will be returned if none of the conditions are evaluated to TRUE. The ELSE block, however, is optional. Above all, the CASE statement has two formats Simple CASE & Searched CASE.

The Searched CASE statement is highlighted in this post. To get the results, you'll need to use search and pattern matching.

Monday, January 22, 2024

Top 30 MySQL Interview Questions for 2 to 3 Years Experienced Developers

Hello guys, if you are preparing for MySQL interviews or you are preparing for a tech interviews where MySQL skills are needed and you are looking for MySQL questions then you have come to the right place. Earlier, I have shared 15 SQL queries from interviews and 30 Database Interview Questions and in this article, I am going to share 50+ MySQL questions which you can practice before interviews. These MySQL question touches many different topics on MySQL like Database and SQL related as well MySQL engine specific questions like MyISAM vs InnoDB etc.

How to find Find all tables containing column with specified name - MS SQL Server

Hey there! Today, we're going on a quest to uncover one of SQL Server's well-kept secrets: how to find all the tables containing a column with a specified name. Don't worry; this isn't a treasure hunt involving maps and riddles – just a few SQL queries and a sense of curiosity similar to how to find Nth highest salary we have seen earlier. 

Have you ever found yourself lost in a vast SQL Server database, wondering where a particular column is hiding? Or perhaps you need to perform some maintenance tasks on all tables with a specific column name? Well, you're in luck, because I'm here to guide you through this adventure.


How to find Find all tables containing column with specified name - MS SQL Server Example

Have you ever found yourself in a labyrinthine SQL Server database, desperately searching for that one elusive column with a specific name? If so, you're in the right place. Today, I'm going to be your guide on a thrilling quest to unveil the secrets of SQL Server – how to find all the tables containing a column with a specified name.

This adventure isn't about maps, compasses, or treasure chests, but rather about SQL queries and a knack for exploration. So, grab your digital magnifying glass, dust off your SQL knowledge, and let's embark on this thrilling journey to uncover the hidden gems within your SQL Server databases.

Imagine you're in charge of a colossal SQL Server database with countless tables and columns, and you've been tasked with identifying all the tables that house a column named 'customer_id.' It's like searching for a needle in a colossal data haystack, isn't it? Fear not, my friend, because we're about to shed light on this intriguing SQL riddle.

Introduction

In the world of SQL Server, where databases can be vast and complex, locating a specific column can feel like navigating a maze blindfolded. But worry not; SQL Server provides us with tools and techniques to tackle this challenge head-on.

Whether you're a database administrator, a developer, or just a curious explorer of data, the ability to find tables containing a specific column can be a game-changer. So, let's unravel this mystery together and equip you with the skills to become a proficient SQL detective.

Are you ready to embark on this SQL adventure? Great! Let's dive right in.




Approach 1: Using SQL Server Management Studio (SSMS)

Let's start with the most user-friendly method. If you're a fan of graphical user interfaces, SQL Server Management Studio (SSMS) is your trusty sidekick. Here's how you can find all tables with a specific column using SSMS:

Open SQL Server Management Studio: Fire up SSMS and connect to your SQL Server instance.

Object Explorer: In the Object Explorer pane on the left, expand your database to reveal its tables.

Search for the Column: Right-click on the Tables folder and select "Filter" -> "Filter Settings." In the "Filter Settings" dialog, choose the "Columns" tab, and then click "Add Filter."

Specify Column Name: In the "Filter Column" dialog, select the "Name" column and set the operator to "Contains." Enter the name of the column you're looking for, such as 'email.' Click "OK" to apply the filter.

Browse the Results: Voila! You'll now see a list of tables that contain a column with the name 'email.' You can right-click on these tables to perform various actions.

While SSMS is user-friendly, keep in mind that this method is limited to one database at a time. If you need to search across multiple databases or automate the process, we'll explore more SQL-based methods next.

Approach 2: Using SQL Queries

Now, let's get our hands dirty with some SQL queries. This approach offers more flexibility and can be automated for larger tasks.

Method 1: Information Schema

SQL Server provides a special system view called information_schema.columns that stores information about columns in all tables within a database. We can query this view to find tables containing our desired column. Here's a sample SQL query:

SELECT TABLE_NAME
FROM information_schema.columns
WHERE COLUMN_NAME = 'email';

This query retrieves the names of all tables that have a column named 'email' in the current database. You can adapt it to search in specific databases by changing the information_schema.columns reference accordingly.




Method 2: Using sys.tables and sys.columns

Another way to tackle this is by using the system views sys.tables and sys.columns provided by SQL Server. These views give you more control over the information you retrieve. Here's a query using this method:

SELECT t.name AS table_name
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'email';

This query joins the sys.tables and sys.columns views to list the names of tables that contain a 'email' column. It's slightly more verbose but offers greater flexibility.

Conclusion

Congratulations! You've just mastered the art of finding tables with specific columns in SQL Server. Whether you prefer the user-friendly environment of SQL Server Management Studio or the flexibility of SQL queries, you now have the skills to unearth hidden database treasures.

Remember, SQL is all about exploration and problem-solving, so don't hesitate to apply these techniques to real-world scenarios. With a bit of curiosity and the right SQL tools, you can navigate even the most labyrinthine databases. Happy SQL hunting!

FAQ: Your Burning Questions Answered

Q1: Can I search for columns across all databases on my SQL Server instance?

Yes, you can. To search across all databases, you'll need to create a dynamic SQL query that iterates through the databases on your server. It's a bit more advanced but doable.

Q2: What if I want to find columns with similar names, like 'email' and 'e-mail'?

For such cases, you can modify your query to use wildcard characters in the WHERE clause. For example, WHERE COLUMN_NAME LIKE '%email%' would find columns with 'email' anywhere in their names.




Q3: Is there a way to retrieve additional information about these tables, like schema names or data types of the columns?

Absolutely! You can expand your query to include more columns from the information_schema.columns view or join other system views to retrieve additional information.

Q4: Can I automate this search and export the results to a file or another database?

Yes, you can. You can use SQL Server's SQLCMD utility or PowerShell scripts to automate this task, export the results, and perform further actions.

Q5: What if I'm working with SQL Server versions older than 2005?

While the methods described here should work for most SQL Server versions, some older versions may require slight variations in the SQL queries. Be sure to check your version's documentation for any specific differences.

Quiz Time: Test Your SQL Explorer Skills

Let's put your SQL exploration skills to the test with a quick quiz:

1. What is the primary advantage of using SQL queries to find tables with specific columns?

a) It's quicker than using SQL Server Management Studio.
b) It provides more flexibility and can be automated.
c) It doesn't require any prior SQL knowledge.

2. Which SQL system view stores information about columns in all tables within a database?

a) sys.tables
b) sys.columns
c) information_schema.columns

3. How can you search for columns with similar names, such as 'email' and 'e-mail'?

a) Modify the SQL Server Management Studio filter settings.
b) Use wildcard characters in the SQL query's WHERE clause.
c) Manually inspect each table's columns.

Sunday, January 14, 2024

How to combine AND and OR operators in SQL Queries? Example Tutorial

 You can use Parentheses to combine AND and OR in complex SQL statements. Combining AND and OR operators in SQL queries allows you to create more complex conditions to filter data from your database. Parentheses allows you to control the order of evaluation. Here's an example of how you can combine AND and OR operators in a SQL query. Let's see an example to understand this concept better. 

Difference between Equi Join and Non-Equi Join in SQL with Examples

Hello guys, If you want to learn about the difference between Equi and Non-Equi join in SQL then you have come to the right place. Earlier, I have shared the difference between LEFT and RIGHT join in SQL and now we will understand equi and non-equi join in SQL and database. The term "Non-Equi join" in SQL Server may sound abstract, but it's far from it. Equi joins are the same way. I think you'll agree with me after reading this essay. I'll attempt to explain what they are and when they should be used today. So, let's get this party started. 

Friday, January 12, 2024

10 Example of SubQuery in SQL

Hey there! Today, we're diving headfirst into the fascinating world of SQL subqueries. As someone who's spent countless hours exploring databases, almost 24 years now, I'm excited to show you how these versatile subqueries can supercharge your SQL game. Along with Joins, subquery is one of the must know concept for SQL developers. It's also slightly easier to understand then join, especially non-correlated subquery. Correlated subquery is  slightly harder to understand but don't worry these 10 example will ensure you know subquery well. 

Top 10 Websites to Learn SQL for FREE

If you want to learn SQL and looking for free resources then you are not alone, I too have learned a lot from free resources and that's what I am going to share in this article.  In the dynamic landscape of data management and analysis, Structured Query Language (SQL) stands as a cornerstone for interacting with databases. 

How to Sort or Order results in SQL query? ORDER BY Example Tutorial

Ordering results in SQL is a fundamental aspect of retrieving and presenting data in a meaningful way. When querying a database, the order in which the results are displayed can significantly impact the user's understanding of the information. The SQL ORDER BY clause provides a powerful mechanism to sort query results based on one or more columns in ascending or descending order. Whether you are working with a small dataset or a large database, understanding how to order results allows you to tailor the presentation of information to meet specific requirements. 

How to use ROW_NUMBER() Function in SQL with nondeterministic order? Example Tutorial

The ROW NUMBER window function has a wide range of uses, much beyond the apparent ordering requirements. Most of the time, while computing row numbers, you need to do it in a specified order, which you provide in the function's window order clause. 

Thursday, January 11, 2024

How to filter data in SQL? WHERE, LIKE, and BETWEEN Example

In SQL, you can filter data using the WHERE clause in your queries. The WHERE clause allows you to specify a condition that must be met for a row to be included in the result set. You can use basic conditional operator, logical operator to filter based upon any condition as well as LIKE operator filter based upon any pattern. You can also filter on rang using IN and BETWEEN clause whose examples we will see in this article. 

Monday, January 8, 2024

Difference between Primary and Foreign key in Database and SQL

Hello guys, if you are preparing for database or developer interview then questions form primary key is very common on interviews. One of such question is primary key vs foreign key which is asked ot me multiple times during early stage of my career. Now my experience has grown so I don't see this question quite often but the concept is still very important for any database admin or programmer to understand. 

The key difference between primary key and foreign key is that primary key on one table can be foreign key on other related table. 

For example, you have two tables Employee and Department, each have primary key emp_id and dept_id, now if you want to link or join these two table then you created a column called dept_id on employee table to indicate which department a particular employee work and now dept_id is a foreign key in Employee table. 

How to find Nth Highest Salary of Employee in SQL? Example Tutorial

The Problem description

Finding the Nth highest salary of workers, where N might be 2, 3, 4, or anything, is one of the most typical SQL interview questions. This query is sometimes rephrased as "find the nth minimal wage in SQL." Many programmers struggle to design the SQL query when the Interviewer keeps asking for the 4th highest, 5th highest, and so on since they only know the easiest approach to address this problem, like by utilizing the SQL IN clause, which doesn't scale well. 

Sunday, January 7, 2024

Difference between Correlated and Non-Correlated SubQuery in SQL

What is difference between correlated and non-correlated subquery in SQL?
A correlated subquery in SQL is a subquery that is dependent on the outer query. This means that the subquery relies on data from the outer query to provide a result. The subquery is executed for each row in the outer query and its result is used in the WHERE clause of the outer query. While, a non-correlated subquery, on the other hand, is independent of the outer query and is executed only once. The result of the subquery is stored in a temporary table, which is then used by the outer query.

Top 15 SQL Query Interview Questions for Practice (with Solutions)

Hello folks, if you are preparing for technical interviews for software developer, system admin, data analyst, or data scientist, one skill you must prepare is SQL. It's one of the important topic for programmers, support engineers and DBAs as you have to deal with SQL in your day to day job. If you don't know, SQL is the declarative language that allows you to interact with the database. By using SQL you can insert, update, search, and delete data in a relational database. The code you write is called SQL queries and it's one of the essential skills for many technical people who have to deal with databases. That's why SQL queries are very popular in interviews and you will often see one or more SQL queries on interviews. 

How to use LEFT, RIGHT, INNER, OUTER, FULL, and SELF JOINs in SQL? Example Tutorial

What are joins and why are they required?

Hello, everyone. Have you ever faced any issues while generating reports or loading data but were using data of just one table was not enough? One table's data is typically insufficient to provide important insights when doing sophisticated analytical processing and data discovery, therefore merging numerous tables is required. SQL, as a method for communicating with relational databases, allows you to create relationships between tables