1z0-047 | Leading 1z0-047 Exam Study Guides With New Update Exam Questions
Q41. View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom.
Which SQL statement would you execute to fulfill this requirement?
A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE-warranty_period;
B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE+warranty_period;
C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE;
D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM product_information WHERE warranty_period >SYSDATE;
Answer: B
Q42. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: DE
Q43. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A
Q44. Which statement best describes the GROUPING function?
A. It is used to set the order for the groups to be used for calculating the grand totals and subtotals.
B. It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators.
C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.
D. It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals.
Answer: C
Q45. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
A. It wouldbeautomatically enabled and deferred.
B. It wouldbeautomaticallyenabledand immediate.
C. Itwouldremaindisabled and has to beenabled manuallyusingthe ALTER TABLEcommand.
D. Itwould remain disabled and can be enabled only by droppingtheforeign key constraint andre-creating it.
Answer: ABD
Q46. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. Which is the best method to load data from the MARKS_DETAILStable to the MARKS table?
A. PivotingINSERT
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: A
Q47. View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement:
SELECT category_name ,category_description FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)
A. WHEREREGEXPJJKE(category_description, 'hard+.s’);
B. WHERE REGEXPJJKE(category_description,‘^H|hard+.s’);
C. WHERE REGEXPJJKE (category_description, '^H|hard+.s$');
D. WHEREREGEXPJJKE (category_description, '[^Hlhard+.s]');
Answer: AB
Q48. View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.
A. Statement 1 would return only one row of output.
B. Both the statements would give the same output.
C. Statement 2 would return multiple rows of output.
D. Statement 1 would not return any row because the GROUP BY clause is missing.
E. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.
Answer: ACE
Q49. View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id);
Which two statements are true regarding the output of this command? (Choose two.)
A. Theoutputwould display thetotal salaryforallthedepartments.
B. Theoutputwoulddisplay the total salaryforall theJOB_IDsinadepartment.
C. The output would display only the grand total ofthesalary for allJOB_IDsin a LOCATION_ID.
D. Theoutput would displaythegrandtotalof thesalaryfor only the groups specified in the GROUP BY clause.
Answer: AB
Q50. Which two statements are true regarding operators used with subqueries? (Choose two.
A. The NOT IN operator is equivalent to IS NULL
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in single-row subqueries.
E. TheNOT operator can be used with IN, ANY and ALL operators.
Answer: BE