Computer Knowledge

Database and SQL

4,213 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice

Which of the following statements is valid regarding index clusters?

  1. Index clusters can only be used for tables with low cardinality columns.

  2. Index clusters are generally well suited for tables that have many full table scans.

  3. Normal B-Tree indexes do not store null key values, whereas cluster indexes store null keys.

  4. A cluster index always takes up much more storage space than a normal index for the same set of key values.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle databases, standard B-Tree indexes exclude rows with NULL key values to save space, while cluster indexes include NULL entries in the index structure. This distinction is important because cluster indexes maintain the cluster key relationship even when values are null. Options A and B are incorrect because index clusters are typically used for high-cardinality columns and are not optimized for full table scans. Option D is incorrect because storage overhead depends on clustering factor, not the index type itself.

Multiple choice

A view was created containing groups of data. It does not allow DML operations, and does not contain a subquery. What type of view was created?

  1. Inline

  2. Simple

  3. Complex

  4. Explicit
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A complex view contains GROUP BY clauses, DISTINCT expressions, or aggregate functions, which prevent DML operations through the view. The presence of GROUP BY makes this view complex by definition. Simple views allow DML operations and don't have such complexity. Inline views are subqueries in the FROM clause, not a separate view type. Explicit refers to how the view is created, not its characteristics.

Multiple choice

As SYSDBA TOM created the PAYCLERK role and granted the role to JOHN. JOHN in turn attempts to modify the authentication method of the PAYCLERK role from SALARY to NOT IDENTIFIED, but when doing so he receives the insufficient privilege error as shown:

SQL> connect JOHN/crusader

Connected.

SQL> alter role payclerk not identified;

alter role payclerk not identified

*

ERROR at line 1:

ORA-01031: insufficient privileges

Which privilege does JOHN require to modify the authentication method of the PAYCLERK role?

  1. ALTER ANY ROLE

  2. MANAGE ANY ROLE

  3. UPDATE ANY ROLE

  4. MODIFY ANY ROLE
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To modify a role's authentication method, a user needs the ALTER ANY ROLE system privilege. JOHN only has the PAYCLERK role granted to him, but this doesn't include the privilege to alter the role's properties. Option B (MANAGE ANY ROLE) doesn't exist in Oracle. Options C and D are also non-existent privilege names. Only SYSDBA or a user with ALTER ANY ROLE can change role authentication methods.

Multiple choice

Evaluate the following SQL script:

CREATE USER hr IDENTIFIED BY hr01;

CREATE ROLE manager;

CREATE ROLE clerk;

GRANT clerk TO manager;

CREATE ROLE director;

GRANT manager TO director;

GRANT director TO hr

/

How many roles will the HR users have access to?

  1. 0

  2. 1

  3. 2

  4. 3
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When roles are granted to other roles, access cascades through the role hierarchy. HR is granted DIRECTOR, which is granted MANAGER, which is granted CLERK. Therefore HR has access to all three roles: DIRECTOR (directly), MANAGER (via DIRECTOR), and CLERK (via MANAGER). This hierarchical role granting is a key feature of Oracle's role-based access control.

Multiple choice

The DBA is creating a new user. Which of the following is not defined at user creation?

  1. default tablespace

  2. profile

  3. idle_time

  4. default role

Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When creating a user, Oracle allows specifying default tablespace, profile, and default roles. The idle_time parameter is a resource limit that belongs in a PROFILE, not a direct user creation parameter. You must create or modify a profile with idle_time settings, then assign that profile to the user. Resource limits are profile-level settings, not user-level.

Multiple choice

Which of the following statements is correct with regard to Query Record Group?

  1. The Record Group is associated with a query.

  2. The Record Group can be created only at run time.

  3. The Record Group can be created and modified only at design time.

  4. The Record Group can be created and modified at design time or at run time.

  5. The query associated with this Record Group can be defined only at design time.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A Query Record Group is specifically defined by its association with a query. This is its fundamental characteristic - it's created based on a SQL query and populated with the query's result set. Other record group types exist (static, non-query), but the defining feature of a Query Record Group is this query association.

Multiple choice

The EMP table contains the following columns: EMPNO NUMBER(6)

HIREDATE DATE

Review the following SELECT statement:

SELECT empno, TO_DATE('31-DEC-05') - hiredate

FROM emp

WHERE hiredate BETWEEN '31-DEC-04' AND '31-DEC-05';

What are the results of this statement?

  1. Only numeric values are returned.

  2. The employee numbers of any employees hired on January 1, 2004 are displayed.

  3. Date and numeric values are returned.

  4. The employee numbers of all employees hired before the year 2000 are displayed.

  5. Only the employee numbers of all employees are displayed because the BETWEEN operator used in the WHERE clause condition is a number function.
Reveal answer Fill a bubble to check yourself
A Correct answer
Multiple choice

JOHN is developing a form for customers to order tickets to events. There is a Date item in the Tickets block of the form that has a LOV whose record group uses the following query:

SELECT event_name, event_date

FROM events

ORDER BY event_date;

In the Choose_Event block of the form, he wants users to be able to select an event name into an Event_Name item.

The event name will be selected from a LOV that is sorted by the name of the event.

He does not want to modify either LOV or record group at run time. Which of the following statements is correct with regard to LOV and record group as specified at design time?

  1. He must define a new LOV for the Choose_Event.Event_Name item that uses a different record group.

  2. He must define a new LOV for the Choose_Event.Event_Name item, but he can base it on the same record group.

  3. He can use the same LOV and record group for the Choose_Event.Event_Name item that he used for the Tickets.Date item.

  4. He can use the same LOV for both items, but he should specify that each item must use a different record group for the LOV.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The two LOVs require different sorting - one by event_date (for the Date item) and one by event_name (for the Event_Name item). Since record groups contain the query including the ORDER BY clause, each LOV needs its own record group with the appropriate ORDER BY. They cannot share a record group because the sorting requirements are different.

Multiple choice

Select can be used in FROM clause of SQL select.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A SELECT statement can indeed be used in the FROM clause of another SELECT statement. This creates an inline view or derived table, allowing the results of a subquery to be treated as a table source in the outer query. This is a fundamental SQL feature used for breaking complex queries into simpler, more readable components. The option claiming 'False' is incorrect.

Multiple choice

Which of the following statements creates a view and does not allow the values to be changed through the view?

  1. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH READ ONLY;

  2. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK OPTION;

  3. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK CONSTRAINT;

  4. None of the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The WITH READ ONLY clause creates a view that prohibits INSERT, UPDATE, or DELETE operations through it. This is the standard Oracle syntax for making views read-only. Option B's WITH CHECK OPTION ensures DML operations don't create rows that would be invisible to the view, but doesn't prevent all DML. Option C's WITH CHECK CONSTRAINT is not valid Oracle syntax. Option D is incorrect because WITH READ_ONLY is the correct answer.

Multiple choice

Which of the following statements regarding DML statement functionality is true?

  1. UPDATE can update multiple columns in one table.

  2. INSERT must contain a VALUES clause.

  3. MERGE will delete rows that do NOT exist in either table.

  4. UPDATE will add rows to a table if an INTO clause is specified.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

UPDATE can modify multiple columns in one table with a single statement using SET column1=value1, column2=value2. INSERT can use subqueries instead of VALUES. MERGE matches rows to update or insert, not delete. UPDATE doesn't add rows - INSERT does. INTO clause is for subqueries, not UPDATE.

Multiple choice

A table is being imported and it must be ensured that the table is put into a single contiguous area. There is not a single contiguous area large enough for the entire space allocated to the table. What should be done to achieve this?

  1. Use the cascade option.

  2. Use the constraint option.

  3. Use the primary key option.

  4. Coalesce the tablespace the table is using.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When a tablespace is fragmented with insufficient contiguous space, COALESCE merges adjacent free extents into larger ones. This creates the needed contiguous space. Cascade, constraint, and primary key options don't relate to space consolidation. Coalescing is the correct approach for import requirements.