Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 231:

  Evaluate the SQL statement:

  SELECT LPAD (salary,10,'*')

  FROM EMP

  WHERE EMP_ID = 1001;

  If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

  A. 17000.00
  B. 17000*****
  C. ****170.00
  D. **17000.00
  E. an error statement
  D. **17000.00

• Question 232:

  Examine the structure of the TRANSACTIONS table:

  Name Null Type

  TRANS_ID NOT NULL NUMBER(3) CUST_NAME VARCHAR2(30) TRANS_DATE TIMESTAMP TRANS_AMT NUMBER(10,2)

  You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?

  A. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), TO_CHAR(trans_amt,'$99999999D99') FROM transactions WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)NULL;
  B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL(TO_CHAR(trans_amt,'$99999999D99'),0) FROM transactions WHERE TO_CHAR(trans_date,'hh24') < 12;
  C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;
  D. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;
  B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL(TO_CHAR(trans_amt,'$99999999D99'),0) FROM transactions WHERE TO_CHAR(trans_date,'hh24') < 12;

• Question 233:

  Which two statements about creating constraints are true? (Choose two)

  A. Constraint names must start with SYS_C.
  B. All constraints must be defines at the column level.
  C. Constraints can be created after the table is created.
  D. Constraints can be created at the same time the table is created.
  E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.
  C. Constraints can be created after the table is created.
  D. Constraints can be created at the same time the table is created.

• Question 234:

  You created an ORDERS table with the following description: Exhibit:

  

  You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?

  A. You cannot add a primary key constraint if data exists in the column
  B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
  C. The primary key constraint can be created only a the time of table creation
  D. You cannot have two constraints on one column
  B. You can add the primary key constraint even if data exists, provided that there are no duplicate values

• Question 235:

  Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

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

  NEW_EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60)

  Which MERGE statement is valid?

  A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
  B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
  C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
  D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||
  E. last_name);
  A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

• Question 236:

  Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

  A. The BLOB data type column is used to store binary data in an operating system file
  B. The minimum column width that can be specified for a VARCHAR2 data type column is one
  C. A TIMESTAMP data type column stores only time values with fractional seconds
  D. The value for a CHAR data type column is blank-padded to the maximum defined column width
  E. Only One LONG column can be used per table
  B. The minimum column width that can be specified for a VARCHAR2 data type column is one
  D. The value for a CHAR data type column is blank-padded to the maximum defined column width
  E. Only One LONG column can be used per table

• Question 237:

  You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

  

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

  A. UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
  B. SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
  C. UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
  D. SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
  A. UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
  B. SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';

• Question 238:

  View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.)

  A. SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;
  B. SELECT prod_id FROM products MINUS SELECT prod_id FROM sales;
  C. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id;
  D. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id s.prod_id;
  A. SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;
  C. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id;

• Question 239:

  The EMPLOYEES table contains these columns:

  EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10)

  Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

  A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1, 1) = 'n';
  B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR (ENAME, -1, 1) = 'n';
  C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, 1, 1) = 'n';
  D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, -1, 1) = 'n';
  A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1, 1) = 'n';

• Question 240:

  Which describes the default behavior when you create a table?

  A. The table is accessible to all users.
  B. Tables are created in the public schema.
  C. Tables are created in your schema.
  D. Tables are created in the DBA schema.
  E. You must specify the schema when the table is created.
  C. Tables are created in your schema.