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 technology programming languages

Which statement is used to remove the found set of records from a list?

  1. REMOVE RECORDS IN label FROM [LIST] listname

  2. REMOVE RECORDS FROM [LIST] listname

  3. DELETE RECORDS IN label FROM [LIST] listname

  4. DELETE RECORDS FROM [LIST] listname
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct syntax is REMOVE RECORDS IN label FROM [LIST] listname. This removes records identified by a found set label from a specified list. The IN clause specifies which found set to use, and FROM [LIST] identifies the target list. Option B is missing the required IN label clause.

Multiple choice technology programming languages

The ADD statement is used to add the new field value to the record only inside the FOR EACH RECORD loop statement.

  1. True

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

In Model 204, the ADD statement for adding new field values to records is indeed restricted to use within a FOR EACH RECORD loop. This is because ADD operates on the current record context, which is only established inside the loop. Outside the loop, there is no current record to which fields can be added.

Multiple choice technology databases

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

  5. Foreign Key
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A PRIMARY KEY constraint uniquely identifies each row AND implicitly enforces NOT NULL, satisfying both rules. UNIQUE allows nulls, CHECK doesn't enforce uniqueness, NOT NULL doesn't enforce uniqueness, and FOREIGN KEY references other tables.

Multiple choice technology databases

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

  5. Foreign Key
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A Primary Key constraint enforces both uniqueness (no duplicate values) and the NOT NULL constraint (no null values). This makes it the only single constraint type that guarantees both business rules are satisfied simultaneously for a table column.

Multiple choice technology databases

John has created a procedure named SALARY_CALC. Which SQL query allow him to view text of procedure?

  1. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

  2. SELECT * FROM user_source WHERE name='SALARY_CALC';

  3. SELECT * FROM user_objects WHERE object_name='SALARY_CALC';

  4. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC';

  5. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To view the source code text of a stored procedure in Oracle, you query the 'text' column of the 'user_source' data dictionary view where the 'name' matches the procedure name in uppercase.

Multiple choice technology databases

Consider this syntax MERGE INTO t1 USING t2 ON (join predicate) .... What does the MERGE syntax do ?

  1. It performs a merge join of the row from T2 only if it does'nt exist in the T1 table.

  2. It creates a natural join of table T1 and T2 for all columns that have the same name.

  3. It creates a Cartesian prodcut of table T1 and table T2 for all columns that have the same name.

  4. For each row from T2, it updates the row if it exists witin table T1, otherwise it inserts the row into T1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The MERGE statement (also called UPSERT) combines UPDATE and INSERT operations. For each row from the source table T2, it evaluates the join predicate against T1. If a matching row exists in T1, it updates that row; if no match exists, it inserts the new row into T1.

Multiple choice technology databases

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

  5. Foreign Key
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A Primary Key constraint enforces both uniqueness and non-null requirements simultaneously. It uniquely identifies each row (no duplicates) and implicitly prevents NULL values. A Unique constraint allows one NULL value, Not Null doesn't prevent duplicates, Check validates expressions, and Foreign Key references other tables. Only Primary Key combines both rules.

Multiple choice technology databases

John has created a procedure named SALARY_CALC. Which SQL query allow him to view text of procedure?

  1. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

  2. SELECT * FROM user_source WHERE name='SALARY_CALC';

  3. SELECT * FROM user_objects WHERE object_name='SALARY_CALC';

  4. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC';

  5. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The USER_SOURCE view contains the source code for stored objects owned by the current user. The TEXT column specifically holds the code text. Option A correctly filters by name and selects the text column. Option B retrieves all columns but still works. Option C queries USER_OBJECTS which only contains metadata about objects, not their source code. Option D queries USER_PROCEDURES which is also a metadata view. Option E incorrectly adds an OWNER filter to USER_SOURCE, but this view only shows objects owned by the current user.

Multiple choice technology databases

Consider this syntax MERGE INTO t1 USING t2 ON (join predicate) .... What does the MERGE syntax do ?

  1. It performs a merge join of the row from T2 only if it does'nt exist in the T1 table.

  2. It creates a natural join of table T1 and T2 for all columns that have the same name.

  3. It creates a Cartesian prodcut of table T1 and table T2 for all columns that have the same name.

  4. For each row from T2, it updates the row if it exists witin table T1, otherwise it inserts the row into T1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The MERGE statement combines INSERT and UPDATE operations in a single statement. For each row in the source table (T2), it checks if a matching row exists in the target table (T1) based on the join condition. If a match exists, it updates the existing row in T1. If no match exists, it inserts a new row into T1. This is an efficient way to synchronize data between tables. Options A, B, and C describe other types of operations (merge join, natural join, Cartesian product) that are not what MERGE does.