[2026] Microsoft Querying Microsoft SQL Server 2012/2014

문제1

You create a view based on the following statement:

You grant the Select permission to User1 for this view.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

정답:

문제2

문제3

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the following requirements are met:
* Students must be ranked based on their average marks.
* If one or more students have the same average, the same rank must be given to these students.
* Ranks must be sequential without gaps in between.
Which Transact-SQL query should you use?

A. Option C

B. Option H

C. Option E

D. Option G

E. Option D

F. Option F

G. Option A

H. Option B

정답: H

설명: (Fast2test 회원만 볼 수 있음)

문제4

문제5

You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
* Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
* Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?

A. SELECT CustomerName, OrderDate
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

B. SELECT CustomerName, OrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

C. SELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

D. SELECT CustomerName, OrderDate
FROM Customers
CROSS JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

정답: C

설명: (Fast2test 회원만 볼 수 있음)

문제6

You are a database developer of a Microsoft SQL Server server that hosts a business-critical high-activity OLTP system.
The database contains a table named Customer that has more than one million records. The table has the following defintion:

Customer data is updates in an external system. These changes are copied into a staging table on a weekly basis. This table has the following definition.

You need to write a Transact-SQL statement that will update the records in the Customer table by using the values in the Customer_Staging table that has matching CustomerID values. You must ensure that only one record is updated at a time.
Which Transact-SQL query should you run?

A. Option

B. Option

C. Option

D. Option

정답: A

설명: (Fast2test 회원만 볼 수 있음)

문제7

You have a SQL Server database that contains all of the customer data for your company.
You need to extract a random 1,000 row sample from a table Customers.
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.

정답:

TABLESAMPLE SYSTEM (1000 ROWS)
Explanation:
Update line 3 to get the following:
SELECT *
FROM Customers
TABLESAMPLE SYSTEM (1000 ROWS)
The TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number orPERCENT of rows.
Syntax: TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
References: https://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx

문제8

You support a database structure shown in the exhibit. (Click the Exhibit button.)

You need to write a query that displays the following details:
* Total sales made by sales people, year, city, and country
* Sub totals only at the city level and country level
* A grand total of the sales amount
Which Transact-SQL query should you use?

A. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

B. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))

C. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

D. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

정답: D

설명: (Fast2test 회원만 볼 수 있음)

문제9

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

B. PIVOT

C. CROSS APPLY

D. OUTER JOIN

정답: C

설명: (Fast2test 회원만 볼 수 있음)

문제10

A database contains tables as shown in the exhibit. (Click the Exhibit button.) Products that are discontinued are moved from the Products table to the DiscontinuedProducts table. Any orders for discontinued products are removed from the Orders table.
You write the following SELECT statement to return all the discontinued products:
SELECT ProductId FROM DiscontinuedProducts
You need to extend the SELECT statement to include products who do not have any orders.
Which four 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.
Exhibit

정답:

문제11

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
* Status information must be logged to a status table.
* If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?

A. Table-valued user-defined function

B. Inline user-defined function

C. Scalar user-defined function

D. Stored procedure

정답: D

설명: (Fast2test 회원만 볼 수 있음)

문제12

You have a database named Sales that contains the tables sworn in the exhibit. (Click the Exhibit button.)

You need to create a query for a report. The query must meet the following requirements:
* Return the last name of the customer who placed the order.
* Return the most recent order date for each customer.
* Group the results by CustomerID.
* Display the most recent OrderDate first.
The solution must support the ANSI SQL-99 standard and must not use table or column aliases.
You need to create a query for a report. The query must meet the following requirements:
* Return the last name of the customer who placed the order.
* Return the most recent order date for each customer.
* Group the results by CustomerID.
* Display the most recent OrderDate first.
The solution must support the ANSI SQL-99 standard and must not use table or column aliases.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment 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.

A. 1. SELECT LastName,
2 MAX(OrderDate) AS MostRecentOrderDate
3 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
4 GROUP BY CustomerID
5 ORDER BY OrderDate DESC
On line 3 add Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID On line 4 add CustomerID On line 5 add OrderDate DESC

B. 1. SELECT LastName,
2 MAX(OrderDate)
3 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
4 GROUP BY CustomerID
5 ORDER BY OrderDate DESC
On line 3 add Customers INNER JOIN Orders ON
On line 4 add CustomerID
On line 5 add OrderDate DESC

정답: A

설명: (Fast2test 회원만 볼 수 있음)

문제13

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 PrevProfit FROM Profits

B. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

C. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

D. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

정답: C

설명: (Fast2test 회원만 볼 수 있음)

문제14

You have a database that contains the following related tables:

You create a view named OrderSummary by using the following Transact-SQL statement:

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

정답:

Explanation:
The SalesOrderID column is used in a join statement and cannot be updated.
The Status column is used can be updated.
The SubTotal column is an aggregate column and cannot be updated.

Microsoft Querying Microsoft SQL Server 2012/2014 - 70-461무료 덤프문제 풀어보기

우리와 연락하기

유용한 링크

최신 업데이트