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

select INSTR('TCS_SQL','S',4) from dual;

  1. 3

  2. 6

  3. 5

  4. 4
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

INSTR('TCS_SQL','S',4) searches for 'S' starting from position 4 in 'TCS_SQL'. Positions 1-3 are 'T', 'C', 'S' (the first S). From position 4, the string is '_SQL', where 'S' is at position 5 of the original string (second S in the string).

Multiple choice technology programming languages

Which clause should you use to exclude group results?

  1. WHERE

  2. HAVING

  3. RESTRICT

  4. GROUP BY

  5. ORDER BY
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To answer this question, the user needs to understand the purpose of each of the SQL clauses listed and how they are used in a query.

  • WHERE: This clause is used to filter rows of data based on a specified condition or set of conditions. It is used to limit which rows are selected from a table.

  • HAVING: This clause is used to filter the results of an aggregate function applied to a group of rows. It is used to limit which groups are displayed based on a specified condition or set of conditions.

  • RESTRICT: There is no such SQL clause as RESTRICT.

  • GROUP BY: This clause is used to group rows of data based on one or more columns. It is used in conjunction with aggregate functions to calculate results for each group of data.

  • ORDER BY: This clause is used to sort the results of a query by one or more columns in ascending or descending order.

Based on the above information, the correct answer to the question is:

The Answer is: B. HAVING.

The HAVING clause is used to filter the results of an aggregate function applied to a group of rows. It is used to limit which groups are displayed based on a specified condition or set of conditions. Therefore, it can be used to exclude group results that don't meet the specified condition(s).

Multiple choice technology programming languages

Which constraint can be defines only at the column level?

  1. UNIQUE

  2. NOT NULL

  3. CHECK

  4. PRIMARY KEY

  5. FOREIGN KEY
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

NOT NULL is strictly a column-level constraint that prevents null values in a specific column and cannot be defined at the table level. UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY can all be defined at either column level (for single columns) or table level (for multiple columns or composite constraints).

Multiple choice technology databases

Select odd item from the list.

  1. VARCHAR2

  2. CHAR

  3. NCHAR

  4. VARCHAR
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

VARCHAR is the ANSI SQL standard character type, while VARCHAR2 and CHAR are Oracle-specific. NCHAR is Oracle's Unicode character type. VARCHAR stands out as the odd one because it's the standard SQL type rather than Oracle's proprietary extension.

Multiple choice technology databases

CHAR is not compatible with.

  1. NCHAR

  2. DATE

  3. VARCHAR2

  4. BLOB
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

CHAR is for fixed-length character strings. NCHAR and VARCHAR2 are also character types compatible with CHAR. DATE is compatible in some contexts (implicit conversion). BLOB is for binary data and has no compatibility with character types like CHAR.

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.