Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 41:

  You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB with a table named Partners.

  The Partners table was created using the following Transact-SQL code:

  CREATE TABLE [dbo].[Partners]

  (

  [CompanyID] [int] NOT NULL PRIMARY KEY,

  [CompanyName] [varchar] (150) NOT NULL,

  [Location] [varchar] (150) NOT NULL,

  [ContactName] [varchar] (150) NOT NULL,

  [Email] [varchar] (150) NOT NULL,

  [Phone] [varchar] (10) NOT NULL

  )

  You develop a new table named Events using the following Transact-SQL code:

  CREATE TABLE [dbo].[Events]

  (

  [EventID] [int] NOT NULL PRIMARY KEY,

  [CompanyID] [int] NOT NULL,

  [EventDescription] [varchar] (2500),

  [EventCordinator] [varchar] (150) NOT NULL

  )

  How would you guarantee that values in the Events.CompanyID column already exist in the Partners.CompanyID column?

  A. You should add a Foreign Key Constraint on the Events table.
  B. You should add a Check Constraint on the Events table.
  C. You should add a Unique Constraint on the Events table.
  D. You should specify Events.CompanyID as a spars column.
  E. You should change the Events table to a partitioned table.
  A. You should add a Foreign Key Constraint on the Events table.

  ---

  Ref: http://msdn.microsoft.com/en-us/library/ms179610.aspx

• Question 42:

  You are developing a Microsoft SQL Server 2012 database for a company. The database contains a table that is defined by the following Transact-SQL statement.

  

  You use the following Transact-SQL script to insert new employee data into the table. Line numbers are included for reference only.

  

  If an error occurs, you must report the error message and line number at which the error occurred and continue processing errors. You need to complete the Transact-SQL script. Which Transact- SQL segment should you insert at line 06?

  A. SELECT ERROR_LINE(), ERROR_MESSAGE()
  B. DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message = ERROR_MESSAGE (), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(); RAISERROR (@message, @severity, @state);
  C. DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message = ERROR_MESSAGE (), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(); THROW (@message, @severity, @state);
  D. THROW;
  B. DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message = ERROR_MESSAGE (), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(); RAISERROR (@message, @severity, @state);

  ---

  When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must

  be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

  Reference: TRY... CATCH (Transact-SQL)

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

• Question 43:

  You administer a Microsoft SQL Server database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema.

  You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

  A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
  B. Include the Sales schema as an owned schema for the db_denydatawriterrole. Add the users to the db_denydatawriter role.
  C. Deny Delete permissions on each table in the Sales schema for each user.
  D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.
  A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

• Question 44:

  What is the restriction that grouped queries impose on your expressions?

  A. If the query is a grouped query, you must invoke an aggregate function.
  B. If the query has an aggregate function, it must have a GROUP BY clause.
  C. The elements in the GROUP BY clause must also be specified in the SELECT clause.
  D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function
  D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function

• Question 45:

  Which of the following is applicable to a TRUNCATE statement? (Choose all that apply.)

  A. The statement writes more to the transaction log than DELETE.
  B. The statement resets an IDENTITY property.
  C. The statement is disallowed when a foreign key points to the target table.
  D. The statement is disallowed when an indexed view based on the target table exists.
  B. The statement resets an IDENTITY property.
  C. The statement is disallowed when a foreign key points to the target table.
  D. The statement is disallowed when an indexed view based on the target table exists.

• Question 46:

  Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table. Which Transact-SQL query should you use?

  A. UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
  B. UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, inserted.Price, deleted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
  C. UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
  D. UPDATE Products SET Price = Price * 1.05 INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; SELECT ProductCode, Price, Price * 1.05 FROM Products
  A. UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

  ---

  http://msdn.microsoft.com/en-us/library/ms177564.aspx

• Question 47:

  If you use the BEGIN TRANSACTION statement, then execute 2 insert statements and 2 delete statements what will happen if you execute ROLLBACK TRANSACTION?

  A. Only the INSERT statements will be rolled back
  B. All of the INSERTS and DELETES will be rolled back
  C. Only the last INSERT and last DELETE statement will be rolled back
  D. Only the DELETE statements will be rolled back
  B. All of the INSERTS and DELETES will be rolled back

  ---

  BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. All modifications are erased when you issue a ROLLBACK TRANSACTION statement.

• Question 48:

  You're designing a new SQL Server 2012 table that will hold millions of records. The table will be used by a finance application to show historical transactions. The table only has 10 columns but it's vital that the table performs well. How many primary keys should you create for the table for best performance?

  A. 2
  B. 10
  C. 3
  D. 1
  D. 1

  ---

  You can only have 1 primary key per table.

• Question 49:

  What is missing from the T-SQL statement below for creating and using a cursor? DECLARE mycursor CURSOR FOR SELECT * FROM Vendor FETCH NEXT FROM mycursor;

  A. KEYSET
  B. OPEN
  C. STATIC
  D. SCROLL
  B. OPEN

  ---

  You need to open the CURSOR before you can call FETCH.

• Question 50:

  You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition. You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated. Which Transact-SQL query should you use?

  

  A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice) - - Create Audit Records
  B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records
  C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice) - - Create Audit Records
  D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records
  D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records

  ---

  http://msdn.microsoft.com/en-us/library/bb510663.aspx http://msdn.microsoft.com/en-us/library/ms186329.aspx