Quiz Questions Over Oracle Software

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Srividya A
Srividya A, Software Engineer
Srividya A G is a software engineer who enjoying taking and creating quizzes on various topics.
Quizzes Created: 2 | Total Attempts: 51,563
| Attempts: 213
SettingsSettings
Please wait...
  • 1/20 Questions

    Which prefixes are available to Oracle triggers?

    • : new only
    • :old only
    • Both :new and :old
    • Neither :new nor :old
Please wait...
About This Quiz

This quiz focuses on Oracle software, assessing knowledge on database structures, integrity constraints, and PL\/SQL syntax. It is designed to validate understanding of Oracle database management, essential for IT professionals and students in database courses.

Oracle Quizzes & Trivia

Quiz Preview

  • 2. 

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

    • True

    • False

    Correct Answer
    A. True
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Adding one or more Datafiles
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Ocuupies data storage space
    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.

    Rate this question:

  • 5. 

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

    • True

    • False

    Correct Answer
    A. 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.

    Rate this question:

  • 6. 

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

    • True

    • False

    Correct Answer
    A. True
    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.

    Rate this question:

  • 7. 

    Databases overall structure is maintained in a file called

    • Redolog file

    • Data file

    • Control file

    • All of the above

    Correct Answer
    A. Control file
    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.

    Rate this question:

  • 8. 

    Which of the following is NOT VALID is PL/SQL

    • Bool boolean;

    • Deptname dept.dname%type;

    • NUM1, NUM2 number;

    • Date1 date := sysdate ;

    Correct Answer
    A. NUM1, NUM2 number;
    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.

    Rate this question:

  • 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_VP King 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_VP King 100 116 AD_PRES Kochhar 101 100 AD_VP

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

    Correct Answer
    A. ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- King 100 116 AD_PRES Kochhar 101 100 AD_VP De Haan 102 100 AD_VP
    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.

    Rate this question:

  • 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]]

    Correct Answer
    A. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias)[,alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Insert into ddd d values(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));
    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.

    Rate this question:

  • 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

    Correct Answer
    A. 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
    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.

    Rate this question:

  • 13. 

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

    • Select ? into

    • Update

    • Create

    • Delete

    Correct Answer
    A. Create
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Cursor holds temporary results
    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.

    Rate this question:

  • 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

    Correct Answer
    A. This would create a new table called companies that included 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.

    Rate this question:

  • 16. 

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

    • True

    • False

    Correct Answer
    A. 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.

    Rate this question:

  • 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_NAME 10567

    • SUPPLIER_ID SUPPLIER_NAME 10565 10567

    • No rows selected

    • Error

    Correct Answer
    A. No rows selected
    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.

    Rate this question:

  • 18. 

    1. 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

    Correct Answer
    A. Error
    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.

    Rate this question:

  • 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

    Correct Answer
    A. 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;
    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.

    Rate this question:

  • 20. 

    What is a Built_in subprogram ?

    • Library

    • Stored procedure & Function

    • Collection of Subprograms

    • None of the above

    Correct Answer
    A. None of the above
    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.

    Rate this question:

Quiz Review Timeline (Updated): Mar 17, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 17, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Nov 22, 2011
    Quiz Created by
    Srividya A
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.