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 technology mainframe

SQLCABC contains value of SQLCA.

  1. True

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

SQLCABC (SQL Communication Area Byte Count) contains the length of the SQLCA structure. It exists to verify SQLCA size compatibility between different program and DB2 versions, making the statement essentially correct.

Multiple choice technology mainframe

88 level used for

  1. condition names

  2. RENAMES clause

  3. All the above

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

In COBOL, level 88 is used exclusively for condition names - boolean flags that can be set true/false based on specific data conditions. Level 88 entries define conditional status flags associated with their parent elementary item, used for program logic flow control, not for RENAMES or regular data storage.

Multiple choice technology mainframe

SEARCH command used for

  1. binary search & the table must be sorted

  2. serial search

  3. binary search

  4. All the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SEARCH verb in COBOL performs a serial (sequential) search by default, examining table elements one-by-one until a match is found. The table need not be sorted. For binary search on sorted tables, COBOL provides SEARCH ALL, not SEARCH. The question specifically asks about SEARCH command without ALL.

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.

Multiple choice technology mainframe

What is a lock in DB2?

  1. A lock is the mechanism that controls access to rows and coloumns

  2. A lock is the mechanism that controls access to table views

  3. A lock is the mechanism that controls access to data pages and table spaces.

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

DB2 locks control access to physical and logical data resources like table spaces, tables, and data pages to ensure concurrency. They do not lock individual columns (making the first option incorrect) or views directly (making the second option incorrect), making the third option the most accurate.

Multiple choice technology databases

Which of the following SQL statements does count the rows in the 'Sales' table?

  1. SELECT COUNT(*) IN Sales

  2. SELECT NUM() FROM Sales

  3. SELECT COUNTER(*) FROM Sales

  4. SELECT COUNT(*) FROM Sales
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

COUNT(*) is the standard SQL aggregate function that returns the total number of rows in a table, including NULL values. The correct syntax requires FROM, not IN, to specify the table source. Option D uses the proper SQL syntax while other options use non-existent functions like NUM(), COUNTER(), or incorrect prepositions.

Multiple choice technology databases

Which SQL statement inserts data into a table called Projects?

  1. INSERT Projects ('Content Development', 'Website content development project')

  2. INSERT INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')

  3. INSERT Projects VALUES ('Content Development', 'Website content development project')

  4. SAVE INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

INSERT INTO is the standard SQL statement with the syntax requiring column names in parentheses followed by VALUES and the data to insert. Option B correctly specifies both column names (ProjectName, ProjectDescription) and their corresponding values. Options A and C miss the required INTO keyword and column specification, while D uses SAVE which is not a SQL keyword.