Try and practice the latest Microsoft : 070-461 real questions & answers

You are a database developer of a Microsoft SQL Server 2012 database.
The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.
Which Transact-SQL statement should you use?

• A. ALTER TABLE Orders
  ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
• B. ALTER TABLE Customer
  ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders (CustomerId)
• C. ALTER TABLE Orders
  ADD CONSTRAINT CK_Orders_CustomerID
  CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
• D. ALTER TABLE Customer
  ADD OrderId INT NOT NULL;
  ALTER TABLE Customer
  ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
• E. ALTER TABLE Orders
  ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)

Explanation: Only visible for Actual4Cert members. You can sign-up / login (it's free).

You need to create a stored procedure to support the following:
* TRY/CATCH error handling
* Transaction management
Which three statements should you include in the stored procedure in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Explanation:
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

You are a database developer of a Microsoft SQL Server 2012 database.
You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.
A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer
  (SourceID int NOT NULL PRIMARY KEY CLUSTERED,
  CustomerID int NOT NULL UNIQUE,
  CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer
  (SourceID int NOT NULL,
  CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
  CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer
  (SourceID int NOT NULL,
  CustomerID int NOT NULL,
  CustomerName varchar(255) NOT NULL,
  CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
  (SourceID, CustomerID));
• D. CREATE TABLE Customer
  (SourceID int NOT NULL IDENTITY,
  CustomerID int NOT NULL IDENTITY,
  CustomerName varchar(255) NOT NULL);

You plan to deploy a stored procedure for a database named TICKETS.
You need to implement error handling for the stored procedure to ensure that the system-defined error messages are returned if an error occurs upon insert.
Part of the correct Transact-SQL has been provided in the answer are 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.

• A. 1 CREATE PROCEDURE AdCase
  2 @CustomerId INT,
  3 @ProblemId INT,
  4 @Comment NVARCHAR (MAX)
  5 AS
  6
  7 BEGIN TRY
  8 INSERT INTO Cases
  9 (CustomerId, ProblemId, Comment)
  10 VALUES
  11 (@CustomerId, @ProblemId, @Comment)
  12 END TRY
  13 BEGIN CATCH
  14 INSERT INTO AppLog
  15 (CurrentTime, ErrorNumber, CustomerId)
  16 VALUES
  17 (getdate(), ERROR_NUMBER(), @CustomerId);
  18 THROW;
  19
  20 END CATCH
  21
  Make changes and additions in the above lines.
  7 BEGIN TRY
  12 END TRY
  13 BEGIN CATCH
  18 THROW;
  20 END CATCH
• B. 1 CREATE PROCEDURE AdCase
  2 @CustomerId INT,
  3 @ProblemId INT,
  4 @Comment NVARCHAR (MAX)
  5 AS
  6
  7 BEGIN TRY
  8 INSERT INTO Cases
  9 (CustomerId, ProblemId, Comment)
  10 VALUES
  11 (@CustomerId, @ProblemId, @Comment)
  12 END TRY
  13 END CATCH
  14 Make changes and additions in the above lines.
  15 BEGIN TRY
  16 END TRY
  17 BEGIN CATCH
  18 THROW;
  19END CATCH

Explanation: Only visible for Actual4Cert members. You can sign-up / login (it's free).

You develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:

You need to meet the following requirements:
* If a product has a product category value "REGULAR", the product should have a sub-category with a length of two characters.
* If a product has the product category other than "REGULAR", the product may or may not have a sub-category.
Which Transact-SQL statement should you use?

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

Explanation: Only visible for Actual4Cert members. You can sign-up / login (it's free).