Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 111:

  The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.

  Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT * FROM ord;

  A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
  B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
  C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
  D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
  D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

• Question 112:

  Which two are true about aggregate functions? (Choose two.)

  A. You can use aggregate functions in any clause of a SELECT statement.
  B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
  C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
  D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
  E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
  F. You cannot group the rows of a table by more than one column while using aggregate functions.
  A. You can use aggregate functions in any clause of a SELECT statement.
  D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

• Question 113:

  See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

  

  You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

  A. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;
  B. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;
  C. SELECT custname, grade FROM customers, grades WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers) AND cust_credit_limit BETWEEN startval AND endval;
  D. SELECT custname, grade FROM customers , grades WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers) AND MAX(cust_credit_limit) BETWEEN startval AND endval;
  B. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;
  C. SELECT custname, grade FROM customers, grades WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers) AND cust_credit_limit BETWEEN startval AND endval;

• Question 114:

  Which two statements complete a transaction? (Choose two)

  A. DELETE employees;
  B. DESCRIBE employees;
  C. ROLLBACK TO SAVEPOINT C;
  D. GRANT SELECT ON employees TO SCOTT;
  E. ALTER TABLE employees SET UNUSED COLUMN sal;
  F. Select MAX(sal) FROM employees WHERE department_id = 20;
  D. GRANT SELECT ON employees TO SCOTT;
  E. ALTER TABLE employees SET UNUSED COLUMN sal;

• Question 115:

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

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

• Question 116:

  Which are /SQL*Plus commands? (Choose all that apply.)

  A. INSERT
  B. UPDATE
  C. SELECT
  D. DESCRIBE
  E. DELETE
  F. RENAME
  D. DESCRIBE

• Question 117:

  Examine the structure of the PROMOS table: You want to generate a report showing promo names and their duration (number of days). If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)

  

  A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
  B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date promo_start_date),'ONGOING') FROM promos;
  C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
  D. SELECT promo_name, DECODE(promo_end_date -promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM promos;
  E. SELECT promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING', promo_end_date - promo_start_date) FROM promos;
  B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date promo_start_date),'ONGOING') FROM promos;
  C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
  D. SELECT promo_name, DECODE(promo_end_date -promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM promos;

• Question 118:

  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 119:

  Which best describes an inline view?

  A. a schema object
  B. a sub query that can contain an ORDER BY clause
  C. another name for a view that contains group functions
  D. a sub query that is part of the FROM clause of another query
  D. a sub query that is part of the FROM clause of another query

• Question 120:

  You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:

  CUST_ID NUMBER(4) NOT NULL CUST_NAME VARCHAR2(100) NOT NULL CUST_ADDRESS VARCHAR2(150) CUST_PHONE VARCHAR2(20)

  Which SELECT statement accomplishes this task?

  A. SELECT * FROM customers
  B. SELECT name, address FROM customers;
  C. SELECT id, name, address, phone FROM customers;
  D. SELECT cust_name, cust_address FROM customers;
  E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
  D. SELECT cust_name, cust_address FROM customers;