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 databases

An application developer has written an AFTER STATEMENT trigger for UPDATE event on the ORDERS table of ORDER ENTRY application. The trigger saves the details of the user performing the update, system date, number of records updated to the AUDIT table. User, ERIC accesses ORDER ENTRY application and submits an UPDATE on ORDERS table through a batch job. However, the update statement affects ZERO records from ORDERS table. What is true in the context of the database trigger being written?

  1. Database Trigger will NOT fire at all as number of records updated are ZERO. However, batch job will run through successfully.

  2. Database Trigger will fire only ONCE and will save details viz. "ERIC", "SYSDATE" and "0" to the AUDIT table. The batch job will run through successfully.

  3. Database Trigger will return an ERROR and abort the batch job

  4. Database Trigger will return an ERROR. However, batch job will run through successfully
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

AFTER STATEMENT triggers fire once per SQL statement regardless of how many rows are affected - even zero rows. The trigger will execute once, recording Eric as user, SYSDATE, and 0 for affected rows. This is statement-level trigger behavior in Oracle.

Multiple choice technology databases

what does the query select * from table_abc where column_xyz like '%oracle%'

  1. selects all the rows from the table having the column_xyz value starting with the word oracle

  2. selects all the rows from the table having the column_xyz value ending with the word oracle

  3. selects all the rows from the table having the column_xyz value containing the word oracle

  4. selects all the rows from the table having the column_xyz value not containing the word oracle
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The % wildcard matches any sequence of characters (including zero characters). '%oracle%' means match any value that contains 'oracle' anywhere - before, after, or in between. It would match 'My oracle database', 'oracle', or '123oracle456'.

Multiple choice technology databases

What is the difference in coding between inserting segments into an existing database and loading a database?

  1. The SSA must be qualified

  2. There is no difference.

  3. The key sequence of the data in loading

  4. There is no need for parentage in loading.

  5. The PCB processing options
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The key difference between inserting segments into an existing database and loading a database is in the PCB (Program Communication Block) processing options. When loading a database, different PROCOPT settings are used compared to inserting segments. The PCB controls what operations are permitted. Other options are incorrect: SSA qualification rules are similar, key sequence matters in both, and parentage is required in both cases.

Multiple choice technology testing

RecordSet object of ADODB library holds

  1. the query issued

  2. the result returned from the issued SQL query

  3. the connection string

  4. a reference to the connection object
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The RecordSet object in ADODB holds the result set returned from executing an SQL query. It provides methods to navigate through the records (MoveFirst, MoveNext, etc.) and access field values. Option A is incorrect because the query itself is held by the Command object. Option C is incorrect because the connection string belongs to the Connection object. Option D is incorrect because the RecordSet doesn't hold a reference to the Connection object.

Multiple choice technology testing

Identify the issues in the code below 

Set con = CreateObject("ADODB.Connection") 
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;
Data source=C:\...\Database1.accdb" 
rs = con.Execute("select * from Table1") 
rs.MoveFirst 
Do While rs.EOF     
    Msgbox rs.Fields(0).Name & " : " &rs.Fields(0).Value & " " & rs.Fields(1).Name & " : " &rs.Fields(1).Value & " " & rs.Fields(2).Name & " : " & rs.Fields(2).Value  
Loop 
rs.Close 
con.Close

Issue1) SQL query is issued as an argument to an incorrect method Issue2) Condition with the loop is incorrect Issue3) Code is not designed navigate to the next record in the recordset Issue4) Connection string is incorrectly formed Issue5) Msgbox cannot display data from Database Issue6) Set keyword is missing in a statement

  1. Issue2, Issue3, Issue6

  2. Issue1, Issue2, Issue3

  3. Issue2, Issue4, Issue6

  4. No issues - The code is perfect
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The code has three issues: (1) The loop condition 'While rs.EOF' should be 'While Not rs.EOF' to iterate while there ARE records; (2) Missing 'rs.MoveNext' inside the loop to advance to the next record; (3) Missing 'Set' keyword in 'rs = con.Execute' - should be 'Set rs = con.Execute' for object assignment.

Multiple choice technology

Select any two portioning method

  1. DB2

  2. Round robin

  3. Sort merge

  4. Ordered
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

DB2 partitioning uses DB2's own partitioning map. Round-Robin distributes rows sequentially across partitions. Both are valid partitioning methods. Sort Merge and Ordered are collection methods, not partitioning methods.

Multiple choice technology

Which stage does this “alters the record schema of the input data. To drop, keep or change the column data type Used for NULL handling”

  1. Head stage

  2. Switch stage

  3. Modify stage

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

The Modify stage in DataStage is specifically designed to alter the record schema of input data by dropping columns, keeping selected columns, or changing column data types. It also provides capabilities for NULL handling and data type conversions, making it the primary stage for schema modification tasks.

Multiple choice technology databases

COUNT(*) returns the number of items in a group - which of the below is correct?

  1. This includes NULL values and duplicate

  2. This includes NULL values but not the duplicates

  3. This does not include NULL values and duplicates

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

The COUNT(*) function in SQL counts all rows that match the query criteria, including rows containing duplicate values and rows containing NULL values, unlike COUNT(column_name) which ignores NULLs.

Multiple choice technology databases

sys.sql_modules system catalog contains a row for each object of type P, RF, V, TR, FN, IF, TF, R, and D.

  1. True

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

The sys.sql_modules catalog view returns a row for each object that is a SQL language module. This includes stored procedures (P, RF), views (V), triggers (TR), scalar functions (FN), inline table-valued functions (IF), table-valued functions (TF), and rules (R). The type codes listed match SQL Server's object type catalog entries.

Multiple choice technology databases

We can write triggers for Truncate.

  1. True

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

TRUNCATE TABLE is a minimally logged operation that cannot fire triggers. Unlike DELETE statements which activate row-level triggers, TRUNCATE deallocates data pages without logging individual row deletions, bypassing trigger execution entirely. If you need trigger behavior, use DELETE instead. TRUNCATE is faster but less flexible.