Exam Code: 1z0-047 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0-047 Exam.
2021 May 1z0-047 Study Guide Questions:
Q161. View the Exhibit and examine the data in the EMPLOYEES tables.
Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id= 90 UNION SELECT employee_id, department_id FROM employees WHERE department_id= 10; What would be the outcome of the above SQL statement?
A. The statementwouldexecute successfullyanddisplay all the rows intheascending order of DEPARTMENT_ID.
B. Thestatement would execute successfullybutitwillignoretheORDERBYclause and display the rows in random order.
C. The statementwouldnot execute because the positional notationinstead of thecolumn name shouldbeusedwiththe ORDER BY clause.
D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
Answer: D
Q162. View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?
A. ALTER TABLE products
ADD price NUMBER(8,2) NOT NULL;
B. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT NOT NULL;
C. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
D. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
Answer: C
Q163. View the Exhibit and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables.
The following SQL statement was executed:
SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total,
GROUPING(e. department_id) GRP_DEPT,
GROUPING(e.job_id) GRPJOB,
GROUPING(d. location_id) GRP_LOC
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id);
View the Exhibit2 and examine the output of the command.
Which two statements are true regarding the output? (Choose two.)
A. The value1in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.
B. The value 1 in GRPJOBandGRP_LOC meansthatJOB_IDandLOCATION_ID columns are nottakeninto accountto generatethesubtotal.
C. Thevalue1inGRPJOB and GRP_LOC meansthatthe NULL value in JOBJD and LOCATIONJD columns aretakeninto account togeneratethe subtotal.
D. The value 0 inGRP_DEPT,GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columnsaretaken into account to generate the subtotal
Answer: BD
Far out 1z0-047 oracle database sql expert pdf:
Q164. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
Evaluate the following SQL statement:
SELECT TO_CHAR(list_price ,'$9,999") FROM product_information; Which two statements would be true regarding the output for this SQL statement? (Choose two.)
A. TheLIST_PRICEcolumn having value1123.90wouldbedisplayed as $1,124.
B. The LIST_PRICEcolumn having value 1123.90 would be displayed as$1,123.
C. The LIST_PRICE columnhavingvalue 11235.90would bedisplayed as $1,123.
D. The LIST_PRICE column having value 11235.90wouldbe displayed as #######.
Answer: AD
Q165. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
A. The outer query stops evaluating the result set of the inner query when the first value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
Answer: AC
Q166. View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement:
SELECT order_id, product_id, unit_price FROM order_jtems WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.
What correction should be made in the above SQL statement to achieve this?
A. Replace = with theINoperator.
B. Replace = withthe >ANYoperator.
C. Replace = with the>ALLoperator.
D. Remove the GROUP BY clause from the subquery and placeitin the main query.
Answer: A
Realistic 1z0-047 dumps free download:
Q167. View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO cust VALUES (1,’RAJ1); --row 1
INSERT INTO cust VALUES (1,'SAM); --row 2
COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1,’LATA’); --row 3
INSERT INTO cust VALUES (2,’KING’); --row 4
COMMIT;
Which rows would be made permanent in the CUST table?
A. row 4 only
B. rows 2 and 4
C. rows 3 and 4
D. rows 1 and 4
Answer: C
Q168. View the Exhibit and examine the description of the CUSTOMERS table.
You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.
Which SOL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,,^A-Z’)NOVALIDATE;
B. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,,'^[0-9]’))NOVALIDATE;
C. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[: alpha:]]'))NOVALIDATE;
D. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[: digit: ]]’))NOVALIDATE;
Answer: C
Q169. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?
A. It produces only aggregates for the groups specified in the GROUP BY clause.
B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.
C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
D. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
Answer: C
Q170. OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence:
CREATE ROLE r1;
GRANT SELECT, INSERT ON oe. orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe. orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;
What would be the outcome after executing the statements?
A. SCOTT would be able to query the OE.ORDERS table.
B. SCOTT would not be able to query the OE.ORDERS table.
C. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
D. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1
Answer: A