SQL Interview Questions You Need to Know NOW

Key Takeaways

You are in a SQL interview and are given two tables with same structure. >> [music] >> The data in them is supposed to be in sync, but it isn't. You are asked to compare the tables and create a report [music] showing whether the data exist in both tables or only in one, and if so, which one? This is a classic SQL interview question that test your knowledge of SQL joins. So, let's start. Here are two tables. As you can see, both have the exact same structure. For demonstration, I have also inserted some data into them. Now, we need to write an SQL query that shows data from both tables, so we can identify where the data is in sync and where it is missing. To create a comprehensive report that shows the data in the desired format, we will use a full outer join. Let's do that. In this query, we are retrieving employee names from both tables. T1.emp_name comes from the first table employees_t1 and t2.emp_name comes from the second table employees_t2. Since we're using a full outer join, we get a complete report. Let's execute and see the result. Here we are. Let's analyze this output to understand it better. As you can see that employee Amit is present in table one, whereas it is absent from table two. It should be there, but it isn't. The employee Neha is present in both tables. Rahul is again present in table one, but not in table two. The last recording employee Pooja is present in table two, but not in table one. Some records are in sync, but some are not. So, how do we show this? To show this, we we take the help of a case statement. What will the case statement do? It will tell us where the data exists. See, if the employee name is missing from table two, it means the data is present in table one. If the data is null in table one, it means the data is present in table two. If the data is present in both tables, it means the data exists in both. That's exactly how we will design our case [music] statement. The column created by this case statement will be named row {underscore} status. It has two conditions or when clauses. The first when clause checks when the data is null in table one, which means the data is present in table two. The second when clause checks when the data is null in table two, which means the data is present in table one. The else clause comes into action when the data is present in both tables. Let's execute it and see the result. Here you go. Most people would think the work is done here, but we will go one notch further, which will definitely impress the interviewer. Next, we will use this query to create a report showing how many rows are present only in table one, only in table two, and in both tables. That will look something like this. To achieve this, we will use a subquery along with the count aggregate function and a group by clause like this. >> So far what we have done is write a simple select a statement. This is statement groups the data based on the categories created by our case statement. For example, one category is only in table one, which indicates the data exists only in table one and so on. As you can see, after the from keyword, we have placed a pair of parentheses. Inside these parentheses, we will paste our SQL query that we wrote earlier. Let's execute it. And here's our report. Hey, are you interested in learning SQL and want to learn it properly? Then in next four days, I'm starting my new batches for live SQL corporate training. This is not going to be the one where you sit and watch PowerPoint slides. >> [music] >> This is going to be the one where you will be writing queries, focusing solely on SQL and solving problems. And in case if you have any doubts and questions, then I will be there to solve and answer your questions live. If you are interested, then check out the link in the description. Enrollment will be closing soon and seats are extremely limited. As you can see, this simple-looking interview question isn't that scary. Even though it tests your knowledge of SQL joins, subqueries, aggregate functions, and the group by clause. Comment part two if you want me to bring its continuation. Thanks for watching. This is Varish from rebellionrider.com. >> Mhm.