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

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 programming languages

A Cartesian product is returned when

  1. join conditions are not specified in a PROC SQL join.

  2. join conditions are not specified in a PROC SQL set operatio

  3. more than two tables are specified in a PROC SQL join.

  4. the keyword ALL is used with the OUTER UNION operator.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A Cartesian product occurs when a PROC SQL join is specified without a WHERE clause condition to link the tables, causing every row from one table to combine with every row from the other. Set operations like OUTER UNION operate on rows differently and don't produce Cartesian products.

Multiple choice technology programming languages

Consider this PROC SQL query: proc sql; select flightnumber, count(*) as Flights, avg(boarded) label="Average Boarded" format=3. from sasuser.internationalflights group by flightnumber having avg(boarded) > 150; The table Sasuser.Internationalflights contains 201 rows, 7 unique values of FlightNumber, 115 unique values of Boarded, and 4 different flight numbers that have an average value of Boarded that is greater than 150. How many rows of output will the query generate?

  1. 150

  2. 7

  3. 4

  4. 1
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The query groups by FlightNumber and filters with HAVING to show only the 4 flights where average boarded passengers exceeds 150. The GROUP BY produces 7 groups (one per unique flight), but HAVING filters this to only the 4 matching flights, regardless of total rows.

Multiple choice technology programming languages

Which statement about the following PROC SQL query is false? proc sql; validate select name label=’Country’, rate label=’Literacy Rate’ from world.literacy where ’Asia’ = (select continent from world.continents where literacy.name = continents.country) order by 2;

  1. The query syntax is not valid.

  2. The outer query must pass values to the subquery before the subquery can return values to the outer query.

  3. PROC SQL will not execute this query when it is submitted.

  4. After the query is submitted, the SAS log will indicate whether the query has valid syntax.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The query syntax is invalid because the WHERE clause references columns incorrectly - it compares a string literal to a subquery without proper correlation. Option A correctly identifies this syntax error, while other options make incorrect claims about query behavior.

Multiple choice technology programming languages

In order for PROC SQL to perform an inner join,

  1. the tables being joined must contain the same number of columns.

  2. the tables must be sorted before they are joined

  3. the columns that are specified in a join condition in the WHERE clause must have the same data type.

  4. the columns that are specified in a join condition in the WHERE clause must have the same name.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

For PROC SQL to perform an inner join, only the columns in the join condition must have compatible data types - they don't need matching names, and tables don't need sorting or equal column counts. Data type compatibility is the fundamental requirement for join operations.

Multiple choice technology programming languages

A PROC SQL inner join can combine

  1. a maximum of 2 tables or in-line views, but multiple joins can be chained together.

  2. a maximum of 32 tables or 2 in-line views.

  3. a maximum of 32 tables, which includes any tables referenced by an in-line view.

  4. a maximum of 2 tables and 32 columns.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

PROC SQL inner joins can combine up to 32 tables total, including any tables referenced by in-line views within the query. Options A and B incorrectly limit the number of tables to 2, while Option D incorrectly limits both tables and columns.

Multiple choice technology programming languages

Which PROC SQL query will remove duplicate values of MemberType from the query output, so that only the unique values are listed?

  1. proc sql nodup; select membertype from sasuser.frequentflyers;

  2. proc sql; select distinct(membertype) as MemberType from sasuser.frequentflyers;

  3. proc sql; select unique membertype from sasuser.frequentflyers group by membertype;

  4. proc sql; select distinct membertype from sasuser.frequentflyers;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

SELECT DISTINCT in PROC SQL removes duplicate values from query output by eliminating rows where all selected columns have identical values. Option A's NODUP syntax is invalid, Option B incorrectly uses DISTINCT as a function, and Option C unnecessarily adds GROUP BY.