Q1. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS tables. Evaluate the following MERGE statement: MERGE INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total); What would be the outcome of the above statement? A. The ORDERS_MASTER table would contain the ORDERJDs1and 2. B. TheORDERS_MASTERtablewould containtheORDERJDs…
Q1. View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables. You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five. Which two SQL statements can accomplish the task? (Choose two.) A. SELECT product_id, quantity_on_hand , supplier_id FROM product_information NATURAL JOIN inventories AND…
Q1. View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables. You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department. Which SOL statement would you execute to accomplish the task? A. UPDATE deptd SETcity=ANY(SELECT city FROMlocations I); B. UPDATE deptd SET city=(SELECT city FROM…
Q1. View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement: SELECT category_name ,category_description FROM categories_tab You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column. Which two WHERE clause options can give you the desired result? (Choose two.) A. WHEREREGEXPJJKE(category_description, 'hard+.s’); B. WHERE REGEXPJJKE(category_description,‘^H|hard+.s’); C. WHERE REGEXPJJKE (category_description, '^H|hard+.s$'); D.…
Q1. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table? A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234; B. DELETE FROM empdet WHERE ename LIKE 'J%' C. CREATE VIEWempvu AS SELECT* FROMempdept; D. CREATEINDEX empdet_dx ON empdet(empno); View AnswerAnswer: CQ2. Which three statements are true regarding group functions? (Choose three.) A. They…
Q1. Given below is a list of functions and their purpose in random order. Function Purpose 1)NVL a) Used for evaluating NOT NULL and NULL values 2)NULLIF b) Used to return the first non- null values in a list of expressions 3)COALESCE c) Used to compare two expressions. If both are same, it returns NULL; otherwise, it returns only the first expression. 4)NVL2…
Q1. 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…
Q1. Evaluate the following CREATE SEQUENCE statement: CREATE SEQUENCE seql START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement: SELECT seql.nextval FROM dual; What is displayed by the SELECT statement? A. 1 B. 10 C. 100 D. an error View AnswerAnswer: AQ2. The first DROP operation is performed on…
Q1. Given below is the list of meta character syntaxes and their descriptions in random order: Meta character syntax Description 1)^ a) Matches character not in the list 2) [^…] b) Matches character when it occurs at the beginning of a line 3) | c) Treats the subsequent meta character as a literal 4) \ d) Matches one of the…
Q1. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Evaluate the following UPDATE statement: UPDATE (SELECT order_date, order_total, customer_id FROM orders) SET order_date = 22-mar-2007' WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts'AND Credit_limit = 600); Which statement is true regarding the execution of the above UPDATE statement? A. Itwouldnot execute becausetwotables cannot be usedin a single UPDATEstatement. B. It wouldexecuteandrestrict modifications…
Q1. 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,…
Q1. View the Exhibit and examine the data in the DEPARTMENTS tables. Evaluate the following SQL statement: SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department__id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.) A. ORDERBY 3; B. ORDER BY'b' C. ORDER BYDEPT_ID; D. ORDER BY…
Q1. 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…
Q1. View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries? A. displaying the total order value for sales representatives 161 and 163 B. displaying the order total for sales representative 161 in the year 1999 C. displaying the number of orders that have order mode online and order date in 1999 D. displaying the number of orders…
Q1. View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables. You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department. Which SOL statement would you execute to accomplish the task? A. UPDATE deptd SETcity=ANY(SELECT city FROMlocations I); B. UPDATE deptd SET city=(SELECT city FROM…
Q1. View the Exhibit and examine the data in the LOCATIONS table. Evaluate the following SOL statement: SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1; Which statement is true regarding the output of this SOL statement? A. It would displayallthe street addresses thatdo nothaveasubstring 'alpha'. B. It would displayallthestreetaddresseswhere the first character isaspecial character. C. It would display allthe streetaddresses wherethefirst character is aletterofthealphabet. D. It would displayall thestreet…
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…
Q1. View the Exhibit and examine the data in EMP and DEPT tables. In the DEPT table, DEPTNO is the PRIMARY KEY. In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table. What would be the outcome of the following statements executed in the given sequence? DROP TABLE emp; FLASHBACK TABLE emp TO…
Q1. View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables. Evaluate the following SOL statement: SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id I. location_id); This statement was written to display LOCATIONJD and CITY where there are no departments located. Which statement is true regarding the execution and output of the command? A. The statement would execute and would return…
Q1. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. Which DELETE statement would execute successfully? A. DELETEorder_id FROMorders WHEREorder_total< 1000; B. DELETEorders WHERE order_total < 1000; C. DELETE FROM orders WHERE (SELECTorder_id FROM order_items); D. DELETE orders o, order_itemsi WHEREo.order…
Q1. View the Exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables. To retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written: SELECT employee_id, first_name, department_name FROM employees NATURAL JOIN departments; The desired output is not obtained after executing the above SQL statement. What could be the reason for this? A. The NATURAL…
Q1. View the Exhibit and examine the data in EMPLOYEES and DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY. Evaluate the following UPDATE statement: UPDATE employees a SET department_jd = (SELECT department_id FROM departments WHERE location_id = ‘2100’), (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b WHERE a. department_jd = b. department_id) WHERE…
Q1. 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…
Q1. View the Exhibit and examine the data in EMP and DEPT tables. In the DEPT table, DEPTNO is the PRIMARY KEY. In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table. What would be the outcome of the following statements executed in the given sequence? DROP TABLE emp; FLASHBACK TABLE emp TO…
Q1. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data…
Q1. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.) A. it can be used to concatenate two strings. B. it can be used to find out the total length of the string. C. it can be used for string manipulation and searching operations. D. it can be used to format the output for a column or…
