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 Oracle access methods is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan

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

ROWID access is the fastest retrieval method because ROWID is the direct physical address of a row in the data file. When Oracle knows the ROWID, it can access the row in a single I/O operation without traversing indexes or scanning tables. Primary key and unique index lookups require index traversal first, then ROWID access. Full table scans read all blocks, which is the slowest method.

Multiple choice

Which of the following types of joins should be written to perform an outer join of tables A and B that returns all rows from B?

  1. Any outer join

  2. A left outer join

  3. A cross join

  4. A right outer join

  5. An inner join
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A right outer join returns all rows from the right table (B) and matching rows from the left table (A). Left outer join returns all from A, not B. Inner join returns only matching rows from both. Cross join creates a Cartesian product.

Multiple choice

How to create a table space?

  1. With alter database command

  2. With alter system command

  3. Using create command

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

Tablespaces are created using the CREATE TABLESPACE command in SQL. ALTER DATABASE and ALTER SYSTEM modify existing structures. The 'create' command is the standard DDL statement for creating new database objects like tablespaces.

Multiple choice

Which of the following SELECT statements should be used to extract the year from the system date to display it in the format “2001”?

  1. SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual;

  2. SELECT TO_DATE(SYSDATE, 'yyyy') FROM dual;

  3. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

  4. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

TO_CHAR converts a date value to a string with the specified format mask. The 'yyyy' format element extracts the 4-digit year from SYSDATE. TO_DATE is incorrect because it converts a string to a date, not the reverse. DECODE is not relevant for date formatting.

Multiple choice

The database EMPLOYEES table has a foreign key constraint referencing the DEPARTMENTS table. A Human Resource application is being developed. HR clerks use the Employees form to query, update, and insert employee records. They occasionally attempt to add an employee who is in a new department that has not yet been entered into the database. When this happens, they receive an error indicating that the parent record cannot be found, so they cannot commit the employee record. The HR clerks have requested that a button be placed on the form to enable them to invoke the Departments form to enter the new department and save it independently. They want to be able to return to the Employees form and decide whether or not to save the new employee record. How should the When-Button-Pressed trigger be coded to achieve this?

  1. CALL_FORM('Departments);

  2. CALL_FORM('Departments',NO_HIDE);

  3. OPEN_FORM('Departments');

  4. OPEN_FORM('Departments', ACTIVATE, SESSION);

  5. OPEN_FORM('Departments', NO_ACTIVATE, NO_SESSION);
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

OPEN_FORM with the ACTIVATE and SESSION parameters opens the Departments form in a separate session while keeping the Employees form available. This allows clerks to enter and save the new department independently, then return to decide about the employee. CALL_FORM would replace the form entirely, and NO_ACTIVATE/NO_SESSION would not provide the needed independence.

Multiple choice

How can whole record be retrieved in PL/SQL without using any loop or cursor?

  1. Not possible

  2. Bulk collect

  3. Procedure

  4. Function
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

BULK COLLECT retrieves entire result sets into collections in a single operation, eliminating the need for explicit loops or cursor fetches. This is a performance optimization that reduces context switching between the PL/SQL and SQL engines. Procedures and functions are program units, not data retrieval mechanisms.

Multiple choice

Which of the following SQL SELECT statement clauses is an example of the selection capability, but not the joining capability?

  1. WHERE d.deptno = 10

  2. SELECT *

  3. WHERE d.deptno = e.deptno

  4. SELECT id_number, description, cost
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Selection refers to filtering rows based on conditions. WHERE d.deptno = 10 selects only rows where deptno equals 10 - this is pure selection. WHERE d.deptno = e.deptno is joining (relating two tables). SELECT * and SELECT id_number... are projection operations (choosing columns), not selection.

Multiple choice

Which of the following constructs can be used to return data based on an unknown condition?

  1. An ORDER BY clause

  2. An GROUP BY clause

  3. A WHERE clause with an OR condition

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

Subqueries allow dynamic query execution where the exact condition may not be known at design time - the inner query's results determine the outer query's behavior. ORDER BY and GROUP BY are for sorting and aggregation, not conditional logic. WHERE with OR only handles known conditions.

Multiple choice

What will the PARTITION BY clause in a partitioned outer join statement do?

  1. Partition the output rows into equal segments

  2. Partition the table into equal subpartitions

  3. Partition the rows in the output based on the expression provided within the clause

  4. Partition the underlying table, using Oracle's partitioning option
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

PARTITION BY in a partitioned outer join divides the result set rows based on the specified expression, creating distinct partitions for the join operation. It does not partition the underlying table or create equal segments - it's about organizing the output for the join, not physical data distribution.

Multiple choice

Examine the given code:SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to the given code?
  1. It returns employees who have 50% of the salary greater than $23,000:
  2. It returns employees who have 50% commission rate or salary greater than $23,000:
  3. It returns employees who have 50% of salary less than $23,000:

  4. None of the above

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

The query uses OR logic - it returns employees where EITHER the commission_pct equals 0.5 (50%) OR salary exceeds 23000. The parentheses in the description matter - it's not '50% OF salary greater than 23000' (which would be mathematically different), but two separate conditions joined by OR.

Multiple choice

Which of the following privileges can only be granted to a user and NOT to a role?

  1. DELETE

  2. ALTER

  3. INSERT

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

The REFERENCES privilege (and the corresponding REFERENCES object privilege) is unique in Oracle - it can only be granted to specific users, not to roles. This security restriction prevents roles from being used as a backdoor to grant foreign key constraints. All other object privileges like SELECT, INSERT, UPDATE, DELETE, ALTER, and EXECUTE can be granted to either users or roles.

Multiple choice

For which constraints are indexes created when the constraint is added?

  1. Check

  2. Unique

  3. Not null

  4. Primary key

  5. Foreign key
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Oracle automatically creates indexes to enforce UNIQUE and PRIMARY KEY constraints. These indexes support the constraint's requirement that values be distinct across rows (or non-null and distinct for PK). CHECK, NOT NULL, and FOREIGN KEY constraints do not automatically create indexes - though you can and often should manually create indexes on foreign key columns for join performance.

Multiple choice

Which of the following SQL statements accepts user input for the columns to be displayed, table name, and the WHERE condition?

  1. SELECT &1, &2FROM &3 WHERE last_name = '&4';

  2. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';

  3. SELECT &1, &2 FROM &3 WHERE last_name = '&4';

  4. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D correctly uses substitution variables: &1 for first column, '&2' as a literal string (single quotes preserved), EMP as hardcoded table, and &4 for the WHERE condition value. Options A and C incorrectly try to substitute the table name. Option B has malformed quoting with incorrect quote placement that would cause a syntax error.