Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 121:

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

• Question 122:

  You have been tasked to delete 1000 rows from a table named NewWidgets. There are 2000 rows in which the column ToBeDeleted set to 1. You need to write a Transact-SQL batch that will delete exactly 1000 rows.

  Which Transact-SQL batch should you use?

  A. DELETE TOP (1000) dbo.NewWidgets WHERE ToBeDeleted = 1;
  B. DECLARE @BatchSize INT = 10; WHILE (@BatchSize = 10) DELETE TOP (@BatchSize) dbo.NewWidgets WHERE ToBeDeleted = 1;
  C. DELETE TOP ((SELECT COUNT(*) FROM dbo.NewWidgets WHERE ToBeDeleted = 1)) w FROM dbo.NewWidgets w WHERE w.ToBeDeleted = 1;
  D. DECLARE @TotalRowCount INT = 0; WHILE (@TotalRowCount
  A. DELETE TOP (1000) dbo.NewWidgets WHERE ToBeDeleted = 1;

• Question 123:

  A table named Contacts includes a column named SmtpAddress.

  You must develop a report that returns e-mail addresses from the Contacts table that have the following format: at least one character, the at sign (@), at least one character, and then ".org". You need to return data that meets the requirements.

  Which Transact-SQL statement should you use?

  A. Select * from Contacts where SmtpAddress like '%@%[.]org'
  B. Select * from Contacts where SmtpAddress like '_%@_%.org'
  C. Select * from Contacts where SmtpAddress like '_%@_.org'
  D. Select * from Contacts where SmtpAddress like '%@%.org'
  B. Select * from Contacts where SmtpAddress like '_%@_%.org'

• Question 124:

  You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment (Line numbers are included for reference only):

  01 SELECT * 02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven, 04 FROM Customer 05 JOIN SalesOrder 06 ON Customer.CustomerID = SalesOrder.CustomerID 07 GROUP BY Customer.CustomerID) AS DonationsToFilter 08 WHERE FilterCriteria <= 100

  You need to insert a Transact-SQL clause in line 03 to complete the query.

  Which Transact-SQL clause should you insert?

  A. RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
  B. NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
  C. ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
  D. DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
  D. DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria

• Question 125:

  You have a user named John. He has SELECT access to the Sales schema. You need to eliminate John's SELECT access rights from the Sales.SalesOrder table without affecting his other permissions.

  Which Transact-SQL statement should you use?

  A. DROP USER John;
  B. DENY SELECT ON Sales.SalesOrder TO John;
  C. GRANT DELETE ON Sales.SalesOrder TO John;
  D. REVOKE SELECT ON Sales.SalesOrder FROM John;
  B. DENY SELECT ON Sales.SalesOrder TO John;

  ---

  REVOKE permanently removes both granted an denied permissions on an object, resulting in no permissions.Main thing you have to remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object for the operation, user will be able to perform the operation.

  DENY Denies permission to the object for an operation. Once it set, it takes precedence over all other GRANT permissions, user will not be able to perform the operation against the object.

  To sum up, because John does not have GRANT permissions on the Sales.SalesOrder table (instead it has GRANT permission on Sales schema), then REVOKE SELECT ON Sales.SalesOrder from John will not remove any permissions.

  Here is a code that shows it clearly. -- create a login and user CREATE LOGIN [John] WITH PASSWORD = '1', CHECK_POLICY = OFF GO USE [AdventureWorks2008] GO CREATE USER [John] FOR LOGIN [John] WITH DEFAULT_SCHEMA = [dbo] GO -- grant permission on Sales schema GRANT SELECT ON SCHEMA :: Sales TO [John] -- Run SELECT with John's credentials and see -- He sees records EXECUTE AS USER = 'John' SELECT * FROM Sales.SalesOrderHeader REVERT -- Revoke permisson for the table from him REVOKE SELECT ON Sales.SalesOrderHeader FROM [John] -- He still sees data EXECUTE AS USER = 'John' SELECT * FROM Sales.SalesOrderHeader REVERT -- This explicitly denies permission on SalesOrderHeader to John -- Once this is executed, he will not be able to see data -- even we grant him again. DENY SELECT ON Sales.SalesOrderHeader TO [John] -- He sees error message: The SELECT permission was denied on the object 'SalesOrderHeader', database 'AdventureWorks2008', schema 'Sales'. EXECUTE AS USER = 'John' SELECT * FROM Sales.SalesOrderHeader REVERT

• Question 126:

  You have a table named Customer that has an XML column named Locations. This column stores an XML fragment that contains details of one or more locations, as show in the following examples.

  You need to write a query that returns a row for each of the customer's locations. Each resulting row must include the customer name, city, and an XML fragment that contains the location details.

  Which query should you use?

  A. SELECT CustomerName, Locations.query('for $i in /Location return data($i/@City)'), Locations.query('for $i in /Location return $i') FROM Customer
  B. SELECT CustomerName, Locations.query('for $i in /Location return element Location {$i/@City, $i}') FROM Customer
  C. SELECT CustomerName, Locations.query('data(/Location/@City)'), Locations.query('/Location') FROM Customer
  D. SELECT CustomerName, Loc.value('@City','varchar(100)'), Loc.query('.') FROM Customer CROSS APPLY Customer.Locations.nodes ('/Location') Locs(Loc)
  D. SELECT CustomerName, Loc.value('@City','varchar(100)'), Loc.query('.') FROM Customer CROSS APPLY Customer.Locations.nodes ('/Location') Locs(Loc)

• Question 127:

  A table named Locations contains 5000 locations and the number of sales made at each location.

  You need to display the top 5 percent of locations by sales made. Which Transact-SQL code segment should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

• Question 128:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance. You create tables named order, customer, and product as follows:

  

  You need to write a query to sum the sales made to each customer by the following entries: The Customer name and product name The grand total of all sales

  Which SQL query should you use?

  

  A. B. C. D. E.
  F. G. H. I. J.
  A. B. C. D. E.

• Question 129:

  You develop a new stored procedure for an existing database. You create two tables named Customer and Orders. The tables have the following definitions:

  

  Users are restricted from accessing table objects directly. You need to ensure that users are able to retrieve customer data. You need to create a stored procedure that meets the following requirements:

  Returns a row that contains the name, address, and number of orders made by a customer by specifying the CustomerID value Returns a row even if the customer has made no orders. Does not return a row if the CustomerID does not exist.

  Which Transact-SQL statement or statements should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

• Question 130:

  Your company's database contains Customers and Orders tables. You have been tasked to write a SELECT statement that exposes the data as a valid and well- formed XML document. The XML data must be attribute-based, and the order

  data XML must be nested in the customer data XML.

  You need to write a SELECT statement to meet the requirements.

  Which Transact-SQL statement should you use?

  A. SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML RAW('Contact'), ROOT('ContactOrderDate')
  B. SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML PATH('ContactOrderDate')
  C. SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML AUTO
  D. SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML AUTO, ROOT('ContactOrderDate')
  D. SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML AUTO, ROOT('ContactOrderDate')

  ---

  SELECT c.ContactName, o.OrderDate, o.RequiredDate

  FROM Customers c INNER JOIN Orders o

  ON c.CustomerID = o.CustomerID

  FOR XML RAW('Contact'), ROOT('ContactOrderDate')

  Produce the following result:

  ...

  SELECT c.ContactName, o.OrderDate, o.RequiredDate

  FROM Customers c INNER JOIN Orders o

  ON c.CustomerID = o.CustomerID

  FOR XML PATH('ContactOrderDate')

  Produce the following result:

  Paul Henriot

  1996-07-04T00:00:00

  1996-08-01T00:00:00

  Karin Josephs

  1996-07-05T00:00:00

  1996-08-16T00:00:00

  ...

  Paula Wilson

  1998-05-06T00:00:00

  1998-06-03T00:00:00

  SELECT c.ContactName, o.OrderDate, o.RequiredDate

  FROM Customers c INNER JOIN Orders o

  ON c.CustomerID = o.CustomerID

  FOR XML AUTO

  Produce the following result:

  ...

  SELECT c.ContactName, o.OrderDate, o.RequiredDate

  FROM Customers c INNER JOIN Orders o

  ON c.CustomerID = o.CustomerID

  FOR XML AUTO, ROOT('ContactOrderDate')

  Produce the following result:

  ...