Home > IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 7
Study guide:
EMAIL THIS

IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 7

26 Aug 2008 | Kathryn Marquis, Assistant Editor

Tips, expert advice and sample chapters
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google

DB2

The following sample exam questions are taken from DB2 9 Fundamentals Certification Study Guide, by Roger E. Sanders (copyright 2007) and are reprinted here with permission from MC Press.

Test your knowledge of DB2 9 by answering the questions below, which are from a real, previous DB2 9 Fundamentals certification exam (Exam 730). When you make your answer selection, click on "Get Answer" to check your answers and read the explanations. If you haven't done so already, be sure to take part 1, part 2, part 3, part 4, part 5 and part 6 of this DB2 9 quiz series about tables.



Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google



RELATED CONTENT
IBM DB2 management
How to select an MPP database: DB2 vs. Teradata
What are the top database management systems (DBMS)?
Are there benefits to using both Teradata and a DB2 database?
Tips for evaluating top database management systems and choosing a small DBMS
Exec explains IBM's Information On Demand (IOD) initiative
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 6
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 5
DB2 basics
IBM DB2 basics
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 4

Data management tutorials
How to design project management plan templates for large projects
Handling project management issues for a proprietary ownership conflict
Should we use traditional or agile software development processes?
Next steps for business intelligence: Video
Improving ODBC application performance and coding
Selecting ODBC functions for optimized SQL statements
Guidelines for managing data updates to optimize ODBC performance
Is it better to have a centralized or decentralized master data structure?
Can we leverage existing data quality tools for an MDM program?
Do we need business intelligence (BI) tools to be successful?

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary



Sample Questions
Question 1

Which of the following isolation levels will lock all rows scanned to build a result data set?

    A. Uncommitted Read
    B. Cursor Stability
    C. Read Stability
    D. Repeatable Read

  • Get answer


  • Question 2

    Which of the following is a NOT a valid reason for defining a view on a table?

      A. Restrict users' access to a subset of table data
      B. Ensure that rows inserted remain within the scope of a definition
      C. Produce an action as a result of a change to a table
      D. Provide users with an alternate view of table data

  • Get answer


  • Question 3

    Which of the following is NOT a characteristic of a declared temporary table?

      A. Declared temporary tables are not persistent and can only be used by the application that creates them
      B. It is possible for many applications to create declared temporary tables that have the same name
      C. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified
      D. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed

  • Get answer


  • Question 4

    After the following SQL statement is executed:

    GRANT ALL PRIVILEGES ON TABLE employee TO USER user1

    Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform?

      A. Drop an index on the EMPLOYEE table
      B. Grant all privileges on the EMPLOYEE table to other users
      C. Alter the table definition
      D. Drop the EMPLOYEE table

  • Get answer


  • Question 5

    Given the following table definition:

    EMPLOYEES
    ¯¯¯¯¯¯¯¯¯¯¯
    EMPIDINTEGER
    NAMECHAR(20)
    DEPTCHAR(10)
    SALARYDECIMAL(10,2)
    COMMISSIONDECIMAL(8,2)

    Assuming the DEPT column contains the values 'ADMIN', 'PRODUCTION', and 'SALES', which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

      A. SELECT name, dept FROM employees ORDER BY dept
      B. SELECT name, dept FROM employees GROUP BY dept
      C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)
      D. SELECT name, dept FROM employees GROUP BY CUBE (dept))

  • Get answer


  • Question 6

    Which two of the following are optional and do not have to be specified when creating a table?

      A. Table name
      B. Column name
      C. Default constraint
      D. Column data type
      E. NOT NULL constraint

  • Get answer


  • Question 7

    If the following SQL statement is executed:

    CREATE TABLE sales
        (invoice_no   NOT NULL PRIMARY KEY,
         sales_date   DATE,
         sales_amt    NUMERIC(7,2))
        IN tbsp0, tbsp1, tbsp2, tbsp3
        PARTITION BY RANGE (sales_date NULLS FIRST)
            (STARTING '1/1/2007' ENDING '12/31/2007'
             EVERY 3 MONTHS)
    Which of the following statements is true?

      A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level
      B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement
      C. If an index is created for the SALES table, its data must be stored in table space TBSP0
      D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned

  • Get answer


  • Question 8

    A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges?

      A. CALL privilege on the procedure; SELECT privilege on the table
      B. CALL privilege on the procedure; REFERENCES privilege on the table
      C. EXECUTE privilege on the procedure; SELECT privilege on the table
      D. EXECUTE privilege on the procedure; REFERENCES privilege on the table

  • Get answer


  • Question 9

    Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)?

      A. Each application that defines a DGTT has its own instance of the DGTT
      B. Two different applications cannot create DGTTs that have the same name
      C. DGTTs can only be used by the application that creates them, and only for the life of the application
      D. Data stored in a DGTT can exist across transaction boundaries

  • Get answer


  • Question 10

    Given the following table definition:

    SALES
    ¯¯¯¯¯¯¯¯¯¯¯
    SALES_DATEDATE
    SALES_PERSONCHAR(20)
    REGIONCHAR(20)
    SALESINTEGER

    Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?

      A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995
      B. DELETE FROM sales WHERE YEAR(sales_date) = 1995
      C. DROP * FROM sales WHERE YEAR(sales_date) = 1995
      D. DROP FROM sales WHERE YEAR(sales_date) = 1995

  • Get answer

  • ANSWER KEY


    Question 1

    The correct answer is D -- Repeatable Read

    The Repeatable Read isolation level will lock all rows scanned in response to a query. (The Read Stability isolation level will only lock the rows returned in the result data set; the Cursor Stability isolation level will only lock the row in the result data set that the cursor is currently pointing to; and the Uncommitted Read isolation level will not lock any rows during normal read processing.)


    Question 2

    The correct answer is C -- Produce an action as a result of a change to a table

    A trigger is used to produce an action as a result of a change to a table. Views provide users with alternate ways to see table data. And because a view can reference the data stored in any number of columns found in the base table it refers to, views can be used, together with view privileges, to control what data a user can and cannot see. Furthermore, if a view is created with the WITH [LOCAL | CASCADED] CHECK OPTION specified, it can be used to ensure that all rows added to a table through it conform to its definition.


    Question 3

    The correct answer is C -- Declared temporary tables are created by executing a CREATE TABLE state-ment with the DECLARED GLOBAL TEMPORARY clause specified

    Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them—and only for the life of the application. (Once the application that created the global temporary table is terminated, any records in the table are deleted and the table itself is destroyed.) When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the definition of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference between the two centers around naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. And where base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement.


    Question 4

    The correct answer is C -- Alter the table definition

    The GRANT ALL PRIVILEGES statement gives USER1 the following privileges for the EMPLOYEE table: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. To drop an index, USER1 would need CONTROL privilege on the index – not the table the index is based on; USER1 cannot grant privileges to other users because the WITH GRANT OPTION clause was not specified with the GRANT ALL PRIVILEGES statement used to give USER1 table privileges; and in order to drop the EMPLOYEE table, USER1 would have to have CONTROL privilege on the table – CONTROL privilege is not granted with the GRANT ALL PRIVILEGES statement.


    Question 5

    The correct answer is A -- SELECT name, dept FROM employees ORDER BY dept

    The ORDER BY clause is used to tell the DB2 Database Manager how to sort and order the rows that are to be returned in a result data set produced in response to a query. In this example, all rows containing the value "ADMIN" in the DEPT column would be listed first, followed by all rows containing the value "PRODUCTION", followed by all rows containing the value "SALES".


    Question 6

    The correct answers are C and E -- Default constraint and NOT NULL constraint

    At a minimum, when a new table is created, a table name, one or more column names, and corresponding column data types must be specified. Primary keys, constraints (NOT NULL, default, check, unique, referential integrity, and informational), and table space information is optional and is not required.


    Question 7

    The correct answer is B -- Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement

    The SALES table in the example is partitioned such that each quarter's data is stored in a different data partition, and each partition resides in a different table space. Advantages of using table partitioning include:


    Question 8

    The correct answer is C -- EXECUTE privilege on the procedure; SELECT privilege on the table

    The EXECUTE privilege, when granted, allows a user to invoke a routine (a routine can be a user-defined function, a stored procedure, or a method that can be invoked by several different users), create a function that is sourced from the routine (provided the routine is a function), and reference the routine in a Data Definition Language SQL statement (for example, CREATE VIEW and CREATE TRIGGER) statement. When the EXECUTE privilege is granted for a routine, any privileges needed by the routine must also be granted – in this case, the SELECT privilege is needed for the table the procedure will query.


    Question 9

    The correct answer is B -- Two different applications cannot create DGTTs that have the same name

    Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them—and only for the life of the application. When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the description of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference focuses on naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name.


    Question 10

    The correct answer is B -- DELETE FROM sales WHERE YEAR(sales_date) = 1995

    The DELETE statement is used to remove specific records from a table (the DROP statement completely destroys the table object), and the correct syntax for the DELETE statement is DELETE FROM [TableName] …


  • Go back to the list of sample DB2 9 exam questions.
  • Go to Part 1 of this DB2 certification quiz series about tables
  • Go to Part 2 of this DB2 certification quiz series about tables
  • Go to Part 3 of this DB2 certification quiz series about tables
  • Go to Part 4 of this DB2 certification quiz series about tables
  • Go to Part 5 of this DB2 certification quiz series about tables

     




  • Data Compliance Articles and Research: Data Privacy, Financial Data Management, Healthcare Data
    About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
    SEARCH 
    TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

    TechTarget Corporate Web Site  |  Media Kits  |  Site Map




    All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy
      TechTarget - The IT Media ROI Experts