70-461 무료 덤프문제 온라인 액세스

You develop a Microsoft SQL Server database for an educational institution. The database contains tables named ExamEvaluation and ProjectEvaluation, which are used to track student scores. The tables are created by using the following code:

Students are evaluated based on the following rules:
* Each student will complete a maximum of one exam and one project.
* A student must complete at least one exam.
* A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.
* A single project consists of Section A and Section B, where the student is evaluated by adding 60 percent of Section A and 40 percent of Section B.
* At any given time, a student may have completed only an exam, or both an exam and a project.
You want to create a summary report of student evaluations.
You need to write a query that will return evaluation results for students who have completed any form of evaluation, grouped by the year of undertaling. A sample result set is shown in the following table.

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

정답:

Explanation:
Box 1: SELECT..
The outer select statement
Box 2: (SELECT ..AVG(SectionScore)
Need the average score: A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.
Box 3: LEFT OUTER JOIN
There might be no finished Project
Box 4: ON..
Box 5: (SELECT ..SUM
Need the SUM function to handle addition of NULL values.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.
You plan to build a single process for each employee that will execute the stored procedure based on the country of residence.
Which approach should you use?

• A.Cursor
• B.Trigger
• C.A recursive stored procedure
• D.An UPDATE statement that includes CASE
• E.The foreach SQLCLR statement

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
* Always starts with 1.
* Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

• A.CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  CYCLE
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence
• B.CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence
• C.CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 0
  MAXVALUE 100
  CYCLE
  UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence
• D.CREATE SEQUENCE CustomerSequence AS int
  START WITH 0
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence

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

You need to create a query for a report. The query must meet the following requirements:
* NOT use object delimiters.
* Use the first initial of the table as an alias.
* Return the most recent order date for each customer.
* Retrieve the last name of the person who placed the order.
The solution must support the ANSI SQL-99 standard.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A.SELECT C .LastName,
  MAX(O.OrderDate) AS MostRecentOrderDate
  FROM Customers AS C INNER JOIN Orders AS O
  ON C .CustomerID
  GROUP BY C .Lastname
  ORDER BY MAX (O.OrderDate) DESC
• B.SELECT C .LastName,
  MAX(O.OrderDate) AS MostRecentOrderDate
  FROM Customers AS C INNER JOIN Orders AS O
  ON C .CustomerID=O.CustomerID
  GROUP BY C .Lastname
  ORDER BY MAX (O.OrderDate) DESC

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use?
(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

정답: