Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 11:

  You need to perform these tasks:

  1.

  Create and assign a MANAGER role to Blake and Clark

  2.

  Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark

  Which set of SQL statements achieves the desired results?

  A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE,CLARK;
  B. CREATE ROLE manager; GRANT create table, create voew TO manager; GRANT manager ROLE TO BLAKE,CLARK;
  C. GRANT manager ROLE TO BLAKE,CLARK; GRANT create table, create voew TO BLAKE CLARK; ***MISSING***
  A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE,CLARK;

• Question 12:

  You need to display the date 11-Oct-2007 in words as `Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

  A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL;
  B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
  C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
  D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL;
  C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;

• Question 13:

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

  

  You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

  A. UPDATE customers SET cust_credit_limit = NULL WHERE CUST_INCOME_LEVEL = NULL;
  B. UPDATE customers SET cust_credit_limit = NULL WHERE cust_income_level IS NULL;
  C. UPDATE customers SET cust_credit_limit = TO_NUMBER(NULL) WHERE cust_income_level = TO_NUMBER(NULL);
  D. UPDATE customers SET cust_credit_limit = TO_NUMBER(' ',9999) WHERE cust_income_level IS NULL;
  B. UPDATE customers SET cust_credit_limit = NULL WHERE cust_income_level IS NULL;

• Question 14:

  Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

  

  Which DELETE statement is valid?

  A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
  B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
  C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = 'Carrey');
  D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey');
  C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = 'Carrey');

• Question 15:

  You are granted the CREATE VIEW privilege. What does this allow you to do?

  A. Create a table view.
  B. Create a view in any schema.
  C. Create a view in your schema.
  D. Create a sequence view in any schema.
  E. Create a view that is accessible by everyone.
  F. Create a view only of it is based on tables that you created.
  C. Create a view in your schema.

• Question 16:

  See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:

  

  The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

  Evaluate the following the CREATE TABLE command:

  Exhibit:

  

  Which statement is true regarding the above command?

  A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
  B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
  C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
  D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
  B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table

• Question 17:

  You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position. Which query would give the required output?

  A. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')0 AND SUBSTR(cust_first_name, -2, 1)='a';
  B. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')'' AND SUBSTR(cust_first_name, -2, 1)='a';
  C. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)='a';
  D. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';
  A. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')0 AND SUBSTR(cust_first_name, -2, 1)='a';

• Question 18:

  Evaluate these two SQL statements:

  SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

  What is true about them?

  A. The two statements produce identical results.
  B. The second statement returns a syntax error.
  C. There is no need to specify DESC because the results are sorted in descending order by default.
  D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
  A. The two statements produce identical results.

• Question 19:

  You own a table called EMPLOYEES with this table structure:

  EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE

  What happens when you execute this DELETE statement?

  DELETE employees;

  A. You get an error because of a primary key violation.
  B. The data and structure of the EMPLOYEES table are deleted.
  C. The data in the EMPLOYEES table is deleted but not the structure.
  D. You get an error because the statement is not syntactically correct.
  C. The data in the EMPLOYEES table is deleted but not the structure.

• Question 20:

  Evaluate the following SQL statements: Exhibit:

  

  Which is the correct output of the above query?

  A. +00-300, +54-02,+00 11:12:10.123457
  B. +00-300,+00-650,+00 11:12:10.123457
  C. +25-00, +54-02, +00 11:12:10.123457
  D. +25-00,+00-650,+00 11:12:10.123457
  C. +25-00, +54-02, +00 11:12:10.123457