sql-certification.aspx

Exam 70-461

Querying Microsoft SQL Server 2012

Rs: 3,350.00 INR*

Create database objects (20–25%)

Create and alter tables using T-SQL syntax (simple statements) Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE Create and alter views (simple statements) Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP Design views Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications Create and modify constraints (simple statements) Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints Create and alter DML triggers Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Work with data (25–30%)

Query data by using SELECT statements Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce Implement sub-queries Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement Implement data types Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns Implement aggregate queries New analytic functions; grouping sets; spatial aggregates; apply ranking functions Query and manage XML data Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing

Modify data (20–25%)

Create and alter stored procedures (simple statements) Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL Modify data by using INSERT, UPDATE and DELETE statements Given a set of code with defaults, constraints and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement Combine datasets Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements Work with functions Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Troubleshoot and optimise (25–30%)

Optimise queries Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterised queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in Manage transactions Mark a transaction; understand begin tran, commit and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount Evaluate the use of row-based operations vs. set-based operations When to use cursors; impact of scalar UDFs; combine multiple DML operations Implement error handling Implement try/catch/throw; use set-based rather than row-based logic; transaction management
–> Exam Highlights:
– There were total 54 Questions
– Total time to attempt the paper was 3 Hrs.
– Total Marks: 1000
– Passing Marks: 700
– The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.

–> The Exam is divided into 4 modules:

1. Create Database Objects | 24%
– CREATE/ALTER/DROP Database, [link], [video]
– CREATE/ALTER/DROP Table, [link], [video]
– ADD/DROP/Rename table’s Columns, [video]
– Data Types [video]:
– – (XML, DATETIME, SPATIAL, VARCHAR)
– Constraints [link], [video]
– – (Primary Key, Unique Key, Foreign Key, NOT NULL, Check, Default)
– Computed Columns [video]:
– Views
– Indexed Views (SCHEMABINDING, COUNT_BIG(*), CLUSTERED INDEX)
– Stored Procedures, [link].
– DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint
– TRIGGER for VIEWS (INSTEAD OF)
– UDF (Functions), [link].
– SP vs UDF, [link].

2. Work with Data | 27%
– New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].
– FETCH-OFFSET, [link].
– SEQUENCE, [link].
– Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link], [video].
– OVER() Window Function, [link]
– JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), [link].
– APPLY Operators (CROSS APPLY vs OUTER APPLY), [link].
– CTE and Sub-Queries, [link].
– PIVOT, [link].
– ROLLUP, CUBE & GROUPING SETS, [link].
– Dynamic SQL
– ANY, SOME, ALL
– CASE vs ISNULL vs COALESCE, [link].
– FOR XML RAW/AUTO/PATH [ELEMENTS], [link].
– Implementing XML Schemas and Handling of XML data

3. Modify Data | 24%
– Stored Procedure (with EXECUTE AS, RECOMPILE)
– MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT), [link].
– EXCEPT vs INTERSECT
– UNION vs UNION ALL, [link].
– SCALAR vs TABLE Valued Functions.
– Use of APPLY with UDFs, [link].
– VARCHAR(MAX) and .WRITE(), [link].

4. Troubleshoot & Optimize | 25%
– Using Statistics
– SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted), [link].
– TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT), [link].
– ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
– TRY/CATCH, [link].
– RAISE vs THROW [link]
– CURSORS (Row-Based) vs SET Based Approach
– Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)
– Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))

–> You can visit following Study materials to prepare for this Exam:
Microsoft Link for this Certification:
Books on AMAZON.com:
– Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012
– Microsoft SQL Server 2012 T-SQL Fundamentals
https://www.microsoft.com/learning/en-us/exam-70-461.aspx

MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Practice Questions:

1.You've created a simple select statement that returns the customers first name and last name. This statement needs changing so that it only includes the customers first initial and their last name in the result set. There is no column for initial. Which function could you use to get the initial?

SUBSTRING

STUFF

REPLACE

RTRIM

2.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;

KEYSET

OPEN

STATIC

SCROLL

3.DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of Microsoft SQL Server constraints, defaults, and rules. Which of the following is FALSE regarding DML triggers?

A DML trigger cannot reference objects outside of the current database

You can create a DML trigger only in the current database

A DML trigger cannot be created on a temporary or system table

The WRITETEXT statement does not cause the INSERT or UPDATE triggers to fire.

4.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?

9.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?

All of these

INSTEAD OF triggers fire in place of the triggering action and before constraints are processed

If there are AFTER triggers on the table, they will fire after constraint processing.

If the constraints are violated, the AFTER trigger is not executed.

10.Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the ___ clause generates control-breaks and subtotals in the result set.

AVG

COMPUTE

None of these

SUM

11.A ___ is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table.

Trigger

Constraint

View

DLL

12.You're designing a new query that will eventually be used by a new software application. It's important that the application knows the id value of the last row that was inserted. Rather than re-run another query just to get the maximum id value, your friend has told you to include an inbuilt function. What might that function be?

SCOPE_IDENTITY

ROWCOUNT

@@ROWCOUNT

@@IDENTITY

Note:

SCOPE_IDENTITY is the best answer. Although you could use @@IDENTITY, you need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

uestion 1 of 15

19.You've created a standard stored procedure that's not a CLR procedure. This procedure has several parameters, some of which are output parameters. Which of the following CANNOT be passed back as an output parameter from a stored procedure?

Text

image

Ntext

Any of these

20.You need to write a new query that will return names from the employee table for people who are not part of the contractors table (the two tables share a unique id, similar to social security or NI number). Your query will use a sub query to check that the employee is not in the contractors table. How might you achieve this?

Use COALESCE

Use EXISTS

Use IS_MEMBER

Use EXP

21.Which of the following is an example of a scalar subquery?

select max(price) from products
select sum(price) from products
select min(price) from products
All of these

Answer:
All of these

Comments:
A scalar subquery is a subquery that returns one value.

22.Which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT OR CAST?

varchar to decimal
decimal to bit
date to varchar
datetime to text

Answer:
datetime to text

Comments:
There are very few conversions which are allowed to the Text data type. They include varchar, char, nchar, nvarchar and ntext. Converting datetime to text is not an allowed conversion using either CAST or CONVERT.

23.You're designing a new SQL Server 2012 query for the HR department. The query will find records from the persons table for people whose name starts with 'Ja'. Which WHERE statement would be the correct choice?

where (name like 'Ja%')

where (name = 'Ja%')

where (name > 'Ja')

where (name like '%Ja%')

24.You're designing a new SQL Server 2012 table that will hold millions of records. The table will be used by a finance application to show historical transactions. The table only has 10 columns but it's vital that the table performs well. How many primary keys should you create for the table for best performance?

25.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?

bit

tinyint

numeric

float

26.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.

Answer: D

27.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.

Answer: A

28.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

Answer: C

29.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.

Answer: C

30.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);

Answer: A

31.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

Answer: D

32.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)

Answer: AB

33.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

Answer: B

34.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.

Answer: A

35.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.

Answer : I

36.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);

Answer: A

37.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.

Answer : I

38.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

Answer : D

39.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

Answer: A

40.Question : 31
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

Answer : C

http://onlinetestcentre.com/simulator/testing.html

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view. What should you do?

A: Create an AFTER UPDATE trigger on the view.

B: Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

C: Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

D: Create an INSTEAD OF UPDATE trigger on the view.

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

A: ENCRYPTION

B: ENCRYPTBYCERT

C: ENCRYPTBYKEY

D: ENCRYPTBYPASSPHRASE

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
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;
SELECT ProductCode, Price, Price * 1.05 FROM Products

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
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

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 ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

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 GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy,
SaleDate)), (Country, 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 CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate
the unique values of the ProductName field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

A: UNPIVOT

B: PIVOT

C: CROSS JOIN

D: CROSS APPLY

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: Create a custom database role that includes the users. Deny Delete permissions on each table in the
Sales schema for the custom database role.

C: Deny Delete permissions on each table in the Sales schema for each user.

D: Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. Which Transact-SQL statement should you use?

A: UPDATE BlogEntry
SET Summary.WRITE(N' This is in a draft stage', 0, 0)
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

B: UPDATE BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM (
SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s
WHERE BlogEntry.Id = s.ID

C: UPDATE BlogEntry
SET Summary = CAST(N' This is in a draft stage' as nvarchar(max))
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

D: UPDATE TOP(10) BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0)

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data
in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

A: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p

B: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders

C: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders

D: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

A: SET XACT_ABORT ON

B: SET ARITHABORT OFF

C: BEGIN

D: SET ARITHABORT ON

E: TRY

F: SET XACT_ABORT OFF

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to invoke a table-valued function for each row returned by a query. Which Transact-SQL operator should you use?

A: PIVOT

B: CROSS APPLY

C: CROSS JOIN

D: UNPIVOT

A: SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1

B: SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks

C: SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1

D: SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

E: SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

F: SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1

G: SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1

H: SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report. You need to display dates and times in several international formats. What should you do
A: Use a user-defined table type.

B: Use the TODATETIMEOFFSET function.

C: Use the VARBINARY data type.

D: Use the DATETIMEOFFSET data type.

E: Use the DATE data type.

F: Use the DATETIME data type.

G: Use the FORMAT function.

H: Use the CAST function.

I: Use an appropriate collation.

J: Use the DATETIME2 data type.

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?
A: SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers. CustomerId = 1
FOR XML RAW

B: SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML AUTO, ELEMENTS

C: SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')

D: SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')

E: SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML AUTO, ELEMENTS

F: SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers=CustomerId = 1
FOR XML RAW, ELEMENTS

G: SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML AUTO

H: SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers. CustomerId = 1
FOR XML AUTO

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query:

INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?
A: TABLOCK

B: UPDLOCK

C: HOLDLOCK

D: ROWLOCK

E: XLOCK

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
CROSS JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

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

C: SELECT CustomerName, CrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

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

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements: Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A: CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS

SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result
GO

B: CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT AS
DECLARE @Result INT SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerVaLue
FROM Customers
WHERE Customer= = @CustomerID RETURNS @Result

C: CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS
SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomerID RETURN
GO

D: DECLARE @CustomerRatIngByCustcmer INT
EXECUTE dbo.GetCustomerRating @CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

E: DECLARE @customerRatingBycustomer INT DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745
, @CustomerRatingSyCustomer

F: EXECUTE dbo.GetCustomerRatIng 1745

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

A: GRANT SELECT ON Purchases.Suppliers TO ContosoUser

B: CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C: CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

D: GRANT DELETE ON Purchases.Suppliers TO ContosoUser

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

A: Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

B: Cover the unique clustered index with a columnstore index.

C: Include a SET FORCEPLAN ON statement before you run the query.

D: Add a HASH hint to the query.

E: Include a SET STATISTICS PROFILE ON statement before you run the query.

F: Add an INCLUDE clause to the index.

G: Add a FORCESCAN hint to the Attach query.

H: Add a FORCESEEK hint to the query.

I: Enable the optimize for ad hoc workloads option.

J: Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

K: Add a LOOP hint to the query.

L: Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M: Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

N: Add a columnstore index to cover the query.

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. 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 SUM(ItemsInStore, ItemsInWarehouse) PERSISTED

C: ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

D: ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?

A: Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

B: Convert the view into an indexed view.

C: Convert the view into a Common Table Expression (CTE).

D: Convert the view into a table-valued function.

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:

<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer A" Country="Australia" />

Which Transact-SQL query should you use?

A: SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS

B: SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')

C: SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId- 1
FOR XML AUTO

D: SELECT Name, Country, Orderld, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS

E: SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW

F: SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

G: SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')

H: SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.
CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS

You generate a daily report according to the following query:

You need to improve the performance of the query. What should you do?

A: Drop the UDF and rewrite the report query as follows:

SELECT c.CustomerName FROM Sales.Customer c WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)

B: Rewrite the report query as follows:

SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.
CustomerID, 90))

Rewrite the UDF as follows:

CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) RETURNS TABLE AS RETURN (
SELECT OrderDate
FROM Sales.SalesOrder
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())

C: Drop the UDF and rewrite the report query as follows:

SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())

D: Drop the UDF and rewrite the report query as follows:

WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

A: READ COMMITTED SNAPSHOT

B: REPEATABLE READ

C: SERIALIZABLE

D: SNAPSHOT

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and
TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

A: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END

B: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END

C: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END

D: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN END
END

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

A: IF (XACT_STATE ( ) ) = 0

B: IF (XACT_STATE ( ) ) = 1

C: IF @@TRANCOUNT = l

D: IF @@TRANCOUNT = 0

A: ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED

B: ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

C: ALTER TABLE Inventory
ADD TotalItems AS ItemslnStore + ItemsInWarehouse

D: ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

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: Smallmoney

B: Numeric

C: Money

D: Float

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view. What should you do?

A: Create an INSTEAD OF UPDATE trigger on the view.

B: Create an AFTER UPDATE trigger on the view.

C: Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

D: Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition:

You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated. Which Transact-SQL query should you use?

A: CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR
EXISTS (SELECT WholeSalePnce FROM inserted)
- - Create Audit Records

B: CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice)
- - Create Audit Records

C: CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice)
- - Create Audit Records

D: CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice)
- - Create Audit Records

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: EXECUTE AS CALLER

D: USE Database1

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements: Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

A: CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS
SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomerID RETURN
GO

B: EXECUTE dbo.GetCustomerRatIng 1745

C: DECLARE @customerRatingBycustomer INT DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745
, @CustomerRatingSyCustomer

D: DECLARE @CustomerRatIngByCustcmer INT
EXECUTE dbo.GetCustomerRating @CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

E: CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT AS
DECLARE @Result INT SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerVaLue
FROM Customers
WHERE Customer= = @CustomerID RETURNS @Result

F: CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS

SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result
GO

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 preceding year. Which Transact-SQL query should you use?

A: SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits

B: SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits

C: SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits

D: SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits

A: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p

B: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders

C: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p

D: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders

A: UNPIVOT

B: PIVOT

C: CROSS APPLY

D: CROSS JOIN

You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

A: Use the DATE data type.

B: Use the CAST function.

C: Use an appropriate collation.

D: Use the DATETIMEOFFSET data type.

E: Use the VARBINARY data type.

F: Use the TODATETIMEOFFSET function.

G: Use a user-defined table type.

H: Use the DATETIME2 data type.

I: Use the DATETIME data type.

J: Use the FORMAT function.

You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A: SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

B: SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)

C: SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)

D: SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)

You have a database 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.
Return the most recent orders first.
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.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.

You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB with a table named Partners.

The Partners table was created using the following Transact-SQL code:

CREATE TABLE [dbo].[Partners]  (  [CompanyID] [int] NOT NULL PRIMARY KEY,  
[CompanyName] [varchar] (150) NOT NULL,  [Location] [varchar] (150) NOT NULL,  
[ContactName] [varchar] (150) NOT NULL,  [Email] [varchar] (150) NOT NULL,  
[Phone] [varchar] (10) NOT NULL  )

You develop a new table named Events using the following Transact-SQL code:

CREATE TABLE [dbo].[Events]  (  [EventID] [int] NOT NULL PRIMARY KEY,  
[CompanyID] [int] NOT NULL,  [EventDescription] [varchar] (2500),  
[EventCordinator] [varchar] (150) NOT NULL  )

How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?

You should add a Check Constraint on the Events table.

You should change the Events table to a partitioned table.

You should add a Unique Constraint on the Events table.

You should specify Events.CompanyID as a spars column.

You should add a Foreign Key Constraint on the Events table.

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

You deploy a new server that has SQL Server 2012 installed. You need to create a table named
Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:

Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.

Which code segment should you use?
To answer, type the correct code in the answer area.

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

Grant the sysadmin role on the database to User1.

Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.

Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

Grant the db_owner role on the database to User1.

Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The SalesDB database is shown in the following database diagram:

You create a view on the SalesDB using the following Transact-SQL code:

CREATE VIEW SalesV  WITH SCHEMABINDINGS  AS  SELECT Products.ProductID, 
Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price  FROM OrderDetail 
INNER JOIN Products ON derDetail.ProductID = Products.ProducID 
INNER JOIN Invoices ON OrderDetail.InvoiceID = Invoices.InvoiceID  
GROUP BY Products.ProductID, Invoices.InvoiceDate GO

How should you alter this view to allow users to update data through the SalesV?

You should add a clustered index to the SalesV view.

You should add an INSTEAD OF trigger to the SalesV view.

You should add an AFTER UPDATE trigger to the SalesV view.

You should add a CHECK constraint to the SalesV view.

Create a columnstore index on all columns used in the SalesV view.

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

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use? To answer, type the correct code in the answer area.

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that a large amount of memory is consumed by single-use dynamic queries. You need to reduce procedure cache usage from these statements without creating any additional indexes. What should you do?

Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

Enable the optimize for ad hoc workloads option.

Add a columnstore index to cover the query.

Cover the unique clustered index with a columnstore index.

Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

Add a LOOP hint to the query.

Add an INCLUDE clause to the index.

Add a FORCESCAN hint to the Attach query.

Include a SET STATISTICS PROFILE ON statement before you run the query.

Add a HASH hint to the query.

Include a SET FORCEPLAN ON statement before you run the query.

Add a FORCESEEK hint to the query.

You create a view based on the following statement:

You grant the Select permission to User1.
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 Transact-SQL statement should you use?

Option B

Option C

Option D

Option A

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. The view has the following definition:

You need to ensure that users can update only the phone numbers and email addresses by using this view.
What should you do?

Create an INSTEAD OF UPDATE trigger on the view.

Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

Create an AFTER UPDATE trigger on the view.

Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement
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.)
Select and Place:

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.)
Build List and Reorder:

Add an INCLUDE clause to the index.

Enable the optimize for ad hoc workloads option.

Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

Add a HASH hint to the query.

Cover the unique clustered index with a columnstore index.

Include a SET STATISTICS PROFILE ON statement before you run the query.

Add a columnstore index to cover the query.

Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

Include a SET FORCEPLAN ON statement before you run the query.

Add a LOOP hint to the query.

Add a FORCESCAN hint to the Attach query.

Add a FORCESEEK hint to the query.

Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

You work as a database developer at ABC.com. You want to create a Transact-SQL query will call a table-valued function for every row the query returns.

How would you accomplish this task?

You should make use of a CONVERT function.

You should make use of a CROSS APPLY.

You should make use of a CAST function.

You should make use of an INNER JOIN.

You should make use of a Trigger.

You should make use of the FORMAT function.

You should make use of an OUTER JOIN.

You should make use of a UNION.

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.

Which six 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.)
Select and Place:

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?

SELECT Location, Week, ItemsProduced,  LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced  FROM Sites

SELECT Location, Week, ItemsProduced,  FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced  FROM Sites

SELECT Location, Week, ItemsProduced,  CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced  FROM Sites

SELECT Location, Week, ItemsProduced,  LEAD(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced  FROM Sites

SELECT Location, Week, ItemsProduced,  LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced  FROM Sites

You work as a Database Administrator (DBA) at ABC.com. The company uses a Microsoft SQL Server 2012 environment and has recently deployed SQL Server 2012 Reporting Services (SSRS).
The company will eventually use hundreds of SSRS reports. All the reports will use the standard corporate branding, fonts and text styles. Many of the reports will also use common functions that will be coded by developers.
You need to recommend a reporting strategy that enables all reports to use the corporate branding and fonts and the function code. You need to be able to modify the corporate branding and function code if required without recreating all the reports.
Where should the corporate branding, fonts and text styles and the function code should be stored?

The corporate branding, fonts and text styles and the function code should be stored in a web service.

The corporate branding, fonts and text styles and the function code should be stored in a report template.

The corporate branding, fonts and text styles and the function code should be stored in a SQL Server database.

The corporate branding, fonts and text styles and the function code should be stored in an assembly on an SSRS server.

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

You have an application named App1. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for App1. usp_Customers must meet the following requirements:

NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.

Which code segment should you use?
To answer, type the correct code in the answer area.

Failed question

Passed question

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

Please review the explanation part for this answer

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The ProductsDB database is shown in the following database diagram:

You need to write a Transact-SQL query that displays all the products received by a single supplier in the following XML format:

<Suppliers SupplierID="22" Company="Company Name" ContactNumber=
"510 250 6400">  <Products ProductID="100" UnitPrice="249.00" UnitsInStock="7" />  
<Products ProductID="118" UnitPrice="559.00" UnitsInStock="12" />  </Suppliers>

Which of the following SELECT statement would you write?

SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock  FROM Suppliers  INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID  WHERE Suppliers.SupplierID = 22  FOR XML AUTO, RAW

SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock  FROM Suppliers  INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID  WHERE Suppliers.SupplierID = 22  FOR XML AUTO

SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID, p.UnitPrice, p.UnitsInStock  FROM Suppliers AS s  INNER JOIN Products AS p ON s.SupplierID = p.SupplierID  WHERE s.SupplierID = 22  FOR XML RAW

SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID, p.UnitPrice, p.UnitsInStock  FROM Suppliers AS s  INNER JOIN Products AS p ON s.SupplierID = p.SupplierID  WHERE s.SupplierID = 22  FOR XML

You should make use of a cursor.

You should make use of the SET FORCEPLAN ON statement in the stored procedure.

You should make use of the SET CONTEXT_INFO statement in the stored procedure.

You should make use of a scalar user-defined function.

You should make use of the TODATETIMEOFFSET function.

You should make use of the DATETIMEOFFSET data type.

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?

WITH CTEDupRecords AS (   SELECT MAX(CreatedDateTime) 
AS CreatedDateTime, ProductName   FROM Products   GROUP BY ProductName   
HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte 
ON p.ProductName = cte.ProductName

WITH CTEDupRecords AS (   SELECT MAX(CreatedDateTime) 
AS CreatedDateTime, ProductName   FROM Products   GROUP BY ProductName   
HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte 
ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime

WITH CTEDupRecords AS (   SELECT MAX(CreatedDateTime) 
AS CreatedDateTime, ProductName   FROM Products   GROUP BY ProductName   
HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte 
ON p.ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime

WITH CTEDupRecords AS (   SELECT MIN(CreatedDateTime) 
AS CreatedDateTime, ProductName   FROM Products   GROUP BY ProductName ) 
DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName

You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB.

You are developing a stored procedure that takes a parameter named @date that uses the varchar datatype. The @date parameter must be compared to the value in a datetime column named OrderDate.

Which of the following WHERE clauses would be the most efficient WHERE clause to use?

WHERE OrderDate = CAST(datetime,@date)

WHERE OrderDate =@date

WHERE OrderDate = PARSE(@date AS Date)

WHERE OrderDate = CONVERT(datetime,@date)

WHERE OrderDate = CAST(@date AS datetime)

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table.
What should you do?

Implement row-level compression.

Implement Unicode Compression.

Implement page-level compression.

Convert all indexes to Column Store indexes.

Question #

You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition:

CREATE VIEW vOrders WITH SCHEMABINDING 
AS SELECT o.ProductID, o.OrderDate,     
SUM(od.UnitPrice, * o.OrderQTY) 
AS Amount FROM OrderDetails AS od      
INNER JOIN Orders AS o      
ON od.OrderID = o.OrderID 
WHERE od.SalesOrderID = o.SalesOrderID 
GROUP BY o.OrderDate, o.ProductID 
GO

You need to improve the performance of the view by persisting data to disk. What should you do?

Create an INSTEAD OF trigger on the view.

Create an AFTER trigger on the view.

Modify the view to use the WITH VIEW_METADATA clause.

Create a clustered index on the view.

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?

ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse

ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItems

ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse

ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

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?

Use the CAST function.

Use the DATE data type.

Use the FORMAT function.

Use an appropriate collation.

Use a user-defined table type.

Use the VARBINARY data type.

Use the DATETIME data type.

Use the DATETIME2 data type.

Use the DATETIMEOFFSET data type.

Use the TODATETIMEOFFSET function.

Use the CAST function.

Use the DATE data type.

Use the FORMAT function.

Use an appropriate collation.

Use a user-defined table type.

Use the VARBINARY data type.

Use the DATETIME data type.

Use the DATETIME2 data type.

Use the DATETIMEOFFSET data type.

Use the TODATETIMEOFFSET function.

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo. ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?

A

    BEGIN TRANSACTION BEGIN TRY
    EXEC dbo.ModifyData
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    IF @@ TRANCOUNT = 0
    ROLLBACK TRANSACTION;
    END CATCH

B

    BEGIN TRANSACTION BEGIN TRY
    EXEC dbo.ModifyData
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    IF @@TRANCOUNT = 0
    ROLLBACK TRANSACTION;
    THROW;
    END CATCH

C

    BEGIN TRANSACTION BEGIN TRY
    EXEC dbo.ModifyData
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    IF @@ERROR != 0
    ROLLBACK TRANSACTION;
    END CATCH

D

    BEGIN TRANSACTION BEGIN TRY
    EXEC dbo.ModifyData
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    IF @@ERROR != 0
    ROLLBACK TRANSACTION;
    THROW;
    END CATCH

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?

A

    Convert the view into an indexed view.

B

    Convert the view into a table-valued function.

C

    Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

D

    Convert the view into a Common Table Expression (CTE).

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column. Retain only the newest Products row.
Which Transact-SQL query should you use?

A

    WITH CTEDupRecords
    AS
    (
    SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
    FROM Products
    GROUP BY ProductName
    HAVING COUNT(*) > 1
    )
    DELETE p
    FROM Products p
    JOIN CTEDupRecords cte ON
    p.ProductName = cte.ProductName

B

    WITH CTEDupRecords
    AS
    (
    SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
    FROM Products
    GROUP BY ProductName
    )
    DELETE p
    FROM Products p
    JOIN CTEDupRecords cte ON
    p.ProductName = cte.ProductName

C

    WITH CTEDupRecords
    AS (
    SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
    FROM Products
    GROUP BY ProductName
    HAVING COUNT(*) > 1
    )
    DELETE p
    FROM Products p
    JOIN CTEDupRecords cte ON
    p.ProductName = cte.ProductName
    AND p.CreatedDateTime > cte.CreatedDateTime

D

    WITH CTEDupRecords
    AS
    (
    SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
    FROM Products
    GROUP BY ProductName
    HAVING COUNT(*) > 1
    )
    DELETE p
    FROM Products p
    JOIN CTEDupRecords cte ON
    cte.ProductName = p.ProductName
    AND cte.CreatedDateTime > p.CreatedDateTime

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

    Scalar user-defined function

B

    Table-valued user-defined function

C

    Inline user-defined function

D

    Stored procedure

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 retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

A

    SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
    FROM StudentMarks
    GROUP BY StudentCode

B

    SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
    FROM StudentMarks
    GROUP BY StudentCode

C

    SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
    FROM StudentMarks

D

    SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
    FROM StudentMarks
    GROUP BY StudentCode

E

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

F

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

G

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

H

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?

A

    Reference a user-defined function within the computed column.

B

    Add a default constraint to the computed column that implements hard-coded values.

C

    Create a BEFORE trigger that maintains the state of the computed column.

D

    Add a default constraint to the computed column that implements hard-coded CASE statements.

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 0
    MAXVALUE 100
    CYCLE
    UPTATE 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 1
    MAXVALUE 100
    CYCLE
    UPDATE 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 are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

A

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

B

    SET IMPLICIT_TRANSACTIONS ON

C

    SET XACT_ABORT OFF

D

    SET IMPLICIT TRANSACTIONS OFF

E

    SET XACT_ABORT ON

F

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

A

    DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;

    EXEC DeleteJobCandidate

    SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0)
    PRINT N'Error = ' + CAST(ERRORSTATE() AS NVARCHAR(8)) +
    N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
    GO

B

    DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;

    EXEC DeleteJobCandidate

    SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0)
    PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +
    N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
    GO

C

    EXEC DeleteJobCandidate

    IF (ERROR_STATE() != 0)
    PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
    N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
    GO

D

    EXEC DeleteJobCandidate

    PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
    N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
    GO

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), ())

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?

A

    CREATE TABLE DocumentStore AS FileTable

B

    CREATE TABLE DocumentStore
    (
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL
    )
    GO

C

    CREATE TABLE DocumentStore
    (
    [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL
    )
    GO

D

    CREATE TABLE DocumentStore
    (
    [Id] hierarchyid,
    [Document] NVARCHAR NOT NULL
    )
    GO

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

    An UPDATE statement that includes CASE

B

    a recursive stored procedure

C

    Trigger

D

    Cursor

E

    The foreach SQLCLR statement

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the
appropriate constraints and table properties to ensure data integrity and visibility. On which column in the
Employee table should you a create a unique constraint?

MiddleName

FirstName

LastName

DepartmentID

EmployeelD

DateHired

EmployeeNum

JobTitle

ReportsToID

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and
SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

Add a clustered index on SalesOrderId in SalesOrderHeader.

Use a FORCESEEK hint in the query.

Update statistics on SalesOrderId on both tables.

Use a FORCESCAN hint in the query.

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the
CustomerId column of the Customer table. Which Transact-SQL statement should you use?

ALTER TABLE Customer
ADD OrderId INT NOT NULL;

ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES Orders
(CrderlD);

ALTER TABLE Orders
ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)

ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES
Orders (CustomerId)

ALTER TABLE Orders
ADD CONSTRAINT CK_Crders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))

ALTER TABLE Orders

ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES
Customer (CustomerId)

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. Which Transact-SQL statement should you use?

CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN

DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15)

SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,
@EmployeeNumber = EmployeeNumber
FROM inserted

INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName)

INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID, @EmployeeNumber

End

CREATE TRIGGER TrgVwEmployee
ON VwEmployee
FOR INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted

END

CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM VwEmployee

INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee

End

CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted

INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted

END

You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);

CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));

CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);

CREATE TABLE Customer
(SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs
WHERE Rnk = 1

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.
BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. 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.)
Build List and Reorder:

Which of the following datatypes has a fixed precision and a scale of six digits?

Money

SmallInt

Numeric

VarInt

Double

Float

Int

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM
Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML PATH ('Customers')

SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN
Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId = Customers.CustomerId WHERE
Customers.CustomerId = 1 FOR XML AUTO

SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount FROM Orders INNER JOIN Customers 
ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML PATH ('Customers')

SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN
Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML AUTO

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId = Customers.CustomerId  
WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId - Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId = Customers-CustomerId WHERE
Customers.CustomerId = 1 FOR XML RAW

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

Query the sys.dm_db_index_usage_stats DMV.

Query the sys.dm_db_missing_index_details DMV.

Use the Database Engine Tuning Advisor.

Query the sys.dm_db_missing_index_columns DMV.

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

You have the following query:

You need to recreate the query to meet the following requirements:

Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.

Which code segment should you use?
To answer, type the correct code in the answer area.

Passed question

Failed question

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:

<row OrderId="1" OrderDate="2000-01-01T00:00:00" 
Amount="3400.00" Name="Customer A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" 
Amount="4300.00" Name="Customer A" Country="Australia" />

Which Transact-SQL query should you use?

SELECT Name, Country, OrderId, OrderDate, Amount  FROM Orders 
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML AUTO

SELECT OrderId, OrderDate, Amount, Name, Country  FROM Orders 
INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

SELECT OrderId, OrderDate, Amount, Name, Country  FROM Orders 
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS

SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount  
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

SELECT OrderId, OrderDate, Amount, Name, Country  FROM Orders 
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML RAW

SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount  FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

SELECT Name, Country, Orderld, OrderDate, Amount  FROM Orders 
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

SELECT OrderId, OrderDate, Amount, Name, Country  FROM Orders 
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId  
WHERE Customers.CustomerId = 1 FOR XML AUTO

One of the cube requirements states that the cube should include aggregation designs.
How should you meet this requirement?

By using the CREATE MINING MODEL statement.

By running the Usage-Based Optimization Wizard.

By dividing the cube into monthly partitions.

By using the CREATE SET statement.

By using the Aggregation Design Wizard.

You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

Please review the explanation part for this answer

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

EXEC DeleteJobCandidate PRINT N'Error = ' + 
CAST(@@ERROR AS NVARCHAR(8)) +    N', Rows Deleted = ' + 
CAST(@@ROWCOUNT AS NVARCHAR(8));
GO

DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate SELECT @ErrorVar = ERROR_STATE(), 
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)   PRINT N'Error = ' + CAST(ERRORSTATE() 
AS NVARCHAR(8)) +      N', Rows Deleted = ' + 
CAST(@@RowCountVar AS NVARCHAR(8));
GO

DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR, 
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)   PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +      
N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
GO

EXEC DeleteJobCandidate IF (ERROR_STATE() != 0)   
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +      
N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?select

SELECT   CASE    WHEN convert(decimal(36,9), 
@var) IS NULL    THEN 'True'    ELSE 'False'   END  AS BadCast

BEGIN TRY   SELECT convert(decimal(36,9), @var) 
AS Value, 'True' AS BadCast END TRY BEGIN CATCH   
SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast END CATCH

TRY(   SELECT convert(decimal(36,9), @var)   
SELECT 'True' AS BadCast  ) CATCH(   SELECT 'False' AS BadCast  )

SELECT IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False') AS BadCast

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The SalesDB is shown in the following database diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:

<Invoices InvoiceID="1001" Date="2012-10-01T00:00:00", Value="1000.00" Customer=
"CustomerName" ShippedTo="Customer City" />

Which of the following SELECT statement would you write?

SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices  INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID  WHERE Customers.CustomerID = 1001  FOR XML

SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in  INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID  WHERE cu.CustomerID = 1001  FOR XML RAW

SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices  INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID  WHERE Customers.CustomerID = 1001  FOR XML AUTO, RAW

SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value],
Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices  INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID  WHERE Customers.CustomerID = 1001  FOR XML AUTO

You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB with a table named Partners.

The Partners table was created using the following Transact-SQL code:

CREATE TABLE [dbo].[Partners]  (  [CompanyID] [int] NOT NULL PRIMARY KEY,  
[CompanyName] [varchar] (150) NOT NULL,  [Location] [varchar] (150) NOT NULL,  
[ContactName] [varchar] (150) NOT NULL,  [Email] [varchar] (150) NOT NULL,  
[Phone] [varchar] (10) NOT NULL  )

You develop a new table named Events using the following Transact-SQL code:

CREATE TABLE [dbo].[Events]  (  [EventID] [int] NOT NULL PRIMARY KEY,  
[CompanyID] [int] NOT NULL,  [EventDescription] [varchar] (2500),  
[EventCordinator] [varchar] (150) NOT NULL  )

How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?

You should specify Events.CompanyID as a spars column.

You should add a Unique Constraint on the Events table.

You should add a Check Constraint on the Events table.

You should add a Foreign Key Constraint on the Events table.

You should change the Events table to a partitioned table.

You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer. Which four T-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.)
Build List and Reorder:

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?

Money

Smallmoney

Float

Numeric

WHERE OrderDate = CAST(@date AS datetime)

WHERE OrderDate = CONVERT(datetime,@date)

WHERE OrderDate = CAST(datetime,@date)

WHERE OrderDate = PARSE(@date AS Date)

WHERE OrderDate =@date

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime >= CONVERT(DATE, GETDATE())    
AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime = GETDATE()

SELECT COUNT(*) FROM SalesOrders  
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime = CONVERT(DATE, GETDATE())

Use the CAST function.

Use the DATE data type.

Use the FORMAT function.

Use an appropriate collation.

Use a user-defined table type.

Use the VARBINARY data type.

Use the DATETIME data type.

Use the DATETIME2 data type.

Use the DATETIMEOFFSET data type.

Use the TODATETIMEOFFSET function.

Use the CAST function.

Use the DATE data type.

Use the FORMAT function.

Use an appropriate collation.

Use a user-defined table type.

Use the VARBINARY data type.

Use the DATETIME data type.

Use the DATETIME2 data type.

Use the DATETIMEOFFSET data type.

Use the TODATETIMEOFFSET function.

You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

ALTER INDEX ALL ON OrderDetail REBUILD

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored procedure that updates rows in several tables.

You want the entire transaction to be rolled back should the stored procedure cause a run-time error.

How would you accomplish this?

You should make use of RAISERROR in the stored procedure.

You should make use of a LOOP hint in the stored procedure.

You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.

You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.

You should make use of the SET XACT_ABORT ON statement in the stored procedure.

You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.

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.)
Build List and Reorder:

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB with a table named Invoices.

Application developers are developing several in-house applications that will access the Invoices table. You need to develop a solution that will allow the applications to access the table indirectly while still allowing them to update the Invoice table.

How would you accomplish this task?

You should drop and recreate the Invoices table as a partitioned table.

You should allow the applications access to the Invoices table via stored procedures.

You should create a view on the Invoices table.

You should create a columnstore index on all columns used by the applications.

You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB with tables named Customer and Orders. The Customer and Orders tables were created using the following Transact-SQL code:

CREATE TABLE SalesDB.Customers  (
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL  )  GO   CREATE TABLE SalesDB.Orders  (
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL  )  GO

You must now create an OrderDetails table as shown in the following database diagram:

The TotalPrice column must be a computed column based on the product of the UnitPrice and
Quantity columns and the data must be stored in the table.

How would you create this table?

To answer, type the correct code in the answer area.

CREATE TABLE SalesDB.OrderDetails  ( 
OrderID int NOT NULL, 
ProductID int NOT NULL, 
Quantity int NOT NULL, 
UnitPrice money NOT NULL, 
TotalPrice AS (Quantity * UnitPrice) PERSISTED  )

Use the Database Engine Tuning Advisor.

Query the sys.dm_db_index_usage_stats DMV.

Query the sys.dm_db_missing_index_details DMV.

Query the sys.dm_db_missing_index_columns DMV.

CORRECT TEXT

You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.

You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to have a rank of 2, and the last 10 to have a rank of 3.

Which of the following SELECT statement would you use?

To answer, type the correct code in the answer area.

SELECT TOP 30 PlayerName,  NTILE (3) OVER (ORDER BY AVG (Votes) 
DESC) AS AveVotes   FROM WeeklyVotes   GROUP BY PlayerName

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,    
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) 
AS PrevProfit  FROM Profits

SELECT Territory, Year, Profit,    
LAG(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 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

You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

SELECT CustomerID FROM Customer WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)

SELECT CustomerID FROM Customer WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)

SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

SELECT CustomerID FROM Customer WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)

SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) 
AS Value  FROM StudentMarks  GROUP BY StudentCode

SELECT StudentCode AS Code, Marks AS Value FROM (   
SELECT StudentCode, Marks AS Marks,     
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank    
FROM StudentMarks) tmp  WHERE Rank = 1

SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS
Value  FROM StudentMarks  GROUP BY StudentCode

SELECT StudentCode AS Code, Marks AS Value FROM (   
SELECT StudentCode, Marks AS Marks,      
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank    
FROM StudentMarks) tmp  WHERE Rank = 1

SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) 
AS Value  FROM StudentMarks  GROUP BY StudentCode

SELECT StudentCode AS Code, Marks AS Value FROM (   
SELECT StudentCode, Marks AS Marks,     
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank    
FROM StudentMarks) tmp  WHERE Rank = 1

SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) 
AS Rank  FROM StudentMarks

SELECT StudentCode AS Code, Marks AS Value FROM (   
SELECT StudentCode, Marks AS Marks,     
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank    
FROM StudentMarks) tmp  WHERE Rank = 1

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.)

SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)

SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)

SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)

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

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use? To answer, type the correct code in the answer area.

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

Perform a filegroup restore.

Perform a file restore.

Perform a transaction log restore.

Perform a restore from a full backup.

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

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:

UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.

Which code segment should you use?
To answer, type the correct code in the answer area

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
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.)
Select and Place:

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:

Takes an input variable
Returns a table of values
Cannot be referenced within a view

Which object should you use?

Inline function

Stored procedure

User-defined data type

Scalar-valued function

You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
The fields must use the minimum amount of space.
The fields must be an incrementing series of values.
The values must be unique among the four tables.
What should you do?

Create a SEQUENCE object that uses the INTEGER data type.

Create a ROWVERSION column.

Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID()

Create a TIMESTAMP column.

Use the INTEGER data type along with IDENTITY

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use? To answer, type the correct code in the answer area.

DECLARE @XML1 XML(Sales.InvoiceSchema)

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.

Grant the db_owner role on the database to User1.

Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

Grant the sysadmin role on the database to User1.

Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the following requirements:
Use one-part names to reference columns.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

You have a view that was created by using the following code:

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:

Does not return an error
Closes all opened transactions

Which Transact-SQL statement should you use?

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@ERROR != 0     
ROLLBACK TRANSACTION;
THROW;
END CATCH

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@TRANCOUNT = 0     
ROLLBACK TRANSACTION;
THROW;
END CATCH

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@ERROR != 0     
ROLLBACK TRANSACTION;
END CATCH

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@ TRANCOUNT = 0     
ROLLBACK TRANSACTION;
END CATCH

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?

Use the DATETIME data type.

Use the TODATETIMEOFFSET function.

Use the VARBINARY data type.

Use a user-defined table type.

Use the FORMAT function.

Use the DATETIMEOFFSET data type.

Use the DATETIME2 data type.

Use the CAST function.

Use the DATE data type.

Use an appropriate collation.

 Which of the following datatypes has a fixed precision and a scale of six digits?

Money

Int

VarInt

Numeric

SmallInt

Double

Float

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view
that is used to look up their email addresses and phone numbers. The view has the following
definition:

You need to ensure that users can update only the phone numbers and email addresses by using
this view. What should you do?

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

B. Create an INSTEAD OF UPDATE trigger on the view.

C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an
index on the view.

D. Create an AFTER UPDATE trigger on the view.

You have a database that contains the tables shown in the exhibit.

You need to create a query that calculates the total sales of each OrderId from the Sales.Details
table. The solution must meet the following requirements:

Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.

Which code segment should you use?

You have a database that contains the tables shown in the exhibit.

You need to create a view named uv_CustomerFullName to meet the following requirements:

The code must NOT include object delimiters.
The view must be created in the Sales schema.
Columns must only be referenced by using one-part names.
The view must return the first name and the last name of all customers.
The view must prevent the underlying structure of the customer table from being changed.
The view must be able to resolve all referenced objects, regardless of the user's default

Schema.

Which code segment should you use?

To answer, type the correct code in the answer area.

You have a database that contains the tables shown in the exhibit.

You deploy a new server that has SQL Server 2012 installed. You need to create a table named

Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:

Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity
for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.

Which code

segment should you use?
To answer, type the correct code in the answer area.

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must

meet the following requirements:

Accept the @T integer parameter.
Use one-part names to reference columns.
Filter the query results by SalesTerritoryID.
Return the columns in the same order as the order used in OrdersByTerritoryView.

Which code segment should you use?

To answer, type the correct code in the answer area.

You have a database that contains the tables as shown in the exhibit.

You have the following query:

You need to recreate the query to meet the following requirements:

Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.

Which code segment should you use?

To answer, type the correct code in the answer area.

You have a database that contains the tables shown in the exhibit.

You need to create a query for a report. The query must meet the following requirements:

NOT use object delimiters.
Return the most recent orders first.
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.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.

The solution must support the ANSI SQL-99 standard. Which code segment should you use?

To answer, type the correct code in the answer area.

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of
the XML type named XML1. The solution must ensure that XML1 is validated by using
Sales.InvoiceSchema. Which code segment should you use?
To answer, type the correct code in the answer area.

You have a database that contains the tables as shown in the exhibit.

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution
must meet the following requirements:

UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.

Which code segment should you use?

To answer, type the correct code in the answer area.

You have a database that contains the tables shown in the exhibit.

You have an application named Appl. You have a parameter named @Count that uses the int data
type. App1 is configured to pass @Count to a stored procedure. You need to create a stored
procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:

NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.

Which code segment should you use?

To answer, type the correct code in the answer area.
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table

CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));

CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);

CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);

CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);

CORRECT TEXT

You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
SalesDB database with a view named SalesV. The SalesV view was created using the following
Transact-SQL code:

CREATE VIEW SalesDB.ProductsSalesV  AS  SELECT OrderID, ProductID, 
ShipDate, OrderDate, Amount  FROM SalesDB.Orders;

You want to create an inline table-valued function named fn_ABC that accepts a @ProductID parameter of the integer data type. The inline table-valued function should also allow for sales orders for each product to be listed by the latest sale.

How would you create this inline table-valued function?

To answer, type the correct code in the answer area.

CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int )  RETURNS TABLE
  AS  RETURN  (  SELECT OrderID, ProductID, ShipDate, OrderDate, Amount 
 FROM Sales. ProductsSalesV  WHERE ProductID = @ProductID  ORDER BY 
OrderDate DESC  );

You administer a Microsoft SQL Server 2012 database.

The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

 Unless stated above, no columns in the Employee table reference other tables.

Confidential information about the employees is stored in a separate 
table named EmployeeData. One record exists within EmployeeData for each
 record in the Employee table.

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.

On which column in the Employee table should you create a Primary Key constraint for this table?

EmployeeNum

LastName

EmployeeID

ReportsToID

FirstName

Departments

MiddleName

DateHired

JobTitle

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

UPDATE TOP(10) BlogEntry  
SET Summary.WRITE(N' This is in a draft stage', NULL, 0)

UPDATE BlogEntry  SET Summary.WRITE(N' This is in a draft stage', 
NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 
AS s  WHERE BlogEntry.Id = s.ID

UPDATE BlogEntry  SET Summary.WRITE(N' This is in a draft stage', 
0, 0)  WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

UPDATE BlogEntry SET Summary = CAST(N' 
This is in a draft stage' as nvarchar(max))  
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

Users have advised that they are not receiving report subscriptions from SQLReporting01. You confirm that the report subscriptions are not being delivered.
Which of the following actions should you perform to resolve the issue?

You should run the SQL Server 2012 Upgrade Wizard to upgrade the active node of the SSAS cluster.

You should start the SQL Server Agent on the active node of the SSAS cluster.

You should start the SQL Server Agent on SQLReporting01.

You should restore the ReportServerTempDB database on SQLReporting01.

You create a view based on the following statement:

Option C

Option A

Option D

Option B

You are developing a SQL Server 2012 database for ABC.com. You need to create a computed column that returns the data by referencing another table using an INNER JOIN.

How would you accomplish this?

You should make use of an inline function.

You should make use of a scalar user-defined function.

You should make use of a stored procedure.

You should make use of the FORMAT function.

You should make use of a table-valued user-defined function.

You need to create a query that calculates the total sales of each OrderlD from a table named Sales.Details. The table contains two columns named OrderlD and
ExtendedAmount. The solution must meet the following requirements:
Use one-part names to reference columns.
Start the order of the results from OrderlD.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderlD column and the calculated TotalSales column.
Provide the correct code in the answer area.

Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.

Grant the sysadmin role on the database to User1.

Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

Grant the db_owner role on the database to User1.

Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

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?

ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse

ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse

ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItems

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The relevant part of the ProductsDB is shown in the following database diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:

<row ProductID="1001" Product="Product Name", Price="24.99" InStock="16"
Supplier="Company Name" Contact="Contact Name" Phone="346 959 2215" />

Which of the following SELECT statement would you write?

SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS

[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone

 FROM Products  INNER JOIN Suppliers ON SupplierID = SupplierID

WHERE ProductID = 1001  FOR XML AUTO, RAW

SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS

[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone

FROM Products  INNER JOIN Suppliers ON SupplierID = SupplierID

WHERE ProductID = 1001  FOR XML RAW

SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS

[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone

FROM Products  INNER JOIN Suppliers ON SupplierID = SupplierID

 WHERE ProductID = 1001  FOR XML AUTO

SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS

[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone

 FROM Products  INNER JOIN Suppliers ON SupplierID = SupplierID

 WHERE ProductID = 1001  FOR XML

You Create a table that has the StudentCode,SubjectCode, and Marks Columns to record mid-year 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.
Consecutive ranks must be skipped when the same rank is assigned

Which Transact-SQL query should you use?

A
SELECT StudentCode as Code,RANK() OVER(ORDER BY AVG(Marks) DESC) AS value FROM StudentMarks
GROUP BY StudentCode

B
SELEC Id,Name,Marks,DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks

C
SELECT StudentCode as Code,DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

D
SELECT StudentCode as Code,
NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
Group by StudentCode

E
SELECT StudentCode AS Code,Marks AS Value
FROM (SELECT StudentCode,Marks AS Marks,RANK() OVER(PARTITON BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank=1

F
SELECT StudentCode as Code,Marks as value
FROM (select StudentCode,Marks as marks,RANK()
OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank=1

G
SELECT StudentCode AS code,Marks Value
FROM (SELECT StudentCode,Marks as Marks,RANK(0
OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank=1

H
SELECT StudentCode as Code,Marks As Value
FROM (SELECT StudentCode,Marks as Marks,RANK() OVER(PARTITON BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank=1

Ref:

http://onlinetestcentre.com/simulator/testing.html

http://www.measureup.com/Querying-Microsoft-SQL-Server-2012-P4155.aspx

http://www.elearningexams.com/70-461-notes

http://www.aiotestking.com/microsoft/category/exam-70-461-querying-microsoft-sql-server-2012-update-january-31th-2014/

https://www.microsoft.com/en-in/learning/exam-70-461.aspx

https://www.microsoft.com/en-in/learning/mcsa-sql-certification.aspx

http://www.accelerated-ideas.com/free-70461-practice-test.aspx#.VtmACPnkp_h

http://www.elearningexams.com/70-461-notes

Sample practise test link see below

http://www.accelerated-ideas.com/exams/practice-exam.aspx?group=70-461&fq=1&qmax=15

http://www.sql-server-helper.com/free-test/sql-question.aspx

for dumps:

http://getfreedumps.com/pages.php?id=70-461

http://dumps4free.com/70-461-exam-questions-pdf-vce.html

http://www.register.prometric.com/Index.asp?tcl=1

for videos

http://www.cathrinewilhelmsen.net/2015/01/28/preparing-for-and-taking-exam-70-461-querying-microsoft-sql-server-2012/

MCSA: SQL Server Solutions Associate

Earning an MCSA: SQL Server certification will qualify you for a position as a database developer or database analyst.

Step	Title	Optional training	Required exam
1	Querying Microsoft SQL Server 2012	461	461
2	Administering Microsoft SQL Server 2012 Databases	462	462
3	Implementing a Data Warehouse with Microsoft SQL Server 2012	463	463

Resources

Sign up to receive occasional email messages about Microsoft Certifications
See all SQL Server certifications
Find out more about the MCSE certification
Download an evaluation copy of SQL Server 2012
Read Training Kit (Exam 70-461): Querying Microsoft SQL Server
Read Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases

Read Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012
Find a Microsoft Official Practice Test
Connect with others studying for Exam 461
Connect with others studying for Exam 462
Connect with others studying for Exam 463
Read a case study

Study Materials:

data types

Decimel
Numeric
Float
Real
int(4 bytes),bigint(8 bytes),smallint(2 bytes),tinyint(1 bytes)

Numeric data types that have fixed precision and scale.

Sample Code:

CREATE TABLE dbo.MyTableALL
(
MyRealcolumn Real,
MySmallMoneyColumn Smallmoney,
MyMoneyColumn Money,
MyFloatColumn real,
MyDecimalColumn decimal,
Myintcolumn int,
MyNumericColumn Numeric
);

INSERT INTO dbo.MyTableALL VALUES (24,38,36,96,21,65,32);

GO

SELECT * FROM dbo.MyTableALL

FOR XML in SQL Server:

Ref:
https://msdn.microsoft.com/en-IN/library/ms178107.aspx

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes:

RAW
AUTO
EXPLICIT
PATH

The RAW mode generates a single <row> element per row in the rowset that is returned by the SELECT statement. You can generate XML hierarchy by writing nested FOR XML queries.

The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape.

The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

Sample:

SELECT TOP 1 CustomerID
FROM Sales.Customer x
FOR XML AUTO

Output:

<x CustomerID="1" />

SELECT TOP 1 CustomerID
FROM Sales.Customer x
FOR XML RAW

Output:

<row CustomerID="1" />

SELECT TOP 1 CustomerID
FROM Sales.Customer x
FOR XML PATH

Output:

<row>

</row>

Also see more sample

SELECT TOP 2 Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,Cust.CustomerType

FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

Output:

<Cust CustomerID="676" CustomerType="S">
<OrderHeader CustomerID="676" SalesOrderID="43659" Status="5" />
</Cust>
<Cust CustomerID="117" CustomerType="S">
<OrderHeader CustomerID="117" SalesOrderID="43660" Status="5" />
</Cust>

SELECT TOP 2 Cust.CustomerID,
OrderHeader.CustomerID OrderheaderCustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,Cust.CustomerType

FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML RAW

Output:

<row CustomerID="676" OrderheaderCustomerID="676" SalesOrderID="43659" Status="5" CustomerType="S" />
<row CustomerID="117" OrderheaderCustomerID="117" SalesOrderID="43660" Status="5" CustomerType="S" />

Use [AdventureWorks]

Create table Customers(CustomerId int,Name Nvarchar(100),Country Nvarchar(30))

Create table Orders(OrderId int,CustomerId int,OrderDate smalldatetime,Amount int)

Insert into Customers
SELECT 1,'Customer A','Australia'
union
SELECT 2,'Customer A','Australia'

Insert into Orders
SELECT 1,1,'2000-01-01 19:13:43.540',3400
UNION
SELECT 2,1,'2001-01-01 19:13:43.540',4300

SELECT * FROM Customers
SELECT * FROM Orders

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers. CustomerId = 1
FOR XML RAW, ELEMENTS

SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers. CustomerId= 1
FOR XML AUTO, ELEMENTS

SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML AUTO

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers. CustomerId = 1
FOR XML AUTO

SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML RAW

SELECT Name AS 'Customers/Name', Country AS 'Custo/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')

SELECT Name AS 'Customers/Name', Country AS 'Custo/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML AUTO

SELECT Name AS 'Customers/Name', Country AS 'Custo/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML RAW

Stored Procedures with Recompile option:

When you create a stored procedure using the WITH RECOMPILE option, it gets a brand new execution plan every time it runs. This can be good for high performance queries because they get a plan perfect for the variables that are passed in. However, this causes increased CPU time due to compiling the execution plan over and over.

Option 1:

CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT)

WITH RECOMPILE

AS

SELECT *



FROM dbo.tblPerson



WHERE Age >= @MinAge AND Age <= @MaxAge

GO

Option 2:

EXEC dbo.PersonAge 65,70 WITH RECOMPILE

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.

Note: Recompilation is not necessarily the best solution.

Physical Representation in tempdb

There’s a common misconception that only temporary tables have a physical representation
in tempdb and that table variables reside only in memory. This isn’t true.

Both temporary tables and table variables have a physical representation in tempdb.

You can find entries in the sys.objects view for the internal tables that SQL Server creates
in tempdb to implement your temporary tables and table variables.

As an example, the

following code creates a temporary table called #T1 and then queries the sys.objects view in

tempdb looking for table names starting with #.

CREATE TABLE #T1
(
col1 INT NOT NULL
);

INSERT INTO #T1(col1) VALUES(10);

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

DROP TABLE #T1;

GO

This code generated the following output in the test system:
name
----------
#T1
____________________________000000000018

As mentioned earlier, SQL Server internally adds a suffix to the user-assigned table names
to prevent name conflicts in case multiple sessions create a temporary table with the same
name.

The following code demonstrates how to declare a table variable and then query the
sys.objects view.

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);

INSERT INTO @T1(col1) VALUES(10);

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

When this code ran on the test system, it produced the following output.
name
----------

#BD095663

As you can see, SQL Server created a table in tempdb to implement the table variable you
declared.

A common question is whether table expressions such as common table expressions (CTEs)
also get persisted like temporary tables and table variables. The answer is no. When SQL
Server optimizes a query against a table expression, it unnests the inner query’s logic and interacts
directly with the underlying tables. This means that unlike temporary tables and table
variables, table expressions have no physical side to them.

FORMAT Function:

FORMAT ( value, format [, culture ] )

For example, NULL is returned if the value specified in format is not valid.

FORMAT() function:

Well I’ve used this function with only dates now and I’m quite happy with it. Now to convert a date to a different format you don’t have to CAST/CONVERT your datetime with lot of different options you don’t memorize or remember. With this new function you can format your dates or datetime values in various formats and in different langauges.
Let’s check this:

SELECT

    GETDATE()                                           AS DateToday,

    FORMAT (GETDATE(), 'D')                             AS DateFormatted, 

    FORMAT (GETDATE(), 'dd/MM/yyyy (MMM)')              AS Date_ddmmyyyyMON, 

    FORMAT (CAST('2012-02-01' AS DATE), 'MMM dd yyyy')  AS Date_MONddyyyy, 

    FORMAT (CAST('2012-02-01' AS DATE), 'MMMM dd yyyy') AS Date_Monthddyyyy;

DECLARE @someDate DATETIME

SET @someDate = '2012-02-01 14:54:39.300' 

SELECT

    FORMAT (@someDate, 'D', 'de')       AS DateNew_German,

    FORMAT (@someDate, 'f', 'fr')       AS DateNew_French,

    FORMAT (@someDate, 'D', 'es-US')    AS DateNew_Snanish,

    FORMAT (@someDate, 'f', 'zh')       AS DateNew_Chinese,

    FORMAT (@someDate, 'D', 'ar')       AS DateNew_Arabic,

    FORMAT (@someDate, 'f', 'hi-IN')    AS DateNew_Hindi;

Output:-

The 3rd parameter Culture is actaully used to specify the locale and is optional. You can use different Cultures to modify your datetimes in different languages and formats as shown above..

CONCAT() function:

as its name suggests helps combine values of columns into a single string value. Well you can also do this by using “+” operator and combine columns. But what about NULL values and datatype conversion. You can also do it but what if somehow you miss. This function takes care aboout all these things, NULL values and datatype conversion.
Let’s check this:

USE [AdventureWorks2012]

GO

-- Handling NULL implicitly:

SELECT TOP 10

    -- Combining columns by using + operator (NULL values are not handled):

    [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS 'FullName',

    -- Combining columns by using + operator by handling NULL values explicitly:

    ISNULL([FirstName],'') + ' ' + ISNULL([MiddleName],'') + ' ' + ISNULL([LastName],'') AS 'FullName_with_ISNULL',

    -- Using CONCAT() function (NULL values are implicitly handled):

    CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS 'FullName_with_CONCAT'

FROM [Person].[Person]

Output:-

So, the output above lists NULL values when you combine multiple columns with NULL values.

The 2nd column lists out expected results as we’ve handled NULL values manually. The 3rd column

also results expected results as NULLs are getting handled implicitly by the CONCAT() function.

Now, let’s check how CONCAT() handles Datatype Conversion:

-- Combining columns of different datatypes, gives error:

SELECT TOP 10

    BusinessEntityID + LastName + EmailPromotion + ModifiedDate AS PersonDetailsFROM [Person].[Person]

Error Message:

Msg 245, Level 16, State 1, Line 1


Conversion failed when converting the nvarchar value 'Sánchez' to data type int.

-- CONCAT() Handling Datatype conversion implicitly:

SELECT TOP 10

    CONCAT(BusinessEntityID, LastName, EmailPromotion, ModifiedDate) AS PersonDetails

FROM [Person].[Person]

Output:-

PersonDetails


1Sánchez0Feb  8 2003 12:00AM
2Duffy1Feb 24 2002 12:00AM
3Tamburello0Dec  5 2001 12:00AM
4Walters0Dec 29 2001 12:00AM
5Erickson0Jan 30 2002 12:00AM
6Goldberg0Feb 17 2002 12:00AM
7Miller2Mar  5 2003 12:00AM
8Margheim0Jan 23 2003 12:00AM
9Matthew0Feb 10 2003 12:00AM
10Raheem2May 28 2003 12:00AM

Logical Functions:

-- CHOOSE()

CHOOSE ( index, val_1, val_2 [, val_n ] )

Returns the item at the specified index from a list of values in SQL Server

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; -- Developer

SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh

SELECT CHOOSE(2,46,24,46,64,48,79) AS Result ---------24

GO

-- IIF()

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; -- TRUE

DECLARE @a char = 'Coimbatore';

DECLARE @b char = 'Mumbai';

SELECT IIF ( @a = @b, 'TRUE', 'FALSE' ) AS Result; -- FALSE

-- PARSE()

SELECT PARSE ('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;

Result:

2010-12-13 00:00:00.0000000

SELECT PARSE ('€345,98' AS money USING 'de-DE') AS Result;

Result:

345.98

select FORMAT(CAST(GETDATE() AS DATE),'MMMM dd yyyy')

Result:

April 19 2016

SELECT PARSE(FORMAT(CAST(GETDATE() AS DATE),'MMMM dd yyyy') as DATETIME USING 'en-US') AS Result

Result:

2016-04-19 00:00:00.000

GO

--TRY_CAST()

SELECT

CASE WHEN TRY_CAST (5 AS INT) IS NULL

    THEN 'Cast failed'

    ELSE 'Cast succeeded'

END AS Result;

GO

Result:

Cast succeeded

--TRY_CONVERT()

SELECT

CASE WHEN TRY_CONVERT(int,6) IS NULL

    THEN 'Cast failed'

    ELSE 'Cast succeeded'

END AS Result;

GO

Result:

Cast succeeded

-- TRY_PARSE()

SELECT

CASE WHEN TRY_PARSE ('Aragorn' AS decimal using 'sr-Latn-CS') IS NULL

THEN 'True'

ELSE 'False'

END

AS Result;

GO

Note:

the TRY_CAST(), TRY_CONVERT() & TRY_PARSE() functions added exception handling to the existing CAST(), CONVERT() and new PARSE() function respectively. Now developers can fully rely on these 2 functions while type converting values and defaulting them to something else on any exception.

Analytic Functions:

USE AdventureWorks2008R2;

GO

-- FIRST_VALUE() and LAST_VALUE()

SELECT

    Name, ListPrice, ProductSubcategoryID,

    FIRST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive,

    LAST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive

FROM Production.Product;

-- LAG()

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression

The value to be returned based on the specified offset.

It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.
offset
The number of rows back from the current row from which to obtain a value.

If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression. 

SELECT

    BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

    LAG (SalesQuota, 1, 0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

(OR)

SELECT

    BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

    LAG (SalesQuota) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

-- LEAD()

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression

The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.
offset
The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression. 

SELECT

    TerritoryName, BusinessEntityID, SalesYTD,

    LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales

FROM Sales.vSalesPerson

WHERE TerritoryName IN (N'Northwest', N'Canada')

ORDER BY TerritoryName;

(OR)

SELECT

    TerritoryName, BusinessEntityID, SalesYTD,

    LEAD (SalesYTD) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales

FROM Sales.vSalesPerson

WHERE TerritoryName IN (N'Northwest', N'Canada')

ORDER BY TerritoryName;

Note:
The LAG() and LEAD() functions are already in Oracle and by introducing them in MS SQL Server’s Denali version is very much welcomed by TSQL developers. Plus the addition of FIRST_VALUE(), LAST_VALUE(), PERCENTILE_XXX(), etc will really add value and provide zeal to programmers in TSQL development, just like Ranking functions and Cube/Rollup did in 2005(08).

Some other new functions introduced are:
– CUME_DIST()
– PERCENTILE_CONT()
– PERCENTILE_DISC()
– PERCENT_RANK()

MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Sample Test Practice Solutions:

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime >= CONVERT(DATE, GETDATE())    
AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime = GETDATE()

SELECT COUNT(*) FROM SalesOrders  
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime = CONVERT(DATE, GETDATE())

SELECT COUNT(*) FROM OrderSales
WHERE CONVERT(VARCHAR, OrderDate, 112) = CONVERT(VARCHAR, GETDATE(I, 112))

Msg 174, Level 15, State 1, Line 2
The getdate function requires 0 argument(s).

SELECT COUNT(*) FROM OrderSales
WHERE OrderDate = GETDATE()

0

SELECT COUNT(*) FROM OrderSales
WHERE OrderDate >= CONVERT(DATE, GETDATE())
AND OrderDate < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

2

So Answer is

A

SELECT COUNT(*) FROM SalesOrders  
WHERE OrderTime >= CONVERT(DATE, GETDATE())    
AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

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?

Money

Smallmoney

Float

Numeric

Ans: D
Numeric

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:

You create a view on the SalesDB using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
How should you alter this view to allow users to update data through the SalesV?
A.
You should add a CHECK constraint to the SalesV view.

B.
You should add an INSTEAD OF trigger to the SalesV view.

C.
You should add a clustered index to the SalesV view.
D.
You should add an AFTER UPDATE trigger to the SalesV view.
E.
Create a columnstore index on all columns used in the SalesV view.

CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named HRDB with tables named Staff and SalaryHistory. The Staff and SalaryHistory
tables were created using the following Transact-SQL code:
CREATE TABLE HRDB.Staff
(
StaffID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
StaffAddress nvarchar (200) NULL,
StaffCity nvarchar(50) NOT NULL,
StaffState nvarchar(50) NOT NULL,
StaffPhone varchar (10) NOT NULL,
Salary smallmoney NOT NULL
)
GO
CREATE TABLE HRDB.SalaryHistory
(
StaffID int NOT NULL,
IncreaseDate date DEFAULT GETDATE(),
PrevSalary smallmoney NOT NULL,
NewSalary smallmoney NOT NULL
)
GO
You must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members with
a JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.
What Transact SQL statement would accomplish this task?
To answer, type the correct code in the answer area.

Answer: See the explanation.

Explanation:



UPDATE Staff
SET Salary = Salary * 1.065

OUTPUT inserted.StaffID, deleted.Salary, inserted.Salary

INTO SalaryHistory(StaffID, PrevSalary, NewSalary)

WHERE JobTitle = 'Support Technician'

Sample Script for above output:

--kindly use SQL 2012 version

Use [AdventureWorks]

CREATE TABLE dbo.Staff
(
StaffID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
StaffAddress nvarchar (200) NULL,
StaffCity nvarchar(50) NOT NULL,
StaffState nvarchar(50) NOT NULL,
StaffPhone varchar (10) NOT NULL,
Salary smallmoney NOT NULL
)
GO
CREATE TABLE dbo.SalaryHistory
(
StaffID int NOT NULL,
IncreaseDate date DEFAULT GETDATE(),
PrevSalary smallmoney NOT NULL,
NewSalary smallmoney NOT NULL
)

SELECT * FROM dbo.Staff

SELECT * FROM dbo.SalaryHistory

INSERT INTO dbo.Staff
select 5555,'kirish','anand','Support Technician','MUMBAI','PUNE','MP','9209202022','75000'
UNION ALL
select 4678,'Kulip','prasad','EEE','MUMBAI','CHNI','TN','9209202022','89000'
UNION ALL
select 5687,'nagaru','chand','Support Technician','MUMBAI','PUNE','MP','9209202022','65000'
UNION ALL
select 6687,'dileep','kurkar','ECE','MUMBAI','PUNE','TN','9209202022','44000'
UNION ALL
select 9687,'lakesh','saran','Support Technician','KOCHI','KOC','KER','9209202022','45000'
UNION ALL
select 6666,'ashok','bansal','IT','COIMBATORE','CBE','TN','9209202022','95000'

INSERT INTO dbo.SalaryHistory(StaffID,IncreaseDate,PrevSalary,NewSalary)

select 4678,'2014-05-12 14:25:23.810','85000','89000'
UNION ALL
select 5687,'2015-03-27 10:23:11.910','62000','65000'
UNION ALL
select 6687,'2015-11-14 12:25:24.610','40000','44000'
UNION ALL
select 96877,'2015-05-20 21:23:12.411','40000','45000'

SELECT * FROM dbo.Staff

SELECT * FROM dbo.SalaryHistory

UPDATE Staff set Salary=Salary*1.605
OUTPUT Inserted.StaffID,Deleted.Salary,Inserted.Salary
INTO dbo.SalaryHistory(StaffID,PrevSalary,NewSalary)
WHERE StaffID=5555 and JobTitle='Support Technician'

Other Example:
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
UPDATE TestTable
SET TEXTVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable

CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named SalesDB with tables named Customer and Orders. The Customer and Orders
tables were created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL
)
GO
You must now create an OrderDetails table as shown in the following database diagram:

The TotalPrice column must be a computed column based on the product of the UnitPrice and
Quantity columns and the data must be stored in the table.
How would you create this table?
To answer, type the correct code in the answer area.

Answer: See the explanation.

Explanation:



CREATE TABLE SalesDB.OrderDetails

(

OrderID int NOT NULL,

ProductID int NOT NULL,

Quantity int NOT NULL,

UnitPrice money NOT NULL,

TotalPrice AS (Quantity * UnitPrice) PERSISTED

)

Sample code:

CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
SalesRepID int NOT NULL
)

Insert into SalesDB.Customers
SELECT 7897,'ANKIN','SIMRI','USA','ST LANE NEAR','BOSTON','4','22','1-1-1141'
UNION ALL
SELECT 9658,'KIROU','WRGSS','UK','THOMAS RIVER','LONDON','6','61','8-9-6964'

select * FROM SalesDB.Customers

Insert into SalesDB.Orders
SELECT 75,7897,GETDATE()-6,GETDATE()+2,6
UNION
SELECT 76,9658,GETDATE()-4,GETDATE()+2,6


select * FROM SalesDB.Orders

CREATE TABLE SalesDB.OrderDetails
(
OrderID int NOT NULL PRIMARY KEY,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice Money not null,
TotalPrice as (Quantity*UnitPrice) PERSISTED
)

INSERT INTO SalesDB.OrderDetails
SELECT 75,001,6,25.36
union all
SELECT 76,003,4,36.36

SELECT * from SalesDB.OrderDetails

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The relevant part of the ProductsDB is shown in the following database
diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:
<row ProductID=”1001″ Product=”Product Name”, Price=”24.99″ InStock=”16″
Supplier=”Company Name” Contact=”Contact Name” Phone=”346 959 2215″ />
Which of the following SELECT statement would you write?

A.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML RAW

B.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML
C.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML AUTO
D.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML AUTO, RAW

Soution:
Ans A:

Let Discuss other option

Option A FOR XML RAW appears means it should be ROW comes it is the answer

if FOR XML RAW Elements means it should be separate closed < > brackets appear

Option B FOR XML syntax error come.

Option B if XML AUTO means select column table name should be appear in output

Option D XML AUTO,RAW not valid for this output syntax error come

if XML PATH means table comes

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB is shown in the following database diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:
<Invoices InvoiceID=”1001″ Date=”2012-10-01T00:00:00″, Value=”1000.00″ Customer=”Customer
Name” ShippedTo=”Customer City” />
Which of the following SELECT statement would you write?

A.
SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName
AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in
INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID
WHERE cu.CustomerID = 1001
FOR XML RAW('Invoices')

B.
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS
[Name], CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML
C.
SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value],
Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML AUTO
D.
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS
[Name], CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML AUTO, RAW

Solution:

Ans: Both Option A and C have same result:

FOR XML WITH TAG see below

FOR XML AUTO

But actual original FOR XML Auto it is not valid it gives syntax

but actual original FOR XML RAW Without tag see below

Option A is Correct answer Author have missed table tag as FOR XML RAW(‘Invoices ‘)

SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName
AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in
INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID
WHERE cu.CustomerID = 1001
FOR XML RAW(‘Invoices ‘)

Option C is not correct answer because if FOR XML AUTO Means end of output should be end with but in our case it was ShippedTo=”Customer City” />
so it is proved that OPTION A is correct.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database diagram:

You need to write a Transact-SQL query that displays all the products received by a single
supplier in the following XML format:
<Suppliers SupplierID=”22″ Company=”Company Name” ContactNumber=”510 250 6400″>
<Products ProductID=”100″ UnitPrice=”249.00″ UnitsInStock=”7″ />
<Products ProductID=”118″ UnitPrice=”559.00″ UnitsInStock=”12″ />
</Suppliers>
Which of the following SELECT statement would you write?
A.
SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID,
p.UnitPrice, p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
WHERE s.SupplierID = 22
FOR XML RAW
B.
SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID,
p.UnitPrice, p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
WHERE s.SupplierID = 22
FOR XML
C.
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE Suppliers.SupplierID = 22
FOR XML AUTO

D.
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE Suppliers.SupplierID = 22
FOR XML AUTO, RAW

Solution:

Ans Option C

There is no FOR XML AUTO,RAW and FOR XML clause only
It gives Syntax Error as see below
Incorrect syntax near ‘XML’.

CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes
over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to
have a rank of 2, and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.

Explanation:
SELECT TOP 30 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes
FROM WeeklyVotes
GROUP BY PlayerName

Sample Screen shots:

CREATE TABLE WeeklyVotes(Week int,PlayerName Nvarchar(300),Votes int)

Insert into WeeklyVotes
SELECT 1,'Sachin',56
union all
SELECT 1,'WATSON',52
UNION ALL
SELECT 1,'GAYLE',67
UNION ALL
SELECT 1,'KOHLI',78
UNION ALL
SELECT 2,'Sachin',65
union all
SELECT 2,'WATSON',46
UNION ALL
SELECT 2,'GAYLE',78
UNION ALL
SELECT 2,'KOHLI',98
UNION ALL
SELECT 3,'Sachin',95
union all
SELECT 3,'WATSON',86
UNION ALL
SELECT 3,'GAYLE',98
UNION ALL
SELECT 3,'KOHLI',97
UNION ALL
SELECT 4,'Sachin',95
union all
SELECT 4,'WATSON',99
UNION ALL
SELECT 4,'GAYLE',99
UNION ALL
SELECT 4,'KOHLI',99

SELECT * FROM WeeklyVotes

To see only for 4 players see below

SELECT TOP 4 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes,SUM(Votes) [SumofVotes]
FROM WeeklyVotes
GROUP BY PlayerName

Also we can achive using Row_number() function as see below

SELECT TOP 4 PlayerName,ROW_NUMBER() OVER(ORDER BY AVG(Votes) DESC) AS AVGvotes,Sum(Votes) [SUMofVotes]
FROM WeeklyVotes
GROUP BY PlayerName

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales
amount for each of ABC.com’s 20 sales representitives.
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.

so this case because of this "with the subsequent rank being skipped" it should be Rank()

Rank() too and not DENSE_RANK() the answer for that specific question.
RANK() Example = 1233557888
DENSE_RANK() Example = 12223344445677

So Answer is OPTION A The RANK( ) OVER function.
Thats is it

You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that returns the cricket player that received the most
votes for each week, as well as the number of votes they received.
Which of the following SELECT statement would accomplish this task?
A.
SELECT PlayerName, Votes
FROM ( SELECT PlayerName, Votes,
RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank
FROM WeeklyVotes ) AS tmp
WHERE Rank = 1

B.
SELECT PlayerName, Votes
FROM ( SELECT PlayerName, Votes,
RANK() OVER (PARTITION BY Week ORDER BY Votes DESC) AS Rank FROM WeeklyVotes)
AS tmp
WHERE Rank = 1

C.
SELECT PlayerName, Votes
FROM ( SELECT TOP 1 PlayerName, Votes,
RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank
FROM WeeklyVotes
ORDER BY Rank) AS tmp
D.
SELECT PlayerName, Votes
FROM ( SELECT TOP 1 PlayerName, Votes,
RANXO OVER (PARTITION BY PlayerName ORDER BY Votes DESC) AS Rank
FROM WeeklyVotes
ORDER BY Rank) AS tmp

Ans: OPTION B

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that stores the total number of a product that ABC.com has.
What Transact-SQL statements would accomplish this task?

A.
ALTER TABLE Inventory
ADD TotalProducts AS (InStore + InWarehouse) PERSISTED

B.
ALTER TABLE Inventory
ADD TotalProducts int SPARSE NOT NULL
C.
ALTER TABLE Inventory
ADD TotalProducts AS SUM (ALL) OVER (GROUP BY InStore, InWarehouse) PERSISTED
D.
DROP TABLE Inventory
GO
CREATE TABLE Inventory
(
ProductID int NOT NULL PRIMARY KEY,
InStore int NOT NULL,
InWarehouse int NOT NULL,
TotalProducts AS SUM (InStore, InWarehouse) PERSISTED
)

ABC.com has a SQL Server 2012 database instance that hosts a database named ComDB. The
ComDB database has a table named Partners that was created using the following Transact-SQL
code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL,
[CompanyName] [nvarchar] (50) NOT NULL,
[Location] [nvarchar] (50) NOT NULL,
[ContactName] [nvarchar] (50) NOT NULL,
[Email] [nvarchar] (50) NOT NULL,
[Phone] [nvarchar] (10) NOT NULL,
CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)
ON PRIMARY
)
You want to create a FOR UPDATE trigger that will track changes to the ContactName and Phone
columns.
Which of the following statements should you use in the trigger definition?
A.
IF COLUMNS_UPDATED (ContactName, Phone)
B.
IF COLUMNS_UPDATED (ContactName) OR COLUMNS_UPDATED (Phone)
C.
IF UPDATED (ContactName, Phone).

D.
IF UPDATED (ContactName) OR UPDATED (Phone)

Actually there is type Error it should be

IF UPDATE (ContactName) OR UPDATE (Phone)

but all other answers are incorrect, so have to choose “D” as correct.

like as Option C IF UPDATE (ContactName, Phone) also not correct

It gives Incorrect syntax near ','.

Sample code:

CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL,
[CompanyName] [nvarchar] (50) NOT NULL,
[Location] [nvarchar] (50) NOT NULL,
[ContactName] [nvarchar] (50) NOT NULL,
[Email] [nvarchar] (50) NOT NULL,
[Phone] [nvarchar] (10) NOT NULL,
CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)
)

CREATE TABLE dbo.PartnerAudit([ContactName] [nvarchar] (50) NOT NULL, [Phone] [nvarchar] (10) NOT NULL,ModifiedDate Datetime)

ALTER Trigger TRG_Update_ContactName_Phone on dbo.Partners
FOR UPDATE
as
--IF COLUMNS_UPDATED (ContactName, Phone)
--IF COLUMNS_UPDATED (ContactName) OR COLUMNS_UPDATED (Phone)
--IF UPDATE (ContactName, Phone)
IF UPDATE (ContactName) OR UPDATE (Phone)
begin
Print 'ContactName/Phone Column Updated on Partners'
Insert into dbo.PartnerAudit(ContactName,Phone,ModifiedDate)
Select ContactName,Phone,Getdate() From deleted
End

Insert into dbo.Partners
select 0154,'Company A','Golden Company','Coimbatore','CompanyA@Company.com','56464'
UNION ALL
select 0158,'Company B','Weldier Company','Salem','CompanyB@Company.com','57884'

SELECT * FROM dbo.Partners

SELECT * FROM dbo.PartnerAudit

Update dbo.Partners SET Phone='9658912345' where CompanyID=154

You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com.
You need to optimize a very large database table that contains several million rows of data by
designing a view based on the table. The view must allow users to perform aggregations on
several columns.
How should you configure the view to ensure optimal performance?

A.
You should create the view as an indexed view.

B.
You should create a clustered index on the view.
C.
You should make use of a stored procedure to return that data.
D.
You should make use of a table-valued function.

You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with a table named Partners.
The Partners table was created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL,
[Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL,
[Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL
)
You develop a new table named Events using the following Transact-SQL code:
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [varchar] (2500),
[EventCordinator] [varchar] (150) NOT NULL
)
How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?

A.
You should add a Foreign Key Constraint on the Events table.

B.
You should add a Check Constraint on the Events table.
C.
You should add a Unique Constraint on the Events table.
D.
You should specify Events.CompanyID as a spars column.
E.
You should change the Events table to a partitioned table.

ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB.
You have created a view using the following Transact-SQL code:
CREATE VIEW ABCCommunications
(Type, CompanyID, CompanyName, Location, ContactName, Email, Phone)
AS
SELECT ‘Clients’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Client’
SELECT ‘Partners’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Partner’
SELECT ‘Guests’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Guests’
GO
You want the view to be used to edit all columns except the CompanyID, CompanyName and
Location columns.
What should you implement on the view?
A.
You should consider implementing an AFTER UPDATE trigger.
B.
You should consider implementing an Index.

C.
You should consider implementing an INSTEAD OF UPDATE trigger.

D.
You should consider implementing a CHECK constraint.

Solution:
OPTION C
Actually VIEW Has UNION ALL missing in code.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:

B.
You should add an INSTEAD OF trigger to the SalesV view.

C.
You should add a clustered index to the SalesV view.
D.
You should add an AFTER UPDATE trigger to the SalesV view.
E.
Create a columnstore index on all columns used in the SalesV view.

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:

Does not return an error
Closes all opened transactions

Which Transact-SQL statement should you use?

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@ERROR != 0     
ROLLBACK TRANSACTION;
THROW;
END CATCH

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@TRANCOUNT = 0     
ROLLBACK TRANSACTION;
THROW;
END CATCH

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@ERROR != 0     
ROLLBACK TRANSACTION;
END CATCH

BEGIN TRANSACTION   
BEGIN TRY     
EXEC dbo.ModifyData     
COMMIT TRANSACTION   
END TRY   
BEGIN CATCH     
IF @@ TRANCOUNT = 0     
ROLLBACK TRANSACTION;
END CATCH

Solution:

So Answer is Option D

sample code of modify data as see below

CREATE proc dbo.ModifyData(@Md int)
 as
 begin

 INSERT INTO dbo.MyTableALL VALUES (24,38,36,96,21,@Md,32);

Update  dbo.MyTableALL set Myintcolumn=Myintcolumn/@Md

   SELECT * FROM dbo.MyTableALL

 end

When you run Both those Option see what will result

OPTION A gives

 (1 row(s) affected)

(0 row(s) affected)
Msg 8134, Level 16, State 1, Procedure ModifyData, Line 10
Divide by zero error encountered.

OPTION B gives

(1 row(s) affected)

(0 row(s) affected)
Msg 8134, Level 16, State 1, Procedure ModifyData, Line 10
Divide by zero error encountered.

 OPTION C gives

(1 row(s) affected)

(0 row(s) affected)

 OPTION D gives

 Command(s) completed successfully.

So Answer is Option D

SELECT * FROM dbo.MyTableALL

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and
TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

A: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END

B: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END

C: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END

D: CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN END
END

Solution:

Correct answer is OPTION C

see Sample Table and Data

Let see Option A is correct or not

After created Trigger on option A u can't able to insert other values other than that two tables

you can see 0 row affected message came.

When you have select rows you can see no rows inserted.

When we go to check Option B is incorrect

Msg 257, Level 16, State 3, Procedure TrgValidateAccountNumberOptionB, Line 11
Implicit conversion from data type smalldatetime to int is not allowed. Use the CONVERT function to run this query.

because

INSERT INTO dbo.Transactions(TransactionId,AccountNumber,Amount,TransactionDate)

above code (TransactionId,AccountNumber,Amount,TransactionDate) missing in option B and option C

it should needed to create triggers

When we go to check Option D it was Instead of Insert Trigger. So even correct values also not able to insert it

So OPTION C is correct

So Correct Answer is OPTION C

Sample code

    Create table SavingAccounts(AccountNumber Nvarchar(10),Name Nvarchar(300))

    Create table LoanAccounts(AccountNumber Nvarchar(10),LoanAccountName Nvarchar(300))

    Insert into SavingAccounts
    SELECT '0546798512','XYZ'
    union all
    SELECT '0546748512','UTR'
    Union all
    SELECT '0546798512','POK'
    union all
    SELECT '0546748512','WSE'

    Insert into LoanAccounts
    SELECT '1234567890','XYZ'
    union all
    SELECT '1234567891','UTR'
    Union all
    SELECT '1234567892','POK'
    union all
    SELECT '1234567893','WSE'

    Create table Transactions (TransactionId int,AccountNumber Nvarchar(10),TransactionDate smalldatetime,Amount int)

    select * from SavingAccounts

    select * from LoanAccounts

    Select * from Transactions



        CREATE TRIGGER TrgValidateAccountNumberOptionA

    ON Transactions

    FOR INSERT

    AS
    BEGIN

    IF EXISTS (

    SELECT AccountNumber FROM inserted EXCEPT

    (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts))

    BEGIN

    ROLLBACK TRAN

    END

    END

    Insert into Transactions SELECT 9,'9234567899',GETDATE(),36

    Insert into Transactions SELECT 9,'1234567890',GETDATE(),36



    CREATE TRIGGER TrgValidateAccountNumberOptionB

    ON dbo.Transactions

    FOR INSERT

    AS

    BEGIN

    INSERT INTO dbo.Transactions--(TransactionId,AccountNumber,Amount,TransactionDate)

    SELECT TransactionId,AccountNumber,Amount,TransactionDate FROM inserted

    WHERE AccountNumber IN

    (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts)

    END

        CREATE TRIGGER TrgValidateAccountNumberOptionC

    ON Transactions

    INSTEAD OF INSERT

    AS

    BEGIN

    INSERT INTO Transactions(TransactionId,AccountNumber,Amount,TransactionDate)

    SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted

    WHERE AccountNumber IN

    (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts)

    END

        Create TRIGGER TrgValidateAccountNumberOptionD

    ON Transactions

    INSTEAD OF INSERT

    AS

    BEGIN

    IF EXISTS (

    SELECT AccountNumber FROM inserted EXCEPT

    (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN

    ROLLBACK TRAN END

    END

    DROP TRIGGER TrgValidateAccountNumberOptionA

    DROP TRIGGER TrgValidateAccountNumberOptionB

    DROP TRIGGER TrgValidateAccountNumberOptionC

    DROP TRIGGER TrgValidateAccountNumberOptionD

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB as illustrated in the following database diagram:

ABC.com has retail stores in a few major cities across the country. The company wants a list of
Customers who live in a city that does not have a ABC.com store, along with the customer’s City.
The result set must be sorted alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?
A.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
B.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)
ORDER BY StoreCity

C.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity

D.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores)
ORDER BY StoreCity

Sample table and solution see below

so Answer is OPTION B,OPTION D if you should need to choose one you can choose OPTION D.

transactions table see below

Loanaccounts table see below

let see options which is correct

OPTION A is incorrect bocs it gives incorrect syntax

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXISTS'.

SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity

it should be like this

SELECT CustomerName, CustomerCity
FROM Customers
WHERE NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity

Also it should be like this give correct result

    Select * from dbo.Transactions T
    where NOT EXISTS (select L.AccountNumber from LoanAccounts L WHERE T.AccountNumber=L.AccountNumber)
    order by AccountNumber

so option A is not correct

option B is correct

Select * from dbo.Transactions
    where AccountNumber NOT IN (select AccountNumber from LoanAccounts)
    order by AccountNumber

Sample code:

Create table SavingAccounts(AccountNumber Nvarchar(10),Name Nvarchar(300))

    Create table LoanAccounts(AccountNumber Nvarchar(10),LoanAccountName Nvarchar(300))

    Insert into SavingAccounts
    SELECT '0546798512','XYZ'
    union all
    SELECT '0546748512','UTR'
    Union all
    SELECT '0546798512','POK'
    union all
    SELECT '0546748512','WSE'

    Insert into LoanAccounts
    SELECT '1234567890','XYZ'
    union all
    SELECT '1234567891','UTR'
    Union all
    SELECT '1234567892','POK'
    union all
    SELECT '1234567893','WSE'

    Create table Transactions (TransactionId int,AccountNumber Nvarchar(10),TransactionDate smalldatetime,Amount int)

    select * from SavingAccounts

    select * from LoanAccounts

    Select * from Transactions

Select * from dbo.Transactions T
    where NOT EXISTS (select L.AccountNumber from LoanAccounts L WHERE T.AccountNumber=L.AccountNumber)
    order by AccountNumber

    Select * from dbo.Transactions
    where AccountNumber NOT IN (select AccountNumber from LoanAccounts)
    order by AccountNumber

    Select * from dbo.Transactions
    where AccountNumber <> ALL (select AccountNumber from LoanAccounts)
    order by AccountNumber

    Select * from dbo.Transactions
    where AccountNumber <> ANY (select AccountNumber from LoanAccounts)
    order by AccountNumber

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure with several databases. You have permissions on two of these databases, namely,
CommDB and SalesDB.
You need to develop a stored procedure named dbo.ABC_addEntry in a database named
AssetsDB. The dbo.ABC_addEntry stored procedure will run stored procedures in CommDB and
SalesDB.
How should you design the stored procedure so that callers that do not paver permissions on
CommDB and SalesDB can run the dbo.ABC_addEntry stored procedure successfully?
A.
You should configure the stored procedure to call the xp_cmdshell extended stored procedure.
B.
You should configure the stored procedure to call the sp_configure system stored procedure.
C.
You should assign users permission to the stored procedure.
D.
You should include the EXECUTE AS CALLER clause when creating the stored procedure.

E.
You should include the EXECUTE AS OWNER clause when creating the stored procedure.

CORRECT TEXT
You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with tables named Partners and Events. These
tables were created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL,
[Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL,
[Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL
)
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [nvarchar] (MAX),
[EventDate] [nvarchar] (50) NOT NULL,
[EventCordinator] [nvarchar] (150) NOT NULL
)
You add a foreign key relationship between the two tables on the CompanyID column.
You need to develop a stored procedure named sp_coEvents that retrieves CompanyName for all
partners and the EventDate for all events that they have coordinated.
To answer, type the correct code in the answer area.

     SELECT * FROM [dbo].[Partners]

   SELECT * FROM [dbo].[Events]

   ALTER TABLE dbo.Events ADD Constraint C_FK FOREIGN KEY(CompanyID) REFERENCES Partners(CompanyID)

Important FOREIGN KEY(Column Name)----------Important to remember

Explanation:

CREATE PROC sp_coEvents
   AS
   BEGIN
   SELECT P.CompanyName,E.EventDate
   froM dbo.Partners P
   join dbo.Events E on P.CompanyID=E.CompanyID
   end


You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure with a very large database named SalesDB. You create a new table named
SalesHistory that will hold historical data from the SalesDB database.
You need to perform a batch update from the SalesDB database to the SalesHistory table. You
want the status information from the batch process to be logged to a SQL Server table that must
be created by the batch process.
How would you accomplish this task?
A.
You should make use of the FORMAT function.
B.
You should make use of the CONVERT function.

C.
You should make use of a scalar user-defined function.

D.
You should make use of an inline function.
E.
You should make use of a table-valued function.
F.
You should make use of a stored procedure.

F is correct.
You can make executing the batch process one of the steps in the stored procedure.
A requirement is that the status of the batch must be logged to “a SQL Server table that must be created by the batch process” – functions cannot create tables but stored procedures can.

You are developing a SQL Server 2012 database for ABC.com. You need to create a computed
column that returns the data by referencing another table using an INNER JOIN.
How would you accomplish this?
A.
You should make use of the FORMAT function.

B.
You should make use of a scalar user-defined function.

C.
You should make use of an inline function.
D.
You should make use of a table-valued user-defined function.
E.
You should make use of a stored procedure.

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.

Answer is Option A

Kindly see below Inside View You cant able to Declare User Defined Data Type it is true

Also you can see View can able to use Inside UDF without any issue

Also Table Valued UDF can able to create it without any parameter defined as input see below

OPTION D is not correct because

You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported

for more ref see below link
https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx

create type cidType as Table
    (
    CompanyID int
    )

    declare @ctvp cidType

    insert into @ctvp(CompanyID)
    values(154)

    SELECT E.CompanyID FROM
    dbo.Partners P
    join dbo.Events E on P.CompanyID=E.CompanyID
    join @ctvp cvp on cvp.CompanyID=P.CompanyID

when you use in normal SQL Statement TVP can use it but TVP cant use in Views but we can use it in SP

Limitations of Table-Valued Parameters

There are several limitations to table-valued parameters:

You cannot pass table-valued parameters to CLR user-defined functions.
Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.
Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.
You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

Facts about SQL Concepts with Example

*UDF Will allow only Select statement, it will not allow us to

use DML statements.

*Table Variable Can use it inside UDF But Temporary Tables can't use inside UDF

*Transactions not allowed inside UDF

*UDF Can use inside/within SP But SP cant use inside/within UDF

*We can use UDF Inside Views Also We can use View inside UDF

*View did n't Accept Parameter (If you achieved you can Table

Valued function to achieve that)

*We can't use SP inside View but We can use View inside SP

You work as a SQL Server 2012 database developer at ABC.com. TesABCig.com has a database
named DataDB.
You are developing a complex stored procedure named sp_updater that will use a single
transaction to update several tables in the DataDB database.
You are concerned about data integrity and incomplete updates should the sp_updater stored
procedure cause a run-time error.
To mitigate this potential problem you want the transaction to terminate and the transaction to be
rolled back if the sp_updater stored procedure raises a run-time error.
How would you accomplish this task?

A.
You should make use of the SET XACT_ABORT ON statement in the stored procedure.
B.
You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
C.
You should make use of a LOOP hint in the stored procedure.
D.
You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
E.
You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.

You work as a SQL Server 2012 database developer at ABC.com.
ABC.com has a database SalesDB with a large Orders table. You create a heap named OldData
that will store historical data from the Orders table.
You need to write a Transact-SQL query that will insert rows of data from the Orders table that are
marked as closed and are more than six months old.
Which of the following table hints should you use in your query if you want to optimize transaction
logging and locking for the query?
A.
You should make use of the READPAST hint.
B.
You should make use of the HOLDLOCK hint.
C.
You should make use of the READCOMMITTED hint.
D.
You should make use of the NOLOCK hint.

E.
You should make use of the TABLOCK hint.
F.
You should make use of the UPDLOCK hint.

You are employed as a SQL Server 2012 database developer at ABC.com. You have a stored
procedure that is executed quite often. The stored procedure joins data from two tables.
ABC.com users report that the stored procedure takes a long time to execute. You analyze the
query plan and find that the stored procedure often makes use of table scans rather than indexes
when the estimated rows do not match the actual rows on one of the tables.
How would you optimize the performance of the stored procedure?
A.
You should make use of the KEEPIDENTITY table hint in the stored procedure.
B.
You should make use of the KEEPDEFAULTS table hint in the stored procedure.
C.
You should make use of the IGNORE_CONSTRAINTS table hint in the stored procedure.

D.
You should make use of the FORCESEEK table hint in the stored procedure.
E.
You should update statistics on the tables queried by the stored procedure.

You work as a SQL Server 2012 database developer at ABC.com. You are developing a database
driven Web application. The application executes a store procedure based on the location of the
web user.
The location of the Web user is determined by IP Geolocation. You want to develop a process that
will execute the correct stored procedure for every Web user based on the user’s location.
How would you accomplish this?
A.
You should make use of a foreach SQLCLR statement.
B.
You should make use of a scalar user-defined function.
C.
You should make use of an inline function.

D.
You should make use of a cursor.

You work as a SQL Server 2012 database administrator at ABC.com. You have developing a
database named WebAnDB that will be used by a web site analysis application. The WebAnDB
database has a table named Visitors that stores date and time data in a column named Accessed.
You must now develop a stored procedure that will insert data into the Accessed column. You
want the stored procedure to store time zone data as well.
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 TODATETIMEOFFSET function.

E.
You should make use of the SET FORCEPLAN ON statement in the stored procedure.
F.
You should make use of a cursor.

You work as a SQL Server 2012 database developer at ABC.com.
You are developing a database that will be used by a Web application. The database will store
small multimedia files in several tables. The largest multimedia file is 975 kB. These multimedia
files will be retrieved quite often.
How would you store these files?
A.
You should make use of a cursor.
B.
You should make use of a row-level compression.
C.
You should make use of the FileStream data type.

D.
You should make use of the VARBINARY data type.

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.

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that is the sum of values in the InStore and InWarehoue
columns for each product.
What Transact SQL statement would accomplish this task?

A.
ALTER TABLE Inventory
ADD TotalProducts AS (InStore + InWarehouse)

B.
ALTER TABLE Inventory
ADD TotalProducts int SPARSE NOT NULL
C.
ALTER TABLE Inventory
ADD TotalProducts AS SUM (ALL) OVER (GROUP BY InStore, InWarehouse)
D.
DROP TABLE Inventory
GO
CREATE TABLE Inventory
(
ProductID int NOT NULL PRIMARY KEY,
InStore int NOT NULL,
InWarehouse int NOT NULL,
TotalProducts AS SUM (InStore, InWarehouse)
)

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:

You create a view on the SalesDB database using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
You want the SalesV view to persist data to disk in order to improve performance.
How would you accomplish this task?
A.
You should add a clustered index to the SalesV view.
B.
You should create a columnstore index on all columns used in the SalesV view.

C.
You should drop and recreate the SalesV view as a system view.

D.
You should drop and recreate the SalesV view as a partitioned view.

You work as a database developer at ABC.com. You need to store Microsoft Word documents in a
SQL Server 2012 database table.
The Word documents must not be accessed by Windows applications that do not use TransactSQL queries.
How would accomplish this task?

A.
You should store the documents using the VARBINARY(MAX) datatype.

B.
You should store the documents using the VARCHAR(MAX) datatype.
C.
You should store the documents using the FILESTREAM datatype.
D.
You should store the documents using the NVARCHAR(MAX) datatype.

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB with a table named Invoices.
Application developers are developing several in-house applications that will access the Invoices
table. You need to develop a solution that will allow the applications to access the table indirectly
while still allowing them to update the Invoice table.
How would you accomplish this task?
A.
You should create a view on the Invoices table.
B.
You should create a columnstore index on all columns used by the applications.

C.
You should allow the applications access to the Invoices table via stored procedures.

D.
You should drop and recreate the Invoices table as a partitioned table.

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.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:

You need to write a Transact-SQL query that displays all Customers, whether they have invoices
or not. The query must also display the InvoiceDate for a Customer that has an Invoice.
How would you accomplish this task?
A.
You should make use of a UNION.
B.
You should make use of an INNER JOIN.
C.
You should make use of a CROSS JOIN.

D.
You should make use of an OUTER JOIN.

E.
You should make use of a CROSS APPLY.

You work as a SQL Server 2012 database developer at ABC.com. You are developing a batch
process that accepts an input parameter and allows the result set to be joined with a table.
How would you accomplish this?
A.
You should make use of a stored procedure to perform the batch process.

B.
You should make use of a table-valued user-defined function.

C.
You should make use of the SET XACT_ABORT ON statement.
D.
You should make use of a scalar user-defined function.
E.
You should make use of a schema-bound user-defined function.

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(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
B.
SELECT Location, Week, ItemsProduced,
FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites

C.
SELECT Location, Week, ItemsProduced,
LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites

D.
SELECT Location, Week, ItemsProduced,
LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
E.
SELECT Location, Week, ItemsProduced,
CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced
FROM Sites

let see OPTION A and B

let see OPTION C and D

let see OPTION E

let see More detail on OPTION C

You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure for ABC.com’s e-Commerce application.
Your stored procedure must display unique values from one column in multiple columns in the
result set.
How would you accomplish this?
A.
You should make use of the OUTER APPLY operator.
B.
You should make use of a dynamic cursor.

C.
You should make use of the PIVOT operator.

D.
You should make use of the CROSS APPLY operator.
E.
You should make use of the UNPIVOT operator.
F.
You should make use of a keyset cursor.

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB as illustrated in the following database diagram:

ABC.com has retail stores in a few major cities across the country. The company wants to
ascertain whether it would be advantageous to open a store in other cities based on feedback
from its customers.
You are required to provide the company’s CEO with a list of Customers who live in a city that
does not have a ABC.com store, along with the customer’s Phone Number and the customer’s
City, and arranged alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?
A.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
B.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)
ORDER BY StoreCity

C.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity

D.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores)
ORDER BY StoreCity

Solution:

both option B and D correct answer but we can choose OPTION D most apporpriate.

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.

You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a large database
named SalesDB.
New rows are inserted into the tables in the SalesDB database and updates to existing rows occur
on a high frequency. The inserts and updates often blocked by queries retrieving and reading
data.
How would you prevent queries that retrieve and read data from blocking queries that insert and
update data?

A.
You should make use of the SERIALIZABLE ISOLATION LEVEL.

B.
You should make use of the SNAPSHOT ISOLATION LEVEL.
C.
You should make use of the REPEATABLE READ ISOLATION LEVEL.
D.
You should make use of the READCOMMITTED ISOLATION LEVEL.
E.
You should make use of the READPAST ISOLATION LEVEL

You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure that updates rows in several tables.
You want the entire transaction to be rolled back should the stored procedure cause a run-time
error.
How would you accomplish this?

A.
You should make use of the SET XACT_ABORT ON statement in the stored procedure.

B.
You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
C.
You should make use of a LOOP hint in the stored procedure.
D.
You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
E.
You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.
F.
You should make use of RAISERROR in the stored procedure.

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 named SalesDB.
ABC.com users report that ABCApp3 is functioning sluggishly. You discover that concurrent
updates are causing blockages on the SalesDB database.
How would you ensure that the query to use the original data when updates occur?
A.
You should have the query run in the REPEATABLE READ ISOLATION LEVEL.
B.
You should have the query run in the SERIALIZABLE ISOLATION LEVEL.
C.
You should have the query run in the READCOMMITTED ISOLATION LEVEL.

D.
You should have the query run in the SNAPSHOT ISOLATION LEVEL.

E.
You should have the query run in the READPAST ISOLATION LEVEL.

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.

ou work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named SalesDB.
The SalesDB database is used by an in-house application named ABCApp3. ABC.com users
report that ABCApp3 is functioning sluggishly.
You discover that application consumes considerable memory when it runs single-use dynamic
queries against the SalesDB database. You suspect that these queries are making excessive use
of procedure cache.
How would you reduce procedure cache if you cannot create new indexes on the SalesDB
database?
A.
You should replace the queries with recursive stored procedures.
B.
You should add make use of the INCLUDE clause in the index.
C.
You should make use of the READPAST hint in the queries.
D.
You should make use of the READCOMMITTED hint in the queries.

E.
You should make use of the optimize for ad hoc workloads option.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named ABCDB.
The ABCDB database is used by an in-house application named ABCApp3 that queries a readonly table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly.
You suspect query the application uses is causing the problem. You analyze the query and
discover that column referenced in the WHERE clause is not part of the clustered index. You also
notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five
columns.
How would you improve the efficiency of this query?
A.
You should replace the query with recursive stored procedure.
B.
You should replace the COUNT (*) clause with a persisted computed column.
C.
You should create nonclustered indexes on all columns used in the query.
D.
You should create a filtered index on the column used in the WHERE clause.
E.
You should add an INCLUDE clause to the clustered index.

F.
You should create a columnstore index on all columns used in the query.

G.
You should create a unique clustered index on the column used in the WHERE clause.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database

You need to write a Transact-SQL query that displays rows of data in the following XML format:
<Suppliers>
<CompanyName>Company Name</CompanyName>
<ContactName>Contact Name</ContactName>
<Phone>453 3545 5224</Phone>
<Products>
<ProductID>10</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>559.00</UnitPrice>
<UnitsInStock>12</UnitsInStock>
</Products>
<Products>
<ProductID>132</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>59.00</UnitPrice>
<UnitsInStock>102</UnitsInStock>
</Products>
<Products>
<ProductID>259</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>599.00</UnitPrice>
<UnitsInStock>6</UnitsInStock>
</Products>
</Suppliers>
Which of the following SELECT statement would you write?
A.
SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice,
p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
FOR XML AUTO, ELEMENTS
B.
SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice,
p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
FOR XML
C.
SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName,
Suppliers.Phone, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
FOR XML AUTO
D.
SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName,
Suppliers.Phone, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
FOR XML AUTO, RAW

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data
in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

A: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p

B: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders

C: SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p

D

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders

Solution:

Correct Answer is OPTION A

Sample Code:

CREATE TABLE InternationalSalesOrders(SalesOrderId int identity(20009,20) not null, SalesAmount int)

Declare @i int
Declare @j int

set @i=1

set @j=9564

while(@i<=300000)

begin
insert into InternationalSalesOrders(SalesAmount)

SELECT @i+@j+32/@i

Set @i=@i+1

end

CREATE TABLE DomesticSalesOrders(SalesOrderId int identity(20009,20) not null, SalesAmount int)

Declare @i int
Declare @j int

set @i=1

set @j=9564

while(@i<=300000)

begin
insert into DomesticSalesOrders(SalesAmount)

SELECT @i+@j+32/@i

Set @i=@i+1

end

SELECT COUNT(*) FROM DomesticSalesOrders

SELECT count(*) FROM InternationalSalesOrders

SELECT * FROM DomesticSalesOrders D
join InternationalSalesOrders I on D.SalesOrderId=I.SalesOrderId

let see Answer is OPTION A see below

kindly OPTION B how it take result in time

kindly OPTION C how it take result in time

kindly OPTION D how it take result in time

So Answer is OPTION A

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following
tasks:

Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.

The view is frequently used in several reports. You need to improve the performance of the reports.

What should you do?

A. Convert the view into a table-valued function.

B. Convert the view into a Common Table Expression (CTE).

C. Convert the view into an indexed view.

D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

Correct Answer: C

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a
table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist
in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

A. ALTER TABLE Orders
ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer
(CustomerId)

B. ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders
(CustomerId)

C. ALTER TABLE Orders
ADD CONSTRAINT CK_Crders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))

D. ALTER TABLE Customer
ADD OrderId INT NOT NULL;
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES Orders
(CrderlD);

E. ALTER TABLE Orders
ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)

Correct Answer: A

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view
that is used to look up their email addresses and phone numbers. The view has the following
definition:

You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and

OrderDetails tables by using the following definition

You need to ensure that users are able to modify data by using the view. What should you do?

A. Create an AFTER trigger on the view.

B. Modify the view to use the WITH VIEW_METADATA clause.

C. Create an INSTEAD OF trigger on the view.

D. Modify the view to an indexed view.

Correct Answer: C

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must

meet the following requirements:

Accept the @T integer parameter.
Use one-part names to reference columns.
Filter the query results by SalesTerritoryID.
Return the columns in the same order as the order used in OrdersByTerritoryView.

Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:

CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue
FROM Sales.OrdersByTerritory-----------VIEW name
WHERE SalesTerritoryID = @T
)

You have a database that contains the tables shown in the exhibit.

You deploy a new server that has SQL Server 2012 installed. You need to create a table named

Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:

Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity
for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.

Which code

segment should you use?
To answer, type the correct code in the answer area.

Solution:

On the 461 certification exam, several questions require:

The code must NOT use any object delimiters

Any idea what this means?

so write code with out [] and ""

Correct Answer:

CREATE TABLE Sales.OrderDetails (
ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

You have a database that contains the tables shown in the exhibit.

You need to create a view named uv_CustomerFullName to meet the following requirements:

The code must NOT include object delimiters.
The view must be created in the Sales schema.
Columns must only be referenced by using one-part names.
The view must return the first name and the last name of all customers.
The view must prevent the underlying structure of the customer table from being changed.
The view must be able to resolve all referenced objects, regardless of the user's default

Schema.

Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:

CREATE VIEW Sales.uv_CustomerFullName
WITH SCHEMABINDING
AS
SELECT FirstName, LastName
FROM Sales.Customers

You have a database that contains the tables shown in the exhibit.

You need to create a query that calculates the total sales of each OrderId from the Sales.Details
table. The solution must meet the following requirements:

Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.

Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:
SELECT OrderID, SUM(ExtendedAmount) AS TotalSales
FROM Sales.Details
GROUP BY OrderID
ORDER BY OrderID

You have a database that contains the tables as shown in the exhibit.

You have the following query:

You need to recreate the query to meet the following requirements:

Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.

Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:

SELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty),
MAX(DiscountAmount)
FROM Sales.Details
GROUP BY SalesTerritoryID , ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

You have a database that contains the tables shown in the exhibit.

You need to create a query for a report. The query must meet the following requirements:

NOT use object delimiters.
Return the most recent orders first.
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.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.

The solution must support the ANSI SQL-99 standard. Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:
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 O.OrderDate DESC

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of
the XML type named XML1. The solution must ensure that XML1 is validated by using
Sales.InvoiceSchema. Which code segment should you use?
To answer, type the correct code in the answer area.

Correct Answer:
DECLARE @XML1 XML(Sales.InvoiceSchema)

You have a database that contains the tables as shown in the exhibit.

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution
must meet the following requirements:

UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.

Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:

SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.UnitPrice DESC)
AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC

You have a database that contains the tables shown in the exhibit.

NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.

Which code segment should you use?

To answer, type the correct code in the answer area.

Correct Answer:

CREATE PROCEDURE usp_Customers @Count int
AS
SELECT TOP(@Count) Customers.LastName
FROM Customers
ORDER BY Customers.LastName

Answer:

You are Member of SQL developer team. You Need to create a new view named vwEmployeePhoneList that is part of the HumanResources schema. The view should select the LastName,FirstName,PhoneExt columns from the HumanResources.Employees table.

Which code Segment should you use?

A.

CREATE VIEW vwEmployeePhoneList
AS
select LastName,FirstName,PhoneExt
FROM humanResources.Employees

B.
Create View HumanResources.vwEmployeePhoneList
AS
select LastName,FirstName,phoneExt
from HumanResources.Employees

C.

Alter view HumanResources.vwEmployeePhoneList
as
Select LastName,FirstName,PhoneExt
from humanResources.Employees

D.
NEW VIEW HumanResources.vwEmployeePhoneList
as
Select LastName,FirstName,PhoneExt
from humanResources.Employees

Correct Answer: A

Which of these view can be used to modify the data of an underlying base table?

Choose the Correct Answer

A.
CREATE VIEW PatientView
as
SELECT TOP 10 Patients.PatientName,Patients.PatientAddress
FROM Patients

B.
Create view PatientView
AS
select top 10 Patients.PatientName,
Patients.PatientAddress
From Patients WITH CHECK OPTION

C.
CREATE VVIEW PatientView
as
SELECT Count(PatientID) AS NumberOfPatients
from Patients

Correct Answer: Option A

Because if we use WITH CHECK OPTION or COUNT function in Statement that is non updatable in table so option A is correct answer.

You develop a Microsoft SQL Server 2012 database. The database contains tables named Patients,Prescriptions, Visits. The Transact-SQL Query that was used to create these tables is show below

CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
PatientName VARCHAR(255) NOT NULL,
PatientAddress VARCHAR(1024)
)

CREATE TABLE Prescriptions
(
PrescriptionID int not null Primary key,
PrescriptionText Varchar(255) not null,
PatientID int FOREIGN KEY REFERENCES Patients(PatientID)
)

CREATE TABLE Visits
(
VisitID int NOT NULL Primary key,
VisitReason Varchar(255) not null,
PatientID INT FOREIGN KEY REFERENCES Patients(PatientID)
)

Patients are considered active If they meet all of these requirements:

*Have at least one visit that is recorderd in the visits table

*Have at least one Prescription that is recorded in the Prescriptions table.

You need to create a view that shows only the name and address of all unique active patients.

which Transact-SQL Statement should you use?

choose the correct answer

A
CREATE VIEW PatientView
as
SELECT PatientName,PatientAddress FROM Patients
WHERE (EXISTS (select PatientID from Prescriptions
WHERE (Patients.PatientID=PatientID)) AND EXIST
(select PatientID from visits WHERE (Patients.PatientID=PatiendID))

B.
create view patientview
AS
select PatientName,PatientAddress
FROM (select Distinct PatientID,PatientName,PatiendAddress
FROM Patients
WHERE (EXISTS(SELECT PrescriptionID,PrescriptionText,PatientID
FROM Presecriptions
where (Patients.PatientID=PatientID))
OR EXISTS (select VisitID,VisitReason,PatientID FROM Visits
where (Patients.PatientID=PatientID))))
PatientData

C.

Correct Answer is OPTION A

Solutions:

Here above Table PatientID 4578,2147 only exists in both Prescriptions,Visits Table.

So Option A would be Correct Answer. See below

OptionA Gives correct 2 values and OptionB gave not that value(Patient A extra)

Sample Code:

USE [AdventureWorks]

CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
PatientName VARCHAR(255) NOT NULL,
PatientAddress VARCHAR(1024)
)

CREATE TABLE Prescriptions
(
PrescriptionID int not null Primary key,
PrescriptionText Varchar(255) not null,
PatientID int FOREIGN KEY REFERENCES Patients(PatientID)
)

CREATE TABLE Visits
(
VisitID int NOT NULL Primary key,
VisitReason Varchar(255) not null,
PatientID INT FOREIGN KEY REFERENCES Patients(PatientID)
)

Insert into Patients
SELECT 6526,'Patient A','CBE'
UNION ALL
SELECT 4578,'Patient E','CBE'
UNION ALL
SELECT 8946,'Patient X','CBE'
UNION ALL
SELECT 2147,'Patient T','CBE'

Insert into Prescriptions
SELECT 025,'Kindly Take Xray',6526
UNION ALL
SELECT 085,'Need Operation',4578
UNION ALL
SELECT 045,'Take it tablets correctly',2147
UNION ALL
SELECT 015,'Need Surgeon consuldation',2147

insert into Visits
SELECT 801,'Meet Doctor',4578
union all
select 805,'Meet Surgeon',2147

SELECT * FROM Patients

SELECT * FROM Prescriptions

SELECT * FROM Visits

CREATE VIEW PatientViewOptionA
as
SELECT PatientName,PatientAddress FROM Patients
WHERE (EXISTS (select PatientID from Prescriptions
WHERE (Patients.PatientID=PatientID)) AND EXISTS
(select PatientID from Visits WHERE (Patients.PatientID=PatientID)))

SELECT * FROM PatientViewOptionA

SELECT * FROM patientviewOptionB

Create view patientviewOptionB
AS
select PatientName,PatientAddress
FROM (select Distinct PatientID,PatientName,PatientAddress
FROM Patients
WHERE (EXISTS(SELECT PrescriptionID,PrescriptionText,PatientID
FROM Prescriptions
where (Patients.PatientID=PatientID))
OR EXISTS (select VisitID,VisitReason,PatientID FROM Visits
where (Patients.PatientID=PatientID))))
PatientData

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)
Answer: AB

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

A

GRANT SELECT ON Purchases.Suppliers TO ContosoUser

B

CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C

CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

D

GRANT DELETE ON Purchases.Suppliers TO ContosoUser

Solution:

see sample table

Initial properties of ContosoUser see below

Initial State we can see user cant able to select table see below of that user

let see option A

GRANT SELECT ON dbo.Suppliers TO ContosoUser

if issue option A ContosoUser can only select that table so it cant use to delete
so it is not correct answer

let see option B

it gives error

let see option C

After Execute SP of ContosoUser it is working fine see below

so OPTION C is the correct Answer

let see OPTION D

GRANT DELETE ON dbo.Suppliers TO ContosoUser

after that above command if run below commands

Delete from Suppliers where IsActive=0

If you execute it will throw message it needs additional select permission

you need select permission on that table as see in option A

so option d is not correct answer it will be correct but you need to execute both OPTION A and OPTION D concurrently.

additional info if you need to get back permission you can use below T-SQL

    REVOKE SELECT ON dbo.Suppliers TO ContosoUser

   REVOKE DELETE ON dbo.Suppliers TO ContosoUser

   REVOKE EXECUTE ON dbo.PurgeInactiveSuppliers TO ContosoUser

Also ContosoUser have only execute permission so ContosoUser user can't able to alter it

Also you can see TBK2012Reader user can able to execute storedprocedure as see below

WITH EXECUTE AS CALLER clause

CREATE PROCEDURE dbo.PurgeInactiveSuppliersWITHCaller

        WITH EXECUTE AS CALLER

        as

    DELETE FROM dbo.Suppliers WHERE IsActive = 0

    GO

        GRANT EXEC ON dbo.PurgeInactiveSuppliersWITHCaller TO [TBK2012Reader]
GO

Now TBK2012 Can able to remove that isactive field as see below

The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a stand alone clause to change the users context. This clause is simply added to the code as follows:

CREATE PROCEDURE dbo.TestProcedure
WITH EXECUTE AS OWNER

There are basically five types of impersonation that can be used:

SELF - the specified user is the person creating or altering the module
CALLER - this will take on the permissions of the current user
OWNER - this will take on the permissions of the owner of the mohttps://www.blogger.com/blogger.g?blogID=8453781330643442479#editor/target=post;postID=4119980947312551542;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=14;src=linkdule being called
'user_name' - a specific user
'login_name' - a specific login

CREATE PROCEDURE dbo.TestProcedure
WITH EXECUTE AS OWNER
AS
BEGIN
SQL Statement
END

You Create a table that has the StudentCode,SubjectCode, and Marks Columns to record mid-year 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.
Consecutive ranks must be skipped when the same rank is assigned

So Correct Answer is option A

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 retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

A

    SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
    FROM StudentMarks
    GROUP BY StudentCode

B

    SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
    FROM StudentMarks
    GROUP BY StudentCode

C

    SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
    FROM StudentMarks

D

    SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
    FROM StudentMarks
    GROUP BY StudentCode

E

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

F

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

G

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

H

    SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
    RANK() OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
    FROM StudentMarks) tmp
    WHERE Rank = 1

So Correct Answer is Option G

see the sample table

1 comment:

Prepare4test22 November 2021 at 01:25
Download the Dell DSDSC-200 Q&A PDF file easily to prepare Dell PS Series Storage Professional exam. It is particularly designed for Dell DSDSC-200 exam and our Dell specialists have created this DSDSC-200 Question Dumps observing the original DSDSC-200 exam.

Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 4 March 2016

SQL Certification MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Study Chapters & Materials & Sample Practice Tests and Solutions

SQL Certification MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Study Chapters & Materials and Sample Tests Questions

Querying Microsoft SQL Server 2012

Question #

MCSA: SQL Server Solutions Associate

Resources

Limitations of Table-Valued Parameters

1 comment:

Recent Comments on this Blog

SQL Question

Visitors Location @ Globe

Featured post

How to Verify Database Backup file is not corrupted one in SQL Server? How to Validate Database Backup in SQL Serer?