You’re designing a new SQL Server 2012 query for the HR department. The query will find records
from the persons table for people whose name starts with ‘Ja’. Which WHERE statement would be
the correct choice?

As part of a new HR project you’re creating several stored procedures that will add logging
information to the logs table. This logging information is very detailed and should contain carriage
returns to make paragraphs more readable. How can you add carriage returns to text when
inserting into a table?

You need to delete a stored procedure from the SQL Server 2012 database. The stored procedure
is called MyProc. Which statement below is correct?

You’ve created a standard stored procedure that’s not a CLR procedure. This procedure has
several parameters, some of which are output parameters. Which of the following CANNOT be
passed back as an output parameter from a stored procedure?

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;