#1. 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?
Stored procedure
Table-valued user-defined function
Scalar user-defined function
#2. 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?
#3. 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?
views
table-valued functions
table partitions
stored procedures
#4. 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?
SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
#5. You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?
CREATE TABLE DocumentStore ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO
CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO
CREATE TABLE DocumentStore AS FileTable
CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO