Exam Prep Questions
In preparation for a major system upgrade, multiple data changes are going to be made on a system. You would like to implement various changes without disturbing any of the existing data. Which of the following operations do not affect any existing data values? (Select all that apply.)
- A. INSERT
- B. UPDATE
- C. Changing column name
- D. Increasing column length
- E. Decreasing column length
You would like to produce a grouped report of sales, by city. You only want included on the report groups that have sales within the last calendar year (2005). Which of the following query elements would you use to solve the problem? (Select all that apply.)
- A. GROUP BY
- B. WHERE
- C. HAVING
- D. BETWEEN
- E. NOT ISNULL
- F. JOIN
You would like to produce a report from a random selection of records within a database. There are 10,000 records in the table, and you would like to include 1,000 of them in the process. How would you implement the process?
- A. Use CURSOR and process every 10th record.
- B. Use the TOP clause to get 1,000 records.
- C. Set the ROWCOUNT to 1000.
- D. Use TABLESAMPLE to get 10% of the records.
- E. Use the RND function to achieve random records.
You would like to add an additional column to a table. The new column will be used to hold a seven-character serial number for assets and will be a mandatory element of data in the future. How would you implement the process? (Select two answers, each of which represents part of the solution.)
- A. Create a new NOT NULL column.
- B. Set a default value of N/A.
- C. Create the new column as an identity column.
- D. Enter serial numbers for all existing records.
- E. Set a default value of NewID().
You need to add a column to a table that will hold a unique value. Which of the following would accommodate the situation? (Select all that apply.)
- A. An identity column with an integer data type
- B. A timestamp data type
- C. A numeric data type with a default of RND()
- D. A user-defined character data type with a CLR that assigns a random set of characters
- E. A uniqueidentifier data type with a default value of NewID()
- F. A column set as the primary key
- G. A column with a unique index attached
You are creating a procedure that will update two tables within a transaction. The code looks similar to the following (line numbers are included for reference only):
1 BEGIN TRANSACTION 2 3 BEGIN TRY 4 UPDATE . . . 5 6 END TRY 7 8 BEGIN CATCH 9 IF . . . 10 11 END CATCH 12
In most common situations, where would you put the ROLLBACK statement?
- A. Line 2
- B. Line 5
- C. Line 7
- D. Line 10
- E. Line 12
You are building a scientific application that will store data in a SQL Server 2005 database. The application does not store data until a final process is complete. One of the procedures you are currently working with performs many processor-intensive calculations. Which of the following would accommodate the situation? (Select all that apply.)
- A. A standard T-SQL stored procedure
- B. A T-SQL stored procedure that uses a cursor
- C. A CLR-integrated stored procedure
- D. A CLR-integrated stored procedure that uses a cursor