You’re designing a new SQL Server 2012 table that will hold millions of records. The table will be
used by a finance application to show historical transactions. The table only has 10 columns but
it’s vital that the table performs well. How many primary keys should you create for the table for
best performance?

Your manager has asked you to extend one of the complex stored procedures used to calculate
time differences between lab test results. In particular, during the stored procedures you need to
check the exact minute value of a datatime value. Your manager has suggested that you use the
DATENAME function and pass in the date and datepart argument. Which datepart argument should
you use to get the minute?

You need to write a new query that will return names from the employee table for people who are
not part of the contractors table (the two tables share a unique id, similar to social security or NI
number). Your query will use a sub query to check that the employee is not in the contractors table.
How might you achieve this?

Your manager has asked you to check the index stats for a particular table called Address. You
have executed the following query (see below) and it reported that the
avg_fragmentation_in_percent is 15%. What should you do?
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N’Test’),
OBJECT_ID(N’Address’), NULL, NULL , ‘DETAILED’);

Complete the missing word below. A ____ is a special kind of stored procedure that executes
automatically when a user attempts the specified data-modification statement on the specified table.

What is missing from the T-SQL statement below for creating and using a cursor?
DECLARE mycursor CURSOR FOR SELECT * FROM Vendor FETCH NEXT FROM mycursor;

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?