Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 181:

  Which CREATE TABLE statement is valid?

  A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date date NOT NULL);
  B. CREATE TABLE ord_details(ord_no NUMBER(2) UNIQUE, NOT NULL,item_no NUMBER(3),ord_date date DEFAULT SYSDATE NOT NULL);
  C. CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3),ord_date date DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));
  D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3),ord_date date DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
  D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3),ord_date date DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

• Question 182:

  View the Exhibit and examine the structure of the ORD table.

  

  Evaluate the following SQL statements that are executed in a user session in the specified order:

  CREATE SEQUENCE ord_seq;

  SELECT ord_seq.nextval FROM dual;

  INSERT INTO ord

  VALUES (ord_seq.CURRVAL, 25-jan-2007',101);

  UPDATE ord

  SET ord_no= ord_seq.NEXTVAL

  WHERE cust_id =101;

  What would be the outcome of the above statements?

  A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUSTJD 101.
  B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
  C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
  D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
  A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUSTJD 101.

• Question 183:

  Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

  A. You can use column alias in the GROUP BY clause.
  B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
  C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

• Question 184:

  Evaluate the following statements:

  CREATE TABLE digits

  (id NUMBER(2),

  description VARCHAR2(15));

  INSERT INTO digits VALUES (1,'ONE);

  UPDATE digits SET description ='TWO'WHERE id=1;

  INSERT INTO digits VALUES (2 .'TWO');

  COMMIT;

  DELETE FROM digits;

  SELECT description FROM digits

  VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

  What would be the outcome of the above query?

  A. It would not display any values.
  B. It would display the value TWO once.
  C. It would display the value TWO twice.
  D. It would display the values ONE, TWO, and TWO.
  C. It would display the value TWO twice.

• Question 185:

  View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.

  

  Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?

  SELECT location_id, city FROM locations

  SELECT location_id, city FROM locations JOIN departments USING(location_id);

  A. UNION
  B. MINUS
  C. INTERSECT
  D. UNION ALL
  C. INTERSECT

• Question 186:

  Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

  A. You can use column alias in the GROUP BY clause.
  B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
  C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

• Question 187:

  Given below are the SQL statements executed in a user session:

  CREATE TABLE product

  (pcode NUMBER(2),

  pnameVARCHAR2(10));

  INSERT INTO product VALUES(1, `pen');

  INSERT INTO product VALUES (2,'pencil);

  SAVEPOINT a;

  UPDATE product SET pcode = 10 WHERE pcode = 1;

  SAVEPOINT b;

  DELETE FROM product WHERE pcode = 2;

  COMMIT;

  DELETE FROM product WHERE pcode=10;

  ROLLBACK TO SAVEPOINT a;

  Which statement describes the consequences?

  A. No SQL statement would be rolled back.
  B. Both the DELETE statements would be rolled back.
  C. Only the second DELETE statement would be rolled back.
  D. Both the DELETE statements and the UPDATE statement would be rolled back.
  A. No SQL statement would be rolled back.

• Question 188:

  Evaluate the following SQL statements that are issued in the given order:

  CREATE TABLE emp

  (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15), salary NUMBER(8,2),

  mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);

  ALTER TABLE emp

  DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

  ALTER TABLE emp

  ENABLE CONSTRAINT emp_emp_no_pk;

  What would be the status of the foreign key EMP_MGR_FK?

  A. It would be automatically enabled and deferred.
  B. It would be automatically enabled and immediate.
  C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
  D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re- creating it.
  C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

• Question 189:

  View the Exhibit and examine the description of the PRODUCT_INFORMATION table.

  Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

  

  A. SELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL;
  B. SELECT COUNT(list_price) FROM product_information WHERE list_price = NULL;
  C. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
  D. SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price IS NULL;
  C. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;

• Question 190:

  View the Exhibit and examine the description of the EMPLOYEES table.

  You executed the following SQL statement:

  SELECT first_name, department_id, salary

  FROM employees

  ORDER BY department_id, first_name, salary desc;

  Which two statements are true regarding the output of the above query? (Choose two.)

  

  A. The values in all the columns would be sorted in the descending order.
  B. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID column.
  C. The values in the FIRST_NAME column would be sorted in ascending order for all the employees having the same value in the DEPARTMENT_ID column.
  D. The values in the FIRST_NAME column would be sorted in the descending order for all the employees having the same value in the DEPARTMENT_ID column.
  E. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
  C. The values in the FIRST_NAME column would be sorted in ascending order for all the employees having the same value in the DEPARTMENT_ID column.
  E. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.