98-364 : Database Fundamentals : Part 03

98-364 : Database Fundamentals : Part 03

1. You have a table named Student that contains 100 rows. Some of the rows have a NULL value in the FirstName column.

   You execute the following statement:

   DELETE FROM Student

   What is the result?

   • All rows in the table will be deleted.
   • All rows containing a NULL value in the FirstName column will be deleted.
   • You will receive an error message.
   • All rows and the table definition will be deleted.

2. You need to establish a set of permissions that you can routinely assign to new users.

   What should you create?

   • Resource
   • Group
   • List
   • Role

3. Which database term is used to describe the process of applying a backup to a damaged or corrupt database?

   • Recover
   • Restore
   • Commit
   • Attach

4. You need to disable User1’s access to view the data in the Customer table.

   Which statement should you use?

   

   • Option A
   • Option B
   • Option C
   • Option D

5. Denormalization is performed in order to:

   • Reduce redundancy.
   • Eliminate repeating groups.
   • Create smaller tables.
   • Improve query performance.

6. You have a table named Product that contains one million rows.

   You need to search for product information in the Product table by using the product’s unique ID.

   What will make this type of search more efficient?

   • A cursor
   • A subquery
   • A trigger
   • An index

7. You have a table named Product that contains the following data.

   

   The PrcducrID column is the primary key. The CategoryID column is a foreign key to a separate table named Category.

   You execute the following statement:

   INSERT INTO Product
   VALUES (3296, ‘Table’, 4444)

   What is the result?

   • a foreign key constraint violation
   • a syntax error
   • a new row in the Product table
   • a primary key constraint violation
   • a new row in the Category table

8. Which two keys establish a relationship between two tables? (Choose two.)

   • candidate
   • foreign
   • superkey
   • local
   • primary

9. One difference between a function and a stored procedure is that a function:

   • Must be called from a trigger.
   • Must return a value.
   • Cannot contain a transaction.
   • Cannot accept parameters.

10. Which keyword must be included in a create view statement?

    • WHERE
    • ORDER BY
    • UPDATE
    • SELECT

11. You have a table named Customer. You need to add a new column named District.
    Which statement should you use?

    

    • Option A
    • Option B
    • Option C
    • Option D

12. You need to remove a view named EmployeeView from your database.

    Which statement should you use?

    • DELETE VIEW EmployeeView
    • DELETE EmployeeView
    • DROP EmployeeView
    • DROP VIEW EmployeeView

13. A named group of SQL statements that can be executed in a database is called a:

    • Subroutine
    • Formula
    • Stored procedure
    • Method

14. A view can be used to:

    • Save an extra copy of data stored in a separate table.
    • Limit access to specific rows or columns of data in a table.
    • Ensure referential integrity.
    • Save historical data before deleting it from the base table.

15. On which database structure does an update statement operate?

    • Table
    • User
    • Trigger
    • Role

16. You need to list the name and price of each product, sorted by price from highest to lowest. Which statement should you use?

    

    •  Option A
    • Option B
    • Option C
    • Option D

17. You delete rows in a table named Order. The corresponding rows in the OrderItem table are automatically deleted. This process is an example of a/an:

    • Inherited delete
    • Cascade delete
    • Functional delete
    • Waterfall delete
    • Domino delete

18. Which statement deletes the rows where the employee’s phone number is not entered?

    

    • Option A
    • Option B
    • Option C
    • Option D

19. You need to insert two new products into the Product table. The first product is named Book and has an ID of 125. The second product is named Movie and has an ID of 126.

    Which statement should you use?

    

    • Option A
    • Option B
    • Option C
    • Option D

20. You have a table named Employee that includes four columns.
    You execute the following statement:

    SELECT *
    FROM Employee

    Which columns are returned?

    • all columns
    • only the last column
    • only the first column
    • only the first and last columns