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, andTWO.
Answer: C
Q2. The following are the steps for a correlated subquery, listed in random order: 1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
A. 4,2,1,3
B. 4,1,2,3
C. 2,4,1,3
D. 2,1,4,3
Answer: C
Q3. 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 alongwitha new index.
B. The PRIMARY KEY is created anditwould use an existinguniqueindex.
C. The PRIMARY KEYwould becreated in a disabled state because it is usinganexisting index.
D. The statementproduces an error because the USING clause is permitted only intheCREATE TABLE command.
Answer: B
Q4. 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 DEPTNAME;
Answer: AC
Q5. Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
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.
Answer: B
Q6. Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. For each DML operation performed, the corresponding indexes are automatically updated.
C. Indexes should be created on columns that are frequently referenced as part of an expression.
D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
Answer: ABD
Q7. 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);
C. SELECT product_status
FROM product_information
GROUP BY product_status
HAVING list_price > (SELECT AVG(list_price)
FROM product_information);
D. SELECT product_name FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price) FROM product_jnformation GROUP BY product_status);
Answer: AD
Q8. View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?
A. ALTER TABLE products
ADD price NUMBER(8,2) NOT NULL;
B. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT NOT NULL;
C. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
D. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
Answer: C
Q9. View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables.
The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.
Which two SQL statements can you execute to accomplish the task? (Choose two.)
A. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id FROM employees e WHERE j.job_id = e.job_id);
B. DELETE FROM job_history j WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
C. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
D. DELETE FROM job_history j WHERE (employee_id, job_id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
Answer: CD
Q10. View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?
A. SELECT product_id, SYSDATE + warranty_period FROM product_information;
B. SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information;
C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;
D. SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;
Answer: A