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

Below DML Commands supported within the FastExport task

  1. Delete

  2. Select

  3. Update

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

FastExport is a read-only utility designed specifically for high-volume data extraction from Teradata. Only SELECT queries are permitted within FastExport tasks. INSERT, UPDATE, and DELETE are data modification commands that are not supported.

Multiple choice technology

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get the required output?

  1. WHERE prod_id LIKE '%D123%' ESCAPE ''

  2. WHERE prod_id LIKE '%_D123%' ESCAPE ''

  3. WHERE prod_id LIKE '%D123%' ESCAPE '%'

  4. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The LIKE operator with '%_D123%' would match any string containing '_D123', but underscore is a wildcard matching any single character. To search for a literal underscore, you need to escape it using the ESCAPE clause. Option B correctly uses backslash as the escape character (ESCAPE '\') and prefixes the underscore with backslash (_) to treat it as a literal character. Options A and C are incorrect because they don't properly escape the underscore. Option D incorrectly uses '_' as the escape character instead of '\'.

Multiple choice technology

Which two statements are true regarding single row functions?

  1. They accept only a single argument.

  2. .They can be nested only to two levels.

  3. Arguments can only be column values or constants.

  4. They always return a single result row for every row of a queried table.

  5. They can return a data type value different from the one that is referenced.
Reveal answer Fill a bubble to check yourself
D,E Correct answer
Explanation

Single-row functions operate on each row independently and return exactly one result per input row (option D). They can return data types different from their input arguments - for example, TO_CHAR converts numbers to strings, and NVL can return different types based on its first argument (option E). Options A, B, and C are false: single-row functions accept multiple arguments (like SUBSTR), can be nested deeply beyond two levels, and accept expressions, not just column values or constants.

Multiple choice technology

Which two statements are true regarding the USING and ON clauses in table joins?

  1. Both USING and ON clauses can be used for equijoins and nonequijoins.

  2. A maximum of one pair of columns can be joined between two tables using the ON clause.

  3. The ON clause can be used to join tables on columns that have different names but compatible data

  4. The WHERE clause can be used to apply additional conditions in SELECT statements containing the
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

The ON clause allows joining tables on columns with different names as long as data types are compatible (option C) - this is its primary advantage over USING. The WHERE clause can always add additional filter conditions after the JOIN clause (option D). Option A is false: USING only works for equijoins (columns with same name), while ON works for any condition. Option B is false: the ON clause can join on multiple column pairs using AND conditions.

Multiple choice technology

Which statement is true regarding the INTERSECT operator?

  1. It ignores NULL values.

  2. Reversing the order of the intersected tables alters the result.

  3. The names of columns in all SELECT statements must be identical.

  4. The number of columns and data types must be identical for all SELECT statements in the query.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

INTERSECT requires that all SELECT statements in the query have the same number of columns and matching data types (option D) - this is a fundamental requirement for set operations. Option A is false: INTERSECT treats NULL values like any other value and includes them in results. Option B is false: INTERSECT is commutative - order doesn't affect the result set. Option C is false: column names don't need to match; only position and data type must align.

Multiple choice technology

Which is the valid CREATE TABLE statement?
  1. CREATE TABLE emp9$# (emp_no NUMBER(4));
  2. CREATE TABLE 9emp$# (emp_no NUMBER(4));

  3. CREATE TABLE emp*123 (emp_no NUMBER(4));
  4. CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Oracle SQL, table names must start with a letter (A-Z or a-z), not a digit. They can contain letters, digits, and special characters like $, #, and _. Option A starts with 'e' (a letter) and uses allowed special characters, making it valid. Option B starts with digit 9, which is invalid. Option C contains '*' which is not allowed in table names.

Multiple choice technology

Which statements are true regarding tables?

  1. A table name can be of any length.

  2. A table can have any number of columns.

  3. A table and a synonym can have the same name in the same schema

  4. The same table name can be used in different schemas in the same database
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Oracle has specific limits on table structures. Table names cannot be of unlimited length (typically max 30 characters in Oracle). Tables also have a maximum column limit (typically 1000 columns). A table and a synonym CAN share the same name in the same schema because they exist in different namespaces. The same table name CAN be used in different schemas within the same database, as schemas provide name isolation.

Multiple choice technology

Which statements are true regarding constraints?

  1. A foreign key cannot contain NULL values.

  2. A column with the UNIQUE constraint can contain NULL values.

  3. A constraint is enforced only for the INSERT operation on a table.

  4. A constraint can be disabled even if the constraint column contains data.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

To answer this question, the user needs to know about the concept of constraints in databases.

A. This statement is true. If a foreign key column contains null values, it cannot reference any primary key values in the referenced table, resulting in a violation of referential integrity. Hence, a foreign key cannot contain NULL values.

B. This statement is true. A column with the UNIQUE constraint can contain NULL values because the UNIQUE constraint only enforces the uniqueness of non-null values. However, only one null value can be present in the column as multiple null values would not be unique.

C. This statement is false. A constraint is enforced for every operation on a table, including INSERT, UPDATE, and DELETE operations.

D. This statement is true. A constraint can be disabled using the ALTER TABLE statement even if the constraint column contains data. However, disabling a constraint can result in data inconsistency, so it should be done with caution.

Therefore, options A, B, and D are true, and option C is false.

The Answer is: D

Multiple choice technology

Which statements are true regarding views?

  1. A subquery that defines a view cannot include the GROUP BY clause.

  2. A view that is created with the subquery having the DISTINCT keyword can be updated.

  3. .A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be

  4. A data manipulation language (DML) operation can be performed on a view that is created with the
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Views have specific restrictions on updability. A view defining subquery CAN include GROUP BY clause - this is allowed. Views created with DISTINCT keyword are NOT updatable because the DISTINCT makes row identification ambiguous. Views with ROWNUM pseudo-column are NOT updatable because ROWNUM values are not stable. DML operations CAN be performed on simple views that meet updability criteria (no joins, aggregates, DISTINCT, etc.).

Multiple choice technology databases

We can use Group functions in the WHERE clause

  1. True

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

Aggregate (group) functions like COUNT, SUM, AVG, MAX, MIN cannot be used in the WHERE clause. WHERE clause filters individual rows BEFORE grouping. Aggregate functions operate on groups of rows, so they must be used in HAVING clause (which filters after grouping) or in the column list with GROUP BY. This is a fundamental SQL execution order rule.

Multiple choice technology databases

Delete from Employee where grade = ‘Y’” deletes

  1. All rows of the Employee table

  2. Rows of the Employee table where the set grade is ‘Y’

  3. Delete 1 row

  4. Nothing will be deleted
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The DELETE statement 'DELETE FROM Employee WHERE grade = 'Y'' will delete ALL rows from the Employee table where the grade column equals 'Y'. If 50 rows have grade='Y', all 50 are deleted. It does not delete all rows in the table, does not delete exactly 1 row, and it definitely deletes something (rows matching the condition). DELETE, like UPDATE, operates on the entire rowset that matches the WHERE condition.

Multiple choice technology databases

Which keyword used in DML statements prohibits you from changing rows that are not in the sub-query?

  1. CHECK keyword

  2. WITH CHECK keyword

  3. WITH CHECK OPTION keyword

  4. None of the Above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

WITH CHECK OPTION is a view constraint clause that prevents DML operations from creating rows that would be invisible to the view. When you update or insert through a view with WITH CHECK OPTION, the database ensures the modified/inserted row will still appear in the view (i.e., satisfies the view's WHERE clause). This prohibits 'escaping' rows from the view's definition. CHECK keyword alone is for table constraints, WITH CHECK is incomplete syntax.

Multiple choice technology databases

Explicit Defaults can be used in

  1. INSERT statement

  2. UPDATE statement

  3. Both of the Above

  4. None of the Above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Explicit Defaults feature in Oracle allows you to explicitly specify default column values. You can use explicit defaults in INSERT statements (to use default value instead of NULL or specified value) and in UPDATE statements (to reset a column to its default using DEFAULT keyword). This provides explicit control over default value application in both INSERT and UPDATE operations.

Multiple choice technology databases

Which is not a group function?

  1. Count

  2. Avg

  3. Total

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

Total is not a standard SQL aggregate function. The correct function for summing values is SUM, not Total. COUNT, AVG, and MAX are all valid SQL aggregate functions used to perform calculations across multiple rows.