Q1) What are different types of joins?
a) Inner Join: The INNER JOIN keyword selects records that have matching values in both tables.
b) Left Join: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
c) Right Join: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
d) Self-Join: A self-JOIN is a regular join, but the table is joined with itself. It can be used in scenario where in the table of the employes I want to check which two employes are from same city.
e) Cross Join : is used to combine each row of one table with each row of another table, return the product of the sets of rows from the tables that are joined.
Q2) What is the difference between cross join and full/outer join?
Cross join don’t have a ON clause as everything is joined with everything. WHEREAS, outer join is combination of left outer join and right outer join. Outer join returns those rows that matches the where clause and show null values for the rows, the ON conditions isn’t met.
Q3) What is normalization? What are all the different normalizations?
Normalization is the process of minimizing repetition and dependency, by organizing fields and table of a database in a more efficient way. It helps us to remove null, duplicate values and enables efficient indexing.
Different types of normalization are:
- 1NF — Removes duplicated attributes, Attribute data should be atomic, and of same kind.
- 2NF — Should be in 1NF and each non-key field is fully dependent on the primary key.
- 3NF — Should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed.
Q4) What is a View? Can I update data in the table using a view?
A view is a virtual table which consists of a subset of data contained in one or more real database table.
View is used when we need to give access to limited amount of data. We can give permission to query a view for a table while denied access to the original table. It acts as a security measure.
Yes, you can update data in the table using a view by using a simple update statements.
Q5) What is an Index? What are all the different types of indexes?
Indexes are special lookup tables that are used by database search engine for faster retrieval of the data. Simply put, an index is a pointer to data in a table. It is like an Index page of a book
Clustered: It stores the data rows in a table in sorted order. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap. You can only have one clustered index on a table. And if you have a PK on the table it automatically creates a clustered index.
Non-clustered: The non-clustered index is created to improve the performance of frequently used queries not covered by clustered index. 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 index on a table or view.
Following differences between clustered and non-clustered indexes.
- There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
- Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
3. Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step
Q6) What is a Cursor?
A SQL query produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time.
Performance of Cursor:
When you write a SELECT statement (that returns 1051 rows) to fetch a bunch of data from that database the system receives the statement and creates or uses an existing query plan, then it uses indexes to locate the data on the disk, fetches the data in one foul swoop and returns the data as a set.
But, cursor does it on a row by row basis, and it takes longer to do so. This is because the set-based logic for which RDBMS systems like SQL Server are optimized is completely broken and the entire query process is repeated for each row.
Q7) What is a trigger?
A trigger is a special type of store d procedure that automatically runs when an event occurs in the database server. There are 3 types of triggers
a) DDL (Data Definition Language) triggers: We can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system-defined stored procedures that perform DDL-like operations. DDL trigger can be used to observe and control actions performed on the server, and to audit these operations.
b) DML (Data Modification Language) triggers: In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. These triggers are of two types:
· After Trigger: This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it. If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
· Instead of Trigger: An INSTEAD OF trigger is a trigger that allows you to skip an INSERT, DELETE, or UPDATE statement to a table or a view and execute other statements defined in the trigger instead.
c) Logon Triggers: Logon triggers are a special type of trigger that fire when LOGON event of SQL Server is raised. We can use these triggers to audit and to track login activity or limit the number of sessions for a specific login.
Q8) What is the difference between DELETE and TRUNCATE commands?
Note: Truncate is a logged operation, it just doesn’t log removing the records but it logs the page deallocation, whereas delete logs every single record it removes from the table hence it takes longer to execute.
Note: If we want to rollback a truncate, then we can write the truncate inside the transaction and then rollback the transaction.
Q9) What are advantages and Disadvantages of Stored Procedure?
Stored procedure can be used as a modular programming — means create once, store and call for several times whenever required.
This facilitates faster execution instead of executing multiple queries.
This reduces network traffic: The commands inside the SP are executed as a single batch of code. This means only the call to execute the procedure is sent over a network instead of every single line of code being sent individually.
utilizes more memory in the database server
Q10) What are magic tables in SQL?
These tables allow you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server. Magic Tables are invisible tables or virtual tables, you can see them only with the help Triggers.
These are the two Magic Tables:
Q11) What is function in database?
- It is a database object which is used for processing and manipulating the data.
- It only supports SELECT and have READ–ONLY Database Access
- We cannot use function to Insert/Update/Delete (DML operations) records in the database table.
- It accepts only input parameters, do not have output parameters. It returns single value.
Q12) What is stored procedure in database?
- A stored procedure groups one or more Transact-SQL statements into a logical unit and is stored as an object in the Database Server.
- When a stored procedure is called at the first time, SQL Server creates an execution plan and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance.
Q13) Can we perform insert/ update/ delete operations from function in database?
No, because functions only have READ-ONLY Database Access.
Q14) Why functions are useful in database?
- User-defined functions help to decompose a large program into small segments
- makes program easy to understand, maintain and debug.
- Code can be reused by creating functions and execute them when needed by calling that function.
Q15) What is the difference between Function and stored procedures?
Q16) What is difference between primary key and unique key?
1. Primary key will not accept NULL values whereas Unique key can accept one NULL value.
2. A table can have only primary key whereas there can be multiple unique key on a table.
3. A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.
Q17) Difference between varchar and nvarchar?
varchar– used for normal strings (not UNI-code) data types. It uses 1byte per character
nvarchar– used to hold UNI-code (like other languages German, Chinese etc.) data as well as normal strings. use ’N’ before the UNI-code data. It uses 2 bytes per character.
UNICODE: UNICODE is a uniform character encoding standard. A UNICODE character uses multiple bytes to store the data in the database. This means that using UNICODE it is possible to process characters of various writing systems in one document. This could be useful if you’re working with an international character set (for example different languages).
Q18) What is the difference between Where and Having?
Q19) What is the difference between Union, Unionall?
The UNION command combines the result set of two or more SELECT statements (only distinct values). The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values)
Q20) What is Coalesce() and IsNull()?
Coalesce: Returns the first non-null value passed into the param list.
SELECT COALESCE(NULL, NULL, NULL, ‘HelloSQL’, NULL, ‘sql.com’); returns ‘HelloSQL’.
IsNull(): Returns the specified value if the passed expression is null, otherwise returns the expression.
SELECT ISNULL(NULL, ‘helloWorld’); returns ‘helloWorld’
Q21) How to find the name of the process that is blocking database resource?
1) Activity Manager: You can use Activity Monitor to view information about the current processes and locks held on SQL Server resources.
2) Use stored procedure like sp_who2
3) Using DMVs like: sys.dm_exec_requests/sys.dm_tran_locks/sys.dm_os_waiting_tasks
Q22) How do I make sure that a column in Employee table only accepts values greater than zero?
Ans: We can use the “CHECK” constraint while creating the column in the table.
CREATE Table Employee(
Age int CHECK (Age>0)
Q23) 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.
Q24) 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
Q25) 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.
Q26) 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.
Q27) 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.
Q28) 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.
Q29) 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.
Q30) 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.
Q31) 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.
Q32) 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.