Free Sharing Of Microsoft 70-461 Brain Dumps.v.2017-05-05.200q

Discussion in '70-461' started by Hamid, May 6, 2017.

  1. Hamid

    Hamid Member
    Member

    Joined:
    May 6, 2016
    Messages:
    142
    Likes Received:
    7
    QUESTION 169
    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.
    B.
    C.
    D.
    Answer: C
    Explanation:
    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
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.




    QUESTION 170
    You are maintaining a SQL Server database that uses the default settings.
    The database contains a table that is defined by the following Transact-SQL statement:


    You must write a query that returns the AddressLine1, AddressLine2, and Region fields separated by carriage returns.
    You must return an empty string for any null values.

    A.
    B.
    C.
    D.
    Answer: A
    Explanation:
    Char(13) is a carriage return.
    Use the IIF construct to return an empty string for NULL values of the Adressline2 column.
    IIF returns one of two values, depending on whether theBoolean expression evaluates to true or false in SQL Server.
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.




    QUESTION 171
    A table named Profits stores the total profit made each year within a territory.
    The Profits table has columns named Territory, Year, and Profit.
    You need to create a report that displays the profits made by each territory for each year and its previous year.
    Which Transact-SQL query should you use?

    A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
    B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
    C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
    D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits

    Answer: C
    Explanation:
    LAG accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Usethis analytic function in a SELECT statement to compare values in the current row with values in a previous row.
    Use ORDER BY Year, not ORDER BY Territory.
    Example: The following example uses the LAG function to return the difference in sales quotas fora specific employee over previous years. Notice that because there is no lag value available for the first row, the default of zero (0) is returned.
    USE AdventureWorks2012;
    GO
    SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory
    WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006'); Incorrect Answers:
    A, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row.
    Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.
    B: Use ORDER BY Year, not ORDER BY Territory.
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.




    QUESTION 172
    Your database contains a table named Products that has columns named ProductID and Name.
    You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time.
    You need to view rows 31 through 45.
    Which Transact-SQL query should you create?
    A.
    B.
    C.
    D.
    Answer: C
    Explanation:
    The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
    Example: Skip first 10 rows from the sorted resultset and return next 5 rows.
    SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.




    QUESTION 173
    A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
    The Production.Product table contains the following two columns:

    - ProductID
    - Name

    The Sales.SalesOrderDetail table contains the following three columns:

    - SalesOrderID
    - ProductID
    - OrderQty

    You need to create a query listing all of the products that were never ordered.
    Which statements should you execute?

    A.
    B.
    C.
    D.
    Answer: A
    Explanation:
    EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries.
    EXCEPT returns distinct rows from the left input query that aren't output by the right input query.
    Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
    Example: The following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query.
    -- Uses AdventureWorks
    SELECT CustomerKey
    FROM FactInternetSales
    EXCEPT
    SELECT CustomerKey
    FROM DimCustomer
    WHERE DimCustomer.Gender = 'F'
    ORDER BY CustomerKey;
    --Result: 9351 Rows (Sales to customers that are not female.) Incorrect Answers:
    B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
    C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.




    QUESTION 174
    You plan to write a query for a new business report that will contain several nested queries.
    You need to ensure that a nested query can call a table-valued function for each row in the main query.
    Which query operator should you use in the nested query?

    A. CROSS APPLY
    B. INNER JOIN
    C. OUTER JOIN
    D. PIVOT

    Answer: A
    Explanation:
    The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
    There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.




    QUESTION 175
    You are designing a table for a SQL Server database.
    The table uses horizontal partitioning.
    You have the following requirements:

    - Each record in the table requires a unique key.
    - You must minimize table fragmentation as the table grows.

    You need to choose the appropriate data type for the key value.
    What should you do?

    A. Use the NEWID function to create a unique identifier.
    B. Use the NEWSEQUENTIALID function to create a unique identifier.
    C. Generate a random value that uses the bigint datatype.
    D. Generate a random value that uses the char(16) data type.

    Answer: B
    Explanation:
    Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.
    NEWSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
    References:

    Dear visitor, you need to Register or Login to view links on Certify Chat.



    Full version:

    Dear visitor, you need to Register or Login to view links on Certify Chat.

     
  2. njan

    njan Member
    Member

    Joined:
    Jun 27, 2017
    Messages:
    3
    Likes Received:
    0
    thanks
     
  3. tinamark

    tinamark Member
    Member

    Joined:
    Oct 31, 2018
    Messages:
    106
    Likes Received:
    7
    I am very happy for my result in Microsoft 70-461 Written Exam All the thanks go to pass4surekey that have done such a great job by designing

    Dear visitor, you need to Register or Login to view links on Certify Chat.

    . I went through all the questions and got clarity in all the concepts that may possibly be tested in the final exam. It would not have been as easy for me to pass this certification without the help of 70-461dumps. I will suggest to all of you to use 300-115 Dumps for preparation of this certification from this site

    Dear visitor, you need to Register or Login to view links on Certify Chat.



    Questions that are very important and also include in every 70-461 exam…

    QUESTION 1

    You develop a Microsoft SQL Server database. You need to create a batch process that meets the following requirements: Returns a result set based on supplied parameters. Enables the returned result set to perform a join with a table. Which object should you use?

    A. Inline user-defined function

    B. Stored procedure

    C. Table-valued user-defined function

    D. Scalar user-defined function

    Correct Answer: C

    QUESTION 2

    You develop a Microsoft SQL Server database that contains a heap named Orders Historical. You write the following Transact-SQL query: INSERT INTO Orders Historical SELECT * FROM Completed Orders You need to optimize transaction logging and locking for the statement. Which table hint should you use?

    A. HOLDLOCK

    B. ROWLOCK

    C. XLOCK

    D. UPDLOCK

    E. TABLOCK

    Correct Answer: E

    QUESTION 3

    You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the Product Name field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

    A. CROSS JOIN

    B. CROSS APPLY

    C. PIVOT D. UNPIVOT

    Correct Answer: C

    QUESTION 4

    You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

    A. Float

    B. Money

    C. Small money

    D. Numeric

    Correct Answer: D
     

Share This Page