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

Which of these following features are correct if SQL is a set-oriented language?

  1. Individual rows must have a unique identifier

  2. Sets of users can be managed in groups

  3. One statement can affect multiple rows

  4. SQL statements can be placed within blocks of code

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

SQL is a set-oriented language because it operates on sets of rows rather than processing rows individually. A single SQL statement can affect multiple rows simultaneously (UPDATE, DELETE, or bulk INSERT), which is the essence of set-oriented processing. This distinguishes SQL from procedural languages that process one record at a time.

Multiple choice technology databases

What are the disadvantages of SQL ?

  1. Cannot drop a field

  2. Cannot rename a field

  3. Cannot manage memory

  4. Index on view or index on index not provided

  5. View updation problem
Reveal answer Fill a bubble to check yourself
A,B,C,D,E Correct answer
Explanation

SQL has several limitations including inability to directly drop or rename columns (historically required workarounds), lack of direct memory management, no indexed indexes or indexed views in early versions, and view update restrictions. These are well-known disadvantages of standard SQL compared to procedural programming languages.

Multiple choice technology databases

What is the result of the following SQL: Select 1 from dual UNION Select 'A' from dual;

  1. Error

  2. 1 & A are retreived

  3. 1 is retreived

  4. A is retreived
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

UNION in Oracle requires compatible data types across the combined result sets. The first SELECT returns numeric (1), the second returns character ('A'). Oracle cannot implicitly convert these to a common type for UNION, causing a type mismatch error. UNION ALL would also fail with the same error.

Multiple choice technology databases

Dual table can be deleted, dropped or altered or updated or inserted ?

  1. True

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

The DUAL table in Oracle is technically a regular table that can be manipulated with DDL/DML operations like DROP, DELETE, ALTER, UPDATE, or INSERT. However, Oracle strongly recommends against modifying DUAL as it's used internally for SELECT statements and system functions. While these operations are possible, altering DUAL can break critical system functionality.

Multiple choice technology databases

For which relational operators in where clause, index is not used ?

  1. <>

  2. like '%...' is NOT functions

  3. field +constant

  4. field||''

  5. AND
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

Oracle indexes are typically bypassed for inequality operators (<>, !=), NOT functions, and expressions that wrap columns in functions or operations. The LIKE operator with a leading wildcard ('%...') prevents index usage, and expressions like field+constant or field||'' (concatenation) similarly disable indexes. The AND operator, when used with sargable (searchable) conditions, can effectively use indexes.

Multiple choice technology databases

Which of the following queries are correct to delete duplicate records in a table?

  1. SELECT * FROM table a WHERE ROWID < (SELECT MAX(ROWID) FROM table b WHERE a.col1 = b.col1);

  2. SELECT * FROM table a WHERE ROWID <= (SELECT MAX(ROWID) FROM table b WHERE a.col1 = b.col1);

  3. SELECT * FROM table a WHERE ROWID > (SELECT MIN(ROWID) FROM table b WHERE a.col1 = b.col1);

  4. SELECT * FROM table a WHERE ROWID >= (SELECT MIN(ROWID) FROM table b WHERE a.col1 = b.col1);
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

To delete duplicate records while keeping one copy, use ROWID comparisons. Options A and C identify duplicates by matching columns (col1) and then keeping records with ROWID less than the MAX or greater than the MIN respectively. Options B and D incorrectly use <= and >=, which would delete all duplicates including the one you want to keep. The subquery finds the group, and the outer query filters based on ROWID position.

Multiple choice technology web technology

Tables can be nested (table inside of another table).

  1. True

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

COBOL supports nested tables ( OCCURS clauses within OCCURS clauses). This allows creation of multi-dimensional arrays and complex data structures. For example, a table of departments can contain nested tables of employees. The nesting can go multiple levels deep, limited only by compiler constraints.

Multiple choice technology databases

What is the limit on the size of a PL/SQL block?

  1. 32K

  2. 64K

  3. 100K

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

PL/SQL blocks have a size limit of 64K. This is a database-specific constraint on the maximum size of code that can be contained in a single PL/SQL block. The limit applies to the compiled code size, not the source text length. This constraint is important for developers writing complex stored procedures or large anonymous blocks.

Multiple choice technology databases

Which of the following are the differences between SQL and PL/SQL?

  1. SQL is executed one statement at a time. PL/SQL is executed as a block of code.

  2. SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).

  3. SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.

  4. You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

All four statements correctly identify key differences between SQL and PL/SQL. SQL is declarative and statement-based, while PL/SQL is procedural and block-based. SQL handles queries and data definition, while PL/SQL handles program logic structures. The embedding relationship is one-directional: SQL can be embedded within PL/SQL, but not vice versa. These distinctions are fundamental for Oracle developers.

Multiple choice technology databases

Can you call a stored function in the constraint of a table ?

  1. True

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

Stored functions cannot be called in table constraints in most database systems including Oracle. Constraints must be deterministic and cannot rely on user-defined functions that may change or have side effects. This restriction ensures data integrity and prevents constraint violations from function changes. Check constraints can only use built-in functions and deterministic expressions.

Multiple choice technology databases

Which scenario below indicates HIGH cardinality column?

  1. Scenario 1 : Distinct values for TableA.ColumnA = 10 Total no. of rows for TableA = 100

  2. Scenario 2 : Distinct values for TableA.ColumnB = 20 Total no. of rows for TableA = 100

  3. Scenario 3 : Distinct values for TableA.ColumnC = 30 Total no. of rows for TableA = 100

  4. Distinct values have no corelation to cardinality
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Cardinality refers to the number of unique values in a table column relative to the total rows. Scenario 3 has the highest ratio of unique values (30 distinct values out of 100 rows) compared to Scenario 1 (10%) and Scenario 2 (20%).

Multiple choice technology databases

Can you change the inserted value in one of the columns in AFTER insert trigger code?

  1. Yes

  2. No

  3. Can't Say

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

In AFTER triggers, the insert operation has already completed and the data is written to the table. The inserted values cannot be modified because the transaction is past the point where changes are allowed. To modify inserted values, you would need an INSTEAD OF trigger or modify the data before insertion using a BEFORE trigger (in databases that support it). AFTER triggers are for post-insert operations like auditing or cascading changes.