Free Microsoft 070-461 Exam Dumps Questions & Answers

You use a Microsoft Azure SQL Database instance named ContosoDb. ContosoDb contains a table named Customers that has existing records.
The Customers table has columns named Id and Name.
You need to create a new column in the Customer table named Status that allows null values and sets the value of the Status column to Silver for all existing records.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the anwer area and arrange them in the correct order.

Correct Answer:

Explanation:
NULL or NOT NULL specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017

You support a database structure shown in the exhibit. (Click the Exhibit button.)

You need to write a query that displays the following details:
* Total sales made by sales people, year, city, and country
* Sub totals only at the city level and country level
* A grand total of the sales amount
Which Transact-SQL query should you use?

• A.  SELECT SalesPerson.Name, Country, City,
  DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
  FROM Sale INNER JOIN SalesPerson
  ON Sale.SalesPersonID = SalesPerson.SalesPersonID
  GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
• B.  SELECT SalesPerson.Name, Country, City,
  DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
  FROM Sale INNER JOIN SalesPerson
  ON Sale.SalesPersonID = SalesPerson.SalesPersonID
  GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
• C.  SELECT SalesPerson.Name, Country, City,
  DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
  FROM Sale INNER JOIN SalesPerson
  ON Sale.SalesPersonID = SalesPerson.SalesPersonID
  GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
• D.  SELECT SalesPerson.Name, Country, City,
  DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
  FROM Sale INNER JOIN SalesPerson
  ON Sale.SalesPersonID = SalesPerson.SalesPersonID
  GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

You use a Microsoft SQL Server database that contains a table. The table has records of customer orders.
Your company has three divisions that have the following names:
East
Central
West
You need to create a query that displays the following information:
* The number of sales for each product (ProductName) grouped by the division (Division) that sold the product
* A column for each division
Which Transact-SQL query should you use?

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

You administer a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Suppliers and an indexed view named VWLocalSuppliers, both of which were created by using the following Transact-SQL statement:

You need to change the data type of the Code column in the Suppliers table to nvarchar(50).
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Correct Answer:

You have a view named Person.vAdditionalContactInfo that uses data from a table named Customers. The Customers table was created by running the following Transact-SQL statement:

You need to create a query that returns the first and last names of a customer from the view. If the CustomerID column does not have a value defined, the following message must be added to the query result: "Customer ID Does Not Exist." Construct the query using the following guidelines:
* Do not use aliases for column or table names.
* Display the columns in the same order as they are defined in the Customers table.
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 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 SELECT FirstName, LastName, CustomerID =
  2 CASE
  3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
  4 ELSE CustomerID
  5 END
  6 FROM Person.vAdditionalContactInfo;
  7
  To line 1 add: CustomerID =
  To line 5 add: END
• B.  1 SELECT FirstName, LastName, CustomerID =
  2 CASE
  3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
  4 ELSE CustomerID
  5 END
  6 FROM Person.vAdditionalContactInfo;
  7
  To line 1 add: CustomerID =
  To line 5 add: END