What does this exam test?

Querying data with Transact-SQL.

You develop a Microsoft SQL Server database that supports an application. The application contains a table that has the following definition: CREATE TABLE Inventory - (ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?

Correct Answer: ALTER TABLEInventory ADD TotalItems = ItemsInStore + ItemsInWarehouse

You develop a Microsoft SQL Server database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to improve the performance of the view by persisting data to disk. What should you do?

Correct Answer: Create anAFTERtrigger on the view.

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series. You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

Correct Answer: Use the CAST function.

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements: ✑ Remove all duplicates of the Products table based on the ProductName column. ✑ Retain only the newest Products row. Which Transact-SQL query should you use?

Correct Answer: WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?

Correct Answer: SELECT IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' )

You have a Microsoft SQL Server database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements: ✑ Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. ✑ Results must include customers who have not placed any orders. Which Transact-SQL query should you use?

Correct Answer: SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

Correct Answer: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p

You administer a Microsoft SQL Server database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

Correct Answer: ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

Correct Answer: SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime

You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

Correct Answer: SELECT CustomerID FROM Customer WHERE TerritoryID SOME(SELECT TerritoryID FROM Salesperson)

You use Microsoft SQL Server to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

Correct Answer: DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar 0) PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO

You develop a Microsoft SQL Server database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

Correct Answer: CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts) END

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to ensure that the following requirements are met: ✑ Students must be ranked based on their average marks. ✑ If one or more students have the same average, the same rank must be given to these students. ✑ Consecutive ranks must be skipped when the same rank is assigned. Which Transact-SQL query should you use?

Correct Answer: SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks

You administer a Microsoft SQL Server database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

Correct Answer: Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.

You administer a Microsoft SQL Server database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact- SQL statement should you use?

Correct Answer: CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

Correct Answer: SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()

You use Microsoft SQL Server to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: ✑ Does not return an error ✑ Closes all opened transactions Which Transact-SQL statement should you use?

Correct Answer: BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH

You develop a database application. You create four tables. Each table stores different categories of products. You create a Primary Key field on each table. You need to ensure that the following requirements are met: ✑ The fields must use the minimum amount of space. ✑ The fields must be an incrementing series of values. ✑ The values must be unique among the four tables. What should you do?

Correct Answer: Create a ROWVERSION column.

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its WHERE clause contained in a non-clustered index and one additional column. You need to optimize the statement. What should you do?

Correct Answer: Add an INCLUDE clause to the index.

You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause. Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1. You need to optimize the statement. What should you do?

Correct Answer: Add a FORCESEEK hint to the query.

What does this exam test?

Querying data with Transact-SQL.

Welcome to awslagi.com – Sharing AWS, Google Cloud, Microsoft Azure IT Exams. It's free for all!

MCSA Querying Microsoft SQL Server 2012/2014

Query SQL Server data with confidence and precision.

4.8★★★★★(393 ratings)

16,603 studentsIntermediate

Learn to retrieve and shape data using Transact-SQL in Microsoft SQL Server 2012/2014. The exam builds the foundation for working effectively with databases, reports, and data-driven applications. This certification is valuable for developers, analysts, and database professionals who need strong querying skills. It helps you become faster and more accurate in extracting useful information from enterprise data systems.

What you'll learn

Write T-SQL queries- Filter and join data- Aggregate results- Use subqueries and functions- Work with database objects

70-461

Lectures1

Quizzes0

Duration30 week

Students16,603

$20.00

📢 Unlock Full Access to Success!

"The content displayed here is a promotional preview and contains only 50% of the actual exam database. To access the complete set of updated questions, please upgrade to the Full Version."

👉 Click the button and follow the instructions to secure your full access immediately!