70-761 – Querying Data with Transact-SQL

HOTSPOT

You have a database that contains the following tables: tblRoles, tblUsers, and tblUsersInRoles.

The table tblRoles is defined as follows.

You have a function named ufnGetRoleActiveUsers that was created by running the following Transact-SQL statement:

You need to list all roles and their corresponding active users. The query must return the RoleId, RoleName, and UserName columns. If a role has no active users, a NULL value should be returned as the UserName for that role.

How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Hot Area:

SIMULATION

You create a table named Sales.Orders by running the following Transact-SQL statement:

You need to write a query that meets the following requirements:

removes orders from the table that were placed before January 1, 2012 uses the date format of YYYYMMDD

ensures that the order has been shipped before deleting the record

Construct the query using the following guidelines:

use one-part column names and two-part table names do not use functions

do not surround object names with square brackets do not use variables

do not use aliases for column names and table names

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 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.

SIMULATION

You have a database that includes the following tables. All of the tables are in the Production schema.

You need to create a query that returns a list of product names for all products in the Beverages category.

Construct the query using the following guidelines:

Use the first letter of the table name as the table alias.

Use two-part column names.

Do not surround object names with square brackets.

Do not use implicit joins.

Do not use variables.

Use single quotes to surround literal values.

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 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. You may check syntax as many times as needed.