Quiz Questions Over Oracle Software

1. Which prefixes are available to Oracle triggers?

: new only

:old only

Both :new and :old

Neither :new nor :old

Both :new and :old are available prefixes in Oracle triggers. The ":new" prefix refers to the new values of the row being affected by the trigger, while the ":old" prefix refers to the old values of the row before the trigger is executed. These prefixes allow the trigger code to access and manipulate both the old and new values of the affected row, providing flexibility and control in trigger execution.

Explanation

Both :new and :old are available prefixes in Oracle triggers. The ":new" prefix refers to the new values of the row being affected by the trigger, while the ":old" prefix refers to the old values of the row before the trigger is executed. These prefixes allow the trigger code to access and manipulate both the old and new values of the affected row, providing flexibility and control in trigger execution.

2. Does the view exist if the table is dropped from the database?

True

False

When a table is dropped from the database, all the data and metadata associated with that table is permanently removed. Therefore, the view that was created based on that table will no longer exist because it is dependent on the underlying table.

Explanation

When a table is dropped from the database, all the data and metadata associated with that table is permanently removed. Therefore, the view that was created based on that table will no longer exist because it is dependent on the underlying table.

3. Size of Tablespace can be increased by

Increasing the size of one of the Datafiles

Adding one or more Datafiles

Cannot be increased

None of the above

The size of a tablespace can be increased by adding one or more datafiles. Datafiles are physical files that store the actual data of the database objects. By adding more datafiles to a tablespace, more storage space is allocated, allowing for an increase in the size of the tablespace. Increasing the size of one datafile alone may not be sufficient, so adding multiple datafiles provides a more effective solution for increasing the size of the tablespace.

Explanation

The size of a tablespace can be increased by adding one or more datafiles. Datafiles are physical files that store the actual data of the database objects. By adding more datafiles to a tablespace, more storage space is allocated, allowing for an increase in the size of the tablespace. Increasing the size of one datafile alone may not be sufficient, so adding multiple datafiles provides a more effective solution for increasing the size of the tablespace.

4. Which of the following is not correct about a View ?

Ocuupies data storage space

To protect some of the columns of a table from other users

To hide complexity of a query

To hide complexity of a calculations

A view is a virtual table that does not occupy any data storage space. It is created based on a query and does not store any data itself. Therefore, the statement "Occupies data storage space" is not correct about a view.

Explanation

A view is a virtual table that does not occupy any data storage space. It is created based on a query and does not store any data itself. Therefore, the statement "Occupies data storage space" is not correct about a view.

5. It is very difficult to grant and manage common privileges needed by different groups of database users using the roles

True

False

Granting and managing common privileges needed by different groups of database users using roles is not difficult. In fact, roles are specifically designed to simplify this process. Roles allow for the creation of predefined sets of privileges that can be assigned to multiple users, making it easier to grant and manage privileges for different groups. Therefore, the correct answer is false.

Explanation

Granting and managing common privileges needed by different groups of database users using roles is not difficult. In fact, roles are specifically designed to simplify this process. Roles allow for the creation of predefined sets of privileges that can be assigned to multiple users, making it easier to grant and manage privileges for different groups. Therefore, the correct answer is false.

6. Is it not possible to use Transactional control statements in Database Triggers?

True

False

Transactional control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, cannot be used within database triggers. This is because triggers are automatically executed as part of a transaction, and using these control statements within a trigger can lead to conflicts and inconsistencies in the transaction. Triggers are designed to automatically perform actions based on specified conditions, and they do not allow explicit control over the transaction. Therefore, it is not possible to use transactional control statements in database triggers.

Explanation

Transactional control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, cannot be used within database triggers. This is because triggers are automatically executed as part of a transaction, and using these control statements within a trigger can lead to conflicts and inconsistencies in the transaction. Triggers are designed to automatically perform actions based on specified conditions, and they do not allow explicit control over the transaction. Therefore, it is not possible to use transactional control statements in database triggers.

7. Databases overall structure is maintained in a file called

Redolog file

Data file

Control file

All of the above

The correct answer is "Control file." The control file is a crucial component of a database as it contains essential information about the overall structure and status of the database. It maintains records of the physical structure of the database, including the names and locations of data files, redo log files, and other important database components. It is used by the database management system to ensure data integrity and consistency during database operations.

Explanation

The correct answer is "Control file." The control file is a crucial component of a database as it contains essential information about the overall structure and status of the database. It maintains records of the physical structure of the database, including the names and locations of data files, redo log files, and other important database components. It is used by the database management system to ensure data integrity and consistency during database operations.

8. Which of the following is NOT VALID is PL/SQL

Bool boolean;

Deptname dept.dname%type;

NUM1, NUM2 number;

Date1 date := sysdate ;

The statement "NUM1, NUM2 number;" is not valid in PL/SQL. In PL/SQL, the correct syntax for declaring variables of type number is "NUM1 number; NUM2 number;". The data type should be mentioned after each variable name.

Explanation

The statement "NUM1, NUM2 number;" is not valid in PL/SQL. In PL/SQL, the correct syntax for declaring variables of type number is "NUM1 number; NUM2 number;". The data type should be mentioned after each variable name.

9. The table is ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ----------

    Kochhar                101        100      AD_VP

    Faviet                 109        108      FI_ACCOUNT

    Chen                    110        108     FI_ACCOUNT

    Sciarra                  111        108     FI_ACCOUNT

    Urman                  112        108       FI_ACCOUNT

    Popp                     113       108      FI_ACCOUNT

  Whalen                   200        101        AD_ASST

  King                       100       116       AD_PRES

  Mavris                    203        101       HR_REP

  Baer                       204       101        PR_REP

  Higgins                  205        101         AC_MGR

    Gietz                   206      205         AC_ACCOUNT

  De Haan                 102        100         AD_VP

  Hunold                   103        102        IT_PROG

The query is

SELECT LPAD(' ',2*(LEVEL-1)) ||  org_chart,

employee_id, manager_id, job_id

    FROM employees

    START WITH job_id = 'AD_PRES'

    CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2;

What is the output?

ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID------------------ ----------- ---------- ----------Kochhar 101 100 AD_VPKing 100 116 AD_PRES De Haan 102 100 AD_VP

ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID------------------ ----------- ---------- ---------- King 100 116 AD_PRES Kochhar 101 100 AD_VP De Haan 102 100 AD_VP

ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID------------------ ----------- ---------- ----------De Haan 102 100 AD_VPKing 100 116 AD_PRESKochhar 101 100 AD_VP

ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID------------------ ----------- ---------- ----------Kochhar 101 100 AD_VPDe Haan 102 100 AD_VPKing 100 116 AD_PRES

The output of the query is the second option: ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID
------------------ ----------- ---------- ----------
King 100 116 AD_PRES
Kochhar 101 100 AD_VP
De Haan 102 100 AD_VP

This is because the query uses the START WITH clause to start the hierarchy with the employee whose job_id is 'AD_PRES'. It then uses the CONNECT BY PRIOR clause to traverse the hierarchy based on the relationship between employee_id and manager_id. The LEVEL condition is used to limit the depth of the hierarchy to 2 levels. The LPAD function is used to add spaces before the org_chart column to create a visual representation of the hierarchy. Therefore, the output shows the org chart with the employee_id, manager_id, and job_id for each employee.

Explanation

The output of the query is the second option: ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID
------------------ ----------- ---------- ----------
King 100 116 AD_PRES
Kochhar 101 100 AD_VP
De Haan 102 100 AD_VP

This is because the query uses the START WITH clause to start the hierarchy with the employee whose job_id is 'AD_PRES'. It then uses the CONNECT BY PRIOR clause to traverse the hierarchy based on the relationship between employee_id and manager_id. The LEVEL condition is used to limit the depth of the hierarchy to 2 levels. The LPAD function is used to add spaces before the org_chart column to create a visual representation of the hierarchy. Therefore, the output shows the org chart with the employee_id, manager_id, and job_id for each employee.

10.

The syntax for creating a view is

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias)[,alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW[(alias)[,alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias)[,alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW[(alias)[,alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]

The correct answer is the first option: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias)[,alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]. This option provides the correct syntax for creating a view in a SQL query. It includes the necessary keywords and syntax elements such as "CREATE", "VIEW", "AS", and "WITH CHECK OPTION" to define the view and its properties. The other options either have missing or incorrect syntax elements, making them incorrect choices.

Explanation

The correct answer is the first option: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias)[,alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]. This option provides the correct syntax for creating a view in a SQL query. It includes the necessary keywords and syntax elements such as "CREATE", "VIEW", "AS", and "WITH CHECK OPTION" to define the view and its properties. The other options either have missing or incorrect syntax elements, making them incorrect choices.

11.

There is a table ddd(d date). Which of the given statement is correct?

Insert into ddd values(to_date('2003/05/03 21:02:44'));

Insert into ddd d values(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

Insert into ddd values(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh:mi:ss'));

None is correct

The correct answer is "insert into ddd d values(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));". This is because the format specifier 'hh24' is used to represent the hour in 24-hour format, which matches the given time value of '21'. The other options either do not include the 'hh24' format specifier or use the incorrect format specifier 'hh' for representing the hour.

Explanation

The correct answer is "insert into ddd d values(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));". This is because the format specifier 'hh24' is used to represent the hour in 24-hour format, which matches the given time value of '21'. The other options either do not include the 'hh24' format specifier or use the incorrect format specifier 'hh' for representing the hour.

12. Why is it better to use an INTEGRITY CONSTRAINT to validate data in a table than to use a STORED PROCEDURE ?

Because an integrity constraint is automatically checked while data is inserted into or updated in a table while a stored procedure has to be specifically invoked

Because the stored procedure occupies more space in the database than a integrity constraint definition

Because a stored procedure creates more network traffic than a integrity constraint definition

All

An integrity constraint is automatically checked while data is inserted into or updated in a table, ensuring that the data meets certain criteria. On the other hand, a stored procedure needs to be specifically invoked in order to validate the data. This means that with an integrity constraint, the validation process is more efficient and seamless as it happens automatically without the need for manual intervention. Additionally, using a stored procedure for data validation may occupy more space in the database and create more network traffic compared to an integrity constraint.

Explanation

An integrity constraint is automatically checked while data is inserted into or updated in a table, ensuring that the data meets certain criteria. On the other hand, a stored procedure needs to be specifically invoked in order to validate the data. This means that with an integrity constraint, the validation process is more efficient and seamless as it happens automatically without the need for manual intervention. Additionally, using a stored procedure for data validation may occupy more space in the database and create more network traffic compared to an integrity constraint.

13. Which of the following is NOT VALID in PL/SQL ?

Select ? into

Update

Create

Delete

The CREATE statement is not valid in PL/SQL. The CREATE statement is used to create database objects such as tables, views, and indexes. In PL/SQL, the focus is on manipulating data and performing operations on existing database objects rather than creating new ones. Therefore, the CREATE statement is not valid in PL/SQL.

Explanation

The CREATE statement is not valid in PL/SQL. The CREATE statement is used to create database objects such as tables, views, and indexes. In PL/SQL, the focus is on manipulating data and performing operations on existing database objects rather than creating new ones. Therefore, the CREATE statement is not valid in PL/SQL.

14. Which of the following is not correct about Cursor ?

Cursor is a named Private SQL area

Cursor is used for retrieving multiple rows

SQL uses implicit Cursors to retrieve rows

Cursor holds temporary results

The statement "Cursor holds temporary results" is not correct because a cursor does not hold temporary results. A cursor is a database object that allows for the retrieval and manipulation of data from a result set. It is used to retrieve multiple rows from a query result and allows for efficient processing of large amounts of data. However, the cursor itself does not hold any data, but rather provides a way to iterate through the result set and access the data.

Explanation

The statement "Cursor holds temporary results" is not correct because a cursor does not hold temporary results. A cursor is a database object that allows for the retrieval and manipulation of data from a result set. It is used to retrieve multiple rows from a query result and allows for efficient processing of large amounts of data. However, the cursor itself does not hold any data, but rather provides a way to iterate through the result set and access the data.

15. What will the below statement do? CREATE TABLE companies AS (SELECT * FROM suppliers WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

This would create a new table called companies that included all columns from the suppliers table, with data from the suppliers table.

This would create a new table called companies that included all columns from the suppliers table, but no data from the suppliers table.

Error

The given statement "CREATE TABLE companies AS (SELECT * FROM suppliers WHERE 1=2);" would create a new table called companies. It would include all columns from the suppliers table, but no data from the suppliers table.

Explanation

The given statement "CREATE TABLE companies AS (SELECT * FROM suppliers WHERE 1=2);" would create a new table called companies. It would include all columns from the suppliers table, but no data from the suppliers table.

16. All datafiles related to a Tablespace are removed when the Tablespace is dropped

True

False

When a tablespace is dropped, only the metadata related to the tablespace is removed from the database. The actual data files associated with the tablespace are not automatically deleted. This allows for the possibility of recovering the data files in case they were dropped accidentally. Therefore, the correct answer is false.

Explanation

When a tablespace is dropped, only the metadata related to the tablespace is removed from the database. The actual data files associated with the tablespace are not automatically deleted. This allows for the possibility of recovering the data files in case they were dropped accidentally. Therefore, the correct answer is false.

17. Consider that the following statements are executed. create table suppliers( supplier_id number, supplier_name varchar2(100)); insert into suppliers (supplier_id, supplier_name ) values ( 10565, null ); insert into suppliers (supplier_id, supplier_name ) values ( 10567, '' ); Then for the below query what will be the output? select * from suppliers where supplier_name = ' ';

SUPPLIER_ID SUPPLIER_NAME10567

SUPPLIER_ID SUPPLIER_NAME1056510567

No rows selected

Error

The query "select * from suppliers where supplier_name = ' ';" will result in "no rows selected" as the condition in the WHERE clause is looking for rows where the supplier_name is an empty string. However, in the given table, there is no row where the supplier_name is an empty string, so no rows will be returned as a result.

Explanation

The query "select * from suppliers where supplier_name = ' ';" will result in "no rows selected" as the condition in the WHERE clause is looking for rows where the supplier_name is an empty string. However, in the given table, there is no row where the supplier_name is an empty string, so no rows will be returned as a result.

18.

There are two schema users A & B User A grants select privilege to user B on tables User A grants execute privilege to user B on packages Now, on executing package from user B, Execute below code from User B to retrieve the record from the table of

user A Declare v_sid tab_user_A.column1%TYPE; v_pid tab_user_A.column2%TYPE; v_value tab_user_A.column3%TYPE; v_pname tab_user_A.column4%TYPE; v_cur package_user_A.Cursor1; Begin package_user_A.procedure_one(0,0,1,1,v_cur); dbms_output.put_line(v_value); End; Then output is

1

Error

No privileges

A must grant some more privileges

The code is attempting to retrieve a record from a table owned by user A using a package owned by user A. However, user B only has execute privilege on the package and does not have select privilege on the table. Therefore, when executing the code, user B will encounter an error indicating that they do not have the necessary privileges to access the table.

Explanation

The code is attempting to retrieve a record from a table owned by user A using a package owned by user A. However, user B only has execute privilege on the package and does not have select privilege on the table. Therefore, when executing the code, user B will encounter an error indicating that they do not have the necessary privileges to access the table.

19. For insertion of multiple rows into multiple tables which of the following is correct?

INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM') INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft') INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York');

INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM') INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft') INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')SELECT * FROM dual;

Both

None

The correct answer is the second option: INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This is the correct syntax for inserting multiple rows into multiple tables using the INSERT ALL statement. It allows you to specify multiple INTO clauses to insert data into different tables, and the SELECT * FROM dual statement at the end is used to execute the insert statement.

Explanation

The correct answer is the second option: INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This is the correct syntax for inserting multiple rows into multiple tables using the INSERT ALL statement. It allows you to specify multiple INTO clauses to insert data into different tables, and the SELECT * FROM dual statement at the end is used to execute the insert statement.

20. What is a Built_in subprogram ?

Library

Stored procedure & Function

Collection of Subprograms

None of the above

A built-in subprogram refers to a predefined subprogram that is provided by the programming language or system. These subprograms are already available and can be directly used without the need for any additional coding or implementation. They are typically included in libraries or packages provided by the programming language or system. Therefore, the correct answer is "None of the above" as the options provided do not accurately describe what a built-in subprogram is.

Explanation

A built-in subprogram refers to a predefined subprogram that is provided by the programming language or system. These subprograms are already available and can be directly used without the need for any additional coding or implementation. They are typically included in libraries or packages provided by the programming language or system. Therefore, the correct answer is "None of the above" as the options provided do not accurately describe what a built-in subprogram is.