Online Access Free 000-545 Practice Test

Which of the following statements is true for declared global temporary tables?

• A.When a session terminates, all declared global temporary tables created in the session are dropped.
• B.Declared global temporary tables are persistent and can be shared with multiple sessions.
• C.Declared global temporary table descriptions are stored in the system catalog
• D.Declared temporary tables support LONG VARCHAR columns.

Which statement will successfully create an SQL procedure that returns the name of the current month?

• A.CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
  BEGIN
  SET month = MONTHNAME(SELECT (CURRENT_DATE))
  END
• B.CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
  BEGIN
  DECLARE today DATE;
  SELECT (CURRENT_DATE) INTO today;
  SET month = MONTHNAME(today);
  END
• C.CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
  BEGIN
  DECLARE today DATE;
  VALUES (CURRENT_DATE) INTO today;
  SET month = MONTHNAME(today);
  END
• D.CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
  BEGIN
  DECLARE today DATE;
  SET (today = CURRENT_DATE);
  SET month = MONTHNAME(today);
  END

Which two types of stored procedures can be developed in IBM Data Studio? (Choose two.)

• A.PL/SQL
• B.Perl
• C.SQL
• D.PHP
• E.Ruby

Which declaration is not valid?

• A.DECLARE var1 CHAR;
• B.DECLARE var1 VARCHAR(20);
• C.DECLARE var1 DECIMAL(9,2);
• D.DECLARE var1 INTEGER DEFAULT;

Which steps must be followed to return a result set from an SQL procedure?

• A.1. Create the procedure using the DYNAMIC RESULT SETS clause.
  2.Declare the cursor using the WITH RETURN clause.
  3.Open the cursor in the SQL procedure.
  4.Return to the application.
• B.1. Create the procedure using the WITH RETURN clause.
  2.Declare the cursor using the DYNAMIC RESULT SETS clause.
  3.Open the cursor in the SQL procedure.
  4.Return to the application.
• C.1. Create the procedure using the WITH RETURN clause.
  2.Declare the cursor using the DYNAMIC RESULT SETS clause.
  3.Open the cursor in the SQL procedure.
  4.Close the cursor.
• D.1. Create the procedure using the DYNAMIC RESULT SETS clause.
  2.Declare the cursor.
  3.Open the cursor in the SQL procedure.
  4.Close the cursor.
  5.Return to the application.