SQL Server includes 5 System Databases. Which one of the following is used only for Reporting Services?

Among 4 different editions of SQL Server, which of the following has support for Always on High Availability?

Which authentication method can be used to log on to SQL Server?

SQL Server Database Engine Processes Select Statement in a logical manner. Choose the best answer that describes the correct Logical Query Processing Order.

From the following T-SQL Elements select the one that can include a Predicate:

Which of the following Statements are True with regards to Categories of T-SQL?

Consider the following Query: Select unit price, qty, (unit price*qty) From Sales. OrderDetails;
The Results appear as follows:



Choose the best answer which describes the error in the above Results:

You have company departments in five countries. You have the following query for the Human Resource Database:
SELECT DISTINCT DeptName, Country from HumanResources.Departments;
The Query Returns the following:



How many rows would be returned?

Which of the following statements are correct regarding T-SQL Case Expressions?

Choose which of the following join types description is true?

What is the purpose of using Self Joins? Chose the best answer/s below:

Consider the following Query:

“From T1 Left Outer Join t2 ON t1.col=t2.col”

Choose the correct answer that best describes the above query:

The main purpose of order by clause is to?

Let’s assume you are filtering data in the where clause? Which of the following statement/s is/are true?

Chose the answer below which states the error in the following Query:

SELECT TOP (5) ordereid, custid, orderdate From Sales. Orders

In Certain scenarios we must convert from one data type to another But, there are important reasons for data type conversion. Listed below are some options, Chose the answer/s which you think is correct:

Collation is a collection of properties that determine several aspects of character data. Which property is/are not a property of Collation?

This question comprises of three parts. First part is about date functions, second part about Date and Time Parts Functions, and third part will focus on date and time difference Functions. For each part, one question with 4 options will be given. You to have to choose the option which best answers the question:

Part 1: Date Functions:

*Following Function is used to get system dates:

Part 2: Date and Time Parts Function:

*Following function is used to get Date and Time Parts:

Part 3: Date and time difference functions:

*Following function is used to get date and time difference:

Which of the following statements are true about Select Into clause:

Choose all the statements which are correct about Merge clause:

This question comprises of three parts. First part is about date functions, second part about Date and Time Parts Functions, and third part will focus on date and time difference Functions. For each part, one question with 4 options will be given. You to have to choose the option which best answers the question:

Part 1: Date Functions:

*Following Function is used to get system dates:

Part 2: Date and Time Parts Function:

*Following function is used to get Date and Time Parts:

Part 3: Date and time difference functions:

*Following function is used to get date and time difference:

Scalar functions in SQL Server can be defined as:

In addition to data type conversions, SQL Server provides functions for conversion or replacement of NULL. Which function is used to replace NULL input with another value?

Which clause/s is/are processed after Group By clause?

Which of the following statement/s is/are true about Aggregate functions?

Answer the following two questions:

1) Which clause only operates on Groups and not detail rows?

2) Which clause controls which rows are available for the next phase of the query?

Part 3: Date and time difference functions:

*Following function is used to get date and time difference:

Subqueries can either be:

Choose all the correct statements regarding Inner and Sub queries:

Correlated subqueries cannot be executed separately from the outer query because of:

Which of the following statements best describe views?

When flexibility of input is preferred, Table Valued Functions are more useful then views Because:

Choose all statements below which are correct about CTE (Common Table Expressions)?

Your company have two databases: HR. Employees and Sales. Customers. You want to return Country, Region and city from both tables with the help of following Query:
SELECT country, region, city FROM HR. Employees

Answer

SELECT country, region, city FROM Sales. Customers;

Your main objective is to get city, region and country from both tables but, making sure Duplicate rows are eliminated. From the options below select the option that will eliminate duplicate rows and can be written between above two queries:

Your company have two databases: Sales. Customers and Production. Suppliers. You want to get data for country and city, from both tables, with the help of following query:
SELECT country, city FROM SALES.CUSTOMERS

ANSWER

SELECT country, city FROM PRODUCTION.SUPPLIERS;

Your main objective is to get country and city from both tables which contains distinct records. Which of the following options from below can cater the requirements and can be written between the two queries:

Following statement contains queries about Joins and Apply Operators. Choose all those which are correct:

In SQL windowing operations, which of the following statements are false?

Choose all the answers that best describes SQL windowing:

Which of the following queries are example of Window Offset function:

In the elements of Pivoting, there are 3 phases: Select the one which is not a phase:

We can use Group by with ROLLUP, CUBE and GROUPING SETS. Select the answer which best describes both.

This question contains three parts. Choose all answers which are correct

Which of the following Batch statements are valid:

Select all the answer that describes the error in the following T-SQL variables declaration and procedure execution :
Line 1:DECLARE Numrows INT=3, catid INT

Line 2:EXEC Production.ProdsByCategory

Line 3:@Numrows= numrows, @catid=catid

You want to populate a table by creating 27 new rows. Before you create the rows, you need to check that the table exists. From the following T-SQL keywords, choose the one that you will NOT need to use.

Following statements describes Error Handling in T-SQL . Choose all those which are not correct

You have the following T-SQL script:
Line 1:BEGIN TRY
Line 2:INSERT INTO HumanResources.PossibleSkills(Skill Name, Category)
Line 3:VALUES ('Associate Consultant', 'IT Professional');
Line 4:END TRY
Line 5:DECLARE @prefix AS NVARCHAR(50) = 'There has been an error: ';
Line 6:BEGIN CATCH
Line 7:PRINT @prefix + ERROR_MESSAGE();
Line 8:THROW;
Line 9:END CATCH;
Line 10:GO
This code will not compile and execute. Select all the options that will execute and compile this code.

You are working on a project in which you are writing some error handling in a T-SQL script. If a problem arises you want to raise and error with a severity of 20. What option below will you choose to raise and error with 20 Severity.

Part 1: Which of the following statement/s about BEGIN TRANSACTION is/are not true?

Part 2: Choose all the correct options which best describe COMMIT TRANSACTIONS.

Part 3: Consider the following Transaction:

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.SimpleOrders(custid, empid, orderdate)
VALUES (68,9,'2006-07-12');
INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty)
VALUES (1, 2,15.20,20);
COMMIT TRANSACTION
END TRY
Should the above transaction be committed or rolled back ?

Part 4:ROLLBACK statement undoes all modifications made in the transaction by reverting the data to the state it was in at the beginning of the transaction. Which of the following is not true about ROLLBACK statement?

Part 5:Following statements are about Implementing Transactions in T-SQL. You have to decide which of them is correct. You can choose all, none, one or two correct answers:

Consider the following Transaction below and select all those options that are correct.

LINE1:BEGIN TRY

Line 2: BEGIN TRANSACTION

Line 3: INSERT INTO SALES.ORDERS

Line 4: INSET INTO SALES.ORDERDETAILS

Line 5: COMMIT TRANSACTION

Line 6:END TRY

LINE 7:BEGIN CATCH

LINE 8: SELECT ERROR_NUMBER()

LINE 9: ROLLBACK TRANSACTION

LINE 8: END CATCH

Which of the following options are not correct about this transaction? :

Select from the description below, what category of transaction does it falls into:

Individual data modification statements (for example, INSERT, UPDATE, and DELETE) submitted separately from other commands are automatically wrapped in a transaction by SQL Server. These single-statement transactions are automatically committed when the statement succeeds or are automatically rolled back when the statement encounters a runtime error.