SQL Server Quiz Questions And Answers
Have you ever worked on an SQL server? What do you know about this database system? We have here this "SQL server quiz questions and answers" for you. Microsoft SQL Server is a DBMS developed by Microsoft for storing and retrieving data. In this SQL quiz, we will test your knowledge and understanding of various SQL concepts and terminologies. You can easily crack this quiz if you consider yourself a true database administrator. Want to check your memory for the same? Take this test, then!
- 1.
Assuming UserProfile is a table containing a column Proession that accepts a NULL value. What is the result of the query below? SET ANSI_NULLS OFF SELECT Profession FROM UserProfile WHERE (Profession <> NULL).
- A.
Gives an error.
- B.
Returns all Professions that do not have null values.
- C.
<> is not an operator in SQL Server.
- D.
None of the above.
Correct Answer
B. Returns all Professions that do not have null values.Explanation
The correct answer is "Returns all Professions which do not have null values." This is because the query is using the operator, which is the "not equal to" operator in SQL. When comparing a value to NULL using this operator, the result will be either true or false, but not NULL. Therefore, the query will return all rows where the Profession column is not NULL.Rate this question:
-
- 2.
What is the result of the below query: SELECT SUBSTRING('DOTNET', 1, 3) AS 'Substring'
- A.
DOT
- B.
OTN
- C.
OT
- D.
None of the above.
Correct Answer
A. DOTExplanation
The result of the given query is "DOT". The SUBSTRING function is used to extract a substring from a given string. In this case, the string "DOTNET" is being passed as the first argument, and the second and third arguments specify the starting position and length of the substring to be extracted. Since the starting position is 1 and the length is 3, the substring "DOT" is returned as the result.Rate this question:
-
- 3.
Which of the following is true about the SERIALIZABLE isolation level ?
- A.
It protects against phantom reads.
- B.
It causes the highest level of contention.
- C.
It causes the highest level of blocking.
- D.
All of the above.
Correct Answer
D. All of the above.Explanation
The correct answer is "All of the above." The SERIALIZABLE isolation level in database transactions protects against phantom reads, which occur when a transaction reads rows that do not exist yet due to concurrent modifications. Additionally, this isolation level causes the highest level of contention, as it locks the entire table for the duration of the transaction, preventing other transactions from accessing it concurrently. It also causes the highest level of blocking, as transactions may have to wait for locks to be released before proceeding. Therefore, all the statements provided are true for the SERIALIZABLE isolation level.Rate this question:
-
- 4.
What is the result of the below query: SELECT PATINDEX('DOT%', 'DOTNET') AS 'Index'
- A.
0
- B.
1
- C.
-1
- D.
2
Correct Answer
B. 1Explanation
The PATINDEX function in SQL Server is used to find the starting position of a specified pattern (in this case, 'DOT%') within a given string ('DOTNET'). In this example, 'DOT%' matches the beginning of 'DOTNET,' so the function returns 1, which is the position where the pattern 'DOT%' starts within the string 'DOTNET.'Rate this question:
-
- 5.
Assuming Emp, EmpCompress 2 tables with the same schema: EmpId(Int), Name(varchar(50)), DeptId(Int). If a company wants to move all rows for Employees working in the IT Department from the Employees table to the Employees Archive table. Which of the following queries can be used?
- A.
DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN deptON EMP.DeptId = Dept.DeptId WHERE DeptName='IT';
- B.
DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN Dept ON EMP.DeptId = Dept.DeptId ;
- C.
DELETE FROM EMP OUT PUT deleted.* INTO EmpCompress FROM Emp JOIN deptON EMP.DeptId = Dept.DeptId WHERE DeptName='IT';
- D.
None of the above.
Correct Answer
B. DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN Dept ON EMP.DeptId = Dept.DeptId ;Explanation
The correct query deletes rows from the "Emp" table where the department is IT and inserts the deleted rows into the "EmpCompress" table. It utilizes the OUTPUT clause to capture the deleted rows and the JOIN clause to match records based on the department ID.Rate this question:
-
- 6.
The SELECT statement must include which of the following clause:
- A.
SELECT clause
- B.
FROM clause
- C.
Both A and B.
- D.
None of the above.
Correct Answer
D. None of the above.Explanation
The statement 'SELECT 2 5 return;' is invalid in SQL. A valid SELECT statement requires at least one column name or expression following the SELECT keyword, and if retrieving data from a table, the table must be specified in the FROM clause. The provided statement lacks the necessary syntax elements for a valid SQL SELECT query.Rate this question:
-
- 7.
Which of the following is true?
- A.
BUILT-IN functions in SQL Server are Nondeterministic.
- B.
ISNULL function in SQL Server is Deterministic.
- C.
All of the above.
- D.
None of the above.
Correct Answer
C. All of the above.Explanation
All of the above is the correct answer because both statements are true. BUILT-IN functions in SQL Server are indeed Nondeterministic, meaning that their output can vary for the same input. On the other hand, the ISNULL function in SQL Server is Deterministic, meaning that its output is always the same for a given input. Therefore, both statements are correct and the correct answer is "All of the above."Rate this question:
-
- 8.
Which of the following is true?
- A.
NULL values are ignored for all the aggregate functions.
- B.
NULL values are included for all the aggregate functions.
- C.
When an aggregate function is included in the SELECT clause, all other expressions in the SELECT clause must either be aggregate functions or included in a GROUP BY clause.
- D.
Both A and C.
- E.
Both B and C.
Correct Answer
D. Both A and C.Explanation
Both A and C are true. A) NULL values are ignored for all the aggregate functions, meaning that if there are any NULL values in the data being aggregated, they will not be included in the calculation of the aggregate function. C) When an aggregate function is included in the SELECT clause, all other expressions in the SELECT clause must either be aggregate functions or included in a GROUP BY clause. This is because the aggregate function is operating on a group of rows and the other expressions need to be either part of that group or also aggregated.Rate this question:
-
- 9.
Assuming UserProfile is a table with PKUserId int, Profession Varchar(50)columns. What is the result of the below query: SELECT PKUserId,Profession FROM UserProfile WHERE Profession = 'Engineer' AND (PKUserId > 12 OR PKUserId = 1)
- A.
Displays Users with Profession as 'engineer' with PKUserId > 12 as well as the users with PKUserId of 1.
- B.
Displays Users with Profession as 'engineer' with PKUserId > 12 only.
- C.
Displays Users with Profession as 'engineer' with PKUserId of 1 only.
- D.
None of the above.
Correct Answer
A. Displays Users with Profession as 'engineer' with PKUserId > 12 as well as the users with PKUserId of 1.Explanation
The query selects rows from the UserProfile table where the Profession column is 'Engineer' and the PKUserId is either greater than 12 or equal to 1. Therefore, the result of the query will display users with the Profession as 'engineer' and the PKUserId greater than 12, as well as the user with a PKUserId of 1.Rate this question:
-
- 10.
BEGIN TRANSACTION INSERT INTO DemoTable VALUES(5,'XYZ','DEMO'); BEGIN TRANSACTION UPDATE DemoTable SET Col3 = 'Test' WHERE TestID = 5; COMMIT TRANSACTION; ROLLBACK; What is the result of the above code execution?
- A.
The data is rolled back to the transaction starting on line1.
- B.
The inserted row does not exist in the table.
- C.
All of the above.
- D.
None of the above.
- E.
Only B.
Correct Answer
C. All of the above.Explanation
The correct answer is "All of the above." This is because the code begins a transaction on line 1, inserts a row into the DemoTable on line 2, begins another transaction on line 3, updates the Col3 value for the row with TestID = 5 on line 4, commits the transaction on line 5, and then rolls back the transaction on line 6. As a result, the data is rolled back to the transaction starting on line 1, meaning that the inserted row does not exist in the table. Therefore, all of the statements in the answer options are true.Rate this question:
-
- 11.
Assuming Emp table with EmpId(int), Name(varchar(100)),HireDate(DateTime),Designation(varchar(20)) columns and Audit table with currentDate(DateTime), EmpId columns. What is the result of the following query: INSERT INTO Emp(Name,HireDate,Designation) OUTPUT getdate(), inserted.EmpId INTO Audit VALUES ('Robot','1/1/2011','Executive');
- A.
Only inserts a new record into the Emp table.
- B.
OUTPUT clause cannot be used in the INSERT command.
- C.
Adds a new row to the Emp table, and also adds a corresponding row with the current date and time and the EmpId for the new employee into the Audit table.
- D.
None of the above.
Correct Answer
C. Adds a new row to the Emp table, and also adds a corresponding row with the current date and time and the EmpId for the new employee into the Audit table.Explanation
The given query will insert a new row into the Emp table with the values 'Robot', '1/1/2011', and 'Executive' for the Name, HireDate, and Designation columns respectively. Additionally, it will also add a corresponding row into the Audit table with the current date and time and the EmpId for the new employee. Therefore, the correct answer is that it adds a new row to the Emp table and also adds a corresponding row with the current date and time and the EmpId for the new employee into the Audit table.Rate this question:
-
- 12.
How many transactions at a time can obtain an update lock on a resource?
- A.
1
- B.
2
- C.
3
- D.
128
Correct Answer
A. 1Explanation
Only one transaction at a time can obtain an update lock on a resource. This means that while one transaction has acquired the update lock, other transactions will have to wait until the lock is released before they can obtain it. This ensures that only one transaction can make changes to the resource at any given time, preventing conflicts and ensuring data integrity.Rate this question:
-
- 13.
Which of the following queries returns the users whose username starts with any of the characters between v to z?
- A.
SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[vz]%')
- B.
SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z]%')
- C.
SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z%]')
- D.
None of the above.
Correct Answer
B. SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z]%')Explanation
The correct answer is "SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z]%')". This query uses the LIKE operator with a character range [v-z] to match usernames that start with any character between v and z. The % wildcard is used to match any number of characters after the specified range.Rate this question:
-
- 14.
Which of the following options can be used for viewing lock status within your computer running SQL Server?
- A.
We can use SQL Profiler to capture lock and blocking information.
- B.
We can use the System Monitor that is part of the performance console to capture statistics on lock wait times, locks per second, and so on.
- C.
We can use the Activity Monitor in SSMS to see information on blocking processes.
- D.
We can use the sys.dm_tran_locks dynamic management view to gather information on locks being held by transactions.
- E.
All of the above.
Correct Answer
E. All of the above.Explanation
The correct answer is "All of the above." This is because all of the options mentioned can be used for viewing lock status within a computer running SQL Server. SQL Profiler can capture lock and blocking information, the System Monitor can capture statistics on lock wait times, locks per second, and more, the Activity Monitor in SSMS can provide information on blocking processes, and the sys.dm_tran_locks dynamic management view can gather information on locks held by transactions.Rate this question:
-
- 15.
Which of the following commands is used to retrieve information about the information contained in the current transaction log ?
- A.
DBCC LOGGING statement
- B.
DBC LOG statement
- C.
DBCC LOG statement
- D.
None of the above.
Correct Answer
A. DBCC LOGGING statementExplanation
The correct answer is DBCC LOGGING statement. This command is used to retrieve information about the information contained in the current transaction log. It allows users to view the log records, including the log sequence number, transaction ID, operation type, and other relevant details. The DBCC LOGGING statement is specifically designed for managing and analyzing transaction logs in a database system.Rate this question:
-
- 16.
The _____ object is used to retrieve information about all active transactions on an instance.
- A.
Sys.dm_active_transactions
- B.
Sys.dm_tran_transactions
- C.
Sys.dm_tran_active_transactions
- D.
None of the above
Correct Answer
C. Sys.dm_tran_active_transactionsExplanation
The correct answer is sys.dm_tran_active_transactions. This object is used to retrieve information about all active transactions on an instance. It provides details such as the transaction ID, transaction state, transaction type, and the time when the transaction was started. By querying this object, administrators can monitor and analyze the active transactions on the instance to identify any potential issues or bottlenecks.Rate this question:
-
- 17.
By default, what type of index is created for a unique constraint?
- A.
Clustered index
- B.
Non-Clustered index
- C.
Both A and B
- D.
None of the above.
Correct Answer
B. Non-Clustered indexExplanation
When a unique constraint is created by default, a non-clustered index is also created. A non-clustered index is a separate structure that contains a sorted list of the indexed column's values and a pointer to the actual data row. This allows for efficient searching and retrieval of data based on the indexed column, while still allowing the data to be physically stored in a different order.Rate this question:
-
- 18.
To rollback a portion of a transaction, We have to define ___.
- A.
Savepoints
- B.
Checkpoints
- C.
Transactions
- D.
None of the above.
Correct Answer
A. SavepointsExplanation
Savepoints are used to define points within a transaction where it can be rolled back to in case of any issues or errors. By using savepoints, we can undo a portion of a transaction without having to roll back the entire transaction. This allows for more flexibility and control over the transaction process. Checkpoints, on the other hand, are used for recovery purposes and do not specifically deal with rolling back a portion of a transaction. Transactions, while related to the concept of rollback, do not specifically define the points within a transaction where rollback can occur. Therefore, the correct answer is savepoints.Rate this question:
-
- 19.
Assuming the following query is executed on 31st Dec 2011. What is the result of the below query? SELECT CONVERT(varchar(30), GETDATE(), 111) AS Expr1
- A.
11/12/31
- B.
31/12/11
- C.
31/12/2011
- D.
2011/12/31
Correct Answer
D. 2011/12/31Explanation
The query is using the GETDATE() function to get the current date and then converting it to a varchar format with the format code 111. The format code 111 represents the format "yyyy/MM/dd". Therefore, the result of the query will be the current date in the format "2011/12/31".Rate this question:
-
- 20.
Which of the following is a best practice to reduce deadlock situations?
- A.
Access resources in the same order whenever possible within transactions
- B.
Collect and verify input data from users before opening a transaction
- C.
Keep transactions long.
- D.
All of the above.
- E.
Only A and B.
Correct Answer
A. Access resources in the same order whenever possible within transactionsExplanation
Accessing resources in the same order whenever possible within transactions is a best practice to reduce deadlock situations. Deadlocks occur when two or more transactions are waiting for each other to release resources, resulting in a deadlock situation. By accessing resources in the same order, the likelihood of deadlocks is reduced because it ensures that transactions will not be waiting indefinitely for resources that are being held by other transactions. This practice helps to maintain a consistent order of resource access and prevents potential deadlocks from occurring.Rate this question:
-
- 21.
What is the default join type used when only the keyword JOIN is specified?
- A.
LEFT OUTER JOIN
- B.
RIGHT OUTER JOIN
- C.
SELF JOIN
- D.
INNER JOIN
Correct Answer
D. INNER JOINExplanation
When only the keyword JOIN is specified without any other specific join type, the default join type used is INNER JOIN. This means that only the matching rows between the two tables will be included in the result set, excluding any non-matching rows.Rate this question:
-
- 22.
What is the output of the below query: SELECT CHARINDEX('DOT%', 'DOTNET') AS 'CharIndex'
- A.
1
- B.
0
- C.
2
- D.
-1
Correct Answer
B. 0Explanation
The CHARINDEX function in SQL Server returns the starting position of a specified expression within a string. In this query, the expression 'DOT%' is being searched within the string 'DOTNET'. Since the expression is not found within the string, the function returns 0 as the output.Rate this question:
-
- 23.
Which of the following is correct?
- A.
The DELETE statement logs information on each row deleted.
- B.
The TRUNCATE TABLE statement logs information on each row deleted.
- C.
The TRUNCATE TABLE statement executes fast and requires fewer resources on the server.
- D.
All of the above.
- E.
Only A and C.
Correct Answer
E. Only A and C.Explanation
The correct answer is "Only A and C." This means that both the DELETE statement and the TRUNCATE TABLE statement log information on each row deleted. Additionally, the TRUNCATE TABLE statement executes fast and requires fewer resources on the server.Rate this question:
-
- 24.
Assuming UserProfile is a table containing a column Profession which accepts NULL value. What is the result of the below query? SET ANSI_NULLS ON SELECT Profession FROM UserProfile WHERE (Profession <> NULL)
- A.
Returns records which has values in profession column
- B.
Gives an error.
- C.
Does not retrieve any records even if profession column contains values.
- D.
None of the above.
Correct Answer
C. Does not retrieve any records even if profession column contains values.Explanation
The query is using the condition (Profession <> NULL) to filter the records. However, comparing a value to NULL using the <> operator will always result in a NULL value, not true or false. Therefore, the condition will not retrieve any records, even if the profession column contains values.Rate this question:
-
- 25.
If the following code was run on 31st Dec 2011, What is the result of the below query: SELECT DATEDIFF(YEAR, GETDATE(), '8/8/2003') AS 'Difference'
- A.
-8
- B.
8
- C.
0
- D.
-1
Correct Answer
A. -8Explanation
The query is calculating the difference in years between the current date and the date '8/8/2003'. Since the current date is after '8/8/2003', the result will be negative. The result of -8 indicates that the current date is 8 years ahead of '8/8/2003'.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 25, 2024Quiz Edited by
ProProfs Editorial Team
Expert Reviewed by
Samy Boulos -
Feb 13, 2012Quiz Created by
Telliant
Back to top