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

Your company has created a custom application that will require the use of several stored procedures. The lifetime of this application is relatively short and you wish to create the stored procedures such that they can all be dropped with a single DROP PROCEDURE statement command at the end of the application's lifetime. How can you group the procedures together such that they can all be dropped with a single DROP PROCEDURE statement?

  1. Create each procedure with a distinct name followed by a semi-colon and a group name.

  2. Create each procedure with a distinct name followed by a semi-colon and a group number.

  3. Create each procedure with the same name followed by a semi-colon and a group name.

  4. Create each procedure with the same name followed by a semi-colon and a distinct number.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SQL Server, stored procedures can be grouped by giving them the same name followed by a semicolon and a distinct number (e.g., 'procname;1', 'procname;2'). This allows dropping the entire group with a single DROP PROCEDURE statement. Options A and B are wrong - the names must be identical, not distinct. Option C is wrong because it needs distinct numbers, not a group name.

Multiple choice technology databases

EasyYatra is a travel booking company that has a web site available for customers to view and book their vacations online. You have been hired to look after the back-end SQL Server that is used to store customer and booking information. You are creating a table called Booking to keep track of vacations booked by customers. This table will contain a computed column called Total that is derived from other columns in the table. You want to be able to create an index on this Total column to increase performance when any queries are executed using this column. To do this, you want to physically store the computed column in the table. How can this be done?

  1. Mark the computed column as PERSISTED.

  2. Define the computed column as NULL.

  3. Specify the collation for the column using COLLATE Physical.

  4. Do nothing. Computed columns are stored in the table by default.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

By marking a computed column as PERSISTED, SQL Server physically stores the computed values in the table and updates them when dependent columns change, allowing you to create indexes on it.

Multiple choice technology programming languages

ORA-01017 ?

  1. Oracle not available (the database is down)

  2. Invalid Username/Password

  3. Snapshot too old (Rollback has been overwritten)

  4. Unique constraint violated
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ORA-01017 specifically means 'invalid username/password; logon denied' - Oracle cannot authenticate the credentials provided. This occurs when the username doesn't exist, the password is wrong, or the account is locked/expired. Option A would be ORA-03114 (not connected), Option C is ORA-01555 (snapshot too old), and Option D is ORA-00001 (unique constraint).

Multiple choice technology programming languages

ORA-00001 Means?

  1. PL/SQL Error

  2. No data found

  3. Insufficient privileges

  4. Unique constraint violated. (Invalid data has been rejected)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ORA-00001 is Oracle's error for 'unique constraint violated' - it fires when an INSERT or UPDATE tries to create a duplicate value in a column with a UNIQUE constraint or PRIMARY KEY. The parenthesized text clarifies that the offending data was rejected. Other options are different errors: ORA-06500 is PL/SQL error, ORA-01403 is no data found, ORA-00990 is insufficient privileges.

Multiple choice technology programming languages

ORA-00001 Means?

  1. PL/SQL Error

  2. No data found

  3. Insufficient privileges

  4. Unique constraint violated. (Invalid data has been rejected)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ORA-00001 is Oracle's error for 'unique constraint violated' - it fires when an INSERT or UPDATE tries to create a duplicate value in a column with a UNIQUE constraint or PRIMARY KEY. The parenthesized text clarifies that the offending data was rejected. Other options are different errors: ORA-06500 is PL/SQL error, ORA-01403 is no data found, ORA-00990 is insufficient privileges.

Multiple choice technology programming languages

ORA-03114 Means?

  1. Not connected to ORACLE

  2. Table or view does not exist

  3. Internal error (contact support)

  4. None Of The Above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

ORA-03114 means 'not connected to ORACLE' - the client has no active database connection. This occurs when trying to execute SQL after the connection was closed, timed out, or never established. Option B is ORA-00942 (table/view doesn't exist), Option C is ORA-00600 (internal error). This is a connectivity/state error, not a SQL logic error.

Multiple choice technology programming languages

ORA-03114 Means?

  1. Not connected to ORACLE

  2. Table or view does not exist

  3. Internal error (contact support)

  4. None Of The Above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

ORA-03114 means 'not connected to ORACLE' - the client has no active database connection. This occurs when trying to execute SQL after the connection was closed, timed out, or never established. Option B is ORA-00942 (table/view doesn't exist), Option C is ORA-00600 (internal error). This is a connectivity/state error, not a SQL logic error.

Multiple choice technology programming languages

1) Does hibernate allow mixing table-per-class hierarchy and table-per-subclass strategies?

  1. a) Yes

  2. b) No

  3. c) can't say

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

Hibernate requires choosing one inheritance strategy per hierarchy. You cannot mix table-per-class (union subclass) and table-per-subclass (joined subclass) within the same hierarchy. Each persistent hierarchy must use a single strategy.

Multiple choice technology mainframe

How does SUM FIELDS = NONE work?

  1. Sum up value in the fields specified in the SORT FIELDS statement and keep it as only one occurence in the o/p

  2. Removes the duplicates for the fields specified in the SORT FIELDS statement and keeps only the 1st occurence of it

  3. Works only for those records in the i/p file where no duplicates for the fields specified in SORT FIELDS statement,

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

SUM FIELDS = NONE is a SORT statement (in JCL and similar utilities) that removes duplicate records. It keeps only the first occurrence of records for the specified sort fields and eliminates subsequent duplicates. It does not sum values - that would require SUM FIELDS = fieldname. The operation applies to all input records, not just non-duplicate ones.

Multiple choice technology databases

In which scenario would an index be most useful?

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Indexes are most effective on columns with high cardinality (wide range of unique values). Low-cardinality columns (like boolean flags or status enums) benefit less from indexing because the index doesn't significantly reduce the search space.