SQL Final

Q: You are a database developer for your company Human Resources database. This database includes a table named Employee that contains confidential ID numbers and bribe amounts to local officials called 'Salary'. The table also includes non-confidential information, such as an employee's alias and last known address. You need to make all the non-confidential information in the Employee table available to the Feds in XML format to an external application. The external application should be able to specify the exact format of the XML data. You also need to hide the existence of the confidential information from the external application. What should you do? Answer 1: Create a view that includes only the non-confidential information from the Employee table. Give the external application permission to submit queries against the view. Answer 2: Create a stored procedure that returns the non-confidential information from the Employee table formatted as XML. Answer 3: Create a user-defined function that returns the non-confidential information from the Employee table in a rowset that is formatted as XML. Answer 4: Set column-level permissions on the Employee table to prevent the external application from viewing the confidential columns. Give the external application permissions to submit queries against the table.

1.Create a view that includes only the non-confidential information from the Employee table. Give the external application permission to submit queries against the view.

Question 38 Q: You are a database developer for Software Systems Inc. Before inserting data into a table, you load it into a temporary table named Stage. When the data load process is complete, the temporary data in the table will be deleted. Your company does not want to ever recover this data. You are assigned the task of deleting this data. How would you accomplish this quickly? 1: Use a TRUNCATE TABLE statement to remove the data from the table. 2: Use a DELETE statement to remove the data from the table. 3: Use a DROP TABLE statements to remove the data from the table. 4: Use an updatable cursor to access and remove each row of data from the table

1: Use a TRUNCATE TABLE statement to remove the data from the table.

Question 37 Q: You are a database developer for a soap company name Squeaky Clean. The company stores its soap inventory data in a SQL Server 2012 database. Many of the critical queries in the database join three tables named Ingredients, Location, and Product. These tables are updated infrequently. You want to improve the response time of the critical queries. What should you do? Answer 1: Create an indexed view on the tables Answer 2: Create a stored procedure that returns data from the tables. Answer 3: Create a scalar user-defined function that returns data from the tables. Answer 4: Create a table-valued user-defined function that returns data from the tables

Answer 1: Create an indexed view on the tables

Question 50 Q: You are a database developer for ReadItNow Publishing. The company stores all sales data in a SQL Server 2012 database that contains a table named Purchases. There is currently a clustered index on the table, which is generated by using a customer's name and the current date. The Purchases table currently contains 500,000 rows, and the number of rows increased by 10 percent each week. The marketing group tells you that they are launching a sale on books and orders should increase by 50 percent. You want to optimize inserts to the Purchases table during the sale. What should you do? Answer 1: Rebuild the clusters with a FILLFACTOR of 50 Answer 2: Create a job that rebuilds the clustered index each night by using the default FILLFACTOR Answer 3: Add additional indexes to the Orders table. Answer 4: Partition the Orders table vertically. Answer 5: Execute the UPDATE STATISTICS statement on the Purchases table

Answer 1: Rebuild the clusters with a FILLFACTOR of 50

Question 53 Q: You are a database developer for an accounting company. The company uses SQL Server 2012. You want to create an indexed view in this database. To accomplish this, you execute the script shown below: Set NUMERIC_ROUNDABORT OFF GO CREATE VIEW viewBillableHours AS SELECT SUM(AccountantHourlyPrice*Hours*(1.00-Discount))AS Due, TransactionDate, ServiceID, COUNT_BIG(*) AS COUNT FROM dbo.BillableHours AS BH JOIN dbo.Accountants AS A ON BH.AccountantID = A.AccountantID GROUP BY BH.TransactionDate, BH.ServiceID GO CREATE UNIQUE CLUSTERED INDEX IX_BillableHours ON viewBillableHours (TransactionDate, ServiceID) GO The index creation fails, and you receive an error message. You want to eliminate the error message and create the index. What should you do? Answer 1: Add the WITH SCHEMABINDING option to the view. Answer 2: Add an ORDER BY clause to the view. Answer 3: Add a HAVING clause to the view. Answer 4: Change the NUMERIC_ROUNDABORT option to ON. Answer 5: Change the index to a unique, nonclustered index.

Answer 1: Add the WITH SCHEMABINDING option to the view.

Question 55 Q: You are a developer for a company that develops a ticket sales e-commerce site for an airline in your city. You are designing the SQL Server 2012 database to support the Web site. New ticket reservations are inserted into a table named Reservations. Customers who have reserved a ticket can return to the Web site and modify their reservation. When a reservation is modified, the entire existing reservation must be copied to a table called History. Once in a while, customers will save an existing reservation without having modified the reservation information. When this happens existing reservations should not be copied to the History table. Which of the following techniques should you use to handle these types of situations? Answer 1: Create a trigger on the reservations table to create the History table entries. Answer 2: Create a cascading referential integrity constraint on the reservations table to create the History table entries. Answer 3: Create a view on the reservations table. Include the WITH SCHEMA BINDING option in the view definition. Answer 4: Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the view definition.

Answer 1: Create a trigger on the reservations table to create the History table entries.

Question 29 Q: You have been asked to develop an OLTP database. Once filled with initial data from a legacy data system, your database will be just under 1GB. You have 5GB available on drive C. You want to achieve the best possible performance for the database. Which script should you use to create the database? Answer 4: CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = 'c:\data\contacts.mdf', SIZE = 1GB) Answer 2: CREATE DATABASE CustomerContacts ON (NAME = Contacts_database, FILENAME = 'c:\data\contacts.mdf', SIZE = 10, MAXSIZE = 1GB FILEGROWTH= 5) Answer 3: CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = 'c:\data\contacts.mdf', SIZE = 10, MAXSIZE = 1GB FILEGROWTH= 10%) Answer 4: CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = 'c:\data\contacts.mdf', SIZE = 100, MAXSIZE = UNLIMITED) Correct Answer: 1

CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = 'c:\data\contacts.mdf', SIZE = 1GB)

Question 46 Q: You are a database developer for WreckUm Insurance Company. The company has a database named Insured. You have designed stored procedures for this database that will use cursors to process large amounts of records. Users report that it takes a while for data to display when certain stored procedures are used. After the delay, performance is adequate. Only certain users, who perform data analysis, use the Insured database. You want to improve the performance of the stored procedures. Which script should you use? Answer 1: EXEC sp_configure 'cursor threshold', 0 Answer 2: EXEC sp_dboption 'Insured' SET CURSOR_CLOSE_ON_COMMIT ON Answer 3: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Answer 4: ALTER DATABASE Insured SET CURSOR_DEFAULT LOCAL Correct Answer: 1

EXEC sp_configure 'cursor threshold', 0

Question 48 Q: You are a database developer for an insurance company. You are informed that database operations such as selects, inserts, and updates, are taking much longer than they were when the database was created a year ago. The previous developer added necessary indexes on the tables when the database was created. Since that time, additional tables and stored procedures have been added to the database. In addition, many of the queries are no longer used. You want to improve the response time of the database operations as quickly as possible. What should you do? Answer 1: Run the Database Engine Tuning Advisor against a workload file to suggest indexes that should be created or dropped. Answer 2: Execute the DBCC UPDATEUSAGE statement against the database to update the sysindexes system table Answer 3: Execute the DBCC SHOW_STATISTICS statement to find high-density indexes. Drop the highdensity indexes. Answer 4: Use SQL profiler to find table scans. Add indexes to tables that were found to have table scans.

Run the Database Engine Tuning Advisor against a workload file to suggest indexes that should be created or dropped.

Question 23 Q: You are a database developer at Books and More. You are designing a stored procedure to process XML documents that hold book information. You use the following script to create the stored procedure: CREATE PROCEDURE spGetXML (@xmlDoc varchar(1000)) AS DECLARE @xmldocHandle int EXEC sp_xml_preparedocument @xmldochandle OUTPUT, @xmlDoc SELECT * FROM OPENXML (@xmldocHandle, '/ROOT/Book/Product', 2) WITH ( BookID int, PubID int, BookName varchar (50), BookDesc varchar (50) ) EXEC sp_xml_removedocument @docHandle You execute this stored procedure and use an XML document as the input document. The XML document is shown below: <ROOT> <Book BookID = "1" BookName= "General Books"> <Product PubID = "10248" BookDesc = "Cooking for you"> </Product> </Book > <Book BookID = "2" BookName= "Videos"> <Product PubID = "80248" BookDesc = "7 Minute Abs"> </Product> </Book > <Book BookID = "3" BookName = "Computer Books"> <Product PubID = "12345" BookDesc = "Inside SQL Server 2012"> </Product> <Product PubID = "22345" BookDesc = "Analysis Services with SQL Server 2012"> </Product> </Book > </ROOT> You receive the output shown below: Output BookID PubID BookName BookDesc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL (4 row(s) affected) You need to replace the body of the stored procedure. Which script should you use? Answer 1: SELECT * FROM OPENXML (@xmldochandle, '/ROOT/Book/Product', 1) WITH ( BookID int '../@BookID', PubID int, BookName varchar(50) '../@BookName', BookDesc varchar (50) ) Answer 2: SELECT * FROM OPENXML (@docHandle, '/ROOT/Book/Product', 1) WITH ( BookID int, PubID int, BookName varchar (50), BookDesc varchar (50) ) Answer 3: SELECT * FROM OPENXML (@docHandle, '/ROOT/Book/Product', 8) WITH ( BookID int, PubID int, BookName varchar(50), BookDesc varchar (50) ) Answer 4: SELECT * FROM OPENXML (@docHandle, '/ROOT/Book/Product', 1) WITH ( BookID int, PubID int '../PubID', BookName varchar(50) '../BookName', BookDesc varchar (50) )

SELECT * FROM OPENXML (@xmldochandle, '/ROOT/Book/Product', 1) WITH ( BookID int '../@BookID', PubID int, BookName varchar(50) '../@BookName', BookDesc varchar (50) )

Question 73 Q: You manage a SQL Server database that is used by a banking transaction management application. You wish to query a list of account holders who are in areas where no branch locations exist. Which queries would be necessary to perform this task? (SELECT TWO). Answer 1: SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) Answer 2: SELECT AccountHolderID FROM AccountHolder WHERE CityID = ALL (SELECT CityID FROM BranchMaster) Answer 3: SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster) Answer 4: SELECT AccountHolderID FROM AccountHolder WHERE CityID = ANY (SELECT CityID FROM BranchMaster)

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

Question 26 Q: You are designing an inventory control system for an exporting company. You create the database design shown below: ORDER ORDER ID PK CUSTOMER ID SHIP DATE PRODUCT PRODUCT ID PK SUPPLIER ID ORDER DETAILS ORDER ID PRODUCT ID SUPPLIER SUPPLIER ID PK You need to maintain referential integrity by adding constraints. Which three constraints should you apply to the design? Answer 1: Create a FOREIGN KEY constraint on the Products table that references the Suppliers table. Create a FOREIGN KEY constraint on the Order Details table that references the Order table. Create a FOREIGN KEY constraint on the Order Details table that references the Products table Answer 2: Create a FOREIGN KEY constraint on the Products table that references the Order Details table. Create a FOREIGN KEY constraint on the Order Details table that references the Order table. Create a FOREIGN KEY constraint on the Order Details table that references the Products table Answer 3: Create a FOREIGN KEY constraint on the Orders table that references the Order Details table. Create a FOREIGN KEY constraint on the Order Details table that references the Order table. Create a FOREIGN KEY constraint on the Order Details table that references the Products table Answer 4: Create a FOREIGN KEY constraint on the Suppliers table that references the Products table. Create a FOREIGN KEY constraint on the Order Details table that references the Order table. Create a FOREIGN KEY constraint on the Order Details table that references the Products table

1. Create a FOREIGN KEY constraint on the Products table that references the Suppliers table. Create a FOREIGN KEY constraint on the Order Details table that references the Order table. Create a FOREIGN KEY constraint on the Order Details table that references the Products table

Question 27 Q: You have been hired by a quarter-horse breeder to develop a database that will help him keep inventory of his horses. This database will store information about a each horse's history including their bloodlines. You create a table named Horses by using the following script: CREATE TABLE[dbo].[Horses] ( [HorseID] [int] NOT NULL, [BreedID] [int] NOT NULL, [Date of Birth] [datetime] NOT NULL, [WeightAtBirth] [decimal] (5, 2) NOT NULL, [NumberOfSiblings] [int] NULL, [MareID] [int] NOT NULL, [SireID] [int] NOT NULL ) on [PRIMARY] GO ALTER TABLE [dbo].[Horses] WITH NOCHECK ADD CONSTRAINT [PK_Horses]PRIMARY KEY CLUSTERED ( [HorseID] ) ON [PRIMARY] GO You must ensure that each horse has a valid value for the MareID and SireID columns. You want to enforce this rule while minimizing disk Input/Output. What should you do? Answer 1: Create two FOREIGN KEY constraints, create one constraint on the MareID column and one constraint on the SireID column. Specify that each constraint reference the HorseID column. Answer 2: Create an ALTER INSERT trigger on the horses table that rolls back the transaction if the MareID or SireID column is not valid. Answer 3: Create a table-level CHECK constraint on the MareID and SireID columns. Answer 4: Create a rule and bind it to the MareID. Bind the same rule to the SireID column

1. Create two FOREIGN KEY constraints, create one constraint on the MareID column and one constraint on the SireID column. Specify that each constraint reference the HorseID column

Question 69 Q: You support a database that contains two tables, Purchasing.Vendor and Purchasing.PurchaseOrderHeader. Management would like to analyze all purchase orders over $75,000 to better define the company's buying habits and see if there might be any opportunities to go back to some vendors and negotiate for better discounts based on the amount of money spent over the past several years. Which query of those shown below would allow you to provide Management with the required information? Answer 1: SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor WHERE 75000 < ANY ( SELECT SubTotal FROM Purchasing.PurchaseOrderHeader WHERE Vendor.VendorID = PurchaseOrderHeader.VendorID) Answer 2: SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor WHERE 75000 < ALL ( SELECT SubTotal FROM Purchasing.PurchaseOrderHeader WHERE Vendor.VendorID = PurchaseOrderHeader.VendorID) Answer 3: SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor WHERE 75000 < ( SELECT MIN(SubTotal) FROM Purchasing.PurchaseOrderHeader WHERE Vendor.VendorID = PurchaseOrderHeader.VendorID) Answer 4: SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor WHERE EXISTS ( SELECT PurchaseOrderHeader.VendorID FROM Purchasing.PurchaseOrderHeader WHERE Vendor.VendorID = PurchaseOrderHeader.VendorID OR PurchaseOrderHeader.SubTotal >= 75000)

Answer 1: SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor WHERE 75000 < ANY ( SELECT SubTotal FROM Purchasing.PurchaseOrderHeader WHERE Vendor.VendorID = PurchaseOrderHeader.VendorID)

Question 58 Q: You are a database developer for a rapidly growing arms dealer. As world tensions rise, the arms dealer is expanding into new sales regions each month. As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database. The RegionSales table is shown below: RegionSales RegionSalesID SaleDate CustomerID ProductID RegionID Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions. It must be as easy as possible to extend the solution as new regions and sales associates are added. What should you do? Answer 1: Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the security table. Grant EXECUTE permissions on the stored procedures to all sales associates. Answer 2: Use GRANT, REVOKE and DENY statements to assign permission to the sales associates. Use SQL Server Enterprise Manager to assign permission on the RegionSales table. Answer 3: Create one view on the RegionSales table for each sales region. Grant the sales associates permission to access the views that correspond to the sales region to which they have been assigned. Answer 4: Use SQL Server Enterprise Manager to assign appropriate permissions to all the sales associates on the RegionSales table. Answer 5: Create a new table named Security to hold combinations of sales associates and sales regions. Create user-defined functions that allow or disallow modifications of the data. In the RegionSales table by validating the user of the function against the security table. Grant EXECUTE permissions on the functions to all sales associates

Answer 1: Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the security table. Grant EXECUTE permissions on the stored procedures to all sales associates.

Question 68 Q: You manage a SQL Server 2008 database which contains three tables named EMPLOYEES, DEPARTMENTS, and JOB_HISTORY. These tables are described below. Look carefully at the SQL statements below: SELECT department_id FROM employees INTERSECT SELECT department _id FROM departments EXCEPT SELECT department _id FROM job_history; Which statement is true regarding the above compound query? Answer 1: It shows department id numbers that have been assigned to employees but do not appear in the job_history table Answer 2: It produces an error Answer 3: It shows department id numbers that have been assigned to employees and appear in the job_history table Answer 4: It shows department id numbers that have been assigned to employees regardless of their use to track job history information

Answer 1: It shows department id numbers that have been assigned to employees but do not appear in the job_history table

Question 54 Q: While attempting to clean up your company's database by removing database objects that are no longer used you encounter a problem. You attempt to drop a view called viewManagers1998. The drop fails. You investigate the view's properties and discover the following characteristics: There is a clustered index on the view. The Managers database role has permissions on the view. The view uses the WITH SCHEMABINDING option. A schema-bound inline function references the view. An INSTEAD OF trigger is defined on the view. What should you do before you can drop the view? Answer 1: Remove the WITH SCHEMABINDING option from the function that is referencing the view. Answer 2: Drop the clustered index on the index. Answer 3: Remove all permissions from the view. Answer 4: Remove the WITH SCHEMABINDING option from the view. Answer 5: Disable the INSTEAD OF trigger on the view.

Answer 1: Remove the WITH SCHEMABINDING option from the function that is referencing the view.

Question 61 Q: You are the admin for a SQL Server database that supports a shopping application, from which you will need to be able to retrieve a list of customers who live in areas that do not have a sales person. Which query or queries should you use? Answer 1: SELECT CustomerID FROM Customer WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson) Answer 2: SELECT CustomerID FROM Customer WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson) Answer 3: SELECT CustomerID FROM Customer WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson) Answer 4: SELECT CustomerID FROM Customer WHERE TerritoryID IN(SELECT TerritoryID FROM Salesperson)

Answer 1: SELECT CustomerID FROM Customer WHERE TerritoryID <> *ALL(SELECT TerritoryID FROM Salesperson) Answer 4: SELECT CustomerID FROM Customer WHERE TerritoryID *IN(SELECT TerritoryID FROM Salesperson) Correct Answer: 1, 4

Question 32 Q: You are a database developer for Acme Learning Academy. The school uses a SQL Server 2012 database that contains a table named Advisors and a table named Students. You want to insert new student information from a linked server into the Students table. The Students table has a FOREIGN KEY constraint that references the Advisors table. An UPDATE trigger is defined on the Students table. You want to load the data as quickly as possible. What should you do? Answer 1: Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Advisors table. Answer 2: Use the ALTER TABLE statement and the ON UPDATE clause to modify the Students table. Answer 3: Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Students table. Answer 4: Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Advisors table. Answer 5: Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Advisors table

Answer 1: Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Advisors table.

Question 65 Q: You manage a SQL Server 2012 database that contains an OrderDetail table. You find that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You want to reduce the fragmentation of the index. Which one of the commands shown below would allow you to defragment the index without requiring you to take the index offline? Answer 1: ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE Answer 2: CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING Answer 3: ALTER INDEX ALL ON OrderDetail REBUILD Answer 4: ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer 1: ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

Question 33 Q: You are sole surviving database developer for WeSurvivedTheDotComCrash, LLC. You are implementing a database for the company's personnel department. This database will store what's left of the employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and EmailAddress. You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do? Answer 1: Add a CHECK constraint on the HomePhone and BusinessPhone columns that prevent null values from being entered into the columns. Answer 2: Create a rule that disallows null values. Bind the rule to both the HomePhone and BusinessPhone columns. Answer 3: Create a rule that prevents null values from being entered into both the HomePhone and BusinessPhone columns. Bind the rule to the table. Answer 4: Create a trigger that counts the number of items entered without a value in the HomePhone column and then counts the number of items entered without a value in the BusinessPhone column. Answer 5: Configure the trigger so that if one or more rows are found that meet these conditions, the trigger will cancel the data modification.

Answer 1: Add a CHECK constraint on the HomePhone and BusinessPhone columns that prevent null values from being entered into the columns.

Question 30 Q: You are a database developer for an auto insurance company. You create a table named Customers, which will contain information about people covered by these insurance policies. You use the following script: CREATE TABLE dbo.Customers ( InsuredID int IDENTITY (1, 1) NOT NULL, PolicyID int NOT NULL, InsuredName char(30) NOT NULL, InsuredBirthDate datetime NOT NULL, CONSTRAINT PK_Insured PRIMARY KEY CLUSTERED ( InsuredID ) CONSTRAINT FK_Insured_Policy FOREIGN KEY (PolicyID ) REFERENCES dbo.Policy ( PolicyID ) ) A person covered by an insurance policy is uniquely identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy. You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies. What should you do? Answer 1: Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns. Answer 2: Add the PolicyID, InsuredName, and InsuredBirthDate columns to the primary key. Answer 3: Add a CHECK constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns. Answer 4: Create a clustered index on the PolicyID, InsuredName, and InsuredBirthDate columns.

Answer 1: Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.

Question 16 Q: You are a database developer for FlyRite Airlines. You are designing a human resources database that contains tables named Employee and Salary. You interview users in the HR department and discover the following information: 1. The Employee table will often be joined with the Salary table on the EmployeeID column. 2. Individual records in the Employee table will be selected by social security number (SSN). 3. A list of employees will be created. The list will be produced in alphabetical order by last name, and then followed by first name. You need to design the indexes for the tables while optimizing the performance of the indexes. Which three scripts should you use? Answer 1: CREATE CLUSTERED INDEX [IX_EmployeeName] ON [dbo].[Employee] ([LastName], [FirstName]) CREATE UNIQUE INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID]) CREATE UNIQUE INDEX [IX_EmployeeSSN] ON [dbo].[Employee] ([SSN]) Answer 2: CREATE INDEX [IX_EmployeeFirstName] ON [dbo].[Employee] ([First Name]) CREATE INDEX [IX_EmployeeLastName] ON [dbo].[Employee] ([Last Name]) CREATE CLUSTERED INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID]) Answer 3: CREATE UNIQUE INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID]) CREATE INDEX [IX_EmployeeFirstName] ON [dbo].[Employee] ([First Name]) CREATE INDEX [IX_EmployeeLastName] ON [dbo].[Employee] ([Last Name]) Answer 4: CREATE UNIQUE INDEX [IX_EmployeeSSN] ON [dbo].[Employee] ([SSN]) CREATE CLUSTERED INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID]) CREATE INDEX [IX_EmployeeLastName] ON [dbo].[Employee] ([Last Name]) Answer 5: CREATE CLUSTERED INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID]) CREATE CLUSTERED INDEX [IX_EmployeeSSN] ON [dbo].[Employee] ([SSN]) CREATE INDEX [IX_EmployeeLastName] ON [dbo].[Employee] ([Last Name]) Correct Answer: 1

Answer 1: CREATE CLUSTERED INDEX [IX_EmployeeName] ON [dbo].[Employee] ([LastName], [FirstName]) CREATE UNIQUE INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID]) CREATE UNIQUE INDEX [IX_EmployeeSSN] ON [dbo].[Employee] ([SSN])

Question 75 Q: You administer a database with the tables shown below: You manage an application named Appl with a parameter named @Count that uses the int data type. App1 passes @Count to a stored procedure. You wish to create a stored procedure named Usp_Customers for the application. The procedure must meet these 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 of the following should you use? Answer 1: CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName Answer 2: CREATE PROCEDURE usp_Customers ON Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName Answer 3: CREATE PROCEDURE usp_Customers @Count int AS SELECT ALL(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName Answer 4: CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.Name

Answer 1: CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName

Question 24 Q: You are a database developer for Acme Learning Academy. You are designing a SQL Server 2012 database that will contain employee and student information. This database will contain a table named Advisors and a table named Students. Advisors are responsible for multiple Students. Students have a primary Advisor and usually have a backup Advisor. The primary Advisors must be identified as the primary Advisor. The Students table can contain up to but no more than thirty thousand rows. You want to increase Input/Output performance when data is selected from the tables. The database should be normalized to the third normal form. Which script should you use to create the tables? Answer 1: CREATE TABLE Students (StudentID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Advisors (AdvisorID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE StudentsAdvisors ( AdvisorID int NOT NULL REFERENCES Advisors (AdvisorID), StudentID int NOT NULL REFERENCES Students (StudentID), PrimaryAdvisors bit NOT NULL, CONSTRAINT PK_StudentsAdvisors PRIMARY KEY (AdvisorID, StudentID) ) Answer 2: CREATE TABLE Advisors (AdvisorID int NOT NULL CONSTRAINT PK_Advisors PRIMARY KEY CLUSTERED, LastName_varchar(25) NOT NULL) GO CREATE TABLE Students (StudentID bigint NOT NULL CONSTRAINT PK_Students PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, PrimaryAdvisors int NOT NULL, SecondaryAdvisors int NOT NULL, CONSTRAINT PK_Students_Advisors1 FOREIGN KEY (PrimaryAdvisors) REFERENCES Advisors (AdvisorID), CONSTRAINT PK_Students_Advisors2 FOREIGN KEY (SecondaryAdvisors) REFERENCES Advisors (AdvisorID) ) Answer 3: CREATE TABLE Students (StudentID smallint NOT NULL CONSTRAINT PK_Students PRIMARY KEY CLUSTERED, LastName_varchar(25) NOT NULL, FirstName varchar (25) NOT NULL, PrimaryAdvisor int NOT NULL, SecondaryAdvisor int NOT NULL,) GO CREATE TABLE Advisors (AdvisorID smallint NOT NULL CONSTRAINT PK_Advisors PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, CONSTRAINT PK_ Advisors_Students FOREIGN KEY (AdvisorID) REFERENCES Students (StudentID) ) Answer 4: CREATE TABLE Students ( StudentID bit NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Advisors ( AdvisorID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE StudentsAdvisors (StudentsAdvisorsID bigint NOT NULL PRIMARY KEY, AdvisorID int NOT NULL, StudentID int NOT NULL, PrimaryAdvisors bit NOT NULL, FOREIGN KEY (AdvisorID) REFERENCES Advisors (AdvisorID), FOREIGN KEY (StudentID) REFERENCES Students (StudentID) ) Answer 5: CREATE TABLE Students (StudentID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Advisors (AdvisorID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE StudentsAdvisors ( AdvisorID int NOT NULL REFERENCES Advisors (AdvisorID), StudentID int NOT NULL REFERENCES Students (StudentID), PrimaryAdvisors bit NOT NULL, CONSTRAINT PK_StudentsAdvisors PRIMARY KEY (AdvisorID, StudentID) )

Answer 1: CREATE TABLE Students (StudentID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Advisors (AdvisorID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE StudentsAdvisors ( AdvisorID int NOT NULL REFERENCES Advisors (AdvisorID), StudentID int NOT NULL REFERENCES Students (StudentID), PrimaryAdvisors bit NOT NULL, CONSTRAINT PK_StudentsAdvisors PRIMARY KEY (AdvisorID, StudentID) )

Question 47 Q: You are a database developer for TelePages that stores customer information. Customer information is retrieved by their phone number. The table named PhoneNumber is shown below: PhoneNumbers -PhoneNumberID -AreaCode -Exchange -Number -LastName -FirstName -StreetAddress -City -State -PostalCode After loading 100,000 names into the table, you create indexes by using the following script: ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName]) GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID]) GO You begin testing the performance of the database. You notice that queries such as the following take a long time to execute: Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'. How should you improve the processing performance of these types of queries? Answer 1: Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column. Add a nonclustered index on the City column. Answer 2: Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column. Add a nonclustered index on the AreaCode, Exchange, and Number columns. Answer 3: Change the PRIMARY KEY constraints to a nonclustered index. Execute an UPDATE STATISTICS FULLSCAN ALL statement in SQL Query Analyzer. Answer 4: Change the PRIMARY KEY constraints to a nonclustered index. Remove the unique index from the PhoneNumberID column. Correct Answer: 1

Answer 1: Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column. Add a nonclustered index on the City column.

Question 28 Q: You are a database developer for Acme Learning Academy. You are designing a table to record the program enrolled in by each student. You create a table named StudentPrograms that includes the columns shown below: StudentPrograms ·StudentID ·ProgramID ·StartDate ·EndDate How should you uniquely identify each student's program? Answer 1: Create a composite PRIMARY KEY constraint on the StudentID and ProgramID columns. Answer 2: Create a PRIMARY KEY constraint on the StudentID column. Answer 3: Create a PRIMARY KEY constraint on the StudentID and StartDate columns. Answer 4: Create a PRIMARY KEY constraint on the StudentID, ProgramID, and StartDate columns.

Answer 1: Create a composite PRIMARY KEY constraint on the StudentID and ProgramID columns.

Question 22 Q: You have designed a database for a Web site that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor. For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor? Answer 1: Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction. Answer 2: Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ. Answer 3: Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an explicit transaction. Answer 4: Create a cursor that uses pessimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an implicit transaction.

Answer 1: Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.

Question 8 Q: You are a database developer for Books and More. You are creating a database for a book inventory system. When a book is sold, the total quantity for that title should be decreased in a table named Inventory. When inventory levels fall below a certain number, the operations manager must be notified via email. You want to automate this process. What should you do? Answer 1: Create a stored procedure that will be called to update the Inventory table. If the resulting quantity is less than the specified quantity, use the xp_logevent system stored procedure to page the manager. Answer 2: Create an INSTEAD OF UPDATE trigger on the Inventory table. If the quantity in the inserted table is less than the specified quantity, use SQLAgentMail to send an e-mail message to the manager. Answer 3: Create a FOR UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the specified quantity, use the xp_send_dbmail system stored procedure to email the manager. Answer 4: Schedule the SQL server job to run at four-hour intervals. Configure the job to use the @notify_level_page = 2 argument. Configure the job so that it tests each item's quantity against the specified quantity. Configure the job so that it returns a false value if the item requires restocking. This will trigger an email to the manager.

Answer 1: Create a stored procedure that will be called to update the Inventory table. If the resulting quantity is less than the specified quantity, use the xp_logevent system stored procedure to page the manager.

Question 57 Q: You are the database developer for a company that provides custom auto painting services. The company maintains data about its employees in a table named tblEmployee. The script that was used to create the employee table is shown below: CREATE TABLE tblEmployee ( EmployeeID int NOT NULL; EmpType char (1) NOT NULL, EmployeeName char (50) NOT NULL, Address char (50) NULL, Phone char (20) NULL, CONSTRAINT PK_Employee PRMARY KEY (EmployeeID) ) The EmpType column in this table is used to identify employees as executive, administrative, or consultants. You need to ensure that the administrative employees can add, update, or delete data for non-executive employees only. What should you do? Answer 1: Create a view, and include the WITH ENCRYPTION clause. Answer 2: Create a view, and include the WITH CHECK OPTION clause. Answer 3: Create a view, and include the SCHEMABINDING clause. Answer 4: Create a view, and build a covering index on the view. Answer 5: Create a user-defined function that returns a table containing the non-executive employees

Answer 1: Create a view, and include the WITH ENCRYPTION clause.

Question 63 Q: You manage a table named Employees. The Employees table contains the following information: EmployeeId WorkStatus 707 Active 708 Active 709 On Leave 710 NULL 711 Retired 712 Retired You write the following query to return all customers that do not have NULL or 'Retired' for their status: SELECT * FROM Employees WHERE Status NOT IN (NULL, 'Retired') What result is returned by this query? Answer 1: EmployeeID WorkStatus 707 Active 708 Active 709 On Leave Answer 2: EmployeeID WorkStatus Answer 3: EmployeeID WorkStatus 707 Active 708 Active 709 On Leave 710 NULL Answer 4: EmployeeID WorkStatus 707 Active 708 Active 709 On Leave 710 NULL 711 Retired 712 Retired

Answer 1: EmployeeID WorkStatus 707 Active 708 Active 709 On Leave

Question 2 Q: You are optimizing an SQL Server 2012 database. You plan to add a new index, drop three older indexes, and convert two other indexes to composite and covering indexes. For documentation purposes, you must create a report that shows the indexes used by queries before and after you make these changes. What should you do? Answer 1: Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report. Answer 2: Execute each query in SQL Query Analyzer, and use the Show Execution Plan option. Use the output for the report. Answer 3: Run the Index Tuning Wizard against a Workload file. Use the output for the report. Answer 4: Execute the DBCC SHOW_STATISTICS statement. Use the output for the report.

Answer 1: Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.

Question 67 Q: You have been asked to create a list of all Manager ID numbers and the employee ID numbers for the employee supervised by a particular manager. All the required information can be found in a table named Employees. You have started to create the query and have written this much so far: SELECT e.ManagerID AS [ManagerID], e.EmployeeID AS [EmployeeID] FROM Employees e You need to finish the query so that it returns a list of all Manager IDs and their assigned Employee IDs. Which join clause shown below would allow you to complete the query? Answer 1: INNER JOIN Employees m ON m.EmployeeID = e.ManagerID Answer 2: LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID Answer 3: RIGHT JOIN Employees m ON m.EmployeeID = e.ManagerID Answer 4: CROSS JOIN Employees e

Answer 1: INNER JOIN Employees m ON m.EmployeeID = e.ManagerID

Question 18 Q: You are a database developer for Acme Learning Academy. The company has a database that contains student information. You are designing transactions to ensure data integrity while data is entered into the database. The script for the transactions is shown below: Transaction 1 BEGIN TRANSACTION UPDATE Student *SET StudentName=@Name <mailto:StudentName=@Name> *WHERE StudentID=@StudID <mailto:StudentID=@StudID> UPDATE StudentPhone *SET PhoneNumber=@Phone <mailto:PhoneNumber=@Phone> *WHERE StudentID=@StudID <mailto:StudentID=@StudID> *AND PhoneType=@PType <mailto:PhoneType=@PType> COMMIT TRANSACTION Transaction 2 BEGIN TRANSACTION UPDATE StudentPhone SET PhoneNumber=@Phone *WHERE StudentID=@StudID <mailto:StudentID=@StudID> AND PhoneType = @PType UPDATE StudentAddress SET Street = @Street *WHERE StudentID=@StudID <mailto:StudentID=@StudID> *AND AddressType=@AType <mailto:AddressType=@AType> UPDATE Student SET StudentName = @Name WHERE StudentID = @StudID COMMIT TRANSACTION While testing these scripts, you discover that the database server occasionally detects a deadlock condition. What should you do? Answer 1: In Transaction 2, move the UPDATE Student statement before the UPDATE StudentPhone statement. Answer 2: Add the SET DEADLOCK_PRIORITY LOW statement to both transactions. Answer 3: Set the isolation level of the Transaction 1 to NONE. Answer 4: Add the SET LOCK_TIMEOUT 0 statement to both transactions

Answer 1: In Transaction 2, move the UPDATE Student statement before the UPDATE StudentPhone statement.

Question 36 Q: You are a database developer for your company's online transaction processing database. Many of the tables have more than 1 million rows. All of the tables have a clustered index, but the heavily accessed tables have at least one nonclustered index. You set up 2 RAID arrays on the database server that will be used to contain the data files. You want to place the tables and indexes to ensure optimal Input/Output performance. You create one filegroup on each RAID array. What should you do next? Answer 1: Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup. Answer 2: Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and their nonclustered indexes on different filegroups. Answer 3: Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and all indexes belonging to those tables on different filegroups. Answer 4: Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroups.

Answer 1: Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.

Question 34 Q: You design the following entities for a school course system. You would like to normalize the entities. What should you do? Examine the entities shown here. Answer 1: Remove the CourseTitle1, CourseTitle2, and CourseTitle3 attributes in the Student entity. Answer 2: Remove the Course entity. Answer 3: Add enough CourseTitle attributes in the Student entity to accommodate all of the courses that a student might take. Answer 4: Do nothing. The database is normalized

Answer 1: Remove the CourseTitle1, CourseTitle2, and CourseTitle3 attributes in the Student entity.

Question 13 Q: You are the database developer for a manufacturing company. Information about each of the company's plant and the equipment located at each plant is stored in a database named Equipment. The plant information is stored in a table named Location, and the equipment information is stored in a table named Parts. The scripts that were used to create these tables are shown below: CREATE TABLE Location ( LocationID int NOT NULL, LocationName char (30) NOT NULL UNIQUE, CONSTRAINT PK_Location PRIMARY KEY (LocationID) ) CREATE TABLE Parts ( PartID int NOT NULL, LocationID int NOT NULL, PartName char (30) NOT NULL, CONSTRAINT PK_Parts PRIMARY KEY (PartID), CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID) REFERENCES Location (LocationID) ) The company is in the process of closing several existing plants and opening several new ones. When a plant is closed, the information about the plant and all of the equipment at that plant must be deleted from the database. You have created a stored procedure to perform this operation. The stored procedure is shown below: CREATE PROCEDURE sp_DeleteLocation @LocName char(30) AS BEGIN DECLARE @PartID int DECLARE crs_Parts CURSOR FOR SELECT p.PartID FROM Parts AS p INNER JOIN Location AS 1 ON p.LocationID = @LocName WHERE 1.LocationName = @LocName OPEN crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID WHILE (@@FETCH_STATUS <> -1) BEGIN DELETE Parts WHERE CURRENT OF crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID END CLOSE crs_Parts DEALLOCATE crs_Parts DELETE Location WHERE LocationName = @LocName END This procedure is taking longer than expected to execute. You need to reduce the execution time of the procedure. What should you do? Answer 1: Replace the cursor operation with a single DELETE statement. Answer 2: Add a nonclustered index on the PartID column of the parts table. Answer 3: Add a BEGIN TRAN statement to the beginning of the procedure, and add a COMMIT TRAN statement to the end of the procedure. Answer 4: Set the transaction isolation level to READ UNCOMMITTED for the procedure.

Answer 1: Replace the cursor operation with a single DELETE statement

Question 66 Q: You are designing a query for a database that contains tables named Employees and Orders. The relevant columns in these tables are described as follows: TABLE: Employees EmployeeID int PRIMARY KEY, Name nvarchar(50) TABLE: Orders OrderID int PRIMARY KEY, SoldByEmployeeID int FOREIGN KEY (References Employees(EmployeeID)) You need to write a query that returns the employee ID, employee name, and the total number of orders that have been sold by each employee in the company. Which query should you use? Answer 1: SELECT e.EmployeeID, e.Name, COUNT(o.OrderID) FROM dbo.Employees AS e LEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeID GROUP BY e.EmployeeID, e.Name Answer 2: SELECT e.Name, COUNT(*) FROM dbo.Employees AS e LEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeID GROUP BY e.Name Answer 3: SELECT e.Name, COUNT(o.OrderID) FROM dbo.Employees AS e LEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeID GROUP BY e.Name Answer 4: SELECT e.EmployeeID, e.Name FROM dbo.Employees AS e JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeID GROUP BY e.EmployeeID, e.Name

Answer 1: SELECT e.EmployeeID, e.Name, COUNT(o.OrderID) FROM dbo.Employees AS e LEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeID GROUP BY e.EmployeeID, e.Name

Q: You are a database developer for OrderItNow.com. The company tracks its order information through its website in a SQL Server 2012 database. The database includes two tables that contain order details. The tables are named Order and LineItem. The company's internal auditors have discovered an error. Every item that was ordered on July 1, 2000 was entered into the database with a price that was $10 more than it should have been. You need to correct the error in the database as quickly as possible. Which script should you use? Answer 1: UPDATE l SET Price = Price - 10 FROM LineItem AS l INNER JOIN [Order] AS o ON l.OrderID = o.OrderID WHERE o.OrderDate >= '7/1/2000' AND o.OrderDate < '7/2/2000' Answer 2: UPDATE l SET Price = Price - 10 FROM LineItem AS l INNER JOIN [Order] AS o ON l.OrderID = o.OrderID WHERE o.OrderDate = '7/1/2000' Answer 3: DECLARE @ItemID int DECLARE items_cursor CUSOR FOR SELECT l.ItemID FROM LineItem AS l INNER JOIN [Order] AS o ON l.OrderID = o.OrderID WHERE o.OrderDate >= '7/1/2000' AND o.OrderDate >= '7/1/2000' FOR UPDATE OPEN items_cursor FETCH NEXT FROM Items_cursor INTO @ItemID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE LineItem SET Price = Price - 10 WHERE CURRENT OF items_cursor FETCH NEXT FROM items_cursor INTO @ItemID END CLOSE items_cursor DEALLOCATE items_cursor Answer 4: DECLARE @OrderID int DECLARE order_cursor CURSOR FOR SELECT ordered FROM [Order] WHERE OrderDate = '7/1/2000' OPEN order_cursor FETCH NEXT FROM order_cursor INTO @OrderID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE LineItem SET Price = Price - 10 WHERE OrderID= @OrderID FETCH NEXT FROM order_cursor INTO @OrderID END CLOSE order_cursor DEALLOCATE order_curso

Answer 1: UPDATE l SET Price = Price - 10 FROM LineItem AS l INNER JOIN [Order] AS o ON l.OrderID = o.OrderID WHERE o.OrderDate >= '7/1/2000' AND o.OrderDate < '7/2/2000'

Question 42 Q: You are a database developer for Acme Learning Academy. Acme wants to implement a survey to all students and instructors regarding materials used in the current curriculum. As the survey responses are received from the survey participants, they are inserted into a table named SurveyResults. Once all of the responses to a survey are received, summaries of the results will be produced. You have been asked to create a non-biased summary by using every tenth row of responses from the survey. You need to produce the summary as quickly as possible. What should you do? Answer 1: Use a cursor to retrieve all of the data for the survey. Use the FETCH RELATIVE 10 statement to select the summary data from the cursor. Answer 2: Use a SELECT INTO statement to retrieve the data for the survey into a temporary table. Use a SELECT TOP 10 statement to retrieve the first row from the temporary table. Answer 3: Set the query rowcount to 10. Use a SELECT statement to retrieve and summarize the survey data. Answer 4: Use a SELECT TOP 10 statement to retrieve and summarize the survey data.

Answer 1: Use a cursor to retrieve all of the data for the survey. Use the FETCH RELATIVE 10 statement to select the summary data from the cursor

Question 1 Q: You are a database developer for a healthcare company. The company's national offices transmit their sales information to the company's main office in an XML document. The XML documents are then stored in a table named SalesXML, which is located in a SQL Server 2012 database. The data contained in these documents includes the names of doctors, the names of patients, and other detailed information about the patient's medical history. You create several tables in this database. Now you need to import data from the XML documents into these tables. What should you do? Answer 1: Use the OPENXML function to access the data and to insert it into the appropriate tables. Answer 2: Use SELECT statements that include the FOR XML AUTO clause to copy the data from the XML documents into the appropriate tables. Answer 3: Use SELECT statements that include the FOR XML EXPLICIT clause to copy the data from the XML documents into the appropriate tables. Answer 4: Build a view on the SalesXML table that displays the contents of the XML documents. Use SELECT INTO statements to extract the data from this view into the appropriate tables.

Answer 1: Use the OPENXML function to access the data and to insert it into the appropriate tables.

Question 35 Q: You have been hired to support an existing SQL Server 2012 database. The database uses a script to input information that includes the following lines of code: DECLARE @RangeStart INT = 0; DECLARE @RangeEnd INT = 8000; DECLARE @RangeStep INT = 1; WITH NumberRange(ItemValue) AS (SELECT ItemValue FROM (SELECT @RangeStart AS ItemValue) AS t UNION ALL SELECT ItemValue + @RangeStep FROM NumberRange WHERE ItemValue < @RangeEnd) SELECT ItemValue FROM NumberRange OPTION (MAXRECURSION 100) Which result will be returned when this code is executed? Answer 1: 101 rows will be returned with a maximum recursion error. Answer 2: 10,001 rows will be returned with a maximum recursion error. Answer 3: 101 rows will be returned with no error. Answer 4: 10,001 rows will be returned with no error.

Answer 1: 101 rows will be returned with a maximum recursion error.

Q: You develop a SQL Server 2012 database named AsherDb that contains a table named Customers that is defined below. CREATE TABLE Customer (CustomerID INT NOT NULL PRIMARY KEY, CustomerName VARCHAR(255) NOT NULL, CustomerAddress VARCHAR(1000) NOT NULL) You have been asked to add a new table named Orders and create the definition shown here: CREATE TABLE Orders (OrderID INT NOT NULL PRIMARY KEY, CustomerID INT NOT NULL, OrderDescription VARCHAR(2000) ) You need to ensure integrity between the CustomerId columns in the Orders and Customer tables, with the same values existing in both tables. Which statement would be best to use? Answer 1: ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) Answer 2: ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders (CustomerId) Answer 3: ALTER TABLE Orders ADD CONSTRAINT CK_Crders_CustomerID CHECK (CustomerId IN (SELECT CustomerId FROM Customer)) Answer 4: ALTER TABLE Customer ADD OrderId INT NOT NULL; ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES Orders (CrderlD); Answer 5: ALTER TABLE Orders ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)

Answer 1: ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)

Question 7 Q: You are a database developer for a Acme Learning Academy. The school has a database named ALA that contains tables named Enrollments and Employees. New student enrollment information is stored in the Enrollments table. Employee information is stored in the Employees table. The Employees table has a bit column named Current. This column indicates whether an employee is currently employed by the school. The Employees table also has a column named EmployeeID that uniquely identifies each employee. All enrollments entered into the Enrollments table must contain the Employee ID of an academic advisor who assisted the student. The academic advisor must be a current employee at the time of the enrollment. How should you enforce this requirement? Answer 1: Add a FOR INSERT trigger on the Enrollments table. In the trigger, join the Employee table with the inserted tabled based on the EmployeeID column, and test the Current value. Answer 2: Add a CHECK constraint on the EmployeeID column of the Enrollments table. Answer 3: Add a Foreign KEY constraint on the EmployeeID column of the Enrollments table that references the EmployeeID column in the Employee table. Answer 4: Use the Distributed Transaction Coordinator to enlist the employee table in a distributed transaction that will roll back the entire transaction if the employee ID is not active

Answer 1: Add a FOR INSERT trigger on the Enrollments table. In the trigger, join the Employee table with the inserted tabled based on the EmployeeID column, and test the Current value.

Question 25 Q: You are designing a logical model to track airline flights. In your system, an airplane is owned by one airline, and a flight would always be flown by one airplane. You create the following entities: *Airplane *Flight *Airline In the following sentence diagrams, the arrows indicate a one-to-many relationship. Which of the following diagrams describes the relationship between these three entities that meets the scenario requirements? Answer 1: Airline -> Airplane -> Flight Answer 2: Flight -> Airline ->Airplane Answer 3: Airplane ->Airline ->Flight Answer 4: Airplane -> Flight ->Airline

Answer 1: Airline -> Airplane -> Flight

Question 64 Q: You use SQL Server 2012 database to develop an application for a shopping cart. You need to invoke a table-valued function for each row returned by a query. Which operator would best accomplish this? Answer 1: CROSS APPLY Answer 2: CROSS JOIN Answer 3: UNPIVOT Answer 4: PIVOT

Answer 1: CROSS APPLY

Question 74 Q: You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You wish to create a view on the OrderDetails and Order tables as shown: CREATE VIEW vOrders WITH SCHEMABINDING AS SELECT o.ProductID, o.OrderDate, SUM(od.UnitPrice * od.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. How would you do so? Answer 1: Create a clustered index on the view. Answer 2: Create an INSTEAD OF trigger on the view. Answer 3: Create an AFTER trigger on the view. Answer 4: Modify the view to use the WITH VIEW_METADATA clause.

Answer 1: Create a clustered index on the view.

Question 6 Q: You are a database developer for FlyRite Airlines. A table named FlightTimes in the Airlines database contains flight information for all airlines. The company's Customer Service department uses an intranet-based application to manage travel reservations for their customers. This application retrieves flight information for each airline from the FlightTimes table. The Customer Service staff wants to book most customer on a FlyRite flight. FlyRite is identified in the database with a CustomerID of 101. You need to modify the application used by the Customer Service team so that they can request information on flights based on flight times without having to specify a value for the airline. The application should default to FlyRite flights. Customer Server personal should only be required to specify a value for the airline only if a different airline's flight times are needed. What should you do? Answer 1: Create a stored procedure that accepts a parameter with a default value of 101. Answer 2: Create two stored procedures, and specify that one of the stored procedures should accept a parameter and that the other should not. Answer 3: Create a user-defined function that accepts a parameter with a default value of 101. Answer 4: Create a view that filters the FlightTimes table on a value of 101. Answer 5: Create a default of 101 on the FlightTimes table.

Answer 1: Create a stored procedure that accepts a parameter with a default value of 101.

Question 56 Q: You are a database developer for an upscale car manufacturer. Each employee of the company is assigned to a 'Chevy' (executive), 'Saturn' (administrative), or 'GMC', (labor) position. The home page of the company intranet displays company news, 'The Morning Drive', that is customized for each position type. When an employee logs on to the company intranet, the home page identifies the employee's position type and displays the appropriate company news. Company news is stored in a table named tblNewDrive, which is located in the corporate database. The script that was used to create the News table is shown below: CREATE TABLE tblNewDrive ( NewsID int NOT NULL, NewsText varchar (8000) NOT NULL, EmployeePositionType char (15) NOT NULL, DisplayUntil datetime NOT NULL, DateAdded datetime NOT NULL DEFAULT (getdate( )), CONSTRAINT PK_ tblNewFlush PRIMARY KEY (NewsID) ) Users of the intranet need to view data in the tblNewDrive table, but do not need to insert, update, or delete data in the table. You need to deliver only the appropriate data to the intranet, based on the employee's position type. What should you do? (other than look for a new job) Answer 1: Create a stored procedure that returns the rows that apply to a specified position type. Answer 2: Create a view that is defined to return the rows that apply to a specified position type. Answer 3: Grant SELECT permissions on the EmployeePositionType column for each position type. Answer 4: Grant permission on the News table for each position type. Correct Answer: 1

Answer 1: Create a stored procedure that returns the rows that apply to a specified position type.

Question 52 Q: You've been hired on as a database developer at the top law firm in your city. The firm has a multi-tier application that is used to enter data about its cases and the clients of the cases. The client information is stored in a table named Clients. The script that was used to create this table is shown below: CREATE TABLE Clients ( ClientID int IDENTITY (1, 1) NOT NULL, FirstName char(20) NULL, LastName char(30) NULL, BirthDate date NULL, CONSTRAINT PK_Clients PRIMARY KEY (ClientID) ) When entering client information the database should raise a custom error message to the data entry application if a BirthData value is not supplied. What technique should you use to raise a custom error message if the BirthData value is not supplied? Answer 1: Create a trigger on the Clients table that validates the BirthDate column Answer 2: Add a CHECK constraint on the BirthDate column. Answer 3: Create a rule and bind the rule to the BirthDate column. Answer 4: Alter the Clients table so that the BirthDate column does not allow null. Correct Answer: 1

Answer 1: Create a trigger on the Clients table that validates the BirthDate column

Question 9 Q: You are a database developer for Acme Learning Academy. The company has created a Web site where potential students can request information. You are designing the SQL Server 2012 database to support the Web site. New requests are inserted into a table named Requests. Students who have requested documentation can return to the Web site and cancel or update their requests. When a request is canceled, the existing request must be copied to a table named Canceled. When a request is updated, the request should not be copied to the Canceled table. You need to develop a way to create the appropriate entries in the Canceled table. What should you do? Answer 1: Create a trigger on the Requests table to create the Canceled table entries. Answer 2: Create a cascading referential integrity constraint on the Requests table to create the Canceled table entries. Answer 3: Create a view on the Requests table. Include the WITH SCHEMABINDING option in the view definition. Answer 4: Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the view definition.

Answer 1: Create a trigger on the Requests table to create the Canceled table entries.

Question 4 Q: You are a database developer for an insurance company. Information about the company's insurance policies is stored in a SQL Server 2012 database. You create a table named Policy for this database by using the script shown below: CREATE TABLE Policy ( PolicyNumber int NOT NULL DEFAULT (0), InsuredLastName char (30) NOT NULL, InsuredFirstName char (20) NOT NULL, InsuredBirthDate dattime NOT NULL, PolicyDate datetime NOT NULL, FaceAmount money NOT NULL, CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber) ) Each time the company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered. What should you do? Answer 1: Create an INSTEAD OF INSERT trigger to generate a new policy number, and includes the policy number in the data inserted into the table Answer 2: Create an INSTEAD OF UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table. Answer 3: Create an AFTER UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table. Answer 4: Replace the DEFAULT constraint with a AFTER INSERT trigger that generates a new policy number and includes the policy number in the data inserted into the table.

Answer 1: Create an INSTEAD OF INSERT trigger to generate a new policy number, and includes the policy number in the data inserted into the table

Question 17 Q: You work as database developer for World Of Wonders Importers. The Sales department wants to track sales by multiple parameters such as age, county, etc to be able to spot relevant sales patterns. To produce such information you need to join four tables from a highly normalized database. How should you make the query response time as fast as possible in this scenario? Answer 1: Denormalize the database design. Answer 2: Create a view on the four tables. Create an index on the view. Answer 3: Further normalize the table. Answer 4: Add more indexes on the relevant columns in the tables that are joined. Answer 5: Improve performance by using functions to implement the query

Answer 1: Denormalize the database design.

Question 15 Q: You are a database developer for Acme Learning Academy. You are designing a SQL Server 2012 database that will be distributed with an application to all 30 locations. You create several stored procedures in the database that contain confidential information. You want to prevent the locations from viewing this confidential information. What should you do? Answer 1: Encrypt the text of the stored procedures. Answer 2: Remove the text of the stored procedures from the syscomments system table. Answer 3: Deny SELECT permissions on the syscomments system table to the public role. Answer 4: Deny SELECT permissions on the sysobjects system table to the public role.

Answer 1: Encrypt the text of the stored procedures.

Question 51 Q: You are a database developer for Global Dynamics. You update several stored procedures in the database that creates end of the quarter reports for the sales department. The stored procedures contain queries that retrieve data from four or more tables. Every table in the database has at least one index. Users have reported that the reports are running much slower than the previous version of the reports. How would you implement a strategy to improve the performance of the reports? Answer 1: Execute each stored procedure in SQL Query Analyzer, and use the show Server Trace option Answer 2: Create a script that contains the Data Definition Language of each stored procedure. Use this script as a workload file for the Index Tuning Wizard Answer 3: Capture the execution of each stored procedure in a SQL Profiler trace. Use the trace file as a workload file for the Index Tuning Wizard. Answer 4: Update the index statistics for the tables used in the stored procedures. Answer 5: Execute each stored procedure in SQL Query Analyzer, and use the Show Execution Plan option. Correct Answer: 1

Answer 1: Execute each stored procedure in SQL Query Analyzer, and use the show Server Trace option

Question 12 Q: You are the database developer for Acme Learning Academy. The database contains a table named Progression. The script that was used to create this table is shown below: CREATE TABLE Progression ( ProgID int IDENTITY(1,1)PRIMARY KEY NONCLUSTERED NOT NULL, ProgDate datetime NULL, StudnetID int NULL, InstructorID int NULL ) GO CREATE CLUSTERED INDEX c_symbol ON Progression (StudentID) The Progression table has frequent inserts and updates during the day. Reports are run against the table each night. You execute the following statement in the SQL Server Management Studio: DBCC SHOWCONTIG (Progression) The output for the statement is shown below: DBCC Statement Output DBCC SHOWCONTIG scanning 'Progression' table. . . . . Table: 'Progression'(1621580815); index ID:1, database ID:12Table level scan performed. -Pages Scanned-----------------------------------------:104 -Extents Scanned---------------------------------------:16 -Extent Switches----------------------------------------:24 -Avg. Pages per Extenbt-------------------------------:6.5 -Scan Density[Best Count:Actual Count]-----------:52.00%[13:25] -Logical Scan Fragmentation-------------------------:7.69% -Extent Scan Fragmentation---------------------------:43.75% -Avg. Bytes Free per page-----------------------------:460.1 -Avg. Page Density (full)------------------------------:94.32% DBCC execution completed. If DBCC printed error messages, contact your system administrator. You want to ensure optimal performance for the insert and select operations on the Progression table. What should you do? Answer 1: Execute the DBCC DBREINDEX statement on the table. Answer 2: Execute the UPDATE STATISTICS statement on the table. Answer 3: Execute the DROP STATISTICS statement on the clustered index. Answer 4: Execute the DROP INDEX and CREATE INDEX statements on the primary key index

Answer 1: Execute the DBCC DBREINDEX statement on the table.

Question 20 Q: You are a database developer for World of Wonders Importers. The company tracks its inventory in an SQL Server 2012 database. You have several queries and stored procedures that are executed against this database. Recently, the number of catalogued inventory items has increased which has caused the execution time of some, but not all, of the stored procedures to increase significantly. You must restore the performance of the slowrunning stored procedures to their original execution times. What should you do? Answer 1: Execute the sp_recompile system stored procedure for each of the slow-running procedures. Answer 2: Use the WITH RECOMPILE option to execute the slow-running stored procedures. Answer 3: Rebuild the Indexes that the under-performing stored procedures query. Answer 4: Delete all under-performing stored procedures and then recreate them. Correct Answer: 1

Answer 1: Execute the sp_recompile system stored procedure for each of the slow-running procedures.

Question 60 Q: You are a database developer for a large travel company. You have been granted CREATE VIEW permissions in the Reservations database. Some worker named Fred, has been granted CREATE TABLE permissions. Neither of you have been given database owner or system permissions, nor have you been added to any fixed server roles. Fred has created a table named Traveler that holds information about your company's customers. Travel agents will connect to the database and view the information stored in this table. The database logins for the travel agents have been assigned to the Agent database role. You want the travel agents to be able to view the name and address information from the Traveler table in two columns instead of six. One column should contain the traveler name and the other column should contain the address. Which three actions should you take? Answer 1: Instruct Fred to grant you SELECT permissions of needed columns on the Traveler table. Create a view named vwTravelers that displays the data in the desired format. Grant the Agent role SELECT permissions on the vwTravelers view. Answer 1: Grant the Agent role SELECT permissions on the Traveler table. Create a view named vwTravelers that displays the data in the desired format. Grant the Agent role SELECT permissions on the vwTravelers view. Answer 2: Instruct Fred to grant the Agent role SELECT permissions on the Traveler table. Create a view named vwTravelers that displays the data in the desired format. Grant the Agent role SELECT permissions on the vwTravelers view. Answer 4: Instruct Fred to create a view named vwTravelers that displays the data in the desired format. Create a view named vwTravelers that displays the data in the desired format. Grant the Agent role SELECT permissions on the vwTravelers view.

Answer 1: Instruct Fred to grant you SELECT permissions of needed columns on the Traveler table. Create a view named vwTravelers that displays the data in the desired format

Question 62 Q: You have been asked to create a SELECT statement that will return the customer ID numbers, customer names, and shipping addresses for all customers for whom a shipping address is recorded. You have also been asked to list each row returned in alphabetical order by Customer Name. The query that you have written so far is this: SELECT customer_id, customer_name AS customer, shipping_address as "SHIP TO" FROM customers WHERE shipping_address IS NOT NULL You know that you need to add an ORDER BY clause to return the results from the query in the order needed by the user. Which of the following clauses will not return the results in the desired order? Answer 1: ORDER BY customer_id, customer_name; Answer 2: ORDER BY customer_name; Answer 3: ORDER BY 2; Answer 4: ORDER BY "CUSTOMER"; +++++++++++++++++++++++++++++++++++

Answer 1: ORDER BY customer_id, customer_name; Answer 2: ORDER BY customer_name; Answer 3: ORDER BY 2;

Question 21 Q: You are the database developer for Global Dynamics. This database contains a table named Invoices. You are a member of the db_owner role. Joe, a member of the HR database role, created the GD_UpdateInvoices trigger on the Invoices table. Joe is out of the office, and the trigger is no longer needed. You execute the following statement in the sales database to drop the trigger: DROP TRIGGER GD_UpdateInvoices You receive the following error message: Cannot drop the trigger 'GD_UpdateInvoices, because it does not exist in the system catalog. What should you do before you can drop the trigger? Answer 1: Qualify the trigger name with the trigger owner in the DROP TRIGGER statement. Answer 2: Add your login name to the HR database role. Answer 3: Rename Joe's Login to match your Login. Answer 4: Disable the trigger before executing the DROP TRIGGER statement

Answer 1: Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

Question 14 Q: You are the developer of a database named Students. You are given a list of critical care reports that you must create for each school. The majority of these reports will be run at the same time. You begin to develop the appropriate queries for each report. As you write the queries you also develop indexes to increase report performance. You want to ensure that you have created the proper indexes. What should you do? Answer 1: Run the Database Engine Tuning Advisor against a workload file that contains the queries used in the reports. Answer 2: Create a SQL Profiler trace, and use the Objects event classes. Answer 3: Run System Monitor, and use the SQLServer:Access Methods counter. Answer 4: Use the command SET SHOWPLAN_TEXT ON; and then execute the queries against the tables.

Answer 1: Run the Database Engine Tuning Advisor against a workload file that contains the queries used in the reports.

Question 5 Q: You are a database developer for a marketing organization. You manage a database containing a table named Sales. Sales holds information about what items were sold, when they were sold, and which sales person made the sale. The sales manager asks you to create a report of the sales people who have the 20 highest total sales. Which query should you use to accomplish this? Answer 1: SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC Answer 2: SELECT TOP 20 PERCENT LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC Answer 3: SELECT LastName, FirstName, COUNT(*) AS sales FROM sales GROUP BY LastName, FirstName HAVING COUNT (*) > 20 ORDER BY 3 DESC Answer 4: SELECT TOP 20 LastName, FirstName, MAX(OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC Answer 5: SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

Answer 1: SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

Question 11 Q: You are a database developer for Blues Shoes. The company uses one SQL Server 2012 database server to hold sales data. This database has a table named Sales, which contains consolidated sales information from company's 15 regional offices and 120 retail stores. During the last year, more than 200,000 rows have been added to the Sales table. Users of the database report that performance during the course of the year has steadily declined. You need to improve the performance of queries against the Sales table. You open SQL Server Management Studio. Now, which script should you execute? Answer 1: UPDATE STATISTICS Sales WITH FULLSCAN Answer 2: CREATE STATISTICS Sales WITH FULLSCAN Answer 3: EXEC sp_updatestats Answer 4: DROP STATISTICS Sales.salesindex Correct Answer: 1

Answer 1: UPDATE STATISTICS Sales WITH FULLSCAN

Question 10 Q: You are a database developer for a SQL Server 2012 database. The database is in the default configuration. The number of users accessing the database has increased from 50 to 2000 in the last month. Users inform you that they are receiving error messages frequently. The following is an example of an error message that was received: Transaction (Process ID 56) was deadlocked on [lock] resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What should you do? Answer 1: Use SQL Profiler to capture deadlock events. Answer 2: Use a higher transaction isolation level for transactions used in the database. Answer 3: Use System Monitor to monitor lock requests and deadlocks. Answer 4: Execute the sp_configure system stored procedure to increase the number of simultaneous user connections allowed to SQL server. Correct Answer: 1

Answer 1: Use SQL Profiler to capture deadlock events.

Question 45 Q: You are database developer for your company's SQL Server 2012 database named Sales. The company has several custom Web-based applications that retrieve data from the Sales database. Some of these applications use the EXECUTE statement to allow users to issue administrator ad hoc queries. As the use of the Web-based applications increases, queries are taking longer and longer to execute. You want to discover which application(s) are sending a high number of these queries to the database server. What should you do? Answer 2: Use SQL profiler to capture the RPC:Completed event. Group the trace by the HostName data column. Answer 3: Use system monitor to monitor the SQLServer:Database counter. Select all counters for the Sales database. Answer 4: Use system monitor to monitor the SQLServer:General Statistics counter. Select all counters for the Sales database.

Answer 1: Use SQL profiler to capture the RPC:Completed event. Group the trace by the ApplicationName data column.

Question 49 Q: You are a database developer for an online casino. You implement a SQL Server 2012 online transaction processing database. You have written several stored procedures that will produce sales reports. The stored procedures access existing tables, which are indexed. Before you put the stored procedures in the production environment, you want to ensure optimal performance of the new stored procedures. You also want to ensure that daily operations in the database are not adversely affected. What should you do? Answer 1: Use output from the Database Engine Tuning Advisor to identify whether indexes should be added. Answer 2: Create a covering index for each query contained in the stored procedures. Answer 3: For each query in the stored procedures, create an index that includes each column contained in the WHERE clause. Answer 4: Create statistics on all columns in the SELECT and WHERE clauses of each query. Correct Answer: 1

Answer 1: Use output from the Database Engine Tuning Advisor to identify whether indexes should be added.

Question 44 Q: You have been commissioned to develop a database that supports time reporting for the large legal firm, Dewey, Cheatum, and Howe, PA. Usually there is an average of five users accessing this database at one time, and query response times are less than one second. However, on Friday afternoons and Monday mornings, when most employees finally get around to entering their timesheet data, the database usage increases to an average of 50 users at one time. During these times, the query response times increase to an average of 15 to 20 seconds. You need to find the source of the slow query response times and correct the problem. What should you do? Answer 1: Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. Use this information to identify and redesign the transactions that are causing the locks. Answer 2: Query the sysprocesses and sysobjects system tables to find deadlocked resources and to identify which processes are accessing those resources. Set a shorter lock timeout for the processes that are accessing the deadlock resources. Answer 3: Query the sysprocesses system table to find which resources are being accessed. Add clustered indexes on the primary keys of all of the tables that are being accesses. Answer 4: Use the sp_monitor system stored procedure to identify which processes are being affected by the increased query response times. Set a less restrictive transaction isolation level for these processes.

Answer 1: Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. Use this information to identify and redesign the transactions that are causing the locks.

Q: You are a database developer for the Athletes R US Corporation. You are creating a database that will store statistics for 15 different college sports. This information will be used by 50 websites that publish various sports information. Each company's Web site arranges and displays the statistics in a different format. How should you package the information to distribute to the companies? Answer 1: Extract the data by using SELECT statements that include the FOR XML clause. Answer 2: Use the sp_makewebtask system stored procedure to generate HTML from the data returned by SELECT statements. Answer 3: Create Data Transformation Services packages that export the data from the database and place the data into tab-delimited text files. Answer 4: Create an application that uses SQL_DMO to extract the data from the database and transform the data into standard electronic data interchange (EDI) files.

Extract the data by using SELECT statements that include the FOR XML clause.

Question 70 Q: You support a database that contains a table named Briefs. This table includes two columns, Abstract and Document. Full-text indexing has been enabled on both columns. You have been asked to create a list of all documents in the table which contains the word 'contraband' in either column. Which code segment could you use to create the required list? Answer 1: SELECT * FROM Briefs WHERE FREETEXT(*,'contraband') Answer 2: SELECT * FROM Briefs WHERE Abstract LIKE '%contraband%' Answer 3: SELECT * FROM Briefs WHERE Abstract = '%contraband%' OR Document = '%contraband%' Answer 4: SELECT * FROM Briefs WHERE FREETEXT(Abstract,'contraband')

SELECT * FROM Briefs WHERE FREETEXT(*,'contraband')

Question 39 Q: You are a database developer for BuyFromMe.com. You implement a system that has two SQL Server 2012 computers named Main1 and Main2. Main1 is the online transaction processing server. Main2 stores past sales data. You add Main2 as a linked server to Main1. You are asked to generate a list of customers who have purchased a certain music CD. This list will be generated each month for promotional mailings. These CDs are represented in the database with a category ID of 21. You must retrieve this information from a table named SalesHistory. This table is located in the Archive database, which resides on Main2. You need to execute this query from Main1. Which script should you use? Answer 1: SELECT CustomerID FROM Main2.Archive.dbo.SalesHistory WHERE CategoryID = 21 Answer 2: EXEC sp_addlinkedserver 'Main2', 'SQL Server' GO SELECT CustomerID FROM Main2. Archive.dbo.SalesHistory WHERE CategoryID = 21 Answer 3: SELECT CustomerID FROM OPENROWSET ('SQLOLEDB', 'Main2'; 'p*word', 'SELECT CustomerID FROM Archive.dbo.SalesHistory WHERE CategoryID = 21') Answer 4: EXEC sp_addserver 'Main2' GO SELECT CustomerID FROM Main2.Archive.dbo.SalesHistory WHERE CategoryID = 21

SELECT CustomerID FROM Main2.Archive.dbo.SalesHistory WHERE CategoryID = 21

Question 31 Q: You are a database developer for Global Dynamics. You are creating a database for the Human Resources department for the company that will contain all employee records and location information. Global Dynamics has a little over 1,000 employees and experiences yearly personnel turnover of approximately 2%. For auditing purposes, Global Dynamics must retain all records of employees that are terminated or have voluntarily resigned. The main reason for the rapid database growth is that Human Resources is required to capture and save a larger amount of Employee demographic information every year. The company also needs to maintain a history of the demographics changes. The schema for the human resources database is shown in the human resources schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Script exhibit. See image below for table details. The scripts that will be used to create the indexes are shown below: ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_EmployeeConstraint] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [pk_EmpDemConstraint] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO You want to save as much disk space as possible and minimize the number of times in which the database files need to grow. All varchar columns are 60 percent full. You will create the database using the T-SQL command CREATE DATABASE. Which parameters of the CREATE DATABASE statement should you use? Each correct answer presents part of the solution. Choose two. Answer 1: SIZE= 2048KB FILEGROWTH = 5% Answer 2: SIZE = 1GB FILEGROWTH = 10 Answer 3: SIZE = 1048MB FILEGROWTH = 5 Answer 4: SIZE = 2GB FILEGROWTH = 10%

SIZE= 2048KB FILEGROWTH = 5%

Question 43 Q: You are a database developer for an online performance auto parts supplier. The company's product catalog is contained in a table named Products. The Products table is frequently accessed during normal business hours. Modifications to the Products table are written to a table named PendingProductUpdate. The tables are shown below: Products PendingProductUpdate productID productID vendorPN vendorPN description description unitprice unitprice The PendingProductUpdate table will be used to update the Products table after business hours. The database server runs SQL Server 2012 and is set to 8.0 compatibility mode. You need to create a script that will be used to update the Products table. Which script should you use? Answer 1: UPDATE p1 SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPrice FROM Products p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO Answer 2: UPDATE Products p1 SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPrice FROM Products p2, PendingProductUpdate p1 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO Answer 3: UPDATE Products p1 SET [Description]=p2.[Description], UnitPrice=P2.UnitPrice FROM Products, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO Answer 4: UPDATE Products p1 SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPrice FROM (SELECT [Description], UnitPrice FROM PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO Correct Answer: 1

UPDATE p1 SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPrice FROM Products p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO