1z0-047 pdf download [Apr 2021]
Vivid of 1Z0-047 free draindumps materials and discount pack for Oracle certification for IT examinee, Real Success Guaranteed with Updated 1Z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!
2021 Apr 1Z0-047 Study Guide Questions:
Q161. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders) AND
Customer_id = 101;
B. SELECT order_id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT order_date
FROM orders
WHERE customer_id = 101);
D. SELECT order_id, order_date FROM orders
WHERE order_date IN (SELECT order_date
FROM orders
WHERE customer id = 101);
Answer: C
Q162. Which statement is true regarding Flashback Version Query?
A. It returns versions of rows only within a transaction.
B. It can be used in subqueries contained only in a SELECT statement.
C. It will return an error if the undo retention time is less than the lower bound time or SCN specified.
D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.
Answer: D
Q163. 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
Q164. You need to create a table for a banking application with the following considerations:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions.
4) The maximum period of the credit provision in the application is 30 days.
5) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. INTERVAL YEAR TOMONTH
B. INTERVALDAYTO SECOND
C. TIMESTAMP WITHTIMEZONE
D. TIMESTAMP WITH LOCAL TIME ZONE
Answer: B
Q165. 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
Renewal oracle 1z0-047 dumps free download:
Q166. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.
The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.
Which statement is true regarding this query?
A. The querywouldexecutetogive the desired output.
B. Thequerywouldnot execute becausedatefunctions cannot be nested.
C. Thequerywould executebuttheoutputwouldgive reviewdates that are Sundays.
D. Thequery would not execute becausetheNEXT_DAY function acceptsastring as argument.
Answer: C
Q167. You executed the following SQL statements in the given order:
CREATE TABLE orders
(order_id NUMBER(3) PRIMARY KEY,
order_date DATE,
customer_id number(3));
INSERT INTO orders VALUES (100,'10-mar-2007,,222);
ALTER TABLE orders MODIFY order_date NOT NULL;
UPDATE orders SET customer_id=333;
DELETE FROM order;
The DELETE statement results in the following error:
ERROR at line 1:
ORA-00942: table or view does not exist
What would be the outcome?
A. All the statements before the DELETEstatementwouldberolled back.
B. Allthestatements before theDELETEstatement wouldbeimplicitly committedwithin thesession.
C. Allthe statements up to theALTER TABLEstatement would be committedandthe outcomeof UPDATEstatementwouldberolled back.
D. All the statements up to theALTERTABLEstatementwould be committed and the outcome oftheUPDATE statement is retained uncommittedwithin thesession.
Answer: D
Q168. View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000.
Which SOL statement would get you the desired result?
A. SELECT employee_id, first_name
FROM employees
WHERE job_id like‘MAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000;
B. SELECT employee_ d, first_name
FROM employees
WHERE job_id like‘%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000);
C. SELECT employee_id, first_name
FROM employees
WHERE (job_id like‘%MAN' AND job_id like '%CLERK’)
AND department_id = 30 OR salary > 3000;
D. SELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' OR job_id like '%CLERK')
AND department_id= 30 AND salary > 3000;
Answer: D
Q169. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'.
Which SQL statement would give you the desired result?
A. SELECTfirst_name, salary FROM employees WHERE (manager_id,salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
B. SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
C. SELECT first_name, salary FROM employees WHERE (manager_id,salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena'
D. SELECT first_name, salary FROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena'
Answer: D
Q170. 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 MARKStable?
A. Pivoting INSERT
B. Unconditional INSERT
C. Conditional ALL INSERT
D. Conditional FIRST INSERT
Answer: A
Free 1z0-047 study guide:
Q171. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)
A. The FIRST_NAME column wouldbedropped provided it does notcontainany data.
B. The FIRST_NAME column would bedropped providedat least one or more columnsremain inthe table.
C. The FIRST_NAME column can be rolledback providedthe SET UNUSED option is added to the above SQL statement.
D. The FIRST_NAME column can be dropped evenif itis part ofacomposite PRIMARY KEY provided the CASCADE option is used.
Answer: BD
Q172. View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
A. INSERT INTO orders
VALUES (1, O9-mar-2007', 'online',",1000);
B. INSERT INTO orders
(order_id ,order_date ,order_mode,
customer_id ,order_total)
VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);
C. INSERT INTO
(SELECT order_id ,order_date .customer_id
FROM orders)
VALUES (1,O9-mar-2007', 101);
D. INSERT INTO orders
VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);
E. INSERT INTO orders
(order_id,order_date ,order_mode .order_total)
VALUES (1 ,'10-mar-2007','online',1000);
Answer: CD
Q173. Which two statements best describe the benefits of using the WITH clause? (Choose two.)
A. It enables users to store the results of a query permanently.
B. It enables users to store the query block permanently in the memory and use it to create complex queries.
C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace.
Answer: CD
Q174. View the Exhibit and examine the description of the ORDERS table.
The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose?
A. SELECT sales_rep_id, COUNT(customer_id)"Total"
FROMorders"
HAVING COUNT(customer_id)>10;
B. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
WHERE COUNT(customer_id)>10
GROUP BY sales_rep_id;
C. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROMorders"
GROUP BY sales_rep_id
HAVING total > 10;
D. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING COUNT(customer_id) > 10;
Answer: D
Q175. Evaluate the following SQL statement:
ALTER TABLE hr.emp SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?
A. Any synonym existing on the EMP table would have to be re-created.
B. Any constraints defined on the MGR_ID column would be removed by the above command.
C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
Answer: B
Q176. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
A. It would return the subtotals for the Order Amount of every CUSTOMER_ID.
B. It would return the subtotals for the Order Amount for every PRODUCT_ID.
C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.
D. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
E. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
Answer: ABD
see more http://www.2passeasy.com/exam/1Z0-047/