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

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

Which Business Component Field Property when set to TRUE prevents the user from editing the value?

  1. a) Validation

  2. b) Read Only

  3. c) Required

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

The Read Only property, when set to TRUE, locks a field so users cannot modify its value. This is commonly used for system-generated fields or values that should only be changed through specific processes. Validation checks data correctness, Required enforces data entry, and Force case controls text formatting - none of these prevent editing.

Multiple choice technology

Which Business Component Field Property automatically assigns a value to a field for a new record?

  1. a) PreDefault

  2. b) Post Default

  3. c) Validation

  4. d) Required
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The PreDefault property assigns values automatically when creating new records, before users interact with the field. PostDefault assigns values after record creation but before commit. Validation and Required serve different purposes - checking data correctness and enforcing mandatory entry respectively.

Multiple choice technology

Find the false statements.

  1. a) A Primary Foreign Key is a foreign key on the parent-side that points to one “primary” record on the child -side of a one-to-many relationship

  2. b) A primary FK creates a one-to-many relationship between the parent and the child

  3. c) A primary FK allows the one initial query to join in one child record per MVG per parent record and display it

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

The question asks which statement is false. Option B states 'A primary FK creates a one-to-many relationship between the parent and the child' - this is FALSE. A Primary Foreign Key points from the CHILD side to the PARENT (one child has one primary parent), creating a many-to-one relationship from child perspective, not one-to-many. The one-to-many exists from parent to child, but the FK itself doesn't create it - the relationship exists first, then FK identifies which child is primary. Options A and C correctly describe primary FKs.

Multiple choice technology

_____ table holds pick list values for all static pick lists

  1. a) S_CONTACT

  2. b) S_LST_OF_VAL

  3. c) S_USERS

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

The S_LST_OF_VAL table (Siebel List of Values) stores all static picklist values in Siebel. Each row represents one picklist value with columns like VAL (display value), TYPE (picklist name), LANG (language), and ACTIVE/SORT order. S_CONTACT stores contact records, S_USERS stores user accounts - neither holds picklist data. Option D 'None' would imply no dedicated table exists, which is false.