Youll get the detailed description when you deal with issues during your 1z0-047 research. Actualtests supply you the 1z0-047 Oracle Database SQL Expert blueprint legibly inside the 1z0-047 places. All these are greatly saving your expenses to take part in the Oracle training courses.
2021 Jun 1z0-047 actual test
Q61. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use?
A. Pivoting INSERT
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: C
Q62. Evaluate the following statements:
CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE);
UPDATE digits SET description =’TWO'WHERE id=1;
INSERT INTO digits VALUES (2 .’TWO’);
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;
What would be the outcome of the above query?
A. Itwouldnot display any values.
B. It would displaythevalue TWO once.
C. Itwould display the valueTWOtwice.
D. Itwould display the values ONE, TWO, andTWO.
Answer: C
Q63. 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. SELECT first_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, salaryFROM 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
Q64. View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.
You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT ID for the last six months.
Which SQL statement would you execute to get the desired output?
A. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_ date,SYSDATE)<=6;
B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) HAVING MONTHS_BETWEEN(order_ date,SYSDATE)<=6;
C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_ date,SYSDATE)>=6;
D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id WHERE MONTHS_BETWEEN(order date,SYSDATE)<=6 GROUP BY ROLLUP (o.customer_id, oi.product_id);
Answer: D
Q65. Evaluate the following command:
CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name job_id FROM employees WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;
Which statements are true regarding the above view? (Choose all that apply.)
A. It allows you to insert details of allnewstaff into the EMPLOYEES table.
B. Itallowsyou todeletethedetails of the existing sales staff fromtheEMPLOYEES table.
C. It allows you to updatethejob ids oftheexisting sales staff to any other job id in the EMPLOYEES table.
D. It allows you to insert the IDs, last names and job ids of the sales staff from theviewif it is used in multitable INSERT statements.
Answer: BD
Rebirth 1z0-047 vs 1z0-051:
Q66. View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the
PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?
A. The NEW_IDRDERS table would not get created because the DEFAULT value can not be specified in the column definition.
B. The NEW_IDRDERS table would get created and only the NOTNULL constraint defined on the specified columns would be passed to the new table.
C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE commandand the SELECT clause do not match.
D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
Answer: B
Q67. Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. For each DML operation performed, the corresponding indexes are automatically updated.
C. Indexes should be created on columns that are frequently referenced as part of an expression.
D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
Answer: ABD
Q68. Evaluate the following CREATE TABLE command:
CREATE TABLE order_item (order_id NUMBER(3),
Item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id jtem_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id item_id)));
Which statement is true regarding the above SOL statement?
A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created.
B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey.
C. It wouldexecutesuccessfully and two indexes ORD_ITM_IDXand ORD_ITM_ID_PKwould becreated.
D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand.
Answer: A
Q69. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?
A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date)
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
B. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS'
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date
WITH CHECK OPTION;
Answer: B
Q70. Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pnameVARCHAR2(10));
INSERT INTO product VALUES(1, ‘pen’);
INSERT INTO product VALUES (2,'pencil);
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?
A. No SQL statementwouldberolledback.
B. Both the DELETE statements would be rolled back.
C. Only the second DELETE statementwould be rolledback.
D. Both theDELETE statementsand the UPDATE statement would berolledback.
Answer: A