How to use WHERE and HAVING clause in SQL? Example Tutorial

Hello guys, if you want to learn about WHERE and HAVING clause in SQL then you have come to the right place. This blog shall be explaining how to use WHERE and HAVING clauses in SQL. but it won't be fine if we just jump to it. We need a good understanding of what SQL is and what it is used for. SQL stands for structured queried language, as it sounds it is a language or syntax that is used to query the database.

Difference between MySQL vs NoSQL (non relational DB) - Pros and Cons

Hello guys, if you are wondering whether to choose a NoSQL database like MongoDB, Cassandara, or MySQL for your application then you have come to the right place. In an earlier article, MySQL vs PostgreSQL I had shared the pros and cons of choosing MySQL over PostgreSQL for your application, and in this article, I am going to show the benefits and drawbacks of choosing MySQL over NoSQL or vice-versa. 

How to do Pagination in SQL Server? Query Example Tutorial

 What is pagination?

Pagination, often known as paging, is a method of breaking down huge amounts of material into smaller, distinct pages. Pagination is a popular feature of online applications, as evidenced by Google. The primary principle of pagination is that when we search for anything on Google, the results are displayed on a different page.

Transposing data in SQL - Example Tutorial

What is Transposition?

A transposition is when data is rotated from one row or column to another in order to modify the data layout and make observations from a different perspective. Row to the column, column to column, and bidirectional transposition are examples of basic transposition algorithms. Others, such as dynamic transposition, transposition with inter-row computations, and join-based transposition, are more complicated. All are prevalent in data analytics and so worthy of investigation.

SQL cross join Example and it's performance

Hello guys, in the past, I gave you a quick overview of SQL JOINS where we learned about different types of JOINS like right, left, inner, and outer join, and in this article, we'll learn about the SQL CROSS JOIN idea in this post, and we'll back up our knowledge with simple examples and illustrations. So, let's start with the basics.

Difference between DDL and DML commands in SQL with examples

What are SQL commands?

SQL commands are just that: commands. It's used to send and receive data from the database. It may also be used to carry out particular activities, functions, and data searches.

SQL can build tables, add data to tables, drop tables, change tables, and define permissions for users, among other things.

MySQL vs PostgreSQL? Pros and Cons

When establishing a new project, especially one on the Web, choosing a database management system is frequently an afterthought. Most frameworks include an object-relational mapping (ORM) tool that hides the differences across platforms and makes them all run at the same speed.

What is Virtual columns in MySQL and How to use it? Example Tutorial

 What is a Virtual Column and where is it used?

Virtual/generated columns are a new feature in MySQL 5.7. Because the data in this column is computed based on a specified expression or from other columns, it is called a generated column.

Virtual columns resemble regular table columns in appearance, but their values are generated rather than saved on a disc.

Virtual columns are one of MySQL 5.7's most useful features; they allow you to store a value in a new field that is derived from one or more other fields in the same table.

Top 5 Common Errors and Mistakes in SQL and How to Solve Them

After writing some SQL code, you're ready to query your database. You type in the code, and nothing happens. Instead, you're presented with an error notice.  Don't give up! SQL, like any other computer language, is prone to coding mistakes. We'll go through a few frequent mistakes individuals make while creating SQL in this tutorial.

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. 

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. 

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 concatenate columns in SQL Server? Example Tutorial

Hello guys, welcome to my new blog SQLrevisited. I have long back created this blog to exclusively share SQL and Database articles but never get time to start it until today. This is the first article on this blog and in this post, you will learn how to contact multiple columns in an SQL server.  Like Java and many other programming languages and databases, you can use the + operator to concatenate multiple columns in SQL Server. You can join as many columns as you want with the + operator but you must remember that concatenating with NULL will produce NULL, which means you can lose the information from other columns if one of them is NULL. 

Top 6 Free SQL Books For Beginners and Experienced [PDF + Online]

 There is no doubt that SQL is one of the most essential skills for Programmers, IT professionals, Software Engineers, Quality Analysts, Project Manager, Data scientists, Database admins, and Business Analysts. I had even mentioned this as one of the top skills in my post about 10 things every programmer should know, if you haven't read that yet, you can read it here, it's completely worth your time. Since many Enterprise applications use the relational database at their backend, like Oracle, Microsoft SQL Server, MySQL, it's crucial to learn SQL to work with those applications and use the data stored on those databases.

How to join two unrelated tables in SQL? Cross Join Example

 In one of the recent programming job interviews, one of my readers was asked the question, how do you join two tables which are not related to each other? i.e. they don't have any common column? is it possible in SQL? My reader got confused because he only knows about INNER join and OUTER join which require a key column like dept_id which is the primary key in one table like Department and foreign key in another table like Employee. He couldn't answer the question, though he did tell them about you can select data from multiple tables by typing multiple table names in from clause using a comma. 

Difference between UNION and UNION ALL in SQL Server?

 Hello guys, what is the difference between UNION vs UNION ALL is one of the most popular SQL interview questions and often asked programmers during a telephonic round of interviews. Though both UNION and UNION ALL is used to combine results of two SELECT queries, the main difference between them is that UNION doesn't include duplicate record but UNION ALL does. Another difference between them is that UNION ALL is faster than UNION but may look slow because it returns more data which takes more time to travel via the network. The difference between UNION and UNION ALL can be a tricky SQL question, especially for developers, who have not used this useful keyword ever. 

10 Examples of ALTER Table Command in SQL

 In this SQL tutorial, you will learn how to use ALTER command in the table on the database. ALTER command is mainly used to add, modify and drop columns, indexes, and constraints on the table in relational databases e.g. MySQL, Oracle, Sybase, and SQL Server.  Though ALTER is not part of classical CRUD operation but it’s one of the important DDL commands. One of the most frequent uses of ALTER command in SQL is adding and removing indexes to improve the performance of SQL SELECT queries.

Difference between View vs Materialized View in Database or SQL

 The difference between View and Materialized view is one of the popular SQL interview questions, much like truncate vs delete, correlated vs noncorrelated subquery, or primary key vs unique key. This is one of the classic questions which keeps appearing in SQL interviews now and then and you simply can’t afford to learn about them. Doesn’t matter if you are a programmer, developer, or DBA, these SQL questions are common to all. Views are a concept which not every programmer is familiar with, it is simply not in the category of CRUD operation or database transactions or SELECT query, its little advanced concept for the average programmer.

When to use TRUNCATE vs DELETE command in SQL?

 While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make the table ready for the next day's transaction, or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete and drop. The last one is a bit different because instead of removing data it just deletes the table. What is the difference between truncate and delete command in SQL or when to use truncate vs delete is one of the most frequently asked SQL interview questions?

How to use JOIN Multiple Tables in MySQL? Example Tutorial

 Hello guys, if you are wondering how to join multiple tables in SQL to produce a combine result which contains columns from all tables but not sure how to do it then you have come to the right place. SQL Join is one of the basic concepts while working in databases or tables but yet less understood and most confusing topic for beginners and even intermediate developers. I compare Joins in SQL with Recursion in programming in terms of confusion because I have found that these two topics are special in their nature and you can't get it right with casual reading until you understand the concept and its various well. Things get worse when the table locked due to such SQL Join queries which were fired without knowing how much time it would and how big the result set could be. 

What is table scan, index scan, and index seek in SQL? Interview Question

 Hello guys, a good understanding of how the index works and how to use them to improve your SQL query performance is very important while working in a database and SQL and that's why you will find many questions based upon indexes on Programming Job interviews. One of such frequently asked SQL questions is the real difference between table scan, index scan, and index seek? which one is faster and why? How does the database chooses which scan or seek to use? and How you can optimize the performance of your SQL SELECT queries by using this knowledge. In general, there are only two ways in which your query engine retrieves the data, using a table scan or by using an index.

Difference between Self and Equi Join in MySQL

 The main difference between Self Join and Equi Join is that In Self Join we join one table to itself rather than joining two tables. Both Self Join and Equi Join are types of INNER Join in SQL, but there is a subtle difference between the two. Any INNER Join with equal as join predicate is known as Equi Join. SQL Joins are the fundamental concept of SQL similar to correlated and noncorrelated subqueries or using group by clause and a good understanding of various types of SQL join is a must for any programmer.

Don't use SELECT * in Production Query

 Hello guys, if you are doing a code review and see a SELECT * in production code, would you allow it? Well, its not a simple question as it looks like but let's find out pros and cons about using SELECT * in a production SQL query and then decide. I have read many articles on the internet where people suggest that using SELECT * in SQL queries is a bad practice and you should always avoid that, but they never care to explain why? Some of them will say you should always use an explicit list of columns in your SQL query, which is a good suggestion and one of the SQL best practices I teach to junior programmers, but many of them don't explain the reason behind it. 

When to use EXISTS and NOT EXISTS in SQL? SQL Server Example

 Hello guys, if you are wondering how to use the IF EXISTS and NOT EXISTS in SQL then you are at the right place. Earlier, I have shared how to use GROUP BY, WHERE, and HAVING clause and in this tutorial I will share how to use exists and not exists clause in SQL. The IF EXISTS and NOT EXISTS commands in T-SQL are covered in depth in this article. When comparing data sets using subqueries, it also illustrates why EXISTS should be preferred over IN and NOT EXISTS over NOT IN. 

Top 50 Database and SQL Interview Questions Answers for 2 to 5 Years Experienced

 Hello guys, whether you are preparing for Java developer interview or any other Software Developer Interview, a DevOps Interview or a IT support interview, you should prepare for SQL and Database related questions. Along with Data Structure, Algorithms, System Design, and Computer Fundamentals, SQL and Database are two of the essential topic for programming or technical Job interview. In the past, I have shared many questions related to MSSQL questions, Oracle Questions, MySQL questions, and PostgreSQL questions and in this article, I am going to share both theory based and query based Database and SQL Interview questions with to the points answers.

What is temporary table? Example Tutorial

Hello folks, if you have heard the term temporary table online or by your colleague in a call and wondering what is temporary table, what is the difference between a normal table and a temporary table, and when and how to use it then you have come to the right place. In this blog, we shall learn how to use a temporary table in SQL. But before we go into that we need an overview of SQL. What is SQL? SQL stands for Structured query language. it is a programming language used in communicating or relating to the relational database. 

Difference between 1NF, 2nd NF, 3rd NF and BCNF? Normalization Example Tutorial

Normalization is one of the essential concept of relational database. It is the process or technique to remove duplicate data from tables and thus reduce the storage size. It also helps to maintain integrity of data. Normalization likewise assists with coordinating the information in the data set. It is a multi-step process that sets the information into even structure and eliminates the copied information from the relational tables. 

How to convert String to Integer SQL? Example Tutorial

Hello guys, if you are wondering how to convert VARCHAR to String in SQL then you are at the right place. In this article, we will cover various techniques to convert String to Integer in all databases - MySQL, Oracle, SQL server and PostgreSQL. To perform tasks or correlations or change between information in a SQL Server data set, the SQL information kinds of those values should coordinate.

Top 10 SQL Commands and Functions Every Developer should learn

 Hello guys, if you are starting with SQL and wondering which commands you should learn first then you have come at the right place. In this article, I have shared 10 most essential SQL commands and functions which I believe every programmer should learn. This includes commands to pull data from database as well write data into data, update data, and remove data from database. While writing in the SQL language, you will utilize an assortment of SQL keywords to make explanations and questions. An assertion includes a series of characters and SQL keywords that adjusts to the designing and grammar rules of the language and may influence information or control processes corresponding to your information. 

How to remove tables in Oracle, MySQL and SQL Server? DROP table Example

 Hello guys, if you want to learn about DROP command in SQL then you have come to the right place. Earlier, I have shared the best free SQL and Database courses and several tutorials to learn SELECT, GROUP BY, and other important commands, and in this article, I will show you to use the DROP command in SQL. DROP is one of the basic SQL commands, which is used to DROP database objects. Since it's part of ANSI SQL, the DROP command is supported by all major database vendors, including Oracle, MySQL, and Microsoft SQL Server. 

Top 20 PL/SQL Interview Questions with Answers for 1 to 3 years

Hello guys, if you are preparing for Oracle or PL/SQL Job or a Developer job where PL/SQL skills are needed and looking for frequently asked PL/SQL Interview Questions then you have come to the right place. Earlier, I have shared both best PL/SQL courses and best PL/SQL books and in this article, I am going to share popular PL/SQL Interview Questions you can revise before your interview. Preparing for PL/SQL Interview is not a rocket science and you can prepare for it the same way you prepare for other interviews. 

Difference between VARCHAR vs CHAR data type in SQL Server? (with Example)

 Hello all, today, I am going to share an interesting SQL Server interview question, which will not only help you in your interview but also in your day-to-day work. It explains one of the critical concepts of SQL Server, the difference between VARCHAR and CHAR data type. I am sure, you all have used both of them numerous times but because it's so common many of us ignore the difference between them and when asked to choose between VARCHAR and CHAR on interviews, they fail to give a convincing reason. 

Top 20 MySQL Interview Questions and Answers for 1 to 3 years Experienced

Hello guys, if you are preparing for MySQL and Database interview or a Java developer role with MySQL experience and looking for frequently asked MySQL interview questions and answers then you have come to the right place. Earlier, I have shared popular SQL Interview Questions and common SQL queries from Interviews and in this article, I will share frequently asked MySQL database interview questions with answers. MySQL is very vital as far as web application is concerned. Therefore, this is an area that cannot be taken for granted. This simply means that various questions regarding MySQL have to be asked whenever you are seeking a job.

Second Highest Salary in MySQL and Microsoft SQL Server - LeetCode Solution

Hello guys, if you are practicing SQL or preparing for tech interview then you may have seen this problem on Leetcode, write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return NULL. You can write SQL query in any of your favorite databases e.g. MySQL, Microsoft SQL Server, or Oracle. 

Correlated vs Non-correlated subquery in SQL

Hello guys, the difference between correlated and non-correlated subquery is one of the popular SQL interview question and an important concept for any programmer to learn. The correlated subquery is one of the tricky concepts of SQL. It's similar to recursion in programming which many programmers struggle to understand, but like recursion, it also offers the unique capability to solve many SQL query-based problems like the second-highest salary problem where you need to compare one row of the table to another row. It gives you a different kind of power. 

Difference between Primary key vs Unique key in a Table

The primary key and unique key are two important concepts in a relational database and are used to uniquely identify a row in a table. 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. In fact, primary key vs unique is a popular SQL interview question along with classics like truncate vs delete and  How to manage transactions in a database, mostly asked to fresher and 2 to 3 years experience guys in any programming language. 

How to get the version of Microsoft SQL Server Database ? 3 Examples

The easiest way to find the version of SQL SERVER you are running is executing SELECT @@VERSION query into SQL Server Management Studio. It will display full detail of product as shown below :

SELECT @@VERSION

This will print product, version, operating system and other details.

It will print Many times while working with SQL SERVER database, we need the version and this is the quickest way to get it.

But this is not the only way, you can also get the SQL SERVER version from Object explorer by right click on any node and looking at details, which we will learn in this article. 

Why Knowing Your SQL Server Version is Important

Before we start, let's talk about why this matters. Knowing your SQL Server version is like knowing which grade you're in at school. It helps you:

1. Use the right tools and tricks
2. Keep your database safe and happy
3. Know when it's time for an upgrade

Now, let's dive into our three easy ways to find out your SQL Server version!

In this blog post we will se three examples of how to get the SQL Server version:

• Using the @@VERSION command
• Using the SERVERPROPERTY function
• Checking the version in SQL Server Management Studio

Example 1: Ask SQL Server Directly

Imagine you could ask SQL Server, "Hey, how old are you?" Well, you can! Here's how:

SELECT @@VERSION

When you run this, SQL Server will tell you everything about itself, including its version. It's like it's introducing itself at a party!

Here is sample output but it can vary depending upon where exactly you are running this 

Microsoft SQL Server 2019 (RTM-CU8) (KB5000642) - 15.0.4083.2 (X64) 

    Feb 15 2021 01:24:17 

    Copyright (C) 2019 Microsoft Corporation

    Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19042: )

Example 2: Use a Special Built-in Function

SQL Server has a special function that tells you its version. It's like a secret handshake! Here's how to use it:

SELECT SERVERPROPERTY('ProductVersion')

This will give you a number like "15.0.2000.5". The first part (15) is the main version number.

Example 3: Check the Version in SQL Server Management Studio

If you're using SQL Server Management Studio (SSMS), finding the version is as easy as looking at the label on your lunchbox:

1. Open SSMS
2. Connect to your SQL Server
3. Right-click on the server name
4. Click on "Properties"
5. Look for "Version" in the window that pops up

It's like peeking at the answer sheet, but it's okay because it's your own SQL Server as shown below:

That's all about 3 ways to find the version of Microsoft SQL Server. Now you know three super easy ways to find out which version of SQL Server you're using. It's important to know this so you can take good care of your database and use all the cool features available.

Remember, keeping your SQL Server up-to-date is like getting new shoes when you outgrow your old ones. It helps you run faster and do more!

Other SQL Tutorials and Articles you may like

• How to use WHERE and HAVING clause in SQL
• How to convert String to Date in SQL?
• Difference between Primary key and Unique key
• Does order of columns matter in Composite index?
• Difference between Self and Equi join in SQL
• 10 Example of SELECT queries in SQL
• 6 Examples of Correlated subqueries for beginners
• How to get rows between two given dates in MSSQL
• How to remove duplicate rows from table in SQL Server
• How to create and rollback transaction in database?
• 4 Examples of CASE expression in SQL Server
• Difference between UNION and UNION ALL in SQL
• 10 Examples of ALTER command in SQL

Thank you for reading this article so far. Feel free to ask if you have any doubts or questions, happy to answer any query. 

How to convert DATETIME to VARCHAR in Microsoft SQL Server? Example Tutorial

One of the most common, yet trick for many programmer, is to convert a DATETIME column into VARCHAR i.e. String in SQL Server. Suppose, in People table, you have a datetime column, birthdate and you want to print the name and birthdate into dd-MM-yyyy format, you need to convert the datetime column into varchar. You can use the CONVERT() function to do this job. It accepts three arguments, the data, the output type and the format in which you want your VARCHAR date e.g. to convert birthdate of a person into dd-MM-yyyy, we need to use the formatting style 1.

What is Primary key in SQL? How to choose Primary key in table? Example

If you've ever dealt with databases, you've almost certainly come across the term - primary key (PK). And if you're following this series to learn about databases, this piece should help you understand what the PK is all about. Even if you're a database specialist, you could learn something new or improve your skills. So sit back, relax, and let's get started with the PKs.

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

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. 

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. 

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.

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?

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.

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.

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.

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.

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. 

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.

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!

Other Related Articles

10 Ways to use GROUP BY command in SQL With Examples

10 ways to use SELECT command in SQL With Examples

How to remove duplicate values from a table? Example

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.

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. 

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 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. 

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