Computer Knowledge

Database and SQL

3,929 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 command will delete all data from a table and will not write to the rollback segment?

  1. DROP

  2. DELETE

  3. CASCADE

  4. TRUNCATE

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

TRUNCATE is a DDL command that deallocates data pages without generating redo or rollback information, making it much faster than DELETE for large tables. DROP removes the entire table structure, CASCADE is used with DROP for dependencies, and DELETE is a DML command that generates extensive rollback and redo logs.

Multiple choice

Which of the following SELECT statements will get the result 'ELCOME' from the string 'WELCOME'?

  1. SELECT SUBSTR ('WELCOME',1) FROM dual;

  2. SELECT INITCAP(TRIM('WELCOME', 1,1) FROM dual;

  3. SELECT LOWER (SUBSTR ('WELCOME', 2,1) FROM dual;

  4. SELECT LOWER (SUBSTR('WELCOME', 2,1) FROM dual;

  5. SELECT LOWER (TRIM ('W' FROM 'WELCOME')) FROM dual;
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

TRIM with the 'W' FROM clause removes the leading 'W' character, leaving 'ELCOME'. The LOWER function then converts the remaining string to lowercase, producing 'elcome'. Option A uses SUBSTR incorrectly and doesn't achieve the transformation, options B and C have syntax errors in their function calls, and option D has incorrect syntax.

Multiple choice

In this PL/SQL statement, which of the following lines will produce an error?

  1. create or replace function vat (vat_rate IN NUMBER, price IN NUMBER)

  2. declare

  3. l_vat NUMBER;

  4. begin

  5. l_vat := (vat_rate/100) * price; return l_vat;

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

In Oracle PL/SQL, functions use the IS or AS keyword to begin the declaration section, not DECLARE. The DECLARE keyword is used only in anonymous PL/SQL blocks, not in named program units like functions or procedures. The correct syntax for a function uses IS/AS after the parameter list to begin the declaration section.

Multiple choice

Which SELECT statement should be used if unique combinations of the POSITION and MANAGER values from the EMP table should be displayed?

  1. SELECT DISTINCT position, manager FROM emp;

  2. SELECT position, DISTINCT manager FROM emp;

  3. SELECT position, manager FROM emp;

  4. SELECT position, manager DISTINCT FROM employee;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Option A correctly uses SELECT DISTINCT to retrieve unique combinations of position and manager values. The DISTINCT keyword applies to the entire row combination, not just the column immediately following it. Option B incorrectly places DISTINCT after only one column name, option C returns all rows including duplicates, and option D has invalid syntax with DISTINCT placed incorrectly.

Multiple choice

Which of the following is not true about indexes?

  1. Indexes are created to enforce uniqueness on columns.

  2. Indexes are created to enable fast retrieval by column values.

  3. Columns that are frequently used with equal conditions in WHERE clauses are good candidates for indexes.

  4. Indexes are created with the ALTER TABLE command.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct answer is D because indexes are created using the CREATE INDEX command, not ALTER TABLE. Options A, B, and C are all true statements about indexes: they can enforce uniqueness (A), enable fast retrieval by column values (B), and columns frequently used in WHERE clauses are indeed good candidates for indexing (C).

Multiple choice

What is the result if two NULL values are compared to each other?

  1. TRUE

  2. FALSE

  3. Undefined

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

The correct answer is D because in SQL, NULL is not a value but a marker for unknown/missing data. Any comparison operation involving NULL returns NULL (unknown), not TRUE or FALSE. Therefore, comparing two NULL values returns NULL, not TRUE, FALSE, or undefined. This is fundamental three-valued logic in SQL.

Multiple choice

Evaluate the following SQL statement:

SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual;

What will be displayed?

  1. 0

  2. 1

  3. 0.00

  4. An error statement
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct answer is A because the expression evaluates step-by-step: MOD(1600,10) returns 0, TRUNC(0,-1) truncates to -1 decimal place giving 0, and ROUND(0,2) rounds to 2 decimal places still giving 0. Option B (1) is incorrect because the mod is 0, not 1600. Option C (0.00) is incorrect because ROUND(0,2) returns 0, not 0.00. Option D is incorrect because the syntax is valid.

Multiple choice

A number column called STATUS has been added to the ORDERS table. The SQL script was run to populate that column. The Orders form should be updated to display the additional data. Currently, there is a non-base table display item in the Orders block called Total that is no longer desired. The Total item is located on the canvas in the position where the Status item should appear. The Property Palette for Total was opened, its name was changed to Status, and its Item Type was changed to Text Item. The form was run, but when a query was executed on the block, no data was displayed in the Status item. What should be done to correct this problem?

  1. Set the Database Item property to Yes for the Status item.

  2. Use the form to populate the Status item, since the SQL script that was run obviously did not work.

  3. Set the Name property for the Status item to STATUS, because it must be uppercase to match the column name in the database.

  4. Use the Data Block Wizard in reentrant mode to add the item, because add a base table item cannot be added by changing the item type of an existing item.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When a non-base table item is renamed and changed to display a database column, you must explicitly set its Database Item property to Yes. This tells Forms that the item should map to a database column during query operations. The name case (uppercase STATUS) is not required for proper mapping.

Multiple choice

Which of the following is not a desirable property of a database transaction?

  1. Atomicity

  2. Consistency

  3. Isolation

  4. Durability

  5. Concurrency
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Concurrency means several users access the database at the same time.It is not desirable as it can leave the database in inconsistent state.Concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible.

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

Coalescing a tablespace combines adjacent free space fragments into larger contiguous extents. When importing a table that requires a single contiguous area larger than any existing free space, coalescing can create the needed space. Other options (cascade, constraint, primary key) are import-related parameters or constraints that don't address space consolidation.

Multiple choice

Evaluate the given SQL statement. SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual; Which of the following values will be displayed?

  1. 46 and 45.93

  2. 50 and 45.93

  3. 50 and 45.9

  4. 45 and 45.93

  5. 45.95 and 45.93
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ROUND(45.953, -1) rounds to 50 because the negative second parameter means round to tens place (45.953 becomes 50). TRUNC(45.936, 2) truncates to 45.93 by keeping only 2 decimal places (no rounding, just truncation).

Multiple choice

Which of the following is not an Oracle-supported trigger?

  1. BEFORE

  2. DURING

  3. AFTER

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

Oracle supports BEFORE, AFTER, and INSTEAD OF triggers. DURING is not a valid trigger timing in Oracle. Triggers fire either before or after a DML operation (or instead of, for views), but not during the operation itself.

Multiple choice

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

  1. select * from EMP where nvl(EMPNO, '00000') = '59384';

  2. select * from EMP where EMPNO = '59384';

  3. select EMPNO, LASTNAME from EMP where EMPNO = '59384';

  4. select 1 from EMP where EMPNO = '59834';
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The NVL function wraps the EMPNO column, which prevents the database from using the index on EMPNO. When a column is modified by a function in the WHERE clause, the optimizer cannot perform an index range scan on that column because the indexed values don't match the function-transformed values. The other options (B, C, D) all use direct equality comparisons on EMPNO, which can leverage the index.

Multiple choice

What does the TRUNCATE statement do?

  1. Removes the table

  2. Removes all rows from a table

  3. Shortens the tale to 10 rows

  4. Removes all columns from a table
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

TRUNCATE is a DDL command that removes all rows from a table while preserving the table structure. Unlike DELETE (which is DML and can be rolled back), TRUNCATE cannot be rolled back and is faster because it doesn't generate redo logs for individual rows. It does not remove the table itself (option A), does not limit rows to 10 (option C), and does not affect column structure (option D).