Skip Navigation Links
 

Microsoft - 70-761: Querying Data with Transact-SQL

Sample Questions

Question: 178
Measured Skill: Query data with advanced Transact-SQL components (30–35%)

You are a database developer for a company. You have a date related query that would benefit from an indexed view.

You need to create the indexed view.

Which two Transact-SQL functions can you use?

(Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.)

ADATEADD
B AT TIME ZONE
C GETUTCDATE
D DATEDIFF

Correct answer: A, D

Explanation:

The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions can participate in deterministic expressions. For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed.

Reference: Create Indexed Views

Question: 179
Measured Skill: Manage data with Transact-SQL (40–45%)

You are a database developer for a company. You are developing a database to track employee progress relative to training goals.

You run the following Transact-SQL statements:

CREATE TABLE [dbo].[Employees](
[EmployeeID] int IDENTITY(1,1) NOT NULL,
[Name] varchar(150) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (EmployeeID ASC)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CoursesTaken](
[CourseID] int NOT NULL,
[EmployeeID] int NOT NULL,
[CourseTakenOn] date NULL,
CONSTRAINT [PK_CoursesTaken] PRIMARY KEY CLUSTERED (CourseID ASC, EmployeeID ASC)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Courses](
[CourseID] int IDENTITY(1,1) NOT NULL,
[Course] varchar(50) NULL,
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED (CourseID ASC)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


You must build a report that shows all Employees and the courses that they have taken.

Employees that have not taken training courses must still appear in the report. The report must display NULL in the course column for these employees.

You need to create a query for the report.

Which Transact-SQL statement should you use?

ASELECT e.Name, c.Course
FROM dbo.Courses c
JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID
INNER JOIN dbo.Employees e ON ct.EmployeeID = e.EmployeeID
B SELECT e.Name, c.Course
FROM dbo.Courses c
JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID
JOIN dbo.Employees e ON ct.EmployeeID = e.EmployeeID
C SELECT e.Name, c.Course
FROM dbo.Courses c
JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID
LEFT JOIN dbo.Employees e ON ct.EmployeeID = e.EmployeeID
D SELECT e.Name, c.Course
FROM dbo.Courses c
JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID
RIGHT JOIN dbo.Employees e ON ct.EmployeeID = e.EmployeeID

Correct answer: D

Explanation:

The answers A and B are identical. An INNER JOIN (short: JOIN) returns only those rows that have a matching value in both tables.

A LEFT JOIN or a RIGHT JOIN returns all rows of the outer table and only the rows of the linked table that contain a matching value.

Reference: Joins (SQL Server)

Question: 180
Measured Skill: Query data with advanced Transact-SQL components (30–35%)

You create a table to store sales information for an online sales application by running the following Transact-SQL statement:

CREATE TABLE Sales(
SalesOrderID int NOT NULL,
OrderDate datetime NOT NULL,
Total money NULL
)


You have a historical report that summarizes the sales for each quarter and year. The query that generated the data for the report is no longer available. A representative report contains the following data:



You need to recreate the query for the report.

How should you complete the Transact-SQL statement?

(To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

AP1: MAX(DATEPART(quarter, OrderDate))
P2: MAX(DATEPART(YEAR, OrderDate))
P3: DATEPART(quarter, OrderDate)
B P1: CASE WHEN DATEPART(quarter, OrderDate)=NULL THEN 1 ELSE 0 END
P2: CASE WHEN DATEPART(YEAR, OrderDate)=NULL THEN 1 ELSE 0 END
P3: DATEPART(quarter, OrderDate) WITH ROLLUP
C P1: DATEPART(quarter, OrderDate) % 1
P2: DATEPART(YEAR, OrderDate) % 1
P3: DATEPART(quarter, OrderDate), YEAR(OrderDate)
D P1: GROUPING(DATEPART(quarter, OrderDate))
P2: GROUPING(YEAR(OrderDate))
P3: DATEPART(quarter, OrderDate), YEAR(OrderDate) WITH ROLLUP

Correct answer: D

Explanation:

ROLLUP can be used to generate a subtotal rows and a grand total row for aggregate rows.

GROUPING() indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT
AP1: @List nvarchar(MAX) = ''
P2: @List=Product + ',' + @List
B P1: @List nvarchar(MAX) = ''
P2: @List=@List + ',' + Product
C P1: @List nvarchar(MAX)
P2: @List=Product + ',' + @List
D P1: @List Table
P2: @List=@List + ',' + Product
E P1: @List Table
P2: @List=Product + ',' + @List
F P1: @List COALESCE(@List, ',', Product)
P2: @List=@List + ',' + Product

Correct answer: B

Explanation:

Use the following code to understand the solution:

CREATE TABLE Bug (
ID uniqueidentifier NOT NULL,
Product nvarchar(255) NOT NULL,
Description nvarchar(max) NOT NULL,
DateCreated datetime NOT NULL,
ReportingUser varchar(50) NULL
)

INSERT INTO Bug VALUES (NEWID(), 'Prod1', 'ABC', GetDate(), 'User1')
INSERT INTO Bug VALUES (NEWID(), 'Prod2', 'ABC', GetDate(), 'User1')
INSERT INTO Bug VALUES (NEWID(), 'Prod3', 'ABC', GetDate(), 'User1')
INSERT INTO Bug VALUES (NEWID(), 'Prod4', 'ABC', GetDate(), 'User1')

DECLARE @List nvarchar(MAX) = ''
SELECT @List=@List + ',' + Product
FROM Bug WHERE ReportingUser = 'User1'
SELECT @List

Question: 182
Measured Skill: Query data with advanced Transact-SQL components (30–35%)

You are an administrator for a company. You run the following Transact-SQL statements:

CREATE TABLE CourseParticipants
(
CourseID int NOT NULL,
CourseDate date NOT NULL,
LocationDescription varchar(100) NOT NULL,
NumParticipants int NOT NULL
)


You need to create a query that returns the total number of attendees for each combination of CourseID, CourseDate, and the following locations: Lisbon, London, and Seattle.

The result set should resemble the following:



Which Transact-SQL code segment should you run?

ASELECT * FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle))
B SELECT * FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle)) as PVTTable
C SELECT * FROM CourseParticipants UNPIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle)
D SELECT * FROM CourseParticipants UNPIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle) AS PVTTable

Correct answer: B

Explanation:

Use the following code to understand the solution:

-- Create the table
CREATE TABLE CourseParticipants
(
CourseID int NOT NULL,
CourseDate date NOT NULL,
LocationDescription varchar(100) NOT NULL,
NumParticipants int NOT NULL
)

-- Insert sample data
INSERT INTO CourseParticipants VALUES (1, '2018-02-01', 'Seattle', 15)
INSERT INTO CourseParticipants VALUES (2, '2018-02-01', 'Lisbon', 33)
INSERT INTO CourseParticipants VALUES (1, '2018-02-02', 'London', 20)
INSERT INTO CourseParticipants VALUES (1, '2018-02-03', 'Lisbon', 15)
INSERT INTO CourseParticipants VALUES (2, '2018-02-03', 'London', 15)

-- Run the query
SELECT * FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle)) as PVTTable



 
Tags: exam, examcollection, exam simulation, exam questions, questions & answers, training course, study guide, vce, braindumps, practice test
 
 

© Copyright 2014 - 2019 by cert2brain.com