Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 21:

  You administer a Microsoft SQL Server 2008 database for an inventory management system. The application contains a product table that has the following definition:

  

  You want to add a new field to the Product table to meet the following requirements:

  Allows user-specified information that will be added to records in the Product table. Supports the largest storage size needed for the field. Uses the smallest data type necessary to support the domain of values that will be entered by users.

  You need to add a field named User_Data_1 to support integer values ranging from -10 through 10.

  Which SQL statement should you use?

  A. ALTER TABLE [Production].[Product] ADD [User_Data_1] TINYINT
  B. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT
  C. ALTER TABLE [Production].[Product] ADD [User_Data_1] INT
  D. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIGINT
  E. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT
  F. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(11,6)
  G. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(6,11)
  H. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6)
  I. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLMONEY
  J. ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY
  B. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT

• Question 22:

  You have a table named Sales.PotentialClients. This table contains a column named EmailAddress. You are tasked to develop a report that returns valid ".com" email addresses from Sales.PotentialClients. A valid email address must have at least one character before the @ sign, and one character after the @ sign and before the ".com." You need to write a Transact-SQL statement that returns data to meet the business requirements.

  Which Transact-SQL statement should you use?

  A. select * from Sales.PotentialClients where EmailAddress like '_%@_%.com'
  B. select * from Sales.PotentialClients where EmailAddress like '%@%.com'
  C. select * from Sales.PotentialClients where EmailAddress like '_%@_.com'
  D. select * from Sales.PotentialClients where EmailAddress like '%@%[.]com'
  C. select * from Sales.PotentialClients where EmailAddress like '_%@_.com'

  ---

  If "at least" expression refer to both "one character before..." and "one character after...", then A is correct. If "at least" expression refer to only "one character before..." which seems the case, then C is correct.

• Question 23:

  You administer a Microsoft SQL Server 2008 database that contains a table named Sales.SalesOrderHeader. The Sales.SalesOrderHeader table has the following definition:

  

  You want to generate an execution plan of XML output document for a query that displays all Sales.SalesOrderHeader records containing orders that have not been shipped.

  You need to ensure that the following requirements are met:

  An XML document is provided only for this query.

  An XML document that contains the estimated execution plan is returned.

  Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 24:

  You are the database developer for an order-processing application.

  After a customer places an order, a confirmation message must be sent to the customer.

  The following Transact-SQL batch has been run in the database: You need to place the message in the EmailSendQueue for the email system to process. Which Transact-SQL batch should you use?

  

  

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

• Question 25:

  You are the database developer for a customer service application. The database has the following table and view.

  

  You need to verify that the entire code is functional. Which Transact-SQL statements will fail?

  

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

• Question 26:

  You administer a Microsoft SQL Server 2008 database named AdventureWork that contains a table named Production.Product. The table has the following definition:

  

  You want to add a new product named Widget and a list price of 10.50 U.S. dollars to the product table.

  You need to add a record for the product information. You also need to set the DateCreated field to the current date and the UserCreated field to your Windows login identification name.

  Which Transact-SQL statement should you use?

  

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

• Question 27:

  You have a table named Products. The table contains a column named Color. You need to write a Transact-SQL statement that calculates the percentage of products of each product color.

  Which Transact-SQL statement should you use?

  A. SELECT Color COUNT(*) OVER(PARTITION BY Color) / (COUNT(*) * 1.0) AS PercentColor FROM Products GROUP BY Color;
  B. SELECT Color COUNT(*) OVER() / (COUNT(*) * 1.0) AS PercentColor / (COUNT(*) * 1.0) AS PercentColor FROM Products GROUP BY Color;
  C. SELECT Color, (COUNT(*) * 1.0)/ COUNT(*) OVER() AS PercentColor FROM Products GROUP BY Color;
  D. SELECT Color COUNT(*) * 1.0) / COUNT(*) OVER(PARTITION BY Color) AS PercentColor FROM Products GROUP BY Color;
  C. SELECT Color, (COUNT(*) * 1.0)/ COUNT(*) OVER() AS PercentColor FROM Products GROUP BY Color;

• Question 28:

  You are working with a SQL Server 2008 instance that is configured to use the Latin1_General_CS_AS collation. You create a database by using the following statements.

  CREATE DATABASE TestDB COLLATE Estonian_CS_AS;

  GO

  USE TestDB;

  GO

  CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar ); You implement a temporary table named #TestTempTab that uses the following code.

  use TestDB;

  GO

  CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar ); INSERT INTO #TestTempTab SELECT * FROM TestPermTab;

  You need to identify which collation will be assigned to #TestTempTab.

  Which collation will be assigned?

  A. No-collation
  B. Estonian_CS_AS
  C. Latin1_General_CS_AS
  D. The collation selected by the Windows system locale of the server
  C. Latin1_General_CS_AS

  ---

  When using temporary tables without specifying a collation (for the column used) SQL Server will inherit the collation for the newly created temporary table from the SQL Server instance default.

  You can use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

• Question 29:

  You need to capture the execution plan for a query.

  Which statement should you use?

  A. SET FORCEPLAN ON;
  B. SET SHOWPLAN_XML ON;
  C. SET STATISTICS IO ON;
  D. SET STATISTICS TIME ON;
  B. SET SHOWPLAN_XML ON;

• Question 30:

  You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1.

  

  You need to write a query to insert a new order into the Orders table for CustomerID 45 with today's date and a cost of 89.00.

  Which statement should you use?

  A. INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, DEFAULT, 89.00);
  B. INSERT INTO Orders (OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, DEFAULT, 89.00);
  C. INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, CURRENT_TIMESTAMP, 89.00);
  D. INSERT INTO Orders (OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, CURRENT_TIMESTAMP, 89.00);
  C. INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, CURRENT_TIMESTAMP, 89.00);