SQL Interview Questions And Answers

The correct answer is d) All of the listed options. This is because all three options - ROLLBACK, COMMIT, and SAVEPOINT - are transaction control commands in SQL. ROLLBACK is used to undo any changes made in the current transaction, COMMIT is used to save the changes made in the current transaction, and SAVEPOINT is used to set a point in the transaction from where it can be rolled back if needed. Therefore, all three options denote transaction control commands in SQL.

An index in a database is used to improve the efficiency of data retrieval by allowing an application program to find specific data without scanning through the entire table. It acts as a pointer to the location of the data, enabling faster access and reducing the need for full table scans. Therefore, option c) is a perfect match with reference to an index.

The outcome of the SELECT statement will be "Select shows au_id = 1001 and au_name = 'Rajeev'". This is because the trigger "tr_au_upd" is an INSTEAD OF UPDATE trigger, which means that when an UPDATE statement is executed on the "authors" table, the trigger will be triggered instead of the actual update. The trigger only prints "Trigger output row updated" and does not perform any actual update on the table. Therefore, the initial INSERT statement that adds the row with au_id = 1001 and au_name = 'Rajeev' remains unchanged, resulting in the SELECT statement showing au_id = 1001 and au_name = 'Rajeev'.

The construct consists of a while loop that runs indefinitely since the condition is always true (1=1). Inside the loop, the statement 'I am here in one' is printed. Then, there is an if-else statement. Since 1=1, the if condition is true and the code inside the if block is executed, printing 'I am here in two'. Therefore, the outcome of executing the construct is that it prints 'I am here in one' and 'I am here in two'.

The outcome of the above statements is that only one clustered index, idx1, will be created on the emp table. This is because a table can have only one clustered index, and the second CREATE INDEX statement for idx2 will not be executed.

A multi-statement table valued function would be suitable for the given scenario because it allows for performing operations like inserts, deletes, and updates on the contents of a table variable before returning a result set. This type of function can contain multiple statements and can manipulate data within the function body. Scalar functions and in-line table valued functions are not suitable because they do not allow for performing these types of operations. Therefore, the correct option is c) Multi-statement table valued function.

Stored procedures do not have the advantage of automatic execution. Unlike triggers or scheduled jobs, stored procedures are not automatically executed at specific times or events. They need to be called explicitly by a user or application. The other options listed, improved security, precompiled execution, and reuse of code, are all advantages of using stored procedures.

@@TRANCOUNT is a system variable in SQL Server that returns the number of active transactions for the current connection. It is commonly used to determine if a transaction is already in progress or to implement error handling and rollback logic.

SQL Server user names and passwords are stored in the sysxlogins table, which is located in the Master database. This table contains information about all the logins and their associated passwords for the SQL Server instance.

The correct firing order for the triggers in the given scenario is tr_1, tr_2, tr_4, tr_3. This means that trigger tr_1 will fire first, followed by tr_2, then tr_4, and finally tr_3. The order of firing is determined by the sp_settriggerorder stored procedure, which allows the user to specify the firing order for triggers based on the operation being performed on the table. In this case, the firing order is specified for the UPDATE operation.

The value of @@FETCH_STATUS printed in the last print statement is 0. This is because the FETCH NEXT statement retrieves the next row from the cursor result set, and if there are no more rows to fetch, it returns a value of -1. Since the while loop condition is @@FETCH_STATUS = 0, the loop will continue as long as there are more rows to fetch. In this case, since there are no more rows to fetch after the last fetch statement, the loop will exit and the last print statement will display a value of 0.

We identify a transaction by its ACID property. ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the key properties that ensure the reliability and integrity of a transaction. By checking if a transaction adheres to these properties, we can determine its identity and assess its correctness and reliability.

The trigger "tr_au_upd" is created on the "authors" table with the condition "IF UPDATE(city)". This means that the trigger will only be activated if the "city" column is updated. In the given statement, the "city" column is updated for the row with "au_id = 1001" and set to "MUMBAI". Therefore, the trigger will not be activated and the update statement will be successfully executed, resulting in the city for "au_id = 1001" being changed to "MUMBAI" in the authors table.

When a view is created that joins columns from two base tables, it is not possible to directly use the UPDATE statement on the view to update columns from both tables. Instead, an INSTEAD OF UPDATE trigger needs to be created on the view. This trigger will be executed instead of the actual UPDATE statement on the view and can be used to update the respective tables by writing the necessary trigger code. By creating an INSTEAD OF UPDATE trigger on the view and updating the tables as part of the trigger code, the task of updating specific columns from both the base tables can be achieved successfully.

The new isolation level in SQL Server 2005 called "Snapshot" ensures that the values accessed in a transaction remain the same until the transaction is either committed or rolled back. This means that even if other transactions modify the data during the course of the transaction, the snapshot isolation level ensures that the transaction sees a consistent view of the data as it existed at the start of the transaction.

The highest isolation level in SQL Server 2008 is "Serializable". This isolation level ensures that transactions are completely isolated from each other, meaning that no other transaction can access the data that is being modified by a transaction until it is committed. This level provides the highest level of data integrity and consistency, but it can also lead to a higher degree of concurrency issues and performance degradation due to the strict locking requirements.

Stored procedures in databases are used to perform predefined operations on the data stored in the database. The parameters passed to a stored procedure can be local variables, constant values, or expressions. However, functions cannot be passed as input parameter values to a stored procedure because functions are used to perform calculations or return values based on input parameters, but they cannot be directly passed as input parameters themselves. Expressions, which are combinations of variables, constants, and operators, can also not be directly passed as input parameter values to a stored procedure.

The final outcome of issuing the above statements is that the index idx1 is completely removed. The first statement "ALTER INDEX idx1 ON emp DISABLE" disables the index, and the second statement "DROP INDEX emp.idx1" removes the index completely. Therefore, the index idx1 is no longer present in the table 'emp'.

The correct answer is d) Four author profiles are retrieved. This is because the cursor is opened and then four fetch statements are executed, each retrieving one author profile. Therefore, a total of four author profiles are retrieved from the authors table.

The correct answer is b) In-line table valued function. In the given statement, the function dbo.AverageBookPrice('comp_science') is used, which returns a table of values. This table is then used in the select statement to filter the records from the titles table. Therefore, it is an in-line table valued function.

The correct answer is a) Severity 20 or higher errors that do not result in closing of a connection. This means that within a TRY..CATCH construct, errors with a severity level of 20 or higher can be handled without causing the connection to be closed. This allows for the error to be caught and handled within the stored procedure, without disrupting the overall connection to the database.

To make modifications to a stored procedure, there are two possible ways. The first option is to drop the procedure using the DROP command and then recreate it using the CREATE command. This involves removing the existing procedure and creating a new one with the desired modifications. The second option is to modify the procedure directly using the ALTER command. This allows for making changes to the existing procedure without having to drop and recreate it.

The WAITFOR DELAY statement causes a delay of 2 hours, 22 seconds before executing the first procedure, sp_process_total_one. Then, the WAITFOR TIME statement causes a delay until 2:00:22 AM before executing the second procedure, sp_process_total_two. Since both procedures are executed after the specified delays, the expected outcome is that both procedures, sp_process_total_one and sp_process_total_two, get executed at the same time.

The correct trigger order values for defining multiple triggers for an UPDATE action on a given table are FIRST, NONE, and LAST.

"FIRST" indicates that the trigger should be executed first before any other triggers on the table. "NONE" means that the trigger should not be executed at all. "LAST" indicates that the trigger should be executed after all other triggers on the table.

The outcome of the SELECT statement will be au_id = 1001 and au_name = 'Rajeev'. This is because the trigger that was created on the authors table updates the au_name to 'Raman' only when the au_id is 1001. However, in the UPDATE statement that was executed before the SELECT statement, the au_name was updated to 'Raghav' for au_id 1001. Therefore, when the SELECT statement is executed, it will show the updated value of au_name, which is 'Raghav'.

not-available-via-ai

The trigger is scoped at the database level and is set to fire on CREATE_TABLE, ALTER_TABLE, and DROP_TABLE events. The ALTER statements provided in the question attempt to add a new column to the "titles" table and then drop that same column. However, since the trigger is scoped at the database level and not the table level, it will fire for any table within the database. Therefore, when the first ALTER statement tries to create the new column, it will trigger the trigger and fail due to the scope being at the database level. As a result, the creation of the new column on the "titles" table itself fails.