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-061

Oracle 1z0-061 Exam Practice Questions (P. 4)

  • Full Access (339 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 #31
View the Exhibit and examine the data in the promotions table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post category that were launched before January 1, 2000.
Which SQL statement would you use?

  1. A
    Option A
  2. B
    Option B
  3. C
    Option C
  4. D
    Option D

Correct Answer:
D

Question #32
You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'active1 if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?

  1. A
    Option A
  2. B
    Option B
  3. C
    Option C
  4. D
    Option D

Correct Answer:
D
CLOB Character data (up to 4 GB)
NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from 84 to 127.)

Question #33
Examine the structure of the sales table:

Evaluate the following create table statement:

Which two statements are true about the creation of the SALES1 table?
  1. A
    The SALES1 table is created with no rows but only a structure.
  2. B
    The SALES1 table would have primary key and unique constraints on the specified columns.
  3. C
    The SALES1 table would not be created because of the invalid where clause.
  4. D
    The SALES1 table would have not null and unique constraints on the specified columns.
  5. E
    The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,

Correct Answer:
AD
A: the WHERE clause 1=2 is false so no rows will be inserted into the new table.
D: The NOT NULL constraints will be included in the new table. There are no UNIQUE constraints.

Question #34
Which two statements are true regarding subqueries?
  1. A
    A subquery can retrieve zero or more rows.
  2. B
    Only two subqueries can be placed at one level.
  3. C
    A subquery can be used only in SQL query statements.
  4. D
    A subquery can appear on either side of a comparison operator.
  5. E
    There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Correct Answer:
AD
Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abels salary.
To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query).
Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

Subquery Syntax -
A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including the following:

WHERE clause -

HAVING clause -

FROM clause -
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.

Guidelines for Using Subqueries -
Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to "only" 255 levels in a WHERE clause. They can be used in the SELECT list and in the
FROM, WHERE, and HAVING clauses of a query.

Question #35
Examine the structure of the products table:

You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?

  1. A
    Option A
  2. B
    Option B
  3. C
    Option C
  4. D
    Option D

Correct Answer:
A

Question #36
Which statement is true regarding the UNION operator?
  1. A
    By default, the output is not sorted.
  2. B
    Null values are not ignored during duplicate checking.
  3. C
    Names of all columns must be identical across all select statements.
  4. D
    The number of columns selected in all select statements need not be the same.

Correct Answer:
B
For the UNION operator the nulls values are not ignored during duplicate checking.
Incorrect:
Not A: The UNION operator implicitly sorts the output.
Not D: Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

Question #37
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?

  1. A
    Option A
  2. B
    Option B
  3. C
    Option C
  4. D
    Option D

Correct Answer:
CD

Question #38
View the Exhibit and examine the structures of the employees and departments tables.

You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.
You issue the following command:

What is the outcome?
  1. A
    It executes successfully and gives the correct result.
  2. B
    It executes successfully but does not give the correct result.
  3. C
    It generates an error because a subquery cannot have a join condition in an update statement.
  4. D
    It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.

Correct Answer:
B
Not that employees is used both in the first line (UPDATE employees) and later (FROM employees, departments). This would not cause the correct output.
Instead aliases should be use.
The following would be the correct query:

UPDATE employees a -
SET department_id =
(SELECT department_id

FROM departments -
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)

FROM employees b -
WHERE a.department_id = b.department_id)

WHERE department_id IN -
(SELECT department_id

FROM departments -

WHERE location_id = 2900 -
OR location_id = 2700);
Reference:
http://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708

Question #39
You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading
Name and the incremented credit limit should be labeled New credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

  1. A
    Option A
  2. B
    Option B
  3. C
    Option C
  4. D
    Option D

Correct Answer:
C
A column alias:
- Renames a column heading
- Is useful with calculations
- Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
- Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.

Question #40
View the Exhibit and examine the data in the costs table.

You need to generate a report that displays the IDs of all products in the costs table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.
You issue the following query:

Which statement is true regarding the above query?
  1. A
    It executes and produces the required result.
  2. B
    It produces an error because an expression cannot be used in the order by clause.
  3. C
    It produces an error because the DESC option cannot be used with an expression in the order by clause.
  4. D
    It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.

Correct Answer:
A

Previous Questions Next Questions

All Pages