Simulation Questions RRS feed

  • Question

  • Can anyone please help with the answers on these simulation question from the 70-229 Exam?


    1.) You are disigning a new precurement database. While defining the suppliers and the orders relationship you are faced with a choice of what has to be done when a supplier has to be deleted from a database.

    You must prepose all the SQL Server possible choices .

    What are they?

          1. Restrict - You cannot delete the supplier when it is linked to existing orders

          2. No Effect - You can delete the supplier even if it is linked to existing orders

          3. Default - All Foreign keys are defined to a default value if the matchin primary key is deleted

          4. Cache - Every order is deleted if the linkes is deleted


    2.) Esembly lines automatically record information to your SQL Server Database. Which tools can you use to extract this information so that you can report it?

         1. DTS

         2. bcp.exe

         3. Bulk insert

         4. Bulk extract


    3.) You have OrderNr Price and Quantity Columns in the orders table. You want to calculate the  grant total. You want the grant total to calculated automatically when the order is valid.

    What are the 3 solutions you can use to acheive the expected result

         1. Make the grand total column a calculated column

         2. Use a insert trigger to calculate the grand total

         3. Calculate the grand total in the client application and store it in a table

         4. Use a stored procedure to perform the insert and calculate the grand total in the stored procedure


    4.) You are creating a new banking system. Each time a account is recorded into the movements table on the banking system you need to recalculate and store the account balance.

    You are asked to implement this feature while lowering the impact on the existing application.

    How can you do that?

         1. Create a stored procedure that inserts the movement and update the balance

         2. Create a view that joins the movement and accounts table to update both at the same time

         3. Create a user defined function that joins the movement and accounts table and excepts AccountID as parameter

         4. Create a after trigger on the movement table to update the accounts table


    5.) Having normalized your database model you need a way to implement alternate keys.

    What is the most efficient way to do that?

         1. Unique constraint

         2. Primary key constraint

         3. Unique indexes

         4. Nullability


    6.) You create a database with a initial size of 100MB and a maxsize of 200MB.

    The database has grown and the size is now 150MB. You want the database to ecrease to its initial size.

    What statement will you run to do so immediately?

         1. DBCC shrinkfile (consultants data, "something")

         2. Alterdatabase consultants set autoshrink on

         3. DBCC shrinkdatabase (consultants, 25)

         4. DBCC shrinkdatabase (consultants, 100)


    7.) You have a databas en your server which size has increased. Now the server is slow you archive 50% of the old data that you only use 0.5% of the time.

    What can you do to improve performance?

         1. Buy a new RAID subsystem and spread the data all over the disks

         2. Change the server to a new 4 way machine

         3. Its not possible

         4. Split the data horizontally and store the archive tables on another disk


    8.) You run this Query:

           Select * from Customers

               where country = 'Finland'

          and find that it is very slow.

    What can you do to improve execution withput modifying table structure and the existing index and primary key which is clustered?

         1. Add a clustered index on the country column

         2. Add a non-clustered index on the country column

         3. A clustered index is already used so the table may be fragmented you should defragment it

         4. Create a table, name it customers_from_Finland and create a partitioned view


    9.) You are a database developer for a bank. A customer comes to the bank the clerk can't find her on the system after some investigation you find her but with the wrong customerID someone changed the CustomerID. You need to Implement a way so that the CustomerID which is the primary key can not be changed.

    In SQL Server what is the fastest way to implement this feature without modifying the existing application?


    10.) You have diffrent databases in different locations. You need a global unique identifier.

    What column definition will create a product by column with a unique feature?

         1. ProductID unique identifier not null

         2. ProductID unique identifier newID

         3. ProductID unique identifier default newID( )

         4. ProductID unique identifier as newID


    11.) You have been asked to insert a series of rows into the categories table there is already a identity field on this table called CatID.

    Which two Transact SQL statements are neded to complete this operation?

         1. Insert Categories (CatID, CatName)

              select ID, Name, from TempCat

         2. Insert Categories (CatName)

              select Name from TempCat

         3. Set Identity Insert Category on

         4. Set Identity Insert Category off


    12.) You need to store a short job description for every employee. The length of each job description varies and is less than 500 characters for 90% of them but some can go up to 10 000 characters.

    What is the best solution for storing Job Description while consuming the least amount of space?

         1. Use two varchar columns one with MaxLength of 2 000 and one with MaxLength of 8 000 depending on the real length you will use one of these columns

         2. Use a varchar column with a MaxLength of 10 000

         3. Use a text column on a second filegroup

         4. Use a inrow text column


    13.) Using a credit card masjien, you see at certain checkpoints you do more than 500 transactions per second and at a certain checkpoint it falls to 10 transactions per second.

    Which server options may be responsible for the delay in the checkpoint process?

         1. Lazy writes per second

         2. Recovery interval

         3. Right way proling

         4. Priority boost


    14.) The ordering management database has been on a server to disk. Data has been placed on C drive and the Log file on D drive. Your users complain about the slowness of the ordering application. You discover that the slowness on C drive is due to large read processes. You add one disk to the server E drive and would like to split data amongst the E and C drives.

    How could you achieve this goal?

         1. Create a new datafile on the E drive for the orders database. System will automatically split the data amongst the two files

         2. Create a new datafile on the E drive located on the primary filegroup. It will then balance the new inserts amongst both drives

         3. It is not possible to acheive this goal without a RAID controller

         4. Define the E and C drives as RAID 0partition with the windows 2000 disk management tool


    15.)Is the following trigger valid?

        Create Trigger CheckDate

        on Dates

        for insert, update


        Declare @OrderDate datetime, @DeliveryDate datetime


        select @OrderDate = OrderDate from Inserted

        select @DeliveryDate = DeliveryDate from Inserted


        if (@OrderDate > @DeliveryDate)


           Print 'Delivery Date can't be before the order date'

          Rollback Transaction



    16.) Some of the salesman at the cardealership is entering more than the maximum of 25% discount on the sales. The sales are entered in two applications.

    What can you do to make sure that no more than 25% discount is entered on sales?

         1. Add a constraint to the orders table

         2. Add a function to calculate the discount

         3. Add a trigger to limit discounts

         4. Add a view on the order table to limit discounts


    17.) You create a table with the identity value. You then Insert 988 test records into the table. When you insert a new record the Identity of that record is 55. What can be the reason?

         1. A trigger that inserts records in another table with identity. And it is that insert that is returned

         2. Something was wrong with identity record 55 when you inserted the test rows. And then the new row is inserted at 55 to save space.

         3. The test insert was corrupt and it was rolled back to identity 55

         4. There is something wrong with you r identity so you must run identity diagnostic




    Wednesday, October 29, 2008 11:02 AM


  • This looks more like 17 questions than it does 1 question.  Also, this looks a lot like homework.  I would prefer that you look these answers up and learn the material.


    Wednesday, October 29, 2008 12:23 PM