Oracle 1z0-047 Oracle Database SQL Expert
2020 Oracle Official New Released 1z0-047 Q&As
100% Free Download! 100% Pass Guaranteed!
Q1. 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 creat
Q1. View the Exhibit and examine the structure of the PRODUCT INFORMATION table. Which two queries would work? (Choose two.) A. SELECT product_name FROM product_information WHERE list_price = (SELECT AVG(list_price) FROM product_information); B. SELECT product_status FROM product_information GROUP BY product_status WHERE list_price < (SELECT AVG(list_price) FROM product_information);
Q1. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.) A. It is possible to retrieve data only in top-down hierarchy. B. Itis possible to retrieve data in top-down or bottom-up hierarchy. C. It is possible to remove an entire branch from the output of the hierarchical query. D. You cannot specify conditions when you retrieve data by usi
Q1. Evaluate the following SQL statement: SELECT product_name || 'it's not available for order' FROM product_information WHERE product_status = 'obsolete' You received the following error while executing the above query: ERROR: ORA-01756: quoted string not properly terminated What would you do to execute the query successfully? A. Enclose the character literal string i
Q1. View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement: SELECT employee_id, first_name, department_id FROM employees WHER
Q1. View the Exhibit and examine the description of the ORDERS table. Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.) A. WHERE order_date> TO_DATE('JUL 10 2006','MON DD YYYY) B. WHERE TO_CHAR(order_date,'MONDDYYYY) = 'JAN 20 2003' C. WHEREorder_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY") D. WHERE o
Q1. View the Exhibit and examine the structure of the EMPLOYEES table. You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. Which SQL statement would you execute? A. SELECT m.last_name "Manager", e.last_name "Em
Q1. Which SQL statement would display the view names and definitions of all the views owned by you? A. SELECTviewjiame, text FROM user_view; B. SELECTviewjiame, text FROM user_object; C. SELECTviewjiame, text FROM user_objects; D. SELECTview_name, text FROM user views; View AnswerAnswer: DQ2. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement? A.
Q1. 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 ,T
Q1. A subquery is called a single-row subquery when A. the inner query returns a single value to the main query B. the inner query uses an aggregate function and returns one or more values C. there is only one inner query in the main query and the inner query returns one or more values D. the inner query returns one or more values and the main query returns a single value as output View Answ
Q1. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL? A. SELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL; B. SELECT COUNT(list_price) FROM product_information WHERE list_price = NULL; C. SELECT COUNT(NVL(list_price, 0)) FROM product_i
Q1. 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 FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id; What would happen when the above stat
Q1. 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