QUESTION 71
You need to ensure that tables are not dropped from your database. What should you do?
A. Create a DDL trigger that contains COMMIT.
B. Create a DML trigger that contains COMMIT.
C. Create a DDL trigger that contains ROLLBACK.
D. Create a DML trigger that contains ROLLBACK.
Answer: C
QUESTION 72
You are responsible for a SQL Server database. You require the tables to be added or altered only on the first day of the month. You need to ensure that if the tables are attempted to be modified or created on any other day, an error is received and the attempt is not successful.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TRG_TABLES_ON_FIRST
ON DATABASE FOR CREATE_TABLE
AS
IF DATEPART(day,getdate())>1
BEGIN
RAISERROR (‘Must wait til next month.’, 16, 1)
END
B. CREATE TRIGGER TRG_TABLES_ON_FIRST ON DATABASE FOR CREATE_TABLE,ALTER_TABLE
AS
IF DATEPART(day,getdate())>1
BEGIN
RAISERROR (‘Must wait til next month.’, 16, 1)
END
C. CREATE TRIGGER TRG_TABLES_ON_FIRST ON DATABASE FOR CREATE_TABLE,ALTER_TABLE
AS
IF DATEPART(day,getdate())>1
BEGIN
ROLLBACK
RAISERROR (‘Must wait til next month.’, 16, 1)
END
D. CREATE TRIGGER TRG_TABLES_ON_FIRST ON ALL SERVER FOR ALTER_DATABASE AS
IF DATEPART(day,getdate())>1
BEGIN
ROLLBACK
RAISERROR (‘Must wait til next month.’, 16, 1)
END
Answer: C
QUESTION 73
You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources.
You need to deploy this assembly by using the minimum required permissions. You must ensure that your database remains as secure as possible.
Which options should you set?
A. PERMISSION_SET = SAFE
TRUSTWORTHY ON
B. PERMISSION_SET = SAFE
TRUSTWORTHY OFF
C. PERMISSION_SET = UNSAFE
TRUSTWORTHY ON
D. PERMISSION_SET = EXTERNAL_ACCESS
TRUSTWORTHY OFF
Answer: B
QUESTION 74
Your company stores vendor and price information in a database. All items in the database have a list price.
You need to increase the list price for all products of only the vendor named Fabrikam by 20.00.
Which query should you use?
A. UPDATE Production.Product
SET ListPrice = ListPrice + 20.00
WHERE NOT EXISTS (
SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = ‘Fabrikam’);
B. UPDATE Production.Product SET ListPrice = ListPrice + 20.00
WHERE VendorId NOT IN (
SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = ‘Fabrikam’);
C. UPDATE Production.Product SET ListPrice = ListPrice + 20.00
WHERE EXISTS (
SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = ‘Fabrikam’);
D. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE VendorId IN
(SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = ‘Fabrikam’);
Answer: D
QUESTION 75
You have two tables named Customer and SalesOrder.
You need to identify all customers that have not yet made any purchases and those that have only made orders with an OrderTotal less than 100.
Which query should you use?
A. SELECT * FROM Customer
WHERE 100 > ALL (
SELECT OrderTotal FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)
B. SELECT * FROM Customer
WHERE 100 > SOME (
SELECT OrderTotal FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)
C. SELECT * FROM Customer
WHERE 100 > (
SELECT MAX(OrderTotal) FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)
D. SELECT * FROM Customer
WHERE EXISTS (
SELECT SalesOrder.CustomerID FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID AND SalesOrder.OrderTotal <= 100)
Answer: A
QUESTION 76
You have two tables named Customer and SalesOrder.
In the Customer table you have 1000 customers, of which 900 customers have orders in the SalesOrder table.
You execute the following query to list all customers that have had at least one sale.
SELECT * FROM Customer WHERE Customer.CustomerID IN (SELECT Customer.CustomerID FROM SalesOrder)
You need to identify the results of the query. Which results will the query return?
A. No rows
B. A warning message
C. The 1000 rows in the Customer table
D. The 900 rows in the Customer table with matching rows in the SalesOrder table
Answer: C
QUESTION 77
You have the following rows in the
Customer Table:
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant
You write the following query to return all customers that do not have NULL or ‘Dormant’ for their status:
SELECT * FROM Customer
WHERE Status NOT IN (NULL, ‘Dormant’)
You need to identify the results of the query.
Which result should you expect?
A. CustomerId Status
B. CustomerId Status
1 Active
2 Active
3 Inactive
C. CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
D. CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant
Answer: B
QUESTION 78
You have a table named Employee.
You document your company’s organizational hierarchy by inserting the EmployeeID of each employee’s manager in the ReportsTo column.
You need to write a recursive query that produces a list of employees and their manager.
The query must also include the employee’s level in the hierarchy.
You write the following code segment. (Line numbers are included for reference only.)
01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
02 AS (
03 ………
04 )
05 SELECT EmployeeID, FullName, ManagerName, Level
06 FROM EmployeeList;
Which code segment should you insert at line 3?
A. SELECT EmployeeID, FullName, ” AS [ReportsTo], 1 AS [Level]
FROM Employee WHERE ReportsTo IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullNName, mgr.FullName, 1 + 1 AS [Level]
FROM Employee emp
JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
B. SELECT EmployeeID, FullName, ” AS [ReportsTo], 1 AS [Level]
FROM Employee WHERE ReportsTo IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId
C. SELECT EmployeeID, FullName, ” AS [Reports To], 1 AS [Level]
FROM Employee
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, 1 + 1 AS [Level]
FROM Employee emp
LEFT JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
D. SELECT EmployeeID, FullName, ” AS [ReportsTo], 1 AS [Level]
FROM Employee
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeID
Answer: B
QUESTION 79
You need to determine the result of executing this code segment.
DECLARE @RangeStart INT = 0;
DECLARE @RangeEnd INT = 10000;
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?
A. 101 rows will be returned with no error.
B. 10,001 rows will be returned with no error.
C. 101 rows will be returned with a maximum recursion error.
D. 10,001 rows will be returned with a maximum recursion error.
Answer: C
QUESTION 80
You need to implement a common table expression (CTE). Which code segment should you use?
A. CREATE VIEW SalesByYear AS
SELECT Year, Region, SUM(OrderTotal)
FROM Orders
GROUP BY Year, Region;
GO
SELECT Year, Region, Total
FROM SalesByYear;
B. WITH SalesByYear(Year,Region,Total)
AS (SELECT Year, Region, SUM(OrderTotal)
FROM Orders GROUP BY Year,Region)
SELECT Year, Region, Total FROM SalesByYear;
C. SELECT Year, Region, Total
FROM (
SELECT Year, Region, SUM(OrderTotal) AS Total
FROM Orders
GROUP BY Year, Region) AS [SalesByYear];
D. SELECT DISTINCT Year, Region, (
SELECT SUM(OrderTotal) FROM Orders SalesByYear
WHERE Orders.Year = SalesByYear.YEAR AND Orders.Region = SalesByYear.Region) AS [Total]
FROM Orders;
Answer: B
If you want to pass Microsoft 70-433 successfully, donot missing to read latest lead2pass Microsoft 70-433 practice tests.
If you can master all lead2pass questions you will able to pass 100% guaranteed.