2015 New Updated 70-433 Exam Dumps Questions and Answers are all from Microsoft Official Exam Center! Some new questions added into this new released 70-433 Dumps! Download 70-433 Exam Dumps Full Version Now and Pass one time!
Exam Code: 70-433
Exam Name: TS: Microsoft SQL Server 2008, Database Development
Certification Provider: Microsoft
Keywords: 70-433 Exam Dumps,70-433 Practice Tests,70-433 Practice Exams,70-433 Exam Questions,70-433 PDF,70-433 VCE Free,70-433 Book,70-433 E-Book,70-433 Study Guide,70-433 Braindump,70-433 Prep Guide
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
Explanation:
DDL triggers can execute either when a DDL statement is executed or when the user logs on to the SQL Server instance.
DDL triggers can be scoped at either the database or instance level.
To scope a DDL trigger at the instance level, you use the ON ALL SERVER option.
To scope a DDL trigger at the database level, you use the ON DATABASE option.
The following is an example of a DDL trigger:
CREATE TRIGGER tddl_tabledropalterprevent
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You are attempting to drop or alter tables in production!’
ROLLBACK;
Almost all DDL commands run within the context of a transaction. Because a DDL trigger also runs within the same transaction context, any DDL statement running in the context of a transaction can be rolled back.
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:
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. Option A
B. Option B
C. Option C
D. Option D
Answer: A
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
Braindump2go New Published Exam Dumps: Microsoft 70-433 Practice Tests Questions, 210 Latest Questions and Answers from Official Exam Centre Guarantee You a 100% Pass! Free Download Instantly!
