Your manager has asked you to design a new table and enforce rules for the type of information
that can be stored in a particular column. The information must match a particular pattern e.g. a
number between 1 and 7. This checking must be performed during insert or update operations.
Which parameter should you use when you create the column?

If you use the BEGIN TRANSACTION statement, then execute 2 insert statements and 2 delete
statements what will happen if you execute ROLLBACK TRANSACTION?

Which of the following is an example of a scalar subquery?

You’re designing a new table that will hold information about medical records. Another table called
documents contains a foreign key that references this table. To make sure information is cleaned
up when a delete operation is performed, you’ve added an ON DELETE CASCADE statement to
the foreign key relationship. What will happen when a row is deleted from the medical records table?

What is wrong with the T-SQL batch statement below.
@find varchar(30);
SET @find = ‘Man’;
SELECT LastName FROM Person WHERE LastName = @find;

Which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT
OR CAST?

As part of a new enterprise project, you’re designing a new table to store financial transactions.
This table could eventually store millions of rows and so storage space is very important. One of
the columns in the table will store either a 1 or 0 value. Which data type would be most appropriate?

Your application contains a stored procedure for each country. Each stored procedure accepts an
employee identification number through the @EmplD parameter. You need to build a single
process for each employee that will execute the appropriate stored procedure based on the country
of residence. Which approach should you use?

Drag and Drop Question
You write the following SELECT statement to get the last order date for a particular customer.

You need to create the user-defined function to return the last order date for the specified customer.
Which five Transact-SQL statements should you use? (To answer, move the appropriate SQL
statements from the list of statements to the answer area and arrange them in the correct order.)

Your database contains a table named Customer that has columns named CustomerID and Name.
You want to write a query that retrieves data from the Customer table sorted by Name listing 20
rows at a time. You need to view rows 41 through 60. Which Transact-SQL query should you create?