Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 61:

  The STUDENT_GRADES table has these columns:

  STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3)

  The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest. Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

  A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
  B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
  C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
  D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
  E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
  F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
  F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

• Question 62:

  View the Exhibit and examine the structure of the PRODUCTS table.

  

  You want to display the category with the maximum number of items. You issue the following query:

  SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);

  What is the outcome?

  A. It executes successfully and gives the correct output.
  B. It executes successfully but does not give the correct output.
  C. It generates an error because the subquery does not have a GROUP BY clause.
  D. It generates an error because = is not valid and should be replaced by the IN operator.
  C. It generates an error because the subquery does not have a GROUP BY clause.

• Question 63:

  You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

  Which statement accomplishes this task?

  A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE
  B. dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
  C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
  D. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX (sal) maxsal FROM employees b GROUP BY dept_id);
  E. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
  B. dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;

• Question 64:

  View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.

  

  Which two queries give the correct result? (Choose two.)

  A. SELECT promo_name, TO_CHAR(promo_end_date,'Day') ', ' TO_CHAR(promo_end_date,'Month') ' ' TO_CHAR(promo_end_date,'DD, YYYY') AS last_day FROM promotions;
  B. SELECT promo_name,TO_CHAR (promo_end_date,'fxDay') ', ' TO_CHAR(promo_end_date,'fxMonth') ' ' TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day FROM promotions;
  C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR (promo_end_date,'Month')) ' ' TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day FROM promotions;
  D. SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')',' TO_CHAR(promo_end_date,'fmMonth') ' ' TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day FROM promotions;
  C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR (promo_end_date,'Month')) ' ' TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day FROM promotions;
  D. SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')',' TO_CHAR(promo_end_date,'fmMonth') ' ' TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day FROM promotions;

• Question 65:

  You want to display the date for the first Monday of the next month and issue the following command:

  SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL;

  What is the outcome?

  A. It executes successfully and returns the correct result.
  B. It executes successfully but does not return the correct result.
  C. It generates an error because TO_CHAR should be replaced with TO_DATE.
  D. It generates an error because rrrr should be replaced by rr in the format string.
  E. It generates an error because fm and double quotation marks should not be used in the format string.
  A. It executes successfully and returns the correct result.

• Question 66:

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

  What is true of using group functions on columns that contain NULL values?

  A. Group functions on columns ignore NULL values.
  B. Group functions on columns returning dates include NULL values.
  C. Group functions on columns returning numbers include NULL values.
  D. Group functions on columns cannot be accurately used on columns that contain NULL values.
  E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
  A. Group functions on columns ignore NULL values.

• Question 68:

  See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:

  

  You issue the following query:

  

  Which statement is true regarding the outcome of this query?

  A. It produces an error because the NATURAL join can be used only with two tables
  B. It produces an error because a column used in the NATURAL join cannot have a qualifier
  C. It produces an error because all columns used in the NATURAL join should have a qualifier
  D. It executes successfully
  B. It produces an error because a column used in the NATURAL join cannot have a qualifier

• Question 69:

  In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'ABIGAIL'. Evaluate the following query:

  

  What would be the outcome?

  A. Abigail PA
  B. Abigail Pa
  C. Abigail IS
  D. an error message
  B. Abigail Pa

• Question 70:

  The CUSTOMERS table has these columns:

  

  A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France?

  A. WHERE lower(country_address) = "france"
  B. WHERE lower(country_address) = 'france'
  C. WHERE lower(country_address) IS 'france'
  D. WHERE lower(country_address) = '%france%'
  E. WHERE lower(country_address) LIKE %france%
  B. WHERE lower(country_address) = 'france'