70-461 Exam
sql 70-461 questions [Oct 2021]
Proper study guides for Replace Microsoft Querying Microsoft SQL Server 2012 certified begins with Microsoft 70-461 preparation products which designed to deliver the Simulation 70-461 questions by making you pass the 70-461 test at your first time. Try the free 70-461 demo right now.
2021 Oct microsoft sql server 70-461:
Q1. You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?
A. CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO
B. CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO
C. CREATE TABLE DocumentStore AS FileTable
D. CREATE TABLE DocumentStore ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO
Answer: A
Q2. Your database contains a table named Customer that has columns named CustomerID
and Name.
You want to write a query that retrieves data from the Customer table sorted by Name
listing 20 rows at a time.
You need to view rows 41 through 60.
Which Transact-SQL query should you create?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q3. You administer a Microsoft SQL Server 2012 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?
A. GRANT DELETE ON Purchases.Suppliers TO ContosoUser
B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
Answer: D
Q4. You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
Answer: A,B
Explanation:
Verified the answers as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms173545.aspx
Q5. CORRECT TEXT
You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements:
. Contain a new column named LineltemTotal that stores the product of ListPrice
and Quantity for each row.
. The calculation for a line item total must not be run every time the table is queried.
. The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
Answer:
Up to date exam 70-461:
Q6. You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.
You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?
A. Perform a file restore.
B. Perform a transaction log restore.
C. Perform a restore from a full backup.
D. Perform a filegroup restore.
Answer: A
Q7. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.
You need to ensure that users are able to modify data by using the view.
What should you do?
A. Create an AFTER trigger on the view.
B. Modify the view to use the WITH VIEW_METADATA clause.
C. Create an INSTEAD OF trigger on the view.
D. Modify the view to an indexed view.
Answer: C
Q8. DRAG DROP
You use a Microsoft SQL Server 2012 database.
You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Q9. You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table.
What should you do?
A. Convert all indexes to Column Store indexes.
B. Implement Unicode Compression.
C. Implement row-level compression.
D. Implement page-level compression.
Answer: D
Q10. 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?
A. Create a ROWVERSION column.
B. Create a SEQUENCE object that uses the INTEGER data type.
C. Use the INTEGER data type along with IDENTITY
D. Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID()
E. Create a TIMESTAMP column.
Answer: D