Which Transact-SQL statement should you use?
Click the Exhibit button.
You have the following XML:
<Site URL="http://www.contoso.com/index.htm">
<Site URL="http://www.contoso.com/finance/index.htm">
<Site URL="http://www.contoso.com/finance/reports/index.htm" />
<Site URL="http://www.contoso.com/finance/main/index.htm" />
</Site>
<Site URL="http://www.contoso.com/marketing/index.htm">
<Site URL="http://www.contoso.com/marketing/reports/index.htm" />
<Site URL="http://www.contoso.com/marketing/main/index.htm" />
</Site>
<Site URL="http://www.contoso.com/sales/index.htm" />
</Site>
You are tasked to query the sites listed in the XML by using OPENXML.
The results will have two columns, ParentSiteURL and SiteURL.
The ParentSiteURL column should contain the URL attribute of the parent site.
The SiteURL column should contain the URL attribute of the site itself.
The output should look like that in the exhibit.
You need to write the OPENXML query.
Which Transact-SQL statement should you use?
Exhibit:
Which hint should you use?
You have the following query:
SELECT EmployeeID, ManagerID, LoginID FROM dbo.Employees WHERE ManagerID = 1500 ORDER BY ManagerID;
You have been tasked to force the query to use the execution plan in the exhibit.
You need to use an appropriate hint to perform the task.
Which hint should you use?
Exhibit:
Which Transact-SQL statement should you use?
You have the following two tables.
The foreign key relationship between these tables has CASCADE DELETE enabled.
You need to remove all records from the Orders table.
Which Transact-SQL statement should you use?
Exhibit:
Which statement should you use?
You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1.
You need to write a query to insert a new order into the Orders table for CustomerID 45 with today’s date and a cost of 89.00.
Which statement should you use?
Exhibit:
Which trigger syntax should you use?
You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the exhibit. You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit, the delete should not occur and the IsDeleted column for the row should be changed to ‘True’. Parts can be deleted if they do not belong to a kit. You have the following Transact-SQL statement to be used in a trigger:
UPDATE p SET IsDeleted = 1 FROM KitPart kp
JOIN deleted d ON kp.PartID = d.PartID
JOIN Part p ON kp.PartID = p.PartID; DELETE FROM p FROM Part p
JOIN deleted d ON p.PartID = d.PartID
LEFT OUTER JOIN KitPart kp ON p.PartID = kp.PartID WHERE kp.KitID IS NULL;
You need to implement the Transact-SQL statement in a trigger. Which trigger syntax should you use?
Exhibit:
Which index should you create?
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
Which statement will you use?
You need to manually raise an error.
The error message that will be returned will display a message that contains
parameters 1, 2, 3
sp_addmessage @msgnum = 6000,
@severity = 16,
@msgtext = N’Your message (%d), (%d), (%d)’;
GO
You have to raise message to get correct message.. ‘Your message (1), (2), (3).’
Which statement will you use?
Which statement would you use?
You have a list of Subscribers in a table.
You were given an updated list of subscribers.
You need to remove the records that are not in the new list of subscribers.
Which statement would you use?
BEGIN TRAN INSERT INTO table (‘Food’) BEGIN TRAN INSERT INTO table (‘Beverages’) COMMI
BEGIN TRAN
INSERT INTO table (‘Food’)
BEGIN TRAN
INSERT INTO table (‘Beverages’)
COMMIT TRAN
ROLLBACK TRAN
In table will be folowing items:
Which USER will the trigger use?
CREATE TRIGGER XY
ON Inventory.XY
FOR UPDATE,INSERT,DELETE
EXECUTE AS SELF
WebUser do INSERT statement on Inventory.XY table
Which USER will the trigger use?