| 1)
|
You have just installed Microsoft SQL Server 2005 Enterprise Edition for use in the company. You need to set up security to allow access to the server to the employees of the company. The server will contain several databases, some of which contain sensitive information. How do you configure access to the server? |
|
|
| 2)
|
You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database because the data they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task? |
|
|
| 3)
|
You are designing an application that will provide data entry clerks the capability of updating the data in several tables. You would like to ease entry and provide common input so the clerks do not need to enter data into all fields or enter redundant values. What types of technologies could you use to minimize the amount of input needed? |
|
|
| 4)
|
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.) |
| 5)
|
An existing sales catalog database structure exists on a system in your company. The company sells inventory from a single warehouse location that is across town from where the computer systems are located. The product table has been created with a nonclustered index, based on the product ID, which is also the primary key. Nonclustered indexes exist on the product category column and also the storage location column. Most of the reporting done is ordered by storage location. How would you change the existing index structure? |
|
|
| 6)
|
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.) |
| 7)
|
Which configuration, of a server, could be used without needing to upgrade to install Microsoft SQL Server 2005 Enterprise Edition. |
|
|
| 8)
|
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? |
|
|
| 9)
|
How to configure the drives that are being used by the data files to attain the best performance while retrieving data? |
|
|
| 10)
|
You are the database developer for a leasing company. Your database includes a table that is defined as shown here: |
|
CREATE TABLE Lease
(Id Int IDENTITY NOT NULL
CONSTRAINT pk_lease_id PRIMARY KEY NONCLUSTERED,
Lastname varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
SSNo char(9) NOT NULL,
Rating char(10) NULL,
Limit money NULL)
Each SSNo must be unique. You want the data to be physically stored in SSNo sequence. Which constraint should you add to the SSNo column on the Lease table? |
| 11)
|
You have a development environment in which a number of individuals create databases regularly. You would like to log the database creation activity so that the username, time, date, and details of the creation are recorded. How would you implement this? |
|
|
| 12)
|
You are creating a procedure that will update two tables within a transaction. The code looks similar to the following - |
|
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? |
| 13)
|
You need to configure access to a second SQL server for an application that will be using the data for a single query. How would you implement the configuration and query? |
|
|
| 14)
|
You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation? |
|
|
| 15)
|
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.) |
| 16)
|
How to prevent non-management individuals from accessing a Microsoft SQL Server 2005 Enterprise Edition instance on a server? |
|
|
| 17)
|
You are preparing a new index on a table that has 1,500 rows. 10 rows are added to this table every day. The table already has a primary key, and the new index does not represent the order in which data in the table is to be stored. Updates to the table occur periodically but are infrequent. Which type of index would you create under this situation? |
|
|
| 18)
|
Choose a configuration to install Microsoft SQL Server 2005 Enterprise Edition for test purposes and have moderate storage capabilities - |
|
|
| 19)
|
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? |
|
|
| 20)
|
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? |
|
|