Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 91:

  The PART_CODE column in the SPARES table contains the following list of values:

  

  Which statement is true regarding the outcome of the above query?

  A. It produces an error.
  B. It displays all values.
  C. It displays only the values A%_WQ123 and AB_WQ123 .
  D. It displays only the values A%_WQ123 and A%BWQ123 .
  E. It displays only the values A%BWQ123 and AB_WQ123.
  D. It displays only the values A%_WQ123 and A%BWQ123 .

• Question 92:

  Top N analysis requires _____ and _____. (Choose two.)

  A. the use of rowid
  B. a GROUP BY clause
  C. an ORDER BY clause
  D. only an inline view
  E. an inline view and an outer query
  C. an ORDER BY clause
  E. an inline view and an outer query

• Question 93:

  The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

  A. GRANT select ON dept TO ALL_USERS;
  B. GRANT select ON dept TO ALL;
  C. GRANT QUERY ON dept TO ALL_USERS
  D. GRANT select ON dept TO PUBLIC;
  D. GRANT select ON dept TO PUBLIC;

• Question 94:

  Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

  Lex De Haan Renske Ladwig Jose Manuel Urman Jason Mallin You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows: CUST NAME

  *** De Haan **** Manuel Urman Which two queries give the required output? (Choose two.)

  A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)0;
  B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)0;
  C. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- INSTR (cust_name,''),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,-2)0;
  D. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- INSTR(cust_name,' '),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)0 ;
  A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)0;
  B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)0;

• Question 95:

  Evaluate this SQL statement:

  SELECT e.emp_name, d.dept_name

  FROM employees e

  JOIN departments d

  USING (department_id)

  WHERE d.department_id NOT IN (10,40)

  ORDER BY dept_name;

  The statement fails when executed. Which change fixes the error?

  A. remove the ORDER BY clause
  B. remove the table alias prefix from the WHERE clause
  C. remove the table alias from the SELECT clause
  D. prefix the column in the USING clause with the table alias
  E. prefix the column in the ORDER BY clause with the table alias
  F. replace the condition "d.department_id NOT IN (10,40)" in the WHERE clause with "d.department_id 10 AND d.department_id 40"
  C. remove the table alias from the SELECT clause
  E. prefix the column in the ORDER BY clause with the table alias

• Question 96:

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

  Exhibit:

  

  Evaluate the following query: Exhibit:

  

  The above query produces an error on execution. What is the reason for the error?

  A. An alias cannot be used in an expression
  B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
  C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
  D. The alias NAME should not be enclosed within double quotation marks
  A. An alias cannot be used in an expression

• Question 97:

  Examine the structure of the PROMOS table:

  

  You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day. Which query gives the correct output?

  A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') FROM promos;
  B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') FROM promos;
  C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'Same Day') FROM promos;
  D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') FROM promos;
  D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') FROM promos;

• Question 98:

  Which two statements are true regarding working with dates? (Choose two.)

  A. The default internal storage of dates is in the numeric format
  B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required
  C. The default internal storage of dates is in the character format
  D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century
  A. The default internal storage of dates is in the numeric format
  B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required

• Question 99:

  The STUDENT_GRADES table has these columns:

  

  Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?

  A. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' OR gpa > 3.;
  B. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa gt 3.0;
  C. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;
  D. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' OR gpa > 3.0;
  E. SELECT student_id, gpa FROM student_grades WHERE semester_end > '01-JAN-2001' OR semester_end < '31-DEC-2001' AND gpa >= 3.0;
  C. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;

• Question 100:

  Which statement is true regarding sub queries?

  A. The LIKE operator cannot be used with single- row subqueries.
  B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
  C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
  D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.
  D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.