SQL interview questions you must know - Part 1

Below is the list of questions I have faced in various interviews over a 8+ years of my career.

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:

1. 1NF — Removes duplicated attributes, Attribute data should be atomic, and of same kind.
2. 2NF — Should be in 1NF and each non-key field is fully dependent on the primary key.
3. 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.

1. There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
2. 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?

Advantages:

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.

Disadvantage:

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:

1. Inserted
2. Deleted

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(

id int,
Age int CHECK (Age>0)
)

For more scenario-based questions refer to “SQL interview questions you must know- Part 2”.

Liked the post? If you found this useful consider buying me a coffee here https://bmc.link/shreetanu9R