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

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

COUNT(DISTINCT expr) returns

  1. No. of rows in a table

  2. No. of rows with non-null values of the expr

  3. No. of distinct non-null values of the expr

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

COUNT(DISTINCT expr) counts the number of unique, non-null values in the specified expression. NULL values are ignored, and duplicate values are counted only once. This is different from regular COUNT(*) which counts all rows including NULLs in some contexts.

Multiple choice technology databases

Which function forces group functions to include null values?

  1. Group By

  2. NVL

  3. Order By

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

Aggregate functions like COUNT, SUM, AVG ignore NULL values by default. The NVL function converts NULL values to a specified value, effectively forcing them to be included in the aggregation. GROUP BY, ORDER BY, and HAVING are clauses, not functions for handling NULL values.

Multiple choice technology databases

Which is the clause used to restrict groups?

  1. HAVING

  2. WHERE

  3. Order By

  4. Group by
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

HAVING is used to filter groups after aggregation, similar to how WHERE filters individual rows before aggregation. You cannot use WHERE with aggregate functions - HAVING is the correct clause for restricting groups based on aggregate conditions.

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

Group functions (aggregate functions) cannot be used in the WHERE clause because WHERE operates on individual rows before grouping. To filter based on aggregate values, you must use HAVING after the GROUP BY clause. This is a fundamental SQL execution order rule.