Log-in   Register  

Skip Navigation Links
 

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

Sample Questions

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

You have a database that tracks customer complaints.

The database contains a table named Complaints that includes the following columns:



You need to create a query that lists complaints about defective products.

The report must include complaints where the exact phrase “defective product” occurs, as well as complaints where similar phrases occur.

Which Transact-SQL statement should you run?

A SELECT ComplaintID, CustomerTranscript FROM Complaints
INNER JOIN FEETEXTTABLE (Complaints, CustomerTRanscript, 'defective product') as Matches
ON Complaints.ComplaintID = Matches[KEY]
B SELECT ComplaintID, CustomerTranscript FROM Complaints
INNER JOIN CONTAINSTABLE (Complaints, CustomerTRanskript, '%defective% %product%') as Matches
ON Complaints.ComplaintID = Matches[KEY]
C SELECT ComplaintID, CustomerTranscript FROM Complaints
WHERE CONTAINS(CustomerTranscript, 'defective product')
D SELECT ComplaintID, CustomerTranscript FROM Complaints
WHERE CONTAINS(CustomerTranscript, 'defective') 
AND CONTAINS(CustomerTranscript, 'product')

Correct answer: A

Explanation:

Write full-text queries by using the predicates CONTAINS and FREETEXT and the rowset-valued functions CONTAINSTABLE and FREETEXTTABLE with a SELECT statement.

To match the meaning, but not the exact wording, use FREETEXT and FREETEXTTABLE.

The following article shows examples of using FREETEXTTABLE.

Query with Full-Text Search

Question: 191
Measured Skill: Program databases by using Transact-SQL (25–30%)

You are developing a training management solution. You run the following Transact-SQL statement:

CREATE TABLE Courses(
CourseID int IDENTITY(1,1) NOT NULL,
Course varchar(50) NULL,
TrainerEval decimal(18,0) NULL
);

You need to create an indexed view to return all the courses where the value of TrainerEval is equal to or higher than 8.5.

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 answer area and arrange them in the correct order.)

www.cert2brain.com

ASequence: 5, 6, 1
B Sequence: 3, 2, 4
C Sequence: 5, 6, 4
D Sequence: 3, 6, 1

Correct answer: C

Explanation:

Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced just like tables in SQL statements. The result set of a standard view is not stored permanently in the database.

A view must meet the following requirements before you can create a clustered index on it:

  • The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

  • The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

  • The view must not reference any other views, only base tables.

  • All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

  • The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

  • User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

  • Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

  • All functions referenced by expressions in the view must be deterministic. The IsDeterministicproperty of the OBJECTPROPERTY function reports whether a user-defined function is deterministic. 

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name.

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

You are developing a training management application. You run the following Transact-SQL statement:

CREATE TABLE Evaluations 
(
EvaluationID int NOT NULL,
EmployeeID int NULL,
CourseID int NULL,
Eval int NOT NULL
);

You must create a report that returns course identifiers and the average evaluation score for each course. The result set must include only one score for each employee for each course.

You need to create a query that returns the required data.

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

A SELECT CourseID, AVG(Eval)
FROM Evaluations
GROUP BY Eval
B SELECT CourseID, AVG(Eval)
FROM Evaluations
GROUP BY CourseID
C SELECT CourseID, AVG(DISTINCT Eval)
FROM Evaluations
GROUP BY Eval
D SELECT CourseID, DISTINCT(Eval)
FROM Evaluations
GROUP BY DISTINCT AVG(Eval)
E SELECT CourseID, DISTINCT(Eval)
FROM Evaluations
GROUP BY CourseID
F SELECT CourseID, SUM(Eval)
FROM Evaluations
GROUP BY DISTINCT CourseID

Correct answer: B

Explanation:

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT, aggregate functions ignore null values.

All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values.

The aggregate function AVG () determines the average value of the values of the specified column. The result set must be grouped by the CourseID to show the average rating per course.

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

You are a database administrator for an online retail store. You create a table to track orders by running the following Transact-SQL statement:

CREATE TABLE Order (
OrderID int NOT NULL,
Amount money NOT NULL,
CategoryID int NOT NULL,
OrderDate datetime NOT NULL
)

You need to create a report that includes the following information:
  • Total sales for each year
  • Total sales for each category
  • Total sales for each category per year
How should you complete the Transact-SQL statement?

(To answer, drag the appropriate Transact-SQL segment to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

AP1: CUBE
P2: WITH ROLLUP
B P1: PIVOT
P2: CUBE
C P1: ROLLUP
P2: GROUP BY
D P1: GROUP BY
P2: CUBE
E P1: PARTITION BY
P2: ROLLUP
F P1: WITH ROLLUP
P2: PIVOT

Correct answer: D

Explanation:

The WITH CUBE clause causes the query to compute all possible totals of the aggregated column.

References:

SELECT - GROUP BY- Transact-SQL

Aggregation WITH ROLLUP

Aggregation WITH CUBE

Question: 194
Measured Skill: Program databases by using Transact-SQL (25–30%)

You are creating a database solution to track sales achievements of your training courses.

You run the following statements:

CREATE TABLE Courses (
CourseID int IDENTITY(1,1) NOT NULL,
Course varchar(50) NULL,
TrainerEvalScore decimal(18,0) NULL
)

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

You plan to add courses to a table named HighlightedCourses. You must add courses that have been delivered to more than 100 participants only.

If the total number of participants for a course is lower than 100, the course must not be added to the HighlightedCourses table. In addition, an error message must be displayed and remaining Transact-SQL code must not run.

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: IF (@ROWCOUNT > 100)
P2: BREAK
B P1: IF (@ROWCOUNT > 100)
P2: RAISERROR('Course is not admissible.',16,0)
C P1: IF (@ROWCOUNT > 100)
P2: THROW 50000,'Course is not admissible.',0
D P1: IF (@TotalParticipants < 100)
P2: BREAK
E P1: IF (@TotalParticipants < 100)
P2: RAISERROR('Course is not admissible.',16,0)
F P1: IF (@TotalParticipants < 100)
P2: THROW 50000,'Course is not admissible.',0

Correct answer: F

Explanation:

THROW raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct. If a TRY...CATCH construct is not available, the statement batch is terminated.

Reference: THROW (Transact-SQL)



 
Tags: exam, examcollection, exam simulation, exam questions, questions & answers, training course, study guide, vce, braindumps, practice test
 
 
© Copyright 2014 - 2024 by cert2brain.com