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 false with respect to a set operation that uses the EXCEPT, UNION, or INTERSECT set operator without a keyword?

  1. Column names in the result set are determined by the first table.

  2. To be overlaid, columns must be of the same data type.

  3. To be overlaid, columns must have the same name.

  4. By default, only unique rows are displayed in the result set.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In SAS SQL set operations (UNION, INTERSECT, EXCEPT) without keywords like ALL or CORR, columns are matched by position (ordinal) not by name. The columns must have compatible data types but need not have the same name. Column names in the result come from the first table. Therefore, the statement that 'To be overlaid, columns must have the same name' is FALSE, making option C the correct answer.

Multiple choice technology programming languages

Which of the following PROC SQL steps gives bonuses (in points) to frequent-flyer program members as follows: - a 50% bonus for members who traveled less than 10,000 miles - a 100% bonus for members who traveled 10,000 miles or more?

  1. proc sql; update work.frequentflyers set pointsearned=pointsearned* case if milestraveled < 10000 then 1.5 if milestraveled >= 10000 then 2 else 1 end;

  2. proc sql; update work.frequentflyers set pointsearned=pointsearned* case when milestraveled < 10000 then 1.5 when milestraveled >= 10000 then 2 else 1 end;

  3. proc sql; update work.frequentflyers set pointsearned=pointsearned* case if milestraveled < 10000 then pointsearned*1.5 if milestraveled >= 10000 then pointsearned*2 else 1 end;

  4. proc sql; update work.frequentflyers set pointsearned=pointsearned* case if milestraveled < 10000 then pointsearned*1.5 if milestraveled >= 10000 then pointsearned*2 else pointsearned*1 end;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B correctly uses CASE with WHEN clauses (not IF) and multiplies pointsearned by the appropriate factor (1.5 for <10000 miles, 2 for >=10000 miles). Options A, C, and D incorrectly use IF instead of WHEN in the CASE statement syntax. Options C and D also incorrectly reference pointsearned again inside the CASE expression, which would cause double multiplication.

Multiple choice technology programming languages

Which keyword must you add to your index definition in the CREATE INDEX statement to ensure that no duplicate values of the key column can exist?

  1. KEY

  2. UNIQUE

  3. NODUPS

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

Option B is correct: the UNIQUE keyword in CREATE INDEX prevents duplicate values in the indexed column. Option A (KEY) is not a valid keyword for enforcing uniqueness. Option C (NODUPS) is not valid SAS syntax. Option D (NODUPKEY) is a SAS dataset option, not a CREATE INDEX keyword.

Multiple choice technology platforms and products

A complex join is -

  1. Primary Key Foreign Key Join

  2. Primary Key Candidate Key Join

  3. Candidate Key Foreign KeyJoin

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

A complex join in OBI EE is defined using a logical SQL expression in a WHERE clause (using AND, OR, CASE, etc.) rather than simple key-based relationships. It's not based on PK-FK, PK-CK, or CK-FK relationships alone - those describe simple foreign key joins.

Multiple choice technology platforms and products

One physical table can have more than one Alias with different primary key.

  1. True

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

In the physical layer, a single database table can have multiple aliases with different primary key definitions. This is useful when the same table plays different roles in a dimensional model (e.g., a Date table used for both Order Date and Ship Date). Each alias can define its own primary key context.

Multiple choice technology mainframe

SEARCH ALL can only be applied to sorted tables??

  1. True

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

To answer this question, the user needs to know what the SEARCH ALL operation does and if it requires sorted tables.

The SEARCH ALL operation is used to find a specific record in a table that matches a given key value. It starts searching from the beginning of the table and stops when it finds the first matching record. If the table is not sorted, the search operation will have to check every record in the table, which can be slow and inefficient.

Therefore, the correct answer is:

The Answer is: A. True.

SEARCH ALL can only be applied to sorted tables for efficient search operations.

Multiple choice technology databases

You are developing a PL/SQL block designed for bulk data operations. When attemping to store table data for multiple rows in a PL/SQL variable, which one of the following choices identifies the mechanism best suited for this task (choose one)?

  1. Cursor

  2. Varchar2

  3. PL/SQL table

  4. Table of records

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

For bulk data operations in PL/SQL, a table of records (declared as TYPE t_tab IS TABLE OF table_name%ROWTYPE) is the most efficient mechanism. It allows storing entire rows from a table in a PL/SQL collection variable. A cursor only points to rows, varchar2 stores single values, a record holds one row, and a PL/SQL table (index-by table) is less efficient than a table of records for bulk operations.

Multiple choice technology databases

how many distinct triggers can be created on the table

  1. 12

  2. 4

  3. 2

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

Oracle allows up to 12 distinct triggers per table: 4 timing levels (BEFORE/AFTER) × 2 events (INSERT/UPDATE/DELETE) × 2 levels (statement/row) for UPDATE, plus INSERT and DELETE at row and statement levels. This gives INSERT (2), UPDATE (4), DELETE (2), and 4 more variations totaling 12.

Multiple choice technology databases

Which of the following is DDL Statement

  1. Truncate

  2. Merge

  3. Revoke

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

TRUNCATE is a DDL (Data Definition Language) operation because it removes all rows and resets storage, performing an implicit commit that cannot be rolled back. MERGE is DML (combines INSERT/UPDATE/DELETE). REVOKE and GRANT are DCL (Data Control Language) statements for permissions.

Multiple choice technology databases

Select the incorrect Oracle Select Statement

  1. select distinct * from T1

  2. select C1 from T1 order by C2

  3. select distinct C1 from T1

  4. select 12*14 from T1

  5. select C1 AS 'column1",C1 AS 'column2',C2 AS 'column1' from T1.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B is incorrect because it uses ORDER BY C2 without C2 being in the SELECT list, which is invalid Oracle SQL. The other options are valid: DISTINCT can be used with * or specific columns, arithmetic expressions in SELECT are allowed, and column aliases can be repeated though it's poor practice.

Multiple choice technology databases

Which of the following is a DML Statement

  1. Truncate

  2. Merge

  3. Revoke

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

MERGE is a DML (Data Manipulation Language) statement that combines INSERT and UPDATE operations based on a condition. TRUNCATE is DDL (removes data, resets storage). REVOKE and GRANT are DCL (Data Control Language) for managing permissions.