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

Which type of Statement can execute parameterized queries?

  1. PreparedStatement

  2. ParameterizedStatement

  3. ParameterizedStatement and CallableStatement

  4. All kinds of Statements (i.e. which implement a sub interface of Statement)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

PreparedStatement is specifically designed to execute parameterized queries efficiently and safely. It pre-compiles SQL statements with placeholders (?) that are filled with parameter values at runtime, providing better performance and protection against SQL injection. Option B is fictional, and CallableStatement is for stored procedures.

Multiple choice technology

Which type of Statement can execute queries stored on db server?

  1. CallableStatement

  2. ParameterizedStatement

  3. ParameterizedStatement and CallableStatement

  4. All kinds of Statements (i.e. which implement a sub interface of Statement)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

CallableStatement executes stored procedures and functions that reside on the database server. It extends PreparedStatement and accepts parameters including OUT parameters for retrieving values from stored procedures. This allows executing complex logic encapsulated in database routines rather than embedding it in application code.

Multiple choice technology

Which of the following are true ?

  1. The executeUpdate method returns all the rows that were affected

  2. The executeUpdate method returns the no. of rows that were affected

  3. Depends , as there is more than one version of this method with different return types

  4. NONE
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice technology databases

If a table is dropped, which of the following statement is true.

  1. indexes and sequences are automatically dropped, but views and constraints remain

  2. views and indexes are automatically dropped, but constraints and sequences remain

  3. indexes and constraints are automatically dropped, but views and sequences remain

  4. views and sequences are automatically dropped, but indexes and constraints remain
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When a table is dropped, the indexes and constraints associated with that table are automatically dropped. Views and sequences are independent objects and remain intact even after dropping the table.

Multiple choice technology databases

Which of the below statement is worth creating an index

  1. The table is small

  2. The columns are often used as a condition in the query

  3. Most queries are expected to retrieve more than 2 to 4 percent of the rows in the table

  4. The table is updated frequently
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Indexes speed up queries when columns appear frequently in WHERE clauses, JOIN conditions, or ORDER BY statements. Small tables don't benefit enough to justify index overhead, and tables updated frequently suffer from index maintenance costs. When queries return more than 2-4% of rows, a full table scan is usually more efficient than using an index.

Multiple choice technology databases

More indexes on a table is always better for faster queries

  1. True

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

Indexes improve read performance but hurt write performance because each INSERT/UPDATE/DELETE must update both the table and all indexes. More indexes consume more disk space and memory, and the query optimizer spends more time choosing among them. The optimal number of indexes depends on the read/write ratio and query patterns.

Multiple choice technology architecture

Which of the following statements are correct w.r.t. entity bean?

  1. Every entity must have a primary key

  2. It is not necessary to define primary key for an Entity

  3. @Primary annotation is used for denoting a simple primary key

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

To solve this question, the user needs to understand the basics of entity beans and their primary keys.

A) Every entity must have a primary key. This statement is true as the primary key is used to uniquely identify each entity bean in the database. Without a primary key, it would be impossible to distinguish between different instances of the same entity.

B) It is not necessary to define a primary key for an Entity. This statement is false. As mentioned above, every entity must have a primary key. It is not optional.

C) @Primary annotation is used for denoting a simple primary key. This statement is false. @Primary annotation is not a built-in annotation in Java Persistence API (JPA). Instead, the @Id annotation is used to denote a primary key.

D) Option B is incorrect. Therefore, the correct answer is:

The Answer is: A

Multiple choice technology architecture

Which of the following are true about composite primary keys. ?

  1. The primary key class must be Serializable

  2. The application must not change the value of the primary key.

  3. Properties of primary key class must be public or protected.

  4. Primary key class must be public

  5. All the above
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

All four statements are true for composite primary keys in EJB: the primary key class must be Serializable to be persisted, its value must not change after entity creation (it's immutable), properties must be public or protected for container access, and the class itself must be public. Option E correctly captures this.

Multiple choice technology architecture

There is a table called Employee having columns are empno, ename , job , deptno,grade and salary. Which of the following is a correct query to change all emp salary with salary<10000 to 1.5 times and grade to ‘Promo’

  1. Update Employee set salary = salary * 1.5 and grade=’Promo’ where salary < 10000;

  2. Update Employee set salary = salary * 1.5, grade=’Promo’ where salary < 10000;

  3. Update table Employee set salary = salary * 1.5, grade=’Promo’ where salary < 10000;

  4. Update table Employee set salary = salary * 1.5 and grade=’Promo’ where salary < 10000;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL UPDATE syntax requires comma-separated assignments: SET column1 = value1, column2 = value2. Option B uses comma correctly. Option A uses 'and' which is invalid for separating column assignments.

Multiple choice technology architecture

Which type of Statement can execute parameterized queries?

  1. PreparedStatement

  2. ParameterizedStatement

  3. ParameterizedStatement and CallableStatement

  4. All kinds of Statements (i.e. which implement a sub interface of Statement)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To answer this question, the user needs to have a basic understanding of Java Database Connectivity (JDBC) and SQL queries.

The correct answer is:

A. PreparedStatement

Explanation:

PreparedStatement is a subinterface of the Statement interface in JDBC. It allows the execution of parameterized queries, which are SQL statements that contain parameters (placeholders) that can be set with specific values at runtime. Parameterized queries are useful for preventing SQL injection attacks and improving query performance by allowing the database to cache query execution plans.

Option B, ParameterizedStatement, is not a valid interface in JDBC.

Option C, ParameterizedStatement and CallableStatement, is incorrect because CallableStatement is a subinterface of PreparedStatement, not Statement.

Option D, All kinds of Statements, is incorrect because not all types of statements in JDBC can execute parameterized queries. Only PreparedStatement can execute parameterized queries.

Therefore, the correct answer is A. PreparedStatement.

Multiple choice technology architecture

Which type of Statement can execute queries stored on db server?

  1. CallableStatement

  2. ParameterizedStatement

  3. ParameterizedStatement and CallableStatement

  4. All kinds of Statements (i.e. which implement a sub interface of Statement)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

CallableStatement is designed to execute stored procedures on the database server. ParameterizedStatement is not a standard JDBC interface. Statement and PreparedStatement execute SQL, not server-stored queries.

Multiple choice technology architecture

Which of the following are true ?

  1. The executeUpdate method returns all the rows that were affected

  2. The executeUpdate method returns the no. of rows that were affected

  3. Depends , as there is more than one version of this method with different return types

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

The executeUpdate() method in JDBC returns an integer value representing the number of rows affected by the SQL operation (INSERT, UPDATE, DELETE). Option A incorrectly states it returns 'all the rows' rather than the count. Option C is incorrect because while overloaded versions exist, they all return row counts.

Multiple choice technology

Find the false statement.

  1. a) Calculated fields cannot be used as sort criteria

  2. b) Querying on calculated fields is not allowed.

  3. c) Calculated fields are read only

  4. d) None
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Calculated fields in Siebel are computed values that cannot be directly queried because they don't exist as stored columns in the database. They are calculated on-the-fly when displayed. They can be used as sort criteria (option A is false). They are read-only (option C is true, not false). Since option C says something false is 'false', it's incorrect. The question asks for the FALSE statement, and B correctly identifies that querying on calculated fields is not allowed.