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

Evaluate the following SQL statement:

SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales WHERE e.employee_id = s.emp_id;

What will happen if all the parentheses are removed from the calculation?

  1. The value displayed in the CALC_VALUE column will be lower

  2. The value displayed in the CALC_VALUE column will be higher

  3. There will be no difference in the value displayed in the CALC_VALUE column

  4. An error will be reported
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL operator precedence ensures multiplication always happens before addition, regardless of parentheses. The expression (.15*e.salary) + (.5*e.commission_pct) + (s.sales_amount * (.35*e.bonus)) evaluates identically without parentheses because all multiplication operations occur before any addition operations. The nesting of parentheses doesn't change this fundamental precedence rule. Options A and B are incorrect because the value remains the same. Option D is incorrect because no error occurs.

Multiple choice

Which SQL phrase is not supported by Oracle?

  1. ON DELETE CASCADE

  2. ON UPDATE CASCADE

  3. CREATE SEQUENCE [SequenceName]

  4. DROP SEQUENCE [SequenceName]
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle SQL supports ON DELETE CASCADE for foreign key constraints, which automatically deletes child rows when the parent row is deleted. However, Oracle does not support ON UPDATE CASCADE - this is a MySQL-specific feature. If you need to update primary key values, you must manually update all dependent foreign key values or recreate the rows. Options A, C, and D are all supported Oracle SQL statements.

Multiple choice

What MySQL property is used to create a surrogate key in MySQL?

  1. UNIQUE

  2. SEQUENCE

  3. AUTO_INCREMENT

  4. None of the above -- Surrogate keys are not implemented in MySQL
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

AUTO_INCREMENT is the MySQL property used to create surrogate keys. It automatically generates a unique numeric value for each new row, typically used for primary key columns. UNIQUE is a constraint that ensures column values are distinct but doesn't auto-generate values. MySQL doesn't use SEQUENCE objects like some other databases (Oracle, PostgreSQL).

Multiple choice

What MySQL property is used to create a surrogate key in MySQL?

  1. UNIQUE

  2. SEQUENCE

  3. AUTO_INCREMENT

  4. None of the above -- Surrogate keys are not implemented in MySQL
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

AUTO_INCREMENT is the MySQL property used to create surrogate keys. It automatically generates a unique numeric value for each new row, typically used for primary key columns. UNIQUE is a constraint that ensures column values are distinct but doesn't auto-generate values. MySQL doesn't use SEQUENCE objects like some other databases (Oracle, PostgreSQL).

Multiple choice

Which of the following statements is /are false?

  1. A cursor defines a private area in memory, when SQL statements are executed.

  2. Implicit cursor are created, when any DML operation occurs in the database.

  3. Explicit cursors are executed by default.

  4. Explicit cursors can process only one record at a time.

  5. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Explicit cursors are not executed by default. They are executed when SELECT statement is used to return more than one row.

Multiple choice

Which of the following statements is/are not true?

  1. Triggers are executed automatically when any associated DML operation is executed.

  2. In row level trigger, an even is fired when any row is updated.

  3. Cyclic cascading in triggers can crash the database.

  4. DESC USER_TRIGGERS function is used to get information about the trigger.

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

Correct choice

Multiple choice

What is/are the advantage(s) of using stored procedure in SQL?

  1. Stored procedure reduces network traffic.

  2. Stored procedures provide security to the database.

  3. Stored procedure provides maintainability to the application.

  4. Stored procedures are limited to T/SQL only and produce somewhat complexity.

  5. (1), (2) and (3) all are true.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, all of these are the advantages of stored procedure.

Multiple choice

Which of the following statement(s) is/are incorrect?

  1. A primary key cannot be NULL.

  2. A foreign key can be NULL.

  3. A foreign key can have the same name as primary key.

  4. A foreign key can have the name other than the primary key.

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

This is the right choice as all the above statements are true.

Multiple choice

Which of the following statement(s) is/are true?

  1. A view in database provides security.

  2. A view can hide the complexity of data in the database.

  3. A view can act as aggregate tables.

  4. A view can join multiple tables and simplify them as a single table.

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

This is the correct choice as all of the above choices are correct.

Multiple choice

Which of the following statement(s) is/are false?

  1. Transitive dependency exists in the 3rd normal form of database.

  2. 1st normal form ensures that there should be a unique identifier for each row.

  3. 3rd normal form should be in 2nd normal form.

  4. Normalisation is used to eliminate redundant data.

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

No, it is not true as 3rd normal form is used to remove the transitive dependency from the database tables.

Multiple choice

Which of the following rules of the E.F. Codd ensures that a user view should be unchanged in case of changing the table structures of the database?

  1. Logical data independence

  2. Physical data independence

  3. Integrity independence

  4. View updation rule

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

Yes, this is the rule given by E.F. Codd determines that the user view should not change despite the logical structures (splitting of tables) has been changed.

Multiple choice

Which of the following is/are true statement(s)?

  1. Indexes are used to retrieve the rows of the database table quickly.

  2. Indexes are not useful for the tables having less data.

  3. The check constraints in SQL defines the business rules that should be follow by all the rows of the database.

  4. Both (1) and (3) are true.

  5. All (1), (2) and (3) are true.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, all the three statements are true.