BizTranSights Solutions Interview Questions and Answers

Stored procedures and functions are precompiled SQL queries that can be stored and executed on the database server.

• Stored procedures are a set of SQL statements that are stored on the database server and can be called by applications.

• Functions are similar to stored procedures but return a value.

• Stored procedures can accept input parameters and return multiple result sets, while functions can only return a single value.

• ...read more

Trigger is a special type of stored procedure that automatically executes when an event occurs in a database. Index is a data structure that improves the speed of data retrieval operations on a database table.

• Trigger is used to maintain the integrity of the database by enforcing business rules or cascading changes.

• Indexes are used to quickly locate data without having to search every row in a database table.

• Examples: A...read more

Commit and Rollback are database transactions used to save or discard changes made to the database.

• Commit is used to save the changes made in a transaction to the database permanently.

• Rollback is used to discard the changes made in a transaction and revert the database to its previous state.

• Commit makes the changes permanent while Rollback cancels the changes.

• Example: If a transaction involves updating multiple records...read more

Join is used to combine rows from two or more tables based on a related column between them.

• Types of Joins: Inner Join, Left Join, Right Join, Full Join, Cross Join

• Inner Join: Returns rows when there is at least one match in both tables

• Left Join: Returns all rows from the left table and the matched rows from the right table

• Right Join: Returns all rows from the right table and the matched rows from the left table

• Full Jo...read more

Union and Intersection are set operations in mathematics. Union combines all elements from two sets, while Intersection finds common elements.

• Union combines all elements from two sets into a single set

• Intersection finds common elements between two sets

• Example: Set A = {1, 2, 3} and Set B = {3, 4, 5}. Union of A and B is {1, 2, 3, 4, 5}. Intersection of A and B is {3}

The new row will start from 1 after truncating a table.

• Truncating a table removes all rows from the table, resetting the row count to 1.

• The next row inserted after truncation will have an ID of 1.

• Truncating a table does not reset the auto-increment value of the primary key column.