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

Can Check constraint be used for self referential integrity ?

  1. Yes

  2. No

  3. Not in Oracle

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

CHECK constraints can enforce conditions on columns including self-referential logic within the same row. A CHECK constraint can reference other columns in the same table to validate row-level data integrity. For example, CHECK (end_date >= start_date) ensures referential integrity within the row itself.

Multiple choice technology databases

Is the below query allowed?Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15));

  1. Yes

  2. No

  3. Not in Oracle

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

The SELECT-INTO statement in PL/SQL can fetch values into a record variable. When x is a record with components matching the selected columns (sal as NUMBER(4) and ename as CHAR(15)), this is perfectly valid syntax. The query will populate the record fields with the retrieved values.

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 databases

Which column is more skewed? Total no. of rows in the table are 1000

  1. Deptno column has 5 distinct values, each having 200 rows

  2. Grade column has 2 distinct values having 1 and 999 rows

  3. Location column has 3 distinct values having 300, 400 and 300 rows

  4. All columns have same skewness
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Skewness measures data distribution unevenness. Grade column with 1 and 999 rows is extremely skewed - 99.9% of rows have one value. Deptno (200 each) and Location (300,400,300) have much more even distributions. Higher skewness means more uneven distribution, which affects query optimization and statistics collection.

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.