MCSA 70-761 SQL Server 2016 练习题搬运

原网站链接：http://www.accelerated-ideas.com/aiMExamsChoose.aspx#mcse

为了方便下次看的时候一眼就理解，顺手加上了粗略翻译。

1. Your team is developing a database for a new online travel application. You need to design tables and other database objects to support the application. One particular table called Airline_Schedules needs to store the departure and arrival dates and times of flights along with time zone information.

What should you do?

你的团队正在开发一个旅游应用的数据库，你需要设计一些数据库和表之类的东西。其中有个特殊的表叫Airline_Schedules，需要往里面保存飞机出发和到达的日期+时间+时区信息，你咋办？

A. Use the CAST function
B.Use a user-defined table type
C.Use the DATETIME2 data type
D.Use the DATETIMEOFFSET data type

答：这里选D，因为DATETIMEOFFSET数据格式可以保存时区信息。

 

2. You've been told to execute the following SQL command to create a new table called employees. The id column of the new table will be an auto increment column but what do the 2 numbers after the IDENTITY clause mean?
你被要求运行下面的sql命令来创建一个雇员表，id列自动递增，那么代码中IDENTITY后面括号中的两个数字分别代表什么？
CREATE TABLE new_employees ( id_num int IDENTITY(100,10), fname varchar (20), minit char(1), lname varchar(30) )

A.seed, increment
B.increment, seed
C.max value, seed
D.max value, increment

答：A，初始值，递增量。

 

3. Can you combine rowstore and columnstore on the same table?

在同一个表中，行存储和列存储可以结合使用吗？

A.Yes

B.NO

答：A，Beginning with SQL Server 2016, you can create an updatable nonclustered columnstore index on a rowstore table. The columnstore index stores a copy of the chosen columns so you do need extra space for this but it will be compressed on average by 10x. By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

从SQL Server 2016开始，您可以在rowstore表上创建一个可更新的非聚集的columnstore索引。columnstore索引存储所选列的一个副本，因此确实需要额外的空间，但它平均会被压缩10倍。通过这样做，您可以同时对columnstore索引和rowstore索引上的事务运行分析。当rowstore表中的数据发生更改时，列存储将被更新，因此两个索引将针对相同的数据工作。

 

4. As part of a new HR project you're creating several stored procedures that will add logging information to the logs table. This logging information is very detailed and should contain carriage returns to make paragraphs more readable. How can you add carriage returns to text when inserting into a table?

在一个HR项目中你需要新建一些存储过程来向日志表中添加日志信息，这个日志很详细，因此你需要插入一些回车符来增加可读性，如何在向表插入时将回车添加到文本?

A.Use CHAR
B.Use CASE
C.Use COS
D.Use TEXTPTR

答：A，You can use CHAR(13) to add carriage returns. The CHAR function converts an int ASCII code to a character. 

您可以使用CHAR(13)来添加回车。CHAR函数将int ASCII码转换为字符。

 

5. You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. Which clause rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output?

你可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。哪个可以将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合？

A.PIVOT
B.UNPIVOT

答：A，感觉这个题难度主要在于读懂英文题意，选答案倒是不难。。。解释：可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

 

6. A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. Which of the following is TRUE regarding INSTEAD OF triggers?

A.INSTEAD OF triggers fire in place of the triggering action and before constraints are processed
B.If the constraints are violated, the AFTER trigger is not executed.
C.If there are AFTER triggers on the table, they will fire after constraint processing.
D.All of these

答：D。

 

7.As part of a global e-commerce business you are developing a Microsoft SQL Server database that supports the company's online website. The application contains a table that has the following definition:

CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ProductsInStore int NOT NULL,
ProductsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ProductsInStore and ProductsInWarehouse values for each row. Which T-SQL statement should you use?

A.ALTER TABLE Inventory
ADD ProductsInStore - ProductsInWarehouse = TotalProducts
B.ALTER TABLE Inventory
ADD TotalProducts AS ProductsInStore + ProductsInWarehouse
C.ALTER TABLE Inventory
ADD TotalProducts AS SUM(ProductsInStore, ProductslnWarehouse);
D.ALTER TABLE Inventory
ADD TotalProducts = ProductsInStore + ProductsInWarehouse

答：B.

 

8. As part of a new enterprise project, you're designing a new table to store financial transactions. This table could eventually store millions of rows and so storage space is very important. One of the columns in the table will store either a 1 or 0 value. Which data type would be most appropriate?

A.tinyint
B.float
C.numeric
D.bit

答：A

 

9. You are developing a new SQL Server database and need to create a batch process that satisfies the following requirements: Returns a result set based on supplied parameters. Enables the returned result set to perform a join with a table. Which object should you use?

A.Table-valued user-defined function
B.Inline user-defined function
C.Stored procedure
D.Scalar user-defined function

答：A。Inline user-defined functions are a subset of user-defined functions that return a table data type. Inline functions can be used to achieve the functionality of parameterized views.

 

10. You're designing a new query that will return all of the medical records from the patients table. This could run into millions of rows so you need to find a way to limit the results to include only the top 100 most recent records. What is the best way to achieve this?

A.Use select TOP and GROUP BY date
B.Use ROWCOUNT and GROUP BY date
C.Use ROWCOUNT and ORDER BY date
D.Use select TOP and ORDER BY date

答：D。

 

11. What needs correcting in the statement below to make it a valid UPDATE statement?

UPDATE titles SET t.ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

A.You cannot use the FROM keyword in an UPDATE statement
B.There is nothing wrong with this SQL statement
C.You cannot use sub query in an UPDATE statement
D.Remove the t from t.ytd_sales

答：D。

 

12. You are developing a Microsoft SQL Server database that supports a web application. The application contains a table that has the following definition:

CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

A.ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse
B.ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
C.ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
D.ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED

答：B。PERSISTED - Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise.

 

13. You're creating a new query that will select rows from a products tables. The query works out the count of products within each category by grouping on the category, filtering by categories that contain more than one product and then sorting the results in category order. In which order should these clauses be used in the query?

A.GROUP BY, HAVING, ORDER BY
B.HAVING, GROUP BY, ORDER BY
C.ORDER BY, GROUP BY, HAVING
D.GROUP BY, ORDER BY, HAVING

答：A。

 

14. Which index type gives high performance gains for analytic queries that scan large amounts of data, especially on large tables?

A. Rowstore
B. Columnstore

答：B。Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, since they tend to require full table scans rather than table seeks.

 

15. A new web application uses a database called Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made online. There is a non-clustered index on the PurchaseTime column. An internal management team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which T-SQL query should you use?

A.SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B.SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
C.SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
D.SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()

答：B。我也做过类似的，都是按照A做的，因为都说当天了。。怪我不够严谨。

 

16. What is missing from the T-SQL statement below for creating and using a cursor?

DECLARE mycursor CURSOR FOR SELECT * FROM Vendor FETCH NEXT FROM mycursor;

A.KEYSET
B.OPEN
C.STATIC
D.SCROLL

答：B。

 

17. Your team have created a Microsoft SQL Server 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 LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
C.SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
D.SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID

答：B。这个简单，在公司一天做一百次了都。

 

18. You want to use a query to discover all of the database names from your server. Which of the following queries would achieve the desired result?

A.SELECT name FROM sys.databases
B.SELECT name FROM sys.database_files
C.SELECT name FROM sys.master_files
D.None of these

答：A。

 

考的主要是基础知识的详细内容，有些点甚至很细，重点在于对基础的全面了解程度而非对某些重点知识的深度。并不考想象中的复杂的查询，sp，或者function。

所以感觉要么有耐力把圣经读完，要么题库多背几遍。