IBM C2090-730 Online Practice Questions and Exam Preparation

IBM C2090-730 Online Questions & Answers

• Question 291:

  A view is created with the following statement:

  CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTION

  What is the effect of the CHECK OPTION clause?

  A. Any row inserted or updated through view V1 must meet the condition that col4 > 1000.
  B. From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.
  C. At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.
  D. Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4
  A. Any row inserted or updated through view V1 must meet the condition that col4 > 1000.

• Question 292:

  Given the following table definitions:

  EMPLOYEES

  EMPID INTEGER

  NAME CHAR(20)

  DEPTID CHAR(3)

  SALARY DECIMAL(10,2)

  COMMISSION DECIMAL(8,2)

  DEPARTMENTS

  DEPTNO INTEGER DEPTNAME CHAR(20)

  Which of the following statements will produce a result data set that satisfies all of these conditions:

  > Displays the total number of employees in each department >> Displays the corresponding department name for each department ID >> Sorted by department employee count, from greatest to least

  A. SELECT *,COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
  B. SELECTdeptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
  C. SELECTdeptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC
  D. SELECTdeptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2
  B. SELECTdeptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC

• Question 293:

  Given the following table definitions:

  TABLE1

  ID INT NAME CHAR(30) PERSON INT CITIES INT TABLE2

  ID INT

  LASTNAME CHAR(30)

  Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table TABLE2?

  A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2)
  B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2)
  C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)
  D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)
  C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)

• Question 294:

  Which of the following is NOT a characteristic of a data warehouse?

  A. Summarized queries that perform aggregations and joins
  B. Heterogeneous data sources
  C. Voluminous historical data
  D. Sub-second response time
  D. Sub-second response time

• Question 295:

  A view named V.VIEW1 is based on a table named T.TABLE1. A user with DBADM authority issues the following statement:

  GRANT INSERT ON v.view1 TO user1 WITH GRANT OPTION

  Which of the following statements is USER1 authorized to execute?

  A. GRANT INSERT ON t.table1 TO user2
  B. GRANT CONTROL ON v.view1 TO user2
  C. GRANT ALL PRIVILEGES ON v.view1 TO user2
  D. GRANT INSERT ON v.view1 TO user2
  D. GRANT INSERT ON v.view1 TO user2

• Question 296:

  A sequence was created with the DDL statement shown below:

  CREATE SEQUENCE my_sequence CACHE 10 ORDER

  The following statements are successfully executed in sequence through separate database connections:

  CONNECTION1 - VALUES NEXT VALUE FOR my_sequence INTO :con1hvar CONNECTION2 - VALUES NEXT VALUE FOR my_sequence INTO :con2hvar CONNECTION1 - VALUES NEXT VALUE FOR my_sequence INTO :con1hvar

  What is the current value of the :con1hvar host variable?

  A. 2
  B. 3
  C. 11
  D. 30
  B. 3

• Question 297:

  Which of the following queries will correctly return the manager information sorted by the manager's last name, department and project name?

  A. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = empno) AS empproj ORDER BY name, dept, projname
  B. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj SORT BY lastname, firstname, dept, projname
  C. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj ORDER BY lastname ASC SORT BY dept, projname DESC
  D. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj ORDER BY lastname, dept, projname
  D. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj ORDER BY lastname, dept, projname

• Question 298:

  Which of the following tools can be used to schedule a backup operation that is to be run every Sunday evening?

  A. Journal
  B. Task Center
  C. Activity Monitor
  D. Command Line Processor
  B. Task Center

• Question 299:

  What is the maximum size that can be specified when creating an XML column in a DB2 table?

  A. No size is specified
  B. The buffer pool size
  C. The page size for the table space
  D. The extent size for the table space
  A. No size is specified

• Question 300:

  A table was created using the following DDL:

  CREATE TABLE employee

  (id SMALLINT NOT NULL,

  name VARCHAR(9),

  dept SMALLINT CHECK (dept BETWEEN 10 AND 100),

  job CHAR(10) CHECK (job IN ('Sales','Mgr','Clerk')), hiredate DATE,

  salary DECIMAL(7,2),

  comm DECIMAL(7,2),

  PRIMARY KEY (id),

  CONSTRAINT yearsal CHECK (YEAR(hiredate) > 2004 OR salary > 80500) );

  Which of the following INSERT statements will fail?

  A. INSERT INTO employee VALUES (2, 'Smith', 80, 'Mgr', '09/03/2006', 80000, NULL)
  B. INSERT INTO employee VALUES (4, 'Smith', 86, 'Mgr', '07/14/2003', 90000, NULL)
  C. INSERT INTO employee VALUES (1, 'Smith', 55, 'Sales', '07/14/2003', NULL, NULL)
  D. INSERT INTO employee VALUES (3, 'Smith', 33, 'Analyst', '11/26/2006', 90000, NULL)
  D. INSERT INTO employee VALUES (3, 'Smith', 33, 'Analyst', '11/26/2006', 90000, NULL)