Nest 0 Cart
Nest Account
Popular Exams
All Categories...
Nest
Nest 0
Contact
Log In / Sign Up
Copyright 2024 © store. All rights reserved.
Home Oracle 1z0-051

Oracle 1z0-051 Exam Practice Questions (P. 2)

  • Full Access (290 questions)
  • Six months of Premium Access
  • Access to one million comments
  • Seamless ChatGPT Integration
  • Ability to download PDF files
  • Anki Flashcard files for revision
  • No Captcha & No AdSense
  • Advanced Exam Configuration
Get Contributor Access
Question #6
View the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
  1. A
    SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_credit_limit, cust_city;
  2. B
    SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_city,cust_credit_limit;
  3. C
    SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_city;
  4. D
    SELECT cust_city, AVG(NVL(cust_credit_limit,0)) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK');

Correct Answer:
C
Creating Groups of Data: GROUP BY Clause Syntax
You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.
In the syntax:
group_by_expression Specifies the columns whose values determine the basis for grouping rows

Guidelines -
If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause.
You receive an error message if you fail to include the column list in the GROUP BY clause.
Using a WHERE clause, you can exclude rows before dividing them into groups.
You must include the columns in the GROUP BY clause.
You cannot use a column alias in the GROUP BY clause.

Question #7
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?
  1. A
    The two statements produce identical results.
  2. B
    The second statement returns a syntax error.
  3. C
    There is no need to specify DESC because the results are sorted in descending order by default.
  4. 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.

Correct Answer:
A
the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect answer:

Bthere is no syntax error -
Cresult are sorted in ascending order by default
DORDER BY 2 will take the second column as sorting column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

Question #8
Where can sub queries be used? (Choose all that apply)
  1. A
    field names in the SELECT statement
  2. B
    the FROM clause in the SELECT statement
  3. C
    the HAVING clause in the SELECT statement
  4. D
    the GROUP BY clause in the SELECT statement
  5. E
    the WHERE clause in only the SELECT statement
  6. F
    the WHERE clause in SELECT as well as all DML statements

Correct Answer:
ABCF
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following are required clauses:
A SELECT list
A FROM clause
The following are optional clauses:
WHERE
GROUP BY
HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

Question #9
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
  1. A
    SELECT TO_CHAR(1890.55,'$99G999D00')FROM DUAL;
  2. B
    SELECT TO_CHAR(1890.55,'$9,999V99')FROM DUAL;
  3. C
    SELECT TO_CHAR(1890.55,'$0G000D00')FROM DUAL;
  4. D
    SELECT TO_CHAR(1890.55,'$99G999D99')FROM DUAL;
  5. E
    SELECT TO_CHAR(1890.55,'$9,999D99')FROM DUAL;

Correct Answer:
ACD

Question #10
Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?
  1. A
    It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
  2. B
    It executes successfully and displays rows in the descending order of PROMO_CATEGORY
  3. C
    It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
  4. D
    It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators A

Correct Answer:
Explanation
Using the ORDER BY Clause in Set Operations
The ORDER BY clause can appear only once at the end of the compound query.
Component queries cannot have individual ORDER BY clauses.
The ORDER BY clause recognizes only the columns of the first SELECT query.
By default, the first column of the first SELECT query is used to sort the output in an ascending order.

Previous Questions Next Questions

All Pages