Get real exam questions for 1z0-047 Oracle Database SQL Expert. 100% Free.
Q1. View the Exhibit and examine the structure of the EMP table. You executed the following command to add a primary key to the EMP table: ALTER TABLE emp ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) USING INDEX emp_id_idx; Which statement is true regarding the effect of the command? A. The PRIMARY KEY is created along with a new index. B. The PRIMARY KEY is created and it would use an ex
Q1. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.) A. The nested query executes after the outer query returns the row. B. The nested query executes first and then the outer query executes. C. The outer query executes only once for the result returned by the inner query. D. Each row returned by the outer query is evaluated for the results retu
Q1. Evaluate the following statement: INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders; Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? A.
Q1. View the Exhibit and examine the data in the PRODUCT INFORMATION table. Which two tasks would require subqueries? (Choose two.) A. displaying the minimum list price for each product status B. displaying all supplier IDs whose average list price is more than 500 C. displaying the number of products whose list prices are more than the average list price D. displaying all the products whose
Q1. Evaluate the following CREATE TABLE commands: CREATE TABLE orders (ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER(4)); CREATE TABLE ord_items (ord_no NUMBER(2), item_no NUMBER(3), qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), expiry_date date CHECK (expiry_date > SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), CONSTRAINT ord_fk FOREIGN KE
Q1. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them? SELECT location_id, city FROM locations SELECT location_id, city FROM locations JOIN departments USING(location_id); A. UNION B. MINUS C. INTERSECT D. UNION
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 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? (Ch
Q1. View the Exhibit and examine the details of the EMPLOYEES table. Evaluate the following SQL statements: Statement 1: SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ; Statement 2: SELECT employee_id, last_name, job_id, manager_id FROM employees WHERE manager_id != 108 START
Q1. 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 /
Q1. 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
Q1. Evaluate the following expression using meta character for regular expression: '[AAle|ax.r$]' Which two matches would be returned by this expression? (Choose two.) A. Alex B. Alax C. Alxer D. Alaxendar E. Alexender View AnswerAnswer: DEQ2. View the Exhibit and examine the structure of the CUSTOMERS table. CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same
Q1. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause? A. to find the groups forming the subtotal in a row B. to create group-wise grand totals for the groups specified within a GROUP BY clause C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtot
Q1. The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE; Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP; Which statement describes the outcome of the FLASHBACK command? A. It recovers only thetablestructure. B. It recovers thetablestructure,data,andtheindexes. C. I
Q1. View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables. You want to display all the cities that have no departments and the departments that have not been allocated cities. Which type of join between DEPARTMENTS and LOCATIONS tables would produce this information as part of its output? A. NATURAL JOIN B. FULL OUTERJOIN C. LEFT OUTERJOIN D. RIGHT OUTER JOINÂ
Q1. View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure. Choose the correct SET operator to fill in the blank space and complete the following query. SELECT employee_id FROM employees WHERE job_id = 'SA_MAN' SELECT employee_id F
Q1. Which two statements are true regarding the types of table joins available in Oracle Database 10g? (Choose two.) A. You can use the JOIN clause to join only two tables. B. You can explicitly provide the join condition with a NATURAL JOIN. C. You can use the USING clause to join tables on more than one column. D. You can use the ON clause to specify multiple conditions while joining tables
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. Evaluate the following statement: CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100); The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less
Q1. Which two statements are true about the GROUPING function? (Choose two.) A. Itis used to find the groups forming the subtotal in a row. B. It is used to identify the NULL value in the aggregate functions. C. It is used to form the group sets involved in generating the totals and subtotals. D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause. View Answer