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 databases

You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?

  1. FOR EACH ROW trigger on the EMP table.

  2. Statement-level trigger on the EMP table.

  3. FOR EACH ROW trigger on the AUDIT_TABLE table.

  4. Statement-level trigger on the AUDIT_TABLE table.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To monitor every row change in EMP and insert into AUDIT_TABLE, you need a row-level trigger (FOR EACH ROW) on the source table EMP. Statement-level triggers fire once per statement regardless of rows affected. The trigger should be on EMP, not AUDIT_TABLE, since you're monitoring changes to EMP.

Multiple choice technology databases

Select correct type of parameter mode used in oracle

  1. IN

  2. OUT

  3. IN OUT

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

Oracle procedures support three parameter modes: IN (read-only input), OUT (write-only output), and IN OUT (read-write input/output). These modes define how parameters can be modified within the procedure. All three modes are valid and commonly used in PL/SQL.

Multiple choice technology databases

You need to remove the database trigger T1. Which command do you use to remove the trigger in the SQL *Plus environment?

  1. DROP TRIGGER T1

  2. DELETE TRIGGER T1

  3. ALTER TRIGGER T1 DISABLE

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

To remove a database trigger, use DROP TRIGGER trigger_name command. DELETE TRIGGER is not valid SQL. ALTER TRIGGER DISABLE would disable but not remove the trigger. DROP TRIGGER is the correct DDL command for permanent removal in SQL*Plus.

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

For Oracle databases, use encodeForOraSQL which handles Oracle-specific syntax like double pipes used for concatenation. Generic encodeForSQL does not cover Oracle-specific requirements. Note that the input should be validated first.

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 parameterized queries is the best practice - it completely separates SQL logic from data, preventing injection. createStatement with dynamic concatenation (C and D) is vulnerable. encodeForOraSQL is a workaround but not the primary methodology.

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

For Oracle databases, encodeForOraSQL is the correct method because Oracle has specific SQL syntax quirks that require special encoding. Option B uses generic SQL encoding which may not handle Oracle-specific characters properly. Option C incorrectly instantiates a Codec object. Always use database-specific encoding methods.

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 parameterized queries is the gold standard for preventing SQL injection because it separates SQL logic from data. Options A and C rely on encoding or string concatenation which are less reliable. Option D is dangerous because it concatenates input before using PreparedStatement, negating its benefits.

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

For Oracle databases, TCS SAPI provides the encodeForOraSQL method which handles Oracle-specific SQL injection concerns including handling of Oracle's particular SQL syntax and escaping rules. Generic SQL encoding methods may not account for Oracle's specific injection vulnerabilities.

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 parameterized queries using setXXX methods is the industry standard for preventing SQL injection. It separates SQL logic from data, ensuring user input is never interpreted as SQL commands. String encoding approaches are secondary defenses, not the primary best-practice methodology.

Multiple choice technology databases

What does the following SQL statement do: SELECT Customer, COUNT(Order) FROM Sales GROUP BY Customer HAVING COUNT(Order) >5

  1. Selects the total number of orders from the Sales table, if this number is greater than 5

  2. Selects all customers from table Sales that have made more than 5 orders.

  3. Selects all Customers from the Sales table

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

To understand this SQL statement, the user needs to know the basic structure of SQL queries. In this case, the statement selects two fields, Customer and COUNT(Order), from the Sales table. It then groups these fields by Customer and applies a filter using the HAVING clause to only return rows where the count of orders for that customer is greater than 5.

A. This option is incorrect because the statement is not selecting the total number of orders, but rather the count of orders for each customer.

B. This option is correct. The statement is selecting all customers from the Sales table that have made more than 5 orders.

C. This option is incorrect because the statement is not selecting all customers, but rather only those who have made more than 5 orders.

D. This option is incorrect because option B is correct.

Therefore, the answer is: B. Selects all customers from table Sales that have made more than 5 orders.

Multiple choice technology databases

Which of the following SQL statements is correct?

  1. TRUNCATE Sales TABLE

  2. TRUNCATE * FROM TABLE Sales

  3. TRUNCATE TABLE Sales

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

The correct SQL syntax for removing all rows from a table is TRUNCATE TABLE followed by the table name. This command quickly deletes all data while keeping the table structure intact. Options A and B use incorrect syntax, and D cannot be correct since only one syntax is proper.

Multiple choice technology databases

What is a stored procedure?

  1. A process for data export/import.

  2. A set of SQL statements stored and executed as a single entity.

  3. A process to convert tables.

  4. Data stored in a database.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A stored procedure is a precompiled collection of one or more SQL statements stored in the database. It can be executed as a single unit by calling its name, which improves code reusability, reduces network traffic, and enhances security by controlling data access through defined procedures.