Technical Reasoning Assessment

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.

You need to protect the code from being viewed by your customers.

Which stored procedure option should you use?

You use a Microsoft SQL Server database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

You develop a Microsoft SQL Server database. The database is used by two web applications that access a table named Products.

You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.

You need to ensure that the following requirements are met:

• Future modifications to the table definition will not affect the applications’ ability to access data.
• The new object can accommodate data retrieval and data modification.

You need to achieve this goal by using the minimum amount of changes to the existing applications.

What should you create for each application?

You develop a Microsoft SQL Server database.

 You need to create a batch process that meets the following requirements:

• Returns a result set based on supplied parameters.
• Enables the returned result set to perform a join with a table.

Which object should you use?

You develop a Microsoft SQL Server database that contains a heap named OrdersHistorical.

You write the following Transact-SQL query:

 INSERT INTO OrdersHistorical

SELECT * FROM CompletedOrders

You need to optimize transaction logging and locking for the statement. Which table hint should you use?

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.

The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.

Which Transact-SQL query should you use?

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

 You develop a database for a travel application. You need to design tables and other database objects.

 You need to store media files in several tables.

 Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently.

What should you do?

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

 You develop a database for a travel application. You need to design tables and other database objects.

 You create a view that displays the dates and times of the airline schedules on a report.

 You need to display dates and times in several international formats.

 What should you do?

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.

 You need to create a report that displays the profits made by each territory for each year and its previous year.

 Which Transact-SQL query should you use?

You use Microsoft SQL Server 2012 database to develop a shopping cart application.

 You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.

 Which Transact-SQL operator should you use?

You administer a Microsoft SQL Server database that supports a shopping application.

 You need to retrieve a list of customers who live in territories that do not have a sales person.

 Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

You are developing a database that will contain price information.

 You need to store the prices that include a fixed precision and a scale of six digits.

 Which data type should you use?

using System;

namespace MyProgram{

class Program{

static void fill in here (string[] args){  // define a main function

string str = “Let’s study C# programming!”;

Console.Write(str);

Console.ReadLine();

}}}

using System;

namespace VariableAndConstant{

class Program{

static void Main(string[] args){

int num;

fill in here a = 10, b = 20, c = 30;   // define variable a, b, c

num = a * b * c;

fill in here string RESULT = “The result is: “;

// define constant RESULT

Console.WriteLine( RESULT + num );

Console.ReadLine();

}}}

using System;

namespace ConditionalOperator{

class Program{

static void Main(string[] args){

int a = 100; int b = 200;

string result1 = (a < b) fill in here “apple” : “banana”;

// conditional operator

Console.WriteLine(“result1 is: ” + result1);

string result2 = (a > b) fill in here “apple” : “banana”;

// conditional operator

Console.WriteLine(“result2 is: ” + result2);

Console.ReadLine();

}}}

using System;

namespace enumExample{

public fill in here Today{    // defines an enum type

morning,          // enum member

afternoon,        // enum member

evening,           // enum member

}

class Program{

static void Main(string[] args){

fill in here  t0, t1, t2;     // defines enum variable

t0 = Today.morning;

t1 = Today.afternoon;

t2 = Today.evening;

Console.WriteLine(“Have a breakfast in ” + t0);

Console.WriteLine(“Have a lunch in ” + t1);

Console.WriteLine(“Hava a supper in ” + t2 );

Console.ReadLine();

}}}

using System;

namespace SwichStatement{

class Program{

static void Main(string[] args){

int number = 20;

fill in here (number){   // evaluate the variable “number”

case 10: Console.WriteLine(“Running case 10”);

​   Console.ReadLine(); break;

case 20: Console.WriteLine(“Running case 20”);

Console.ReadLine(); break;

case 30: Console.WriteLine(“Running case 30”);

Console.ReadLine(); break;

default:  Console.WriteLine(“Running default code”);

Console.ReadLine(); break;

}}}}

using System;

namespace StringAndArray{

class Program{

static void Main(string[] args){

string str1 = “C++ in 8 Hours”;

string str2 = str1.Replace(“C++”, “C#”);

Console.WriteLine(str2);

String[] arr = { “3rd “, “2nd “, “4th “, “1st ” };

fill in here.Sort(arr);      // sort the array

fill in here (String var in arr) {   // iterate the array

Console.Write(var);

}

Console.ReadLine();

}}}

using System;

namespace returnExample{

class program{

static int add(int num1, int num2){   // return type is int

fill in here num1 + num2;    // send back value to caller

}

static void Main(){

int result = add(3,5);

Console.WriteLine(“3 + 5 = ” + result);

Console.ReadLine();

}}}

using System;

namespace TryAndCatch{

class Program{

static void Main(string[] args){

try{

int[] myArr = {0,1,2,3};

myArr[4] = 4;    // exception occurs

}

catch(Exception ex){    // handle the exception

Console.WriteLine(ex.fill in here);  // exception message

Console.ReadLine();

}}}}

using System;

namespace thisExample{

class MyClass{

int num;

void test(int num){

fill in here.num = num;     // the current object

Console.Write(this.num);

}

static void Main(string[] args){

MyClass obj = new MyClass( );   // create an object “obj”

obj.test(10);

Console.ReadLine();

}}}

using System;

namespace Overloading{

fill in here Animal{    // declare a class “Animal”

String dog, cat;   // declare two variable members

Animal() {dog = “black.”; cat = “white.”;}   // constructor

static void Main(string[] args){

Animal pet = fill in here Animal();    // create an object “pet”

Console.WriteLine(“This dog’s color is ” + pet.dog);

Console.WriteLine(“This cat’s color is ” + pet.cat);

Console.ReadLine();

}}}

using System;

namespace MyProgram{

class Program{

static void Main(string[] args){

string str = “Let’s study C# programming!”;

fill in here (str);   // show the value of “str”

Console.ReadLine();

}}}

using System;

namespace UnBoxing{

class Program{

static void Main(string[] args){

int var1 = 100;

object var2 = (fill in here)var1;   // boxing

int var3 = (fill in here)var2;         // unboxing

Console.WriteLine(“The data type of var3 is int type now.”);

Console.ReadLine();

}}}

using System;

namespace structSample{

fill in here Student{    // defines a struct type

public int id;       // struct field

public string name;      // struct field

public int age;        // struct field

}

class Program{

static void Main(string[] args){

fill in here Smith;      // define a struct variable

Smith.id = 1001;

Smith.name = “Andy”;

Smith.age = 16;

Console.WriteLine(“Smith’s id is ” + Smith.id);

Console.WriteLine(“Smith’s name is ” + Smith.name);

Console.WriteLine(“Smith’s age is ” + Smith.age);

Console.ReadLine();

}}}

using System;

namespace ForStatement{

class Program{

static void Main(string[] args){

fill in here (int x = 0; x <= 5; x++){    // loop statement

Console.Write(x);

}

Console.ReadLine();

}}}