Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 61:

  View the Exhibit and examine the structure of CUSTOMERS table.

  

  Evaluate the following query:

  

  Which statement is true regarding the above query?

  A. It executes successfully.
  B. It produces an error because the condition on the CUST_CITY column is not valid.
  C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
  D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
  A. It executes successfully.

• Question 62:

  Examine these statements:

  CREATE ROLE registrar;

  GRANT UPDATE ON student_grades TO registrar;

  GRANT registrar to user1, user2, user3;

  What does this set of SQL statements do?

  A. The set of statements contains an error and does not work.
  B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
  C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
  D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
  E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
  F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.
  C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.

  ---

  the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1, user2 and user3.

  Incorrect answer:

  A. the statement does not contain error

  B. there is no MODIFY privilege

  D. statement does not create 3 users with the role E. privilege is grant to role then grant to user

  F. privilege is grant to role then grant to user

• Question 63:

  View the Exhibit and examine the description for the CUSTOMERS table.

  

  You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.

  Which UPDATE statement will accomplish the task?

  A. UPDATE customersSET cust_income_level = (SELECT cust_income_levelFROM customersWHERE cust_id = 2560),cust_credit_limit = (SELECT cust_credit_limitFROM customersWHERE cust_id = 2566)WHERE cust_id=2360;
  B. UPDATE customersSET (cust_income_level, cust_credit_limit) = (SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id=2560 OR cust_id=2566)WHERE cust_id=2360;
  C. UPDATE customersSET (cust_income_level, cust_credit_limit) = (SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id IN(2560, 2566)WHERE cust_id=2360;
  D. UPDATE customersSET (cust_income_level, cust_credit_limit) = (SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id=2560 AND cust_id=2566)WHERE cust_id=2360;
  A. UPDATE customersSET cust_income_level = (SELECT cust_income_levelFROM customersWHERE cust_id = 2560),cust_credit_limit = (SELECT cust_credit_limitFROM customersWHERE cust_id = 2566)WHERE cust_id=2360;

  ---

  Updating Two Columns with a Subquery

  You can update multiple columns in the SET clause of an UPDATE statement by writing multiple subqueries. The syntax is as follows:

  UPDATE table

  SET column =

  (SELECT column

  FROM table

  WHERE condition)

  [ ,

  column =

  (SELECT column

  FROM table

  WHERE condition)]

  [WHERE condition ] ;

• Question 64:

  See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. SELECT MAX(inv_date), MIN(cust_id) FROM invoice;
  B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
  C. SELECT (AVG(inv_date) FROM invoice;
  D. SELECT AVG(inv_date - SYSDATE), AVG(inv_amt) FROM invoice;
  A. SELECT MAX(inv_date), MIN(cust_id) FROM invoice;
  D. SELECT AVG(inv_date - SYSDATE), AVG(inv_amt) FROM invoice;

• Question 65:

  Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?

  A. SELECTTO_CHAR(TO_DATE('19-Mar-2001', `DD-Mon-YYYY'), `fmDdspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATE. FROM dual;
  B. SELECTTO_CHAR(TO_DATE('19-Mar-2001', `DD-Mon-YYYY'), `Ddspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATEFROM dual;
  C. SELECTTO_CHAR(TO_DATE('19-Mar-2001', `DD-Mon-YYYY'), `fmDdspth "of" Month YYYYHH:MI:SS AM') NEW_DATE FROM dual;
  D. SELECTTO_CHAR(TO_DATE('19-Mar-2001', `DD-Mon-YYYY), `fmDdspth "of" Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual;
  A. SELECTTO_CHAR(TO_DATE('19-Mar-2001', `DD-Mon-YYYY'), `fmDdspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATE. FROM dual;

• Question 66:

  You issued the following command:

  SQL> DROP TABLE employees;

  Which three statements are true?

  A. All uncommitted transactions are committed.
  B. All indexes and constraints defined on the table being dropped are also dropped.
  C. Sequences used in the employees table become invalid.
  D. The space used by the employees table is reclaimed immediately.
  E. The employees table can be recovered using the rollback command.
  F. The employees table is moved to the recycle bin.
  A. All uncommitted transactions are committed.
  B. All indexes and constraints defined on the table being dropped are also dropped.
  F. The employees table is moved to the recycle bin.

  ---

  A: If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will be committed.

  B: Dropping a table invalidates dependent objects, such as indexes and constraints.

  F: The DROP TABLE statement moves a table or object table to the recycle bin.

  Incorrect:

  Not B: In general sequences used in the table would not by affected when the table is dropped.

  Not D: Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user's space quota. Not E:

  Dropping a table invalidates dependent objects and removes object privileges on the table. If you want to re-create the table, then you must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the

  table, and respecify its storage parameters.

  References:

• Question 67:

  You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table. Which query will generate the required result?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

  ---

  FETCH can only be used with FIRST OR NEXT. The syntax is:

  FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY OFFSET { integer-literal | ? } {ROW | ROWS}

• Question 68:

  Which three SQL statements would display the value 1890.55 as $1, 890.55? (Choose three.)

  A. SELECT TO_CHAR(1890.55, '$0G000D00')FROM DUAL;
  B. SELECT TO_CHAR(1890.55, '$9, 999V99')FROM DUAL;
  C. SELECT TO_CHAR(1890.55, '$99, 999D99')FROM DUAL;
  D. SELECT TO_CHAR(1890.55, '$99G999D00')FROM DUAL;
  E. SELECT TO_CHAR(1890.55, '$99G999D99')FROM DUAL;
  A. SELECT TO_CHAR(1890.55, '$0G000D00')FROM DUAL;
  D. SELECT TO_CHAR(1890.55, '$99G999D00')FROM DUAL;
  E. SELECT TO_CHAR(1890.55, '$99G999D99')FROM DUAL;

• Question 69:

  Examine the structure of the PROMOTIONS table:

  

  The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result?

  A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
  B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
  C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
  D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
  D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

• Question 70:

  Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

  A. First normal form
  B. Second normal form
  C. Third normal form
  D. Fourth normal form
  B. Second normal form

  ---

  According to the Second Normal Form (2NF) there must be no partial dependencies on a concatenated key.