SQL Server Quiz Questions And Answers

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.

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.

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.

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.

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.

A basic SQL SELECT statement must include:

SELECT clause → to specify the columns you want to retrieve

FROM clause → to specify the table from which to retrieve the data

Example:

sql

SELECT name, age FROM students;

Without both SELECT and FROM, the statement is incomplete (except in special cases like using SELECT 1, which does not pull from a table). But in standard usage when accessing data, both clauses are required.

Therefore, the correct answer is:

Both A and B

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.

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."

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.

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.

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.

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.

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.

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.

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".

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.'

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.

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.

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.

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.

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'.

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.

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.

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.