Scenario-based SQL interview questions you must know Part-2
In this post, I am listing all the SQL scenario-based interview questions that I have faced in the last 8+ years of work experience. Hope this helps you out!
Q1) Let’s say I don’t permit you to create a foreign key what would be an equivalent way of implementing the foreign key? Let me give you an example.
Let’s say there is an employee and department table. Each employee belongs to a department. Usually, you would have a foreign key for the department id in the employee table to make sure that each employee is admitted to the correct department. Suppose you don’t have this relationship and you are forbidden to create a foreign key, what would you do? How would you implement this scenario?
How would you make sure that when I insert the employee record, I am inserting a correct department id (i.e. a department id that exists inside the department table)?
Ans: I can create a trigger. This trigger will execute when an insert statement is fired on the employee table. This trigger will check if the department id for this new record exists or not in the department table. This way I will be able to mock the foreign key behavior without creating it.
Q2) What is the difference between a primary key and a unique non-clustered index?
Ans: If you are aware of the primary key, you would know that a primary key automatically creates a clustered index. So basically the interviewer wanted to ask me the difference between a clustered and a non-clustered index. Below was my answer:
If you have a Primary key on a column, it automatically creates a clustered index. The clustered index stores the data rows in a table in sorted order. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
A unique non-clustered index is built using the B-tree structure. The data and the Non-clustered index are stored separately thus claiming more storage space. You can have multiple non-clustered indexes on a table
Q3) What is the difference between a function and a stored procedure except for the fact that a function returns a value and a stored procedure doesn’t?
Ans: If you want to use transaction you must use a stored procedure as transactions are not allowed in the function.
Another difference is that a function can be called inside a select query but a stored procedure is not.
Q4) Suppose I have created a global temporary table say GT1 and I have inserted 5 records in it. I am still logged in to the system and then you now have also logged into the system. And then you try to access the GT1 table. a) Will you be able to access GT1? Ans: Yes
b) will you see an empty table or will you see 5 records? Ans: I will see all 5 records
c) Suppose I have created GT1 and now I have disconnected my connection. But you are still logged in. Will you see no table or an empty table or all records in the table? Ans: I will still see all 5 records in the table.
d) Suppose now both of us have disconnected the connection and you have later logged into the server. Still, will you see all the records? Ans: Yes
In short, you must drop the global temporary table otherwise it will continue to live inside the database memory. It will continue to exist until the SQL server is recycled or restarted.
Q5) An architect designed a Stored Procedure in such a way that it should have taken 15 mins to complete. But when the junior developer wrote the script and ran the SP it was taking 1 hour to complete. The junior developer confirmed that this is an issue with the stored procedure and the data and network are not issues here. The data is not blocked and the infrastructure is not overloaded. How would you help the junior developer to rectify the delay in the stored procedure?
Ans: This is another way of asking how to improve the performance of a stored procedure. There are three steps we can take to improve the performance.
a) Remove any sub-queries from the Stored procedure.
b) Rebuilt the indexes on the table.
c) Avoid using temporary tables and functions.
Q6) You have a bank account table that has a column Active. This column contains either 1 or 0. 1 means the account is active and 0 means the account is inactive. Due to some glitch, the values in the column got reversed. So all the values with 0 were replaced by 1 and 1 was replaced by 0. What query would you run to make sure that the correct values are updated? Note: you can not roll back.
Ans: We would write a query with a switch case where if the value is 1 we would update it with 0 and vice-versa.
You can use if-else as well.
Q7) Let’s say I have a student and course table. One student can enroll in multiple courses. One course can have multiple students in it. How will you design the database table for such a scenario? (Many to Many relations) Hint: What is a junction Table?
Ans: We can have a student table with the primary key student id. And we can have a course table with the primary key course id. We can have a third table (a junction table) where we can map students to their respective courses.
Q8) I have a parent-child relationship for product categories and sub-categories. How will you design the table structure? Example: Let’s say I have a category of food that has subcategories like snacks and Meals. Further, let’s say snacks also have subcategories like appetizers and sweets. And sweets also have subcategories like Milk-based and non-milk based. So here we have a parent-child relationship. So how will you create such a database structure? (Hierarchical database structure)
Ans: We can have one table with a list of all types and each row will have a parent id. So, if I have added Milkshake to the table its parent will be Milk-based, milk-based parent id will be sweets. Sweets parent id will be Snaks and Food will be the root element.
I have implemented such a scenario in one of the projects where I had to save folder structures in the database. If you want to get data from such a database, you can do self-joins.
Q9) I have three transactions one is selected with ON LOCK; one is selected with no lock and the third will update. What will happen if I fire all three transactions same time?
Let me know if you find a good answer to this. I am still looking.
Q10) I have an index on the column Stud_Name. I am searching for a name using a wild card. My query is
a) select * from Stud_Details where Stud_Name like ‘A%’
b) select * from Stud_Details where Stud_Name like ‘%A’.
c) select * from Stud_Details where Stud_Name not like ‘A%’
In which case would the SQL server use the Index, that I have created on Stud_Name?
I am still looking for a good explanation for this question. I also added this question on stack overflow. You can check this link for a detailed discussion of my stack overflow question. People are divided on this question. Add comments in this post and we can discuss what should be the right answer for this. But to the best of my knowledge, I can say that for option (a) index will be used. For options (b) and <c> index will not be used.
Question added on 28/9/2023
Q11) I want to delete a record from a table but I want to log the details of this record in the audit table only after it has successfully deleted. How can I add the records details in the audit table after it has been deleted?
Ans: We need to write the After triggers. The after triggers have access to magic tables that have the details of recently updated or deleted records. We can access these magic table “INSERTED” and “DELETED” only via After triggger. These tables are not accessible otherwise. We can read data from “DELETED” magic table and add record to the audit table.
I will keep updating this list as I face more interviews. If you are interested in more real-time interview question related to SQL check my other post here [SQL interview questions you must know — Part 1]
Also check out this [Scenario-based SQL interview questions you must know Part-2] to get access to real time scenario based SQL questions that were asked in many interviews I have given in 8+ years.
Liked the post? If you found this useful consider buying me a coffee here https://bmc.link/shreetanu9R
