98-364 : Database Fundamentals : Part 07

98-364 : Database Fundamentals : Part 07

1. This question requires that you evaluate the underlined text to determine if it is correct.

   ALTER TABLE removes all rows from a table without logging the individual row deletions.

   Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct.

   • No change is needed
   • DROP TABLE
   • TRUNCATE TABLE
   • CREATE TABLE

2. You work at a coffee shop. They ask you to set up a website that stores charges on purchases.

   You need to recommend a data type in a database table to run financial functions against the charged amounts.

   Which data type should you recommend?

   • Money
   • Bit
   • Varchar
   • Binary

3. This question requires that you evaluate the underlined text to determine if it is correct.

   A row holds information for a single record in a table.

   Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct

   • No change is needed
   • Column
   • Data type
   • View

4. HOTSPOT

   Instructions: For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point.

   

   

5. This question requires that you evaluate the underlined text to determine if it is correct.

   Ports 20 and 21 are the default ports to secure a SQL Server.

   Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct.

   • No change is needed
   • 1433 and 1434
   • 411 and 412
   • 67 and 68

6. You ate creating a database object named Student to store the following data:

   

   Which syntax should you use to create the object?

   

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

7. You develop a database to store data about textbooks. The data must be stored to process at a later time.

   Which database object should you use to store the data?

   • View
   • Table
   • Function
   • Stored procedure

8. This question requires that you evaluate the underlined text to determine if it is correct.

   First normal form requires that a database excludes repeating groups.

   Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct.

   • No change is needed.
   • Composite keys
   • Duplicate rows
   • Foreign keys

9. HOTSPOT

   You have two database tables as defined below. The StateID column is unique in the State table. The AddressID column is unique in the Address table. The two tables are related by the StateID column.

   

   Instructions: Use the drop-down menus to select the answer choice that completes each statement. Each correct selection is worth one point.

   

   

10. You have the following SQL query

    SELECT * FROM dbo.ProAthlete WHERE Salary > 500000

    The query takes too much time to return data.

    You need to improve the performance of the query.

    Which item should you add to the Salary column?

    • Non-null constraint
    • Default constraint
    • Index
    • Foreign key

11. You work for a small auto trading company. You need to remove a car from the company database.

    Information about the car is stored in the following tables.

    

    All of the car’s parts are custom made, so they each have a row in every table. Cascading deletes are not enabled but referential integrity is.

    From which table must you first make your deletion?

    • Make
    • Model
    • Engine
    • ModelEngine

12. This question requires that you evaluate the underlined text to determine if it is correct.

    You have a table that contains information about all students in your school.

    You use the INSERT SQL keyword to change a student’s given name in the table.

    Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct.

    Answer: B

    • CHANGE
    • UPDATE
    • No change needed
    • SELECT

13. You create the following table, which lists how many books you have on loan to your friends.

    

    Harry in San Francisco returns your books.

    Which statement will update your table correctly?

    • UPDATE LoanedBooks
      SET Books = 0
      WHERE (Name = ‘Harry’ AND City = ‘San Francisco’)
    • UPDATE LoanedBooks
      SET Books = 0
      WHERE (Name = ‘Harry’ OR City = ‘San Francisco’)
    • UPDATE LoanedBooks
      SET Books = 0
      WHERE (Name in ‘Harry’, ‘San Francisco’)
    • INSERT INTO LoanedBooks
      SET Books = 0
      WHERE ID =
    Explanation:
    The row must match both name and city.

    Incorrect Answers:
    B: The row must match both name and city. Therefore we must use AND and not OR.
    C: Incorrect syntax in the WHERE clause.
    D: This would work, but we’d need to lookup the ID first.

14. You are developing a SQL query.

    Which two SQL constructs represent data manipulation language (DML) statements? Choose two.

    • SELECT EmployeeName FROM Employee
      WHERE EmployeeName = ‘Jack Smith’;
    • INSERT INTO Employee
      VALUES (‘Jack Smith’)
    • ALTER TABLE Employee
      ADD EmployeeName Varchar;
    • CREATE ROW IN Employee
      WHERE EmployeeName = ‘Jack Smith’;

    Explanation:

    Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

    – SELECT … FROM … WHERE … (strictly speaking DQL)
    – SELECT … INTO …
    – INSERT INTO … VALUES …
    – UPDATE … SET … WHERE …
    – DELETE FROM … WHERE …

15. You need to create a view to filter rows of data from an underling table.

    Which type of clause must be included in the CREATE VIEW statement?

    • CONSTRAINT
    • WHERE
    • JOIN
    • FILTER

16. This question requires that you evaluate the underlined text to determine if it is correct.

    A key defines the amount of storage space that is allocated to a value in a column.

    Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct

    • No change is needed
    • format
    • data type
    • validator

17. This question requires that you evaluate the underlined text to determine if it is correct.

    A relational database management system employs the concept of an attribute to ensure that data entered into a field in a column is valid.

    Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct.

    • No change is needed
    • a primary key
    • a constraint
    • an index

18. HOTSPOT

    You are structuring a table in a relational database.

    Instructions: For each of the following statement, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point.

    

    

19. You have a database that contains 10 terabytes of data. You need to back up the database every two hours.

    Which type of backup should you use?

    • archive
    • incremental
    • partial
    • full

20. This question requires that you evaluate the underlined text to determine if it is correct.

    The UNION keyword combines the results of two queries and returns only rows that appear in both result sets.

    Instructions: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct

    • No change is needed
    • INTERSECT
    • ALL
    • JOIN
    Explanation:
    UNION combines the results from both queries.
    INTERSECT returns rows that are common to both queries