1z0-061 practice test

1. Question 1 · 1

   Evaluate the following SQL statement: Which statement is true regarding the outcome of the above query?

   Choose one answer.

   

   • It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
   • It produces an error because positional notation cannot be used in the order by clause with set operators.
   • It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.
   • 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. Question 2 · 1

   Which three SQL statements would display the value 1890.55 as $1, 890.55?

   Choose all answers that apply.

   

   • Option A
   • Option B
   • Option C
   • Option D

3. Question 3 · 1

   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?

   Choose one answer.

   

   • Abigail PA
   • Abigail Pa
   • Abigail IS
   • An error message

4. Question 4 · 1

   View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement: Which statement is true regarding the execution of the above query?

   Choose one answer.

   

   

   • It produces an error because the AMT_SPENT column contains a null value.
   • It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
   • It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
   • It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.

   Read explanation

   The NULLIF Function - The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

5. Question 5 · 1

   You execute the following commands: For which substitution variables are you prompted for the input?

   Choose one answer.

   

   • None, because no input required
   • Both the substitution variables 'hiredate' and 'mgr_id\
   • Only 'hiredate'
   • Only 'mgr_id'

6. Question 6 · 1

   View the Exhibit and examine the data in the employees table: You want to display all the employee names and their corresponding manager names. Evaluate the following query: Which join option can be used in the blank in the above query to get the required output?

   Choose one answer.

   

   

   • INNER JOIN
   • FULL OUTER JOIN
   • LEFT OUTER JOIN
   • RIGHT OUTER JOIN

7. Question 7 · 1

   Evaluate the following SQL commands: The command to create a table fails. Identify the two reasons for the SQL statement failure?

   Choose all answers that apply.

   

   • You cannot use SYSDATE in the condition of a check constraint.
   • You cannot use the BETWEEN clause in the condition of a check constraint.
   • You cannot use the NEXTVAL sequence value as a default value for a column.
   • You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.

   Read explanation

   CHECK Constraint - The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees - (... Salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary > 0),

8. Question 8 · 1

   View the Exhibit and examine the structure of the promotions table. Evaluate the following SQL statement: Which statement is true regarding the outcome of the above query?

   Choose one answer.

   

   

   • It shows COST_REMARK for all the promos in the table.
   • It produces an error because the SUBQUERY gives an error.
   • It shows COST_REMARK for all the promos in the promo category 'TV'
   • It produces an error because SUBQUERIES cannot be used with the case expression.

9. Question 9 · 1

   Examine the data in the ORD_ITEMS table: Evaluate the following query: Which statement is true regarding the outcome of the above query?

   Choose one answer.

   

   

   • It gives an error because the having clause should be specified after the group by clause.
   • It gives an error because all the aggregate functions used in the having clause must be specified in the select list.
   • It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
   • It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

10. Question 10 · 1

    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?

    Choose all answers that apply.

    

    

    • The SALES1 table is created with no rows but only a structure.
    • The SALES1 table would have primary key and unique constraints on the specified columns.
    • The SALES1 table would not be created because of the invalid where clause.
    • The SALES1 table would have not null and unique constraints on the specified columns.
    • The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,

    Read explanation

    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.

11. Question 11 · 1

    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?

    Choose one answer.

    

    

    • Option A
    • Option B
    • Option C
    • Option D

12. Question 12 · 1

    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 all answers that apply.

    

    

    • Option A
    • Option B
    • Option C
    • Option D

13. Question 13 · 1

    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?

    Choose one answer.

    

    • Option A
    • Option B
    • Option C
    • Option D

    Read explanation

    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.

14. Question 14 · 1

    Examine the create table statements for the stores and sales tables. SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id)); You executed the following statement: SQL> DELETE from stores - WHERE store_id=900; The statement fails due to the integrity constraint error: ORA-02292: integrity constraint (HR.STORE_ID_FK) violated Which three options ensure that the statement will execute successfully?

    Choose all answers that apply.

    • Disable the primary key in the STORES table.
    • Use CASCADE keyword with DELETE statement.
    • DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
    • Disable the FOREIGN KEY in SALES table and then delete the rows.
    • Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

15. Question 15 · 1

    Evaluate the following query: SQL> SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL; What would be the outcome?

    Choose one answer.

    • 16
    • 100
    • 160
    • 200
    • 150

    Read explanation

    Function Purpose - ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

Paid access to all 73 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.