Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    23

    Unanswered: some questions about SQL statements

    the questions are:
    Given the tables:
    TABLEA
    Empid name
    1 JOE
    2 BOB
    TABLEB
    empid weeknumber paycheck
    1 1 1000.00
    1 2 1000.00
    2 1 1000.00
    TABLEB was defined as follows:
    CREATE TABLE tableb (empid CHAR(3), weeknumber CHAR(3), paycheck DECIMAL(6,2),
    CONSTRAINT const1 FOREIGN KEY (empid)
    REFERENCES tablea (empid) ON DELETE SET NULL)
    How many rows would be deleted from tableb if the following command is issued:
    DELETE FROM tablea WHERE empid = '2'?
    A 2 B 0 C3 D1
    The answer is B ?why not A ?

    How can i define some SQL statements sets the default qualifier to "user1"?
    set current user to user1? Is it right?

    Given the two following tables:
    Points
    Name Points
    Wayne Gretzky 244
    Jaromir Jagr 168
    Bobby Orr 129

    PIM
    Name PIM
    Mats Sundin 14
    Jaromir Jagr 18
    Bobby Orr 12
    How can i define some statements will display the player's Names, points and PIM for all players?
    if i define SQL statements like this: select points.name,points,pim from points,pim where points.name=pim.name.
    but that can not display all name in these two table,how can i do?

    Thanks any help would be great!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    First question: Why should it be A? The referential action for the foreign key is not ON DELETE CASCADE. Thus, DB2 must not even attempt to delete the data in the child table. (C and D are out-of question anyway.)

    You may want to read up on the concepts of constraints and referential actions. There are 5 defined in the SQL standard for ON UPDATE and ON DELETE (not all of which are fully implemented in DB2):
    • RESTRICT - prevent update/delete if there are dependent rows
    • NO ACTION - just do the update/delete, but the statement fails if there are dangling references after the data modification (the difference to RESTRICT is that triggers or whatever could adjust the references during statement execution)
    • SET NULL - set value(s) in referencing column(s) to NULL, leaving orphans
    • SET DEFAULT - set value(s) in referencing column(s) to the default value of each column; same as SET NULL if the default happens to be NULL
    • CASCADE - delete/update the dependent records


    Second question: The statement you quote is syntactically incorrect. The "TO" keyword cannot be used there. Besides, the CURRENT_USER special register cannot be updated: http://publib.boulder.ibm.com/infoce...c/r0008404.htm

    http://publib.boulder.ibm.com/infoce...c/r0011138.htm
    http://publib.boulder.ibm.com/infoce...c/r0001016.htm

    Third question: have a look at outer joins.

    p.s: I highly recommend that you take some SQL classes before you attempt the certification. All your questions are about standard SQL stuff and every serious database course covers that. That would help you tremendously because you would only have to learn a few DB2-specific things additionally - the SQL part is mostly common across database systems from different vendors.
    Last edited by stolze; 11-01-07 at 09:32.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2007
    Posts
    23
    Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •