Term
Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?
A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; SELECT ProductCode, Price, Price * 1.05 FROM Products |
|
Definition
Answer: A
A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) |
|
|
Term
You develop a Microsoft SQL Server 2012 database.You need to create a batch process that meets 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. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function |
|
Definition
Answer : C
C. Table-valued user-defined function |
|
|
Term
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.You need to ensure that the following requirements are met:Future modifications to the table definition will not affect the applications’ ability to access data.The new object can accommodate data retrieval and data modification.You need to achieve this goal by using the minimum amount of changes to the existing applications.What should you create for each application?
A. views
B. table partitions
C. table-valued functions
D. stored procedures |
|
Definition
|
|
Term
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, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID |
|
Definition
Answer : D
D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID |
|
|
Term
You develop a database for a travel application. You need to design tables and other database objects.You create the Airline_Schedules table.You need to store the departure and arrival dates and times of flights along with time zone information.What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function. |
|
Definition
Answer : I
I. Use the DATETIMEOFFSET data type. |
|
|
Term
You develop a Microsoft SQL Server 2012 server database that supports an 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.Which Transact-SQL statement should you use?
A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
B. ALTER TABLE Inventory ADD ItemsInStore – ItemsInWarehouse = TotalItemss
C. ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse); |
|
Definition
Answer: A
A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse |
|
|
Term
You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function. |
|
Definition
Answer : F
F. Use the VARBINARY data type. |
|
|
Term
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role. |
|
Definition
Answer: A
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role. |
|
|
Term
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?
A. USE Database2
B. EXECUTE AS OWNER
C. USE Database1
D. EXECUTE AS CALLER |
|
Definition
Answer: B
B. EXECUTE AS OWNER |
|
|
Term
You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster) |
|
Definition
Answer: A & B
A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) |
|
|
Term
You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?
A. Float
B. Money
C. Smallmoney
D. Numeric |
|
Definition
|
|
Term
You develop a Microsoft SQL Server 2012 server database that supports an 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. Which Transact-SQL statement should you use?
A. ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse
B. ALTER TABLE InventoryADD ItemsInStore – ItemsInWarehouse = TotalItems
C. ALTER TABLE InventoryADD TotalItems = ItemsInStore + ItemsInWarehouse
D. ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse); |
|
Definition
Answer: A
A. ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse |
|
|
Term
You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.You want to run an execution plan against the query that will provide detailed information on missing indexes.How would you accomplish this task?
A. You should make use of the READPAST hint in the queries.
B. You should make use of the READCOMMITTED hint in the queries.
C. You should make use of the SET SHOWPLAN_XML ON statement in the query.
D. You should make use of the SET STATISTICS XML ON statement in the query.
E. You should make use of the SET XACT_ABORT OFF statement in the query.
F. You should make use of the SET CONTEXT_INFO statement in the query. |
|
Definition
Answer: C
C. You should make use of the SET SHOWPLAN_XML ON statement in the query. |
|
|
Term
You work as a database administrator at manufacturing company named ABC.com. ABC.com has a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table named Sites that was created using the following Transact-SQL code:CREATE TABLE Sites (SiteID int NOT NULL PRIMARY KEY,Location int NOT NULL,Manager nvarchar(200) NOT NULL,Week smallint NOT NULL,ItemsProduced int NOT NULL )You want to write the Transact-SQL query that returns the number of items produced at each location for each week. In addition, you want the result set to include a column named PrevItemsProduced that holds the number of items produced at each location for the week before.What Transact SQL statement would accomplish this task?
A. SELECT Location, Week, ItemsProduced,LEAD(Items Produced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems Produced FROM Sites
B. SELECT Location, Week, ItemsProduced,FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems Produced FROM Sites
C. SELECT Location, Week, ItemsProduced,LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites
D. SELECT Location, Week, ItemsProduced,LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites
E. SELECT Location, Week, ItemsProduced,CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites |
|
Definition
Answer: C
C. SELECT Location, Week, ItemsProduced,LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites |
|
|
Term
Which of the following can be used to protect the code in a stored procedure?
A. The ENCRYPBYKEY statement.
B. The ENCRYPBYASYMKEY statement.
C. The SET TRUSTWORTHY ON option.
D. The SET XACT_ABORT ON statement.
E. The ENCRYPTBYPASSPHRASE statement.
F. The ENCRYPTBYCERT statement.
G. The SIGNBYASYMKEY statement.
H. The CRYPT_GEN_RANDOM statement. |
|
Definition
Answer: A
A. The ENCRYPBYKEY statement. |
|
|
Term
You work as a database administrator at ABC.com. You are developing a database that will be used by a web site analysis application name ABCWeb1.The ABCWeb1 application must display the date and time each visitor visits a page on a website as well as the date and time they leave that web page. This data needs to be displayed in different date and time formats.How would you accomplish this?
A. You should make use of a scalar user-defined function.
B. You should make use of the SET CONTEXT_INFO statement in the stored procedure.
C. You should make use of the DATETIMEOFFSET data type.
D. You should make use of the FORMAT function.
E. You should make use of the SET FORCEPLAN ON statement in the stored procedure.
F. You should make use of a cursor. |
|
Definition
Answer: D
D. You should make use of the FORMAT function. |
|
|
Term
Which of the following statements regarding SQL Server 2012 objects is TRUE?
A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view.
B. A scalar function can accept an input variable and return a table of results but cannot be used within a view.
C. A table-valued function can accept an input variable and return a table of results but cannot be used within a view.
D. A table-valued type can accept an input variable and return a table of results but cannot be used within a view. |
|
Definition
Answer: A
A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view. |
|
|
Term
You work as a database developer at ABC.com. You are developing a SQL server 2012 database for ABC.com’s e-Commerce application. The application allows ABC.com employees from different regions to execute a store procedure based on their location.The location of the ABC.com employees are determined by an input parameter named @location.You want to develop a process that will execute the correct stored procedure for every ABC.com
employee based on their location.How would you accomplish this?
A. You should make use of a client cursor.
B. You should make use of a static cursor.
C. You should make use of a forward-only cursor.
D. You should make use of a dynamic cursor.
E. You should make use of a keyset cursor. |
|
Definition
Answer: E
E. You should make use of a keyset cursor. |
|
|
Term
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB that has a table named Weekly Sales. The Weekly Sales table records the sales amount for each of ABC.com’s 20 sales representatives.You need to write a Transact-SQL query that ranks the sales representatives by the average sales amount for the past year. You want the sales representatives with the same average sales amount to have the same rank with the subsequent rank being skipped. Which ranking function should you use?
A. The RANK( ) OVER function.
B. The NTILE( ) OVER function
C. The DENSE_RANK( ) OVER function
D. The ROW_NUMBER( ) OVER function
E. The FORMAT function |
|
Definition
Answer: C
C. The DENSE_RANK( ) OVER function |
|
|
Term
Which of the following datatypes has a fixed precision and a scale of six digits?
A. Double
B. Money
C. Int
D. Numeric
E. SmallInt
F. VarInt
G. Float |
|
Definition
|
|
Term
| 1. What are the mathematical branches that the relational model is based on? |
|
Definition
| 1. Set theory and predicate logic. |
|
|
Term
| 2. What is the difference between T-SQL and SQL? |
|
Definition
2. SQL is standard; T-SQL is the dialect of and extension to SQL that Microsoft
implements in its RDBMS—SQL Server. |
|
|
Term
| 1. Name two aspects in which T-SQL deviates from the relational model. |
|
Definition
1. A relation has a body with a distinct set of tuples. A table doesn’t have to have
a key. T-SQL allows referring to ordinal positions of columns in the ORDER BY
clause. |
|
|
Term
2. Explain how you can address the two items in question 1 and use T-SQL in a
relational way. |
|
Definition
2. Define a key in every table. Refer to attribute names—not their ordinal
positions—in the ORDER BY clause. |
|
|
Term
1. Why is it important to use standard SQL code when possible and know what is standard
and what isn’t? (Choose all that apply.)
A. It is not important to code using standard SQL.
B. Standard SQL code is more portable between platforms.
C. Standard SQL code is more efficient.
D. Knowing what standard SQL code is makes your knowledge more portable. |
|
Definition
1. Correct Answers: B and D
A. Incorrect: It is important to use standard code.
B. Correct: Use of standard code makes it easier to port code between platforms
because fewer revisions are required.
C. Incorrect: There’s no assurance that standard code will be more efficient.
D. Correct: When using standard code, you can adapt to a new environment more
easily because standard code elements look similar in the different platforms. |
|
|
