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

NULL values violate CHECK constraint

  1. True

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

CHECK constraints only evaluate to TRUE or FALSE, but they are not evaluated when the value is NULL. NULL values bypass CHECK constraint validation entirely, so they never violate the constraint.

Multiple choice technology databases

Which is the syntax for enforcing the rule of deleting the dependent Foreign Key values on the deletion of a Primary Key?

  1. ON DELETE SET NULL

  2. ON DELETE CASCADE

  3. ON DELETE DELETE

  4. ON DELETE RESTRICT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To answer this question, the user needs to have knowledge of database management systems and the concept of Foreign Keys.

When a Foreign Key references a Primary Key in another table, there is a possibility that the referenced record(s) may be deleted. In such cases, four actions can be taken: SET NULL, CASCADE, DELETE, or RESTRICT.

  • ON DELETE SET NULL: This option sets the Foreign Key value to NULL when the referenced record(s) are deleted. This may lead to data integrity issues and is not ideal in most cases.

  • ON DELETE CASCADE: This option deletes all the dependent rows in the child table when the referenced rows in the parent table are deleted. This is usually the preferred option when the child rows are no longer relevant without the parent rows.

  • ON DELETE DELETE: This syntax does not exist, and hence, it is incorrect.

  • ON DELETE RESTRICT: This option prevents the deletion of the referenced rows in the parent table when there are dependent rows in the child table. This is useful when the data in the child table is still relevant and associated with the data in the parent table.

Therefore, the correct answer is:

The Answer is: B. ON DELETE CASCADE

Multiple choice technology databases

TRANSLATE

  1. Restricts the length of the String

  2. Replaces a sequence of characters in a string with another set of characters

  3. Modifies the datatype of a column

  4. Renames a column
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The TRANSLATE function in SQL replaces a sequence of individual characters in a string with another corresponding set of individual characters, mapping them one-to-one.

Multiple choice technology databases

How is IN different form EXISTS ?

  1. IN executes the inner query for each row affected by the outer query and EXISTS executes the inner query only once irrespective of the outer query

  2. IN executes the inner query only once irrespective of the outer query and EXISTS executes the inner query only once irrespective of the outer query

  3. IN executes the inner query only once irrespective of the outer query and EXISTS executes the outer query for each row affected by the outer query

  4. IN executes the inner query for each row affected by the outer query and EXISTS executes the outer query for each row affected by the outer query
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The IN operator evaluates the subquery once to get a list of values, whereas EXISTS evaluates the subquery for each row processed by the outer query to check for the existence of a match.

Multiple choice technology databases

_____ and _____ are true about the Primary Index

  1. Defined immediately after the table CREATE statement, used to find the Primary Key

  2. Defined during the table CREATE statement, used to assign rows to an AMP

  3. Defined immediately after the table CREATE statement, used to assign rows to an AMP

  4. Defined during the table CREATE statement, used to find the Primary Key
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Primary Index is defined within the CREATE TABLE statement (not after). Its core purpose is to distribute rows to AMPs via the hashing algorithm. It is not used to find the Primary Key - PK is for uniqueness and relationships.

Multiple choice technology databases

_____ and _____ are true about the Primary Index

  1. Defined immediately after the table CREATE statement, used to assign rows to an AMP

  2. Defined immediately after the table CREATE statement, used to find the Primary Key

  3. Defined during the table CREATE statement, used to assign rows to an AMP

  4. Defined during the table CREATE statement, used to find the Primary Key
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Teradata, the Primary Index (PI) is defined during the table CREATE statement. It is used by the hashing algorithm to assign and distribute table rows across Access Module Processors (AMPs).

Multiple choice technology databases

Which of the query will print the output as 'TCS' (single quotes should come in the output of the query)

  1. select '''||'TCS'||''' from dual

  2. select 'TCS' from dual

  3. select '''TCS''' from dual

  4. select "TCS" from dual
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Oracle SQL, a single quote is escaped by doubling it. The expression '''||'TCS'||''' concatenates an escaped single quote, the string 'TCS', and another escaped single quote, resulting in 'TCS'.

Multiple choice technology databases

Which of the query will print the output as 'TCS' (single quotes should come in the output)

  1. select '''||'TCS ||''' from dual

  2. select '''TCS''' from dual

  3. select "TCS" from dual

  4. select 'TCS' from dual
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To output literal single quotes around TCS, use two consecutive single quotes: '''TCS'''. In Oracle SQL, a single quote within a string is escaped by doubling it. The outer quotes define the string, inner pairs become literal quotes in output.