Quiz Questions Over Oracle Software
-
Which prefixes are available to Oracle triggers?
-
: new only
-
:old only
-
Both :new and :old
-
Neither :new nor :old
-
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.
Quiz Preview
- 2.
Does the view exist if the table is dropped from the database?
-
True
-
False
Correct Answer
A. TrueExplanation
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 DatafilesExplanation
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 spaceExplanation
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. FalseExplanation
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. TrueExplanation
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 fileExplanation
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_VPExplanation
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 invokedExplanation
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. CreateExplanation
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 resultsExplanation
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. FalseExplanation
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 selectedExplanation
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.
- 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
-
1
-
Error
-
No privileges
-
A must grant some more privileges
Correct Answer
A. ErrorExplanation
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 aboveExplanation
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, 2023Quiz Edited by
ProProfs Editorial Team -
Nov 22, 2011Quiz Created by
Srividya A
Back to top