Q1. - (Topic 2) 

Which is an iSQL*Plus command? 

A. INSERT 

B. UPDATE 

C. SELECT 

D. DESCRIBE 

E. DELETE 

F. RENAME 

View Answer

Q2. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables. 

Evaluate the following SQL statement: Exhibit: 

Which statement is true regarding the execution of the above UPDATE statement? 

A. It would execute and restrict modifications to only the column specified in the SELECT statement 

B. It would not execute because two tables cannot be used in a single UPDATE statement 

C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement 

D. It would not execute because the SELECT statement cannot be used in place of the table name 

View Answer

Q3. - (Topic 1) 

See the Exhibit and examine the structure of ORD table: Exhibit: 

Evaluate the following SQL statements that are executed in a user session in the specified order: 

CREATE SEQUENCE ord_seq; 

SELECT ord_seq.nextval 

FROM dual; 

INSERT INTO ord 

VALUES (ord_seq.CURRVAL, ’25-jan-2007,101); 

UPDATE ord 

SET ord_no= ord_seq.NEXTVAL 

WHERE cust_id =101; 

What would be the outcome of the above statements? 

A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101. 

B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified. 

C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified. 

D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20. 

View Answer

Q4. - (Topic 2) 

You own a table called EMPLOYEES with this table structure: 

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement? 

DELETE employees; 

A. You get an error because of a primary key violation. 

B. The data and structure of the EMPLOYEES table are deleted. 

C. The data in the EMPLOYEES table is deleted but not the structure. 

D. You get an error because the statement is not syntactically correct. 

View Answer

Q5. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

Which INSERT statement is valid? 

A. 

INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’); 

B. 

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’); 

C. 

INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’)); 

D. 

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01); 

View Answer

Q6. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements: 

Which statement is true regarding the above two SQL statements? 

A. statement 1 gives an error, statement 2 executes successfully 

B. statement 2 gives an error, statement 1 executes successfully 

C. statement 1 and statement 2 execute successfully and give the same output 

D. statement 1 and statement 2 execute successfully and give a different output 

View Answer

Q7. - (Topic 2) 

EMPLOYEES and DEPARTMENTS data: EMPLOYEES 

DEPARTMENTS 

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. 

On the DEPARTMENTS table DEPARTMENT_ID is the primary key. 

Evaluate this UPDATE statement. 

UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary 

FROM employees 

WHERE emp_name = 'Smith') 

WHERE job_id = 'IT_ADMIN' 

What happens when the statement is executed? 

A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105. 

B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105. 

C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105. 

D. The statement fails because there is more than one row matching the employee name Smith. 

E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table. 

F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table. 

View Answer

Q8. - (Topic 1) 

Which two statements are true about sequences created in a single instance database? (Choose two.) 

A. CURRVAL is used to refer to the last sequence number that has been generated 

B. DELETE <sequencename> would remove a sequence from the database 

C. The numbers generated by a sequence can be used only for one table 

D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement 

E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted 

View Answer

Q9. - (Topic 2) 

You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table. 

Which set of statements accomplishes this? 

A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3 

B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3 

C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3 

D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3; 

E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar; 

View Answer

Q10. - (Topic 2) 

View the exhibit and examine the description for the SALES and CHANNELS tables. 

You issued the following SQL statement to insert a row in the SALES table: 

INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500); 

Which statement is true regarding the execution of the above statement? 

A. The statement will execute and the new row will be inserted in the SALES table. 

B. The statement will fail because subquery cannot be used in the VALUES clause. 

C. The statement will fail because the VALUES clause is not required with subquery. 

D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks. 

View Answer