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

what are the functions used for calculating sub totals ?

  1. Using Rollup() function

  2. Using SUBTOT() function

  3. Using SUBSUM() function

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

The Rollup function is the standard method for calculating subtotals and aggregated values in data analysis tools, databases (especially MongoDB with $rollup), and spreadsheet applications. While other functions like SUBTOTAL exist, Rollup is the most widely applicable approach for hierarchical aggregation. Option D cannot be correct since SUBTOT and SUBSUM are not standard function names.

Multiple choice technology web technology

CRUD stands for

  1. Create Read Update Delete

  2. Create Request Update Delete

  3. Create Read Update Drop

  4. Create Redo Update Direct
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

CRUD represents the four basic operations of persistent storage: Create (insert new records), Read (retrieve existing data), Update (modify existing records), and Delete (remove records). This pattern is fundamental in database management and web application development.

Multiple choice technology security

"SELECT name FROM users WHERE id = " + form.getUserID()"; Using TCS SAPI what is the best way to remediate the SQL injection vulnerability in the above query to an Oracle Database?

  1. "SELECT name FROM users WHERE id = " + com.tcs.sapi.io.ValidationUtil.encodeForOraSQL(validatedUserId);

  2. "SELECT name FROM users WHERE id = " + com.tcs.sapi.io.ValidationUtil.encodeForSQL(validatedUserId);

  3. "SELECT name FROM users WHERE id = " + com.tcs.sapi.io.ValidationUtil.encodeForSQL( new Codec(), validatedUserId);

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

Oracle databases have specific SQL syntax requirements, so encodeForOraSQL is the correct encoder that handles Oracle-specific escaping (like handling quotes, special characters in Oracle SQL). Generic encodeForSQL might not account for Oracle's unique syntax. Option C is incorrect because ValidationUtil methods are static and don't require a Codec object parameter.

Multiple choice technology security

What is the best methodology to remediate the SQL Injection vulnerability in a Java based web application?

  1. Use the com.tcs.sapi.io.ValidationUtil.encodeForOraSQL(String input) method

  2. Use PreparedStatement constructs and use the setXXX methods on the PreparedStatement object

  3. Use the Java createStatement construct to execute the query

  4. Concatenate your SQL string together using dynamic input and create and execute a PreparedStatement object using that query
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

PreparedStatement with setXXX methods is the strongest defense because it separates SQL logic from data, treating user input as parameters rather than concatenating it into the query string. This completely eliminates SQL injection at the database driver level. Encoding methods (option A) are fragile workarounds - if you forget to encode even once, you're vulnerable. createStatement with concatenated strings (option C/D) is exactly what causes SQL injection vulnerabilities.

Multiple choice technology packaged enterprise solutions

Which is used for looking up values in a table.

  1. Lookup

  2. Lookabove

  3. Source Qualifier

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

In Oracle Warehouse Builder (OWB) and other ETL tools, the Lookup transformation is specifically designed to look up values in a database table or view based on search conditions. Distractors like Lookabove and Setop are incorrect because Lookabove does not exist, and Setop is used for set operations.

Multiple choice technology

The Union transformation does not remove duplicate rows and is similar to ________SQL statement

  1. UNION

  2. UNION ALL

  3. INTERSECT

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

The Union transformation in Informatica combines data from multiple sources without removing duplicate rows, which is identical to the UNION ALL SQL statement. Unlike SQL's UNION (which removes duplicates) or INTERSECT, the Union transformation preserves all rows including duplicates, making it equivalent to UNION ALL in behavior.

Multiple choice technology

Stored Procedure Transformation is a________Transformation A)Passive B)Active C)Unconnected D)Connected

  1. A

  2. A,C,D

  3. B,C,D

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

Stored Procedure Transformation in Informatica is Passive (does not change the number of rows), Unconnected (can be called from other transformations using :SP syntax), and Connected (can be used directly in the data flow). It is NOT Active because it cannot increase or decrease row count. The combination A, C, D correctly describes all applicable characteristics.

Multiple choice technology databases

Choose correct syntax to insert values into r4r_team table

  1. INSERT INTO r4r_team (id, username, exp) VALUES (1, ‘r4r01’, ‘2’);

  2. INSERT INTO r4r_team VALUES (1, ‘r4r01’, ‘2’);

  3. INSERT INTO r4r_team (id, username, exp) VALUE (1, ‘r4r01’, ‘2’);

  4. INSERT INTO table r4r_team (id, username, exp) VALUE (1, ‘r4r01’, ‘2’);
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Both A and B are correct INSERT syntax. A explicitly specifies column names (recommended), while B omits them (requires VALUES match table column order exactly). C and D are incorrect - 'VALUE' is not valid SQL (must be VALUES), and 'INSERT INTO table' syntax is wrong.

Multiple choice technology databases

What is true for following DML SQL statement SELECT username, exp, salary FROM r4r_team ORDER BY username DESC;

  1. Used to select username,exp, salary from r4r_team which is decreasing order by username.

  2. Used to select username,exp, salary from r4r_team which is increasing order by username.

  3. Used to select username,exp, salary from r4r_team which is decreasing order by exp.

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

ORDER BY username DESC sorts in decreasing (descending) order by username, not increasing order and not by the exp column. DESC = highest to lowest, DESC only applies to username, not to exp.