Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 341:

  SIMULATION

  You have a SQL Server database that contains all of the sales data for your company.

  You need to create a query that returns the customers who represent the top five percent of the total actual sales.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been

  provided as well as below it.

  

  

  Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  1 SELECT CustomerID, N

  2 Top5= TOP (0,95) PERCENT Sales (ORDER BY N) OVER (PARTITION BY Sales)

  3 FROM Customers;

  Explanation:

  Add TOP and PERCENT Sales on line 2.

  Syntax:

  TOP (expression) [PERCENT] [ WITH TIES ]

  ]

  expression

  Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

  PERCENT

  Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

  ---

  https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-2017

• Question 342:

  You have a database that contains a table named Customer. The customer table contains a column named LastName that has a column definition of varchar(50).

  An application named App1 reads from the table frequently. You need to change the column definition to nvarchar(100). The solution must minimize the amount of time it takes for App1 to read the data. Which statement should you execute?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

  ---

  To change the data type of a column in a SQL Server (or Microsoft access) table, use the following syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype https://www.w3schools.com/SQl/sql_alter.asp

• Question 343:

  ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB.

  You have created a view using the following Transact-SQL code:

  CREATE VIEW ABCCommunications

  (Type, CompanyID, CompanyName, Location, ContactName, Email, Phone)

  AS

  SELECT 'Clients' AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone

  FROM CommList

  WHERE Relation = 'Client'

  SELECT 'Partners' AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone

  FROM CommList

  WHERE Relation = 'Partner'

  SELECT 'Guests' AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone

  FROM CommList

  WHERE Relation = 'Guests'

  GO

  You want the view to be used to edit all columns except the CompanyID, CompanyName and Location columns.

  What should you implement on the view?

  A. You should consider implementing an AFTER UPDATE trigger.
  B. You should consider implementing an Index.
  C. You should consider implementing an INSTEAD OF UPDATE trigger.
  D. You should consider implementing a CHECK constraint.
  C. You should consider implementing an INSTEAD OF UPDATE trigger.

• Question 344:

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

  A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
  B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modifythe stored procedure to include the EXECUTE AS {newlogin} statement.
  D. Grant the db_owner role on the database to User1.
  E. Grant the sysadmin role on the database to User1.
  D. Grant the db_owner role on the database to User1.
  E. Grant the sysadmin role on the database to User1.

• Question 345:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit.

  

  You need to display rows from the Orders table for the Customers row having the Customerld value set to 1 in the following XML format.

  

  Which Transact-SQL query should you use?

  A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOINCustomers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
  B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOINCustomers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
  C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOINCustomers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
  D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOINCustomers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOINCustomers ON Orders.CustomerId=Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO
  F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOINCustomers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,OrderDate, Amount FROM OrdersINNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')

• Question 346:

  You develop a Microsoft SQL Server 2012 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?

  A. 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
  B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR 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
  C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
  D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
  A. 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

• Question 347:

  You create a table by using the following Transact-SQL Statement:

  You need to return a result set that has a single column named DisplayInformation. The result set must contain the Name value if the Name value is NOT NULL, otherwise the result set must contain the SubName value.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been

  provided as well as below it.

  

  

  

  Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  SELECT IIF (Name IS NOT NULL, Name, SubName)

  Update line 1 to get the following:

  SELECT IIF (Name IS NOT NULL, Name, SubName)

  FROM Products;

  IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

  Syntax: IIF ( boolean_expression, true_value, false_value )

  If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.

  If the value of expression is NULL,IS NOT NULL returns FALSE; otherwise, it returns TRUE.

  To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL

  ---

  https://msdn.microsoft.com/en-us/library/hh213574.aspx

  https://msdn.microsoft.com/en-us/library/ms188795.aspx

• Question 348:

  You use a Microsoft SQL Server database.

  You want to create a table to store files.

  You need to ensure that the following requirements are met:

  The files must include information about the directory structure.

  The files must be accessible in SQL Server.

  The files must be in a folder that is accessible directly by using Windows Explorer.

  Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  D. Option D

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/blob/create-alter-and-drop-filetables?view=sql-server-2017

• Question 349:

  Which of the following are true about the SET QUOTED_IDENTIFIER statement? (Choose all that apply.)

  A. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names.
  B. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names.
  C. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings.
  D. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings.
  A. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names.
  D. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings.

• Question 350:

  What is the row locator when a table is stored as a balanced tree?

  A. RID.
  B. Columnstore index key.
  C. Clustering key.
  D. A table is never stored as a balanced tree.
  C. Clustering key.