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.