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

What is the correct use of the Trunc command on a date?

  1. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  2. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

  3. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

  4. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

TRUNC function on a date truncates it to the specified precision. Using 'YEAR' as the format mask truncates the date to January 1st of that year, discarding the month and day information. Option D correctly demonstrates TRUNC applied to a date with proper syntax to round down to the first day of the year.

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

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

An Application was created in which the various triggers were defined at the Form level, Block level, Item level. The Execution Hierarchy property of the Trigger defined at the Form level was set to Before, For the Block level trigger it was set to Before, For the Item level Trigger it was set to after. How would the various triggers fire, based on the execution Hierarchy?

  1. Firstly Block level then Form and in the last Item level trigger would fire

  2. Firstly Item level then Block level and in the last Form level

  3. Firstly Block level then Form level and in the last Item level.

  4. None of these
Reveal answer Fill a bubble to check yourself
C Correct answer
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.