1z0-061
Free trial
Verified
Question 1
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A: It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
- B: It produces an error because positional notation cannot be used in the order by clause with set operators.
- C: It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.
- D: 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.
Question 2
Which three SQL statements would display the value 1890.55 as $1, 890.55?
- A: Option A
- B: Option B
- C: Option C
- D: Option D
Question 3
You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table.
Which query will generate the required result?
- A: Option A
- B: Option B
- C: Option C
- D: Option D B
Question 4
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
Question 5
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?
- A: It produces an error because the AMT_SPENT column contains a null value.
- B: It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
- C: It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
- D: 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.
Question 6
You execute the following commands:
For which substitution variables are you prompted for the input?
- A: None, because no input required
- B: Both the substitution variables 'hiredate' and 'mgr_id\
- C: Only 'hiredate'
- D: Only 'mgr_id'
Question 7
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?
- A: INNER JOIN
- B: FULL OUTER JOIN
- C: LEFT OUTER JOIN
- D: RIGHT OUTER JOIN
Question 8
View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING (compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?
- A: JOIN; JOIN
- B: FULL OUTER JOIN; FULL OUTER JOIN
- C: RIGHT OUTER JOIN; LEFT OUTER JOIN
- D: LEFT OUTER JOIN; RIGHT OUTER JOIN
Question 9
Evaluate the following SQL commands:
The command to create a table fails. Identify the two reasons for the SQL statement failure?
- A: You cannot use SYSDATE in the condition of a check constraint.
- B: You cannot use the BETWEEN clause in the condition of a check constraint.
- C: You cannot use the NEXTVAL sequence value as a default value for a column.
- D: You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.
Question 10
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?
- A: It shows COST_REMARK for all the promos in the table.
- B: It produces an error because the SUBQUERY gives an error.
- C: It shows COST_REMARK for all the promos in the promo category 'TV'
- D: It produces an error because SUBQUERIES cannot be used with the case expression.
Question 11
Examine the data in the ORD_ITEMS table:
Evaluate the following query:
Which statement is true regarding the outcome of the above query?
- A: It gives an error because the having clause should be specified after the group by clause.
- B: It gives an error because all the aggregate functions used in the having clause must be specified in the select list.
- C: 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.
- D: 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.
Question 12
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?
- A: The SALES1 table is created with no rows but only a structure.
- B: The SALES1 table would have primary key and unique constraints on the specified columns.
- C: The SALES1 table would not be created because of the invalid where clause.
- D: The SALES1 table would have not null and unique constraints on the specified columns.
- E: The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,
Question 13
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?
- A: Option A
- B: Option B
- C: Option C
- D: Option D
Question 14
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?
- A: Option A
- B: Option B
- C: Option C
- D: Option D
Question 15
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?
- A: It executes successfully and gives the correct result.
- B: It executes successfully but does not give the correct result.
- C: It generates an error because a subquery cannot have a join condition in an update statement.
- D: It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
Question 16
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?
- A: Option A
- B: Option B
- C: Option C
- D: Option D
Question 17
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?
- A: Disable the primary key in the STORES table.
- B: Use CASCADE keyword with DELETE statement.
- C: DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
- D: Disable the FOREIGN KEY in SALES table and then delete the rows.
- E: Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
That’s the end of your free questions
You’ve reached the preview limit for 1z0-061Consider upgrading to gain full access!
Free preview mode
Enjoy the free questions and consider upgrading to gain full access!