Free Preparation Discussions
MENU
Microsoft 70-761 Exam Questions
- Microsoft MCSA: SQL 2016 Database Development Certifications
- Microsoft MCSA: SQL Server 2012/2014 Certifications
- Topic 1: Create Transact-SQL SELECT Queries/ Identify Proper SELECT Query Structure, Write Specific Queries to Satisfy Business Requirements
- Topic 2: Determine Proper Usage of INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, and CROSS JOIN
- Topic 3: Implement Functions and Aggregate Data/ Construct Queries Using Scalar-Valued and Table-Valued Functions
- Topic 4: Modify Data/ Write INSERT, UPDATE, and DELETE Statements/ Construct Data Manipulation Language (DML) Statements Using the OUTPUT Statement
- Topic 5: Query Data with Advance Transact-SQL Components/ Query data by Using Subqueries, Table Expressions/ Group and Pivot Data by Using Queries
- Topic 6: Query Temporal Data and Non-Relational Data/ Query Historic Data by Using Temporal Tables/ Query and Output JSON Data/ Query and Output XML Data
- Topic 7: Program Databases by Using Transact-SQL/ Create Database Programmability Objects by Using Transact-SQL
- Topic 8: Implement Error Handling and Transactions/ Implement Data Types and NULLs
Free Microsoft 70-761 Exam Actual Questions
Note: Premium Questions for 70-761 were last updated On 01-01-1970 (see below)
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:
The tables include the data below:
Customer_CRMSystem
Customer_HRSystem
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display customers that have a proper CustomerCode and do not appear in the Customer_HRSystem table.
Which Transact-SQL statement should you run?
To return records from the left table which are not found in the right table use Left outer join and filter out the rows with NULL values for the attributes from the right side of the join.
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a table named Products that contains information about the products that your company sells. The table contains many columns that do not always contain values.
You need to implement an ANSI standard method to convert the NULL values in the query output to the phrase ''Not Applicable''.
What should you implement?
The ISNULL function replaces NULL with the specified replacement value.
References:
https://msdn.microsoft.com/en-us/library/ms184325.aspx
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
You discover an application bug that impacts customer data for records created on or after January 1, 2014. In order to fix the data impacted by the bug, application programmers require a report that contains customer data as it existed on December 31, 2013.
You need to provide the query for the report.
Which Transact-SQL statement should you use?
The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The DATEFROMPARTS function returns a date value for the specified year, month, and day.
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
You need to create a query that meets the following requirements:
- For customers that are not on a credit hold, return the CustomerID and the latest recorded population for the delivery city that is associated with the customer.
- For customers that are on a credit hold, return the CustomerID and the latest recorded population for the postal city that is associated with the customer.
Which two Transact-SQL queries will achieve the goal? Each correct answer presents a complete solution.
Using Cross Joins
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
However, if a WHERE clause is added, the cross join behaves as an inner join.
B: You can use the IIF in the ON-statement.
IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.
References:
https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/hh213574.aspx
You have a database that contains the following tables:
Customer
CustomerAudit
Where the value of the CustomerID column equals 3, you need to update the value of the CreditLimit column to 1000 for the customer. You must ensure that the change to the record in the Customer table is recorded on the CustomerAudit table.
Which Transact-SQL statement should you run?
The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the table variable.
- Select Question Types you want
- Set your Desired Pass Percentage
- Allocate Time (Hours : Minutes)
- Create Multiple Practice tests with Limited Questions
- Customer Support
Currently there are no comments in this discussion, be the first to comment!