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 Jun 1z0-047 Study Guide Questions:
Q51. A non-correlated subquery can be defined as________ .
A. a set of sequential queries, all of which must always return a single value
B. a set of sequential queries, all of which must return values from the same table
C. a SELECT statement that can be embedded in a clause of another SELECT statement only
D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query
Answer: D
Q52. Which view would you use to display the column names and DEFAULT values for a table?
A. DBA_TABLES
B. DBA_COLUMNS
C. USER_COLUMNS
D. USER TAB COLUMNS
Answer: D
Q53. Which statements are true? (Choose all that apply.)
A. The data dictionary is created and maintained by the database administrator.
B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
C. The usernames of all the users including the database administrators are stored in the data dictionary.
D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
Answer: CDF
Down to date 1z0-047 latest dumps free download:
Q54. 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
Q55. View the Exhibit and examine the description of the ORDERS table.
Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?
A. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id;
B. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders GROUP BY sales_rep_id, total_orders;
C. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders;
D. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders WHERE sales_rep_id IS NOT NULL;
Answer: A
Q56. 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 item_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 indexesORD_ITM_IDXand ORD_ITM_ID_PKwould becreated.
D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand.
Answer: A
Tested 1z0-047 oracle database sql expert pdf:
Q57. Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP; Which statement is true regarding the above FLASHBACK operation?
A. It recovers only the firstDEPTtable.
B. Itrecovers onlythesecondDEPTtable.
C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case.
D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN.
Answer: B
Q58. 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
Q59. Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
A. The HAVING clause conditions can have aggregate functions.
B. The HAVING clause conditions can use aliases for the columns.
C. WHERE and HAVING clauses cannot be used together in a SQL statement.
D. The WHERE clause is used to exclude rows before the grouping of data.
E. The HAVING clause is used to exclude one or more aggregated results after grouping data.
Answer: ADE
Q60. 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