Oracle Multiple Choice Questions

All parts of a transaction should complete or none of them. Which property of ACID rule complies with the given statement? A. Atomicity B. Consistency C. Isolation D. Durability

A. Atomicity

What is true about the above INSERT statement? A. If the columns are not mentioned in the INSERT statement, the values are inserted positionally in the columns B. It is mandatory to mention columns after the INSERT statement C. Both A and B D. None of the above

A. If the columns are not mentioned in the INSERT statement, the values are inserted positionally in the columns

An incomplete transaction should be invisible to all the other users. Which of the properties of the ACID state this? A. Isolation B. Consistency C. Atomicity D. Durability

A. Isolation

What does the principle of consistency states? A. It states that the results of a query must be consistent with the state of the DB at the time the query started B. It states that an incomplete transaction should be invisible to all the other users C. It states that once a transaction completes, it must be impossible for the DB to lose it D. None of the above

A. It states that the results of a query must be consistent with the state of the DB at the time the query started

Suppose the above given statement is modified as below: INSERT INTO departments VALUES (100, 'Human Resources', 121, 1000); What will be the outcome of this modification? Assume that the DEPARTMENTS table has four columns namely, department_id , DEPARTMENT_NAME ,MANAGER_ID and LOCATION_ID . A. It will insert values into all the columns of the departments table assuming that column values are provided in the same sequence as the column in the table B. It will throw an ORA error because column names are not explicitly mentioned C. It will throw an ORA error because VALUES clause is wrongly used in the INSERT

A. It will insert values into all the columns of the departments table assuming that column values are provided in the same sequence as the column in the table B. It will th

Which of the following commands allows undoing the changed data? A. ROLLBACK B. COMMIT C. INSERT D. UPDATE

A. ROLLBACK

You need to change the JOB_ID for Bruce (Employee Id 7389) to 'ACCOUNTANT'. Which of the following statements would you use? A. UPDATE employees SET job_id = 'ACCOUNTANT' WHERE employee_id = 7389; B. UPDATE employees SET job_id = 'ACCOUNTANT' WHERE job_id = 'CLERK'; C. UPDATE employees SET job_id = 'ACCOUNTANT';

A. UPDATE employees SET job_id = 'ACCOUNTANT' WHERE employee_id = 7389;

The SQL ALTER statement can be used to: A. change the table structure. B. change the table data. C. add rows to the table. D. delete rows from the table.

A. change the table structure.

INSERT INTO departments (department_id , department_name , manager_id, location_id ) VALUES (100, 'Human Resources', 121, 1000); How many rows will be inserted by the above statement? A. 0 B. 1 C. 2 D. 3

B. 1

Which of the following statements is true about the DELETE statement? A. A DELETE statement must include the WHERE clause B. It is not mandatory or required to write a WHERE clause in a DELETE statement C. DELETE can remove data from multiple tables at a time

B. It is not mandatory or required to write a WHERE clause in a DELETE statement

You execute the following query: DELETE FROM EMPLOYEES; Assuming that there are no active transactions on the EMPLOYEES table in any sessions, which of the following statements is true? A. It removes all the rows and structure of the table B. It removes all the rows which can be rolled back C. It removes all the rows permanently D. None of the above

B. It removes all the rows which can be rolled back

In which order the values will get inserted with respect to the above INSERT statement? A. Location_id , manager_id, department_name , department_id B. department_id , department_name , manager_id, location_id C. department_id , manager_id, department_name , location_id D. department_id , department_name , location_id , manager_id

B. department_id , department_name , manager_id, location_id

Which of the following statements best defines a transaction? A. A transaction consists of DDL statements executed on the database schema B. A transaction consists of executing COMMIT or ROLLBACK in a database session C. A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session D. A transaction consists of collection of DML and DDL statements in different sessions of the database

C. A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session

What does the principle of Durability in the ACID property state? A. It states that a database can lose completed transactions B. It states that a transaction cannot get completed C. It states that once a transaction completes, it must be impossible for the DB to lose it. D. None of the above

C. It states that once a transaction completes, it must be impossible for the DB to lose it.

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) INSERT INTO departments values (15, NULL); What will be the outcome of this INSERT INTO statement? A. It will insert a row with department_id = 15 and all the other values as NULL B. It will execute successfully but insert 0 rows in the table C. It will throw an Oracle (ORA) error as the no. of columns and values do not match D. None of the above

C. It will throw an Oracle (ORA) error as the no. of columns and values do not match

A user named 'Jonathan Adams' is able to SELECT columns from the EMPLOYEES table, but he is unable to insert records into EMPLOYEES. What can be the reason? A. Jonathan is connected to a database which does not support INSERT statements B. The INSERT statement cannot be applied on the table EMPLOYEES C. Jonathan has the privilege to execute SELECT but no access to INSERT INTO the table EMPLOYEES D. None of the above

C. Jonathan has the privilege to execute SELECT but no access to INSERT INTO the table EMPLOYEES

What happens a DELETE statement is executed for a table? (Choose the most appropriate answer) A. A prompt pops up asking the user whether he/she is sure of deleting the rows requested B. The rows matching the condition given in the DELETE statement are removed immediately C. The requested rows are removed immediately without any prompt.

C. The requested rows are removed immediately without any prompt.

Suppose 1 million rows are to be inserted into the AUDIT table. An INSERT transaction runs successfully for the first 1000 rows and an ORA error is thrown 'Constraint violated'. What will happen to the values inserted in the first 1000 rows? A. They will remain as it is B. They all will get deleted C. They all will get rolled back D. None of the above

C. They all will get rolled back

EMPLOYEE_ID FIRST_NAME JOB_ID ------------------- ------------------ -------- 5100 BRUCE 5101 JESSICA SALESMAN 5102 DEBBY SALESMAN How can you delete the data from the JOB_ID column in the row with employee_id 5101. Which of the following queries would be correct? A. UPDATE employees SET job_id = NULL WHERE employee_id = 5101; B. DELETE job_id FROM employees WHERE employee_id = 5101; C. DELETE FROM employees; D. None of the above

D. None of the above

The Oracle environment of database is called as ___________ a) Database Schema b) Database Instances c) Data Structure d) All of the Mentioned

b) Database Instances