[Apr 2021] 1z0-047 dumps free download pdf
Breathing of 1Z0-047 exam answers materials and resource for Oracle certification for consumer, 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:
Q65. View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?
A. UPDATE orders
SETorder_date = 12-mar-2007 order_total IS NULL WHERE order_id = 2455;
B. UPDATE orders
SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;
C. UPDATE orders
SET order_date = '12-mar-2007'
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
D. UPDATE orders
SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')lSET order_total =
TO_NUMBER(NULL) WHERE order id = 2455;
Q66. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECTfirst_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 query would execute to give the desired output.
B. The query would not execute because date functions cannot be nested.
C. The query would execute but the output would give review dates that are Sundays.
D. The query would not execute because the NEXT_DAY function accepts a string as argument.
Q67. The user SCOTT who is the owner of ORDERS and ORDERJTEMS tables issues the following GRANT command:
GRANT ALL ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?
A. PUBLICshould bereplacedwithspecific usernames.
B. ALL should be replaced with a list of specific privileges.
C. WITH GRANTOPTIONshould beaddedto the statement.
D. Separate GRANT statements are required for ORDERSandORDERJTEMS tables.
Q68. 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),
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
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.
Q69. View the Exhibit and examine the ORDERS table.
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?
A. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
B. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;
C. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
D. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
Refresh 1z0-047 oracle database sql expert dumps:
Q70. 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.
Q71. 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_ODBJECTS 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
Q72. View the Exhibit and examine the structure of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id
START WITH employee_id = 101
CONNECT BY PRIOR employee_id=manager_id;
Which statement is true regarding the output for this command?
A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.
B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
C. Itwouldreturn a hierarchical outputstarting withthe employeewhose EMPLOYEE_IDis101, followed by employeesbelowhim orherin thehierarchy.
D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
Q73. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. Thestatement would execute successfullytoproducetherequired output.
B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether.
C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query.
D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view.
Q74. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
High quality 1z0-047 oracle database sql expert exam dumps free:
Q75. 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;
Q76. View the Exhibit and examine the description of the ORDER_ITEMS table.
The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a UNIT_PRICE of more than 1,000 and has been ordered more than five times:
SELECT product_id, COUNT(order_id) total, unit_price FROM order_items WHERE unit_price>1000 AND COUNT(order_id)>5 GROUP BY product_id, unit_price;
Which statement is true regarding this SQL statement?
A. The statementwouldexecute and give youthedesired result.
B. Thestatementwould not execute becausetheaggregatefunction is usedintheWHEREclause.
C. The statementwouldnot execute because the WHERE clause should havetheOR logical operator insteadofAND.
D. The statement wouldnot execute because intheSELECT clause, the UNIT_PRICE column is placed afterthecolumn having the aggregate function.
Q77. Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
Which statement is true regarding the PROD_ID_PK constraint?
A. Itwould becreated only if a unique index is manually created first.
B. Itwould becreated andwould use an automatically created unique index.
C. It would be createdandwould use an automaticallycreatednonunique index.
D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command.
Q78. 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.
Q79. Evaluate the following expression using meta character for regular expression:
Which two matches would be returned by this expression? (Choose two.)
Q80. 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 #######.