SQL Interview Questions And Answers

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By MUKESH443
M
MUKESH443
Community Contributor
Quizzes Created: 2 | Total Attempts: 686
| Attempts: 399
SettingsSettings
Please wait...
  • 1/27 Questions

    Which of the following denotes Transaction Control command in SQL?

    • A) ROLLBACK
    • B) COMMIT
    • C) SAVEPOINT
    • D) All of the listed options
Please wait...
About This Quiz

SQL is modern database system, which is used in programming and designed for managing data. Take this quiz and learn more about it!

SQL Interview Questions And Answers - Quiz

Quiz Preview

  • 2. 

    Which of the following is a perfect match with reference to an Index?

    • A) Smallest unit of data storage

    • B) Automatically created at the time of creating a table

    • C) Allows an application program to find specific data without scanning through the entire table

    • D) contains information about modifications carried out in the database

    Correct Answer
    A. C) Allows an application program to find specific data without scanning through the entire table
    Explanation
    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.

    Rate this question:

  • 3. 

    1. "The following trigger is created an a table called 'authors' that has columns au_id, au_name,city
      CREATE TRIGGER tr_au_upd ON authors INSTEAD OF UPDATE AS PRINT 'Trigger output row updated'
    1.  
      The following statements are executed on the authors table:   INSERT authors(au_id, au_name) VALUES (1001, 'Rajeev') UPDATE authors SET au_name = 'Raghav' WHERE au_id = 1001
    1.  
      SELECT au_id, au_name WHERE au_id =1001   What will be the outcome of the above SELECT statement? "

    • A) Select shows au_id = 1001 and au_name = 'Rajeev'

    • B) Select shows au_id = 1001 and au_name = 'Raghav'

    • C) Select shows au_id = 1001 and au_name as NULL

    • D) None of the listed options

    Correct Answer
    A. A) Select shows au_id = 1001 and au_name = 'Rajeev'
    Explanation
    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'.

    Rate this question:

  • 4. 

    1. "Consider the following construct:
    WHILE (1=1)
    1.  
    PRINT 'I am here in one' IF 1 = 1 begin PRINT 'I am here in two' break end else begin continue end end   What is the outcome of executing the above?"

    • Prints 'I am here in one'

    • B) Prints 'I am here in two' " Prints 'I am here in one' Prints 'I am here in two'

    • C) "Prints 'I am here in one' Prints 'I am here in two'

    • D) Prints 'I am here in one'" Prints nothing

    Correct Answer
    A. C) "Prints 'I am here in one' Prints 'I am here in two'
    Explanation
    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'.

    Rate this question:

  • 5. 

    1. Assume there is a new table 'emp' created with columns as empid, empname, empdob, empaddress
    The following statements are issued for creating indexes: CREATE [CLUSTERED] INDEX idx1 ON emp(empname) CREATE [CLUSTERED] INDEX idx2 ON emp(empdob) What is the outcome of the above statements?

    • A) two clustered indexes idx1 and idx2 will be created on emp table

    • B) Only one clustered index idx1 will be created on emp table

    • C) Only one clustered index idx2 will be created on emp table

    • D) No index will be created on emp table

    Correct Answer
    A. B) Only one clustered index idx1 will be created on emp table
    Explanation
    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.

    Rate this question:

  • 6. 

    "You have to perform operations like inserts, deletes, updates on the contents of a table variable before returning a result set.   Which of the following User Defined Function will be suitable to define for the above scenario?"

    • A) Scalar function

    • B) In-line table valued function

    • C) Multi-statement table valued function

    • D) None of the listed options

    Correct Answer
    A. C) Multi-statement table valued function
    Explanation
    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.

    Rate this question:

  • 7. 

    Which of the following is not an advantage of Stored Procedure?

    • A) Improved Security

    • B) Precompiled Execution

    • C) Automatic Execution

    • D) Reuse Of Code

    Correct Answer
    A. C) Automatic Execution
    Explanation
    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.

    Rate this question:

  • 8. 

    Which of the following we can use to identify the number of active transactions for current connection?

    • A) @@TRANCOUNT

    • B) @@TRANS

    • C) @@TRASACTIONCOUNT

    • D) @@TRANSACTIONS

    Correct Answer
    A. A) @@TRANCOUNT
    Explanation
    @@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.

    Rate this question:

  • 9. 

    Where are SQL Server user names and Passwords stored in sql server?

    • Under msdb database in sysxlogins tables

    • Under tempdb database in sysxlogins tables

    • Under Master database in sysxlogins tables

    • None of the listed options

    Correct Answer
    A. Under Master database in sysxlogins tables
    Explanation
    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.

    Rate this question:

  • 10. 

    1. "Consider the following scenario:
    Four triggers are defined for some operations on a table. The order of firing are defined as under: sp_settriggerorder tr_1,FIRST, 'UPDATE' sp_settriggerorder tr_2,NONE, 'UPDATE' sp_settriggerorder tr_3,LAST,'UPDATE' sp_settriggerorder tr_4,NONE, 'UPDATE' sp_settriggerorder tr_5,NONE, 'INSERT'   Which of the following are valid combination of firing order for above triggers in case an update operation takes place on the table?

    • A) tr_1 , tr_4, tr_2, tr_3

    • B) tr_1, tr_5, tr_4, tr_3

    • C) tr_1, tr_2, tr_4, tr_3

    • D) tr_1, tr_2, tr_5, tr_3

    Correct Answer
    A. C) tr_1, tr_2, tr_4, tr_3
    Explanation
    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.

    Rate this question:

  • 11. 

    We identify a transaction __________.

    • A) by its name

    • B) by its parameters

    • C) by its performance

    • D) by its ACID property

    Correct Answer
    A. D) by its ACID property
    Explanation
    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.

    Rate this question:

  • 12. 

    1. Consider the following cursor processing statements:
    DECLARE Employee_Cursor CURSOR READ_ONLY FOR SELECT LastName, FirstName, status FROM dbo.Employees WHERE LastName like 'B%'   OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor PRINT @@FETCH_STATUS WHILE @@FETCH_STATUS = 0
    1.  
    FETCH NEXT FROM Employee_Cursor PRINT @@FETCH_STATUS
    1.  
      CLOSE Employee_Cursor DEALLOCATE Employee_Cursor   (Assume that the Employees table has 500 rows of employee data) What is the value of @@FETCH_STATUS printed in the last print statement as per above?

    • A) 0

    • b) 1

    • C) -1

    • D) 2

    Correct Answer
    A. A) 0
    Explanation
    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.

    Rate this question:

  • 13. 

    1. "The following trigger is created an a table called 'authors' that has columns au_id, au_name,city
      CREATE TRIGGER tr_au_upd ON authors FOR INSERT,UPDATE AS   IF UPDATE(city)
    1.  
    ROLLBACK TRAN
    1.  
    2.  
      The following statement is executed on the authors table:   UPDATE authors SET city = 'MUMBAI' WHERE au_id = '1001'   What will be the outcome of the above statement? "

    • A) The city for au_id = 1001 is changed to MUMBAI in authors table

    • B) The city for au_id = 1001 is set to NULL in authors table

    • C) The city for au_id = 1001 is not changed in the authors table

    • D) None of the listed options

    Correct Answer
    A. A) The city for au_id = 1001 is changed to MUMBAI in authors table
    Explanation
    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.

    Rate this question:

  • 14. 

    Consider the following scenario: There are two base tables created in a database. A view is created that joins columns from the two base tables. It is required to update specific columns picked from both the tables using the view created. Which of the following will achieve the above task successfully?

    • Directly use the UPDATE statement on the view to update columns from both the base tables

    • There is no solution to update the columns from both tables using the view

    • Create an INSTEAD of UPDATE Trigger on the view and update tables as part of trigger code

    • Create an AFTER Trigger on the view and update tables as part of trigger code

    Correct Answer
    A. Create an INSTEAD of UPDATE Trigger on the view and update tables as part of trigger code
    Explanation
    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.

    Rate this question:

  • 15. 

    1. Which of the following is the Highest isolation level in SQL Server 2008?

    • A) Read uncommitted

    • B) Read Committed

    • C) Repeatable Read

    • D) Serializable

    Correct Answer
    A. D) Serializable
    Explanation
    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.

    Rate this question:

  • 16. 

    1. "Consider the following cursor processing:
    DECLARE authors_cursor FAST_FORWARD FOR SELECT au_lname, au_fname FROM authors ORDER BY au_lname, au_fname OPEN authors_cursor FETCH NEXT FROM authors_cursor FETCH NEXT FROM authors_cursor FETCH NEXT FROM authors_cursor FETCH NEXT FROM authors_cursor CLOSE authors_cursor DEALLOCATE authors_cursor   (Assume that authors table has 200 author profiles) What is the outcome of executing the above cursor statements?"

    • A) Three author profiles are retrieved from authors

    • B) One author profile is retrieved from authors

    • C) No author profiles are retrieved from authors

    • D) Four author profiles are retrieved

    • E) Gives error message

    Correct Answer
    A. D) Four author profiles are retrieved
    Explanation
    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.

    Rate this question:

  • 17. 

    The new Isolation level in SQL Server 2005 wherein the values that are accessed remain the same till the transaction is Committed or Rolled back is called ____________.

    • A) ReadUncommitted

    • B) Committed

    • C) SnapShot

    • D) Consistency

    Correct Answer
    A. C) SnapShot
    Explanation
    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.

    Rate this question:

  • 18. 

    Which of the following cannot be passed as input parameter values to a Stored procedure?(Choose 2)

    • A) Local variables

    • B) functions

    • C) constant values

    • D) expressions

    Correct Answer(s)
    A. B) functions
    A. D) expressions
    Explanation
    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.

    Rate this question:

  • 19. 

    "Assume there is an existing  table 'emp' with columns as empid, empname, empdob, empaddress   In addition, a non-clustered index (idx1) exists on empdob column.   The following statements are issued :   ALTER INDEX idx1 ON emp DISABLE DROP INDEX emp.idx1   What is the final outcome of issuing the above statements?"

    • A) The index idx1 is completely removed

    • B) The index idx1 is temporarily disabled

    • C) The index idx1 is permanently disabled

    • D) The statements end with execution error

    Correct Answer
    A. A) The index idx1 is completely removed
    Explanation
    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'.

    Rate this question:

  • 20. 

    "Consider the following statement:   Select title_id, price, type from titles where price > dbo.AverageBookPrice('comp_science')   What kind of a User Defined Function is used in the above statement?"

    • A) Scalar function

    • B) In-line table valued function

    • C) Multi-statement table valued function

    • D) None of the listed options

    Correct Answer
    A. B) In-line table valued function
    Explanation
    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.

    Rate this question:

  • 21. 

    Which of the following error type can be handled within a TRY..CATCH construct of handling errors in stored procedures?

    • A) Severity 20 or higher errors that do not result in closing of a connection

    • B) Errors with severity 10 or less that are warnings and informational messages

    • C) Compile errors or object name resolution errors that happen during deferred name resolution

    • D) Severity 20 or higher errors that results in closing of a connection

    Correct Answer
    A. A) Severity 20 or higher errors that do not result in closing of a connection
    Explanation
    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.

    Rate this question:

  • 22. 

    "You have a stored procedure and you need to make some modifications to it.   Which of the following ways can this be done?(Choose 2)"

    • A) Drop the procedure using DROP and re-create a new one using CREATE command

    • B) Create a new procedure with the same name using CREATE command

    • C) Modify the procedure using ALTER command

    • D) Drop the procedure using DROP command and modify using ALTER

    Correct Answer(s)
    A. A) Drop the procedure using DROP and re-create a new one using CREATE command
    A. C) Modify the procedure using ALTER command
    Explanation
    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.

    Rate this question:

  • 23. 

    1. "Consider the following statements as part of a stored procedure:
      WAITFOR DELAY '02:00:22' begin EXEC sp_process_total_one WAITFOR TIME '02:00:22' begin EXEC sp_process_total_two end  Supposing the above procedure is is run exactly at midnight, what is the expected outcome?"

    • A) Procedure sp_process_total_two executes before sp_process_total_one

    • B) Procedure sp_process_total_one executes before sp_process_total_two

    • C) Both procedures sp_process_total_one and sp-process_total_two get executed at same time

    • D) Procedure sp_process_two will not get executed at all

    • E) Procedure sp_process_one will not get executed at all

    Correct Answer
    A. C) Both procedures sp_process_total_one and sp-process_total_two get executed at same time
    Explanation
    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.

    Rate this question:

  • 24. 

    In case of defining multiple triggers for an UPDATE action on a given table, which of the following are correct trigger order values?(Choose 3)

    • FIRST

    • NONE

    • ANY

    • LAST

    • SECOND

    Correct Answer(s)
    A. FIRST
    A. NONE
    A. LAST
    Explanation
    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.

    Rate this question:

  • 25. 

    1. "The following trigger is created an a table called 'authors' that has columns au_id, au_name,city
      CREATE TRIGGER tr_au_upd ON authors INSTEAD OF UPDATE AS UPDATE authors SET au_name = 'Raman' WHERE au_id = 1001
    1.  
      The following statements are executed on the authors table:   UPDATE authors SET au_name = 'Raghav' WHERE au_id = 1001
    1.  
      SELECT au_id, au_name WHERE au_id =1001 What will be the outcome of the above SELECT statement? "

    • A) Select shows au_id = 1001 and au_name = 'Rajeev'

    • B) Select shows au_id = 1001 and au_name = 'Raghav'

    • C) Select shows au_id = 1001 and au_name as NULL

    • D) Select shows au_id = 1001 and au_name = 'Raman'

    Correct Answer
    A. A) Select shows au_id = 1001 and au_name = 'Rajeev'
    Explanation
    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'.

    Rate this question:

  • 26. 

    1. Consider the following function:
      CREATE FUNCTION averagepricebytype(@price money = 0.0) RETURNS TABLE
    1.  
    RETURN (SELECT type, avg(isnull(price, 0)) as avg_price FROM titles GROUP BY type HAVING avg(isnull(price, 0)) > @price)   Which of the following statement is correct for the function above?

    • It creates a valid in-line table valued function

    • Create fails because it is an invalid type of table valued function

    • Create fails because it is an invalid type of scalar function

    • None of the listed options

    Correct Answer
    A. Create fails because it is an invalid type of scalar function
  • 27. 

    1. "The following trigger is scoped at database level:
      CREATE TRIGGER tr_table_audit ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
    1.  
    2.  
    3.  
      The following statement is isused on table 'titles' in the database   ALTER TABLE titlesADD new_col INT NULL ALTER TABLE titlesDROP COLUMN new_col   What is the final outcome of issuing the ALTER statements?"

    • A) The new column new_col is created and dropped on table titles

    • B) The new column new_col is created on table titles but drop fails

    • C) Creation of new column new_col on table titles itself fails

    • D) None of the listed options

    Correct Answer
    A. C) Creation of new column new_col on table titles itself fails
    Explanation
    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.

    Rate this question:

Quiz Review Timeline (Updated): Sep 6, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Sep 06, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Jan 13, 2012
    Quiz Created by
    MUKESH443

Recent Quizzes

RDBMS and SQL Knowledge Test RDBMS and SQL Knowledge Test
The Ultimate SQL Syntax and Functionality Knowledge Test The Ultimate SQL Syntax and Functionality Knowledge Test
SQL Basic! Ultimate Trivia Quiz SQL Basic! Ultimate Trivia Quiz
SQL Multiple Choice Questions With Answers SQL Multiple Choice Questions With Answers
Fill in the Blanks on SQL Fill in the Blanks on SQL
Introduction to SQL True or False Quiz Introduction to SQL True or False Quiz
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.