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

which is correct regarding CHECK CONSTRAINT?

  1. check (salary>(select salary from emp where uid=10))

  2. check (salary >(select salary from emp where rowid=10 ))

  3. check (salary >2568)

  4. check (salary >select salary from emp where dno=10)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

CHECK constraints cannot contain subqueries or references to other rows - they must evaluate to a boolean condition using only the current row's values. Option C uses a literal value (2568), making it valid. Options A, B, and D use subqueries, which are not allowed.

Multiple choice technology databases

truncate command

  1. deletes data from table but keeps table structure

  2. deletes data as well table structure

  3. drops table

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

TRUNCATE removes all rows from a table but preserves the table structure and associated objects (indexes, constraints). Unlike DELETE, TRUNCATE is DDL (cannot be rolled back) and is faster for removing all data.

Multiple choice technology databases

A column with varchar datatype is populated with number values. What is the possible output with the default ORDER BY clause?

  1. 1 11 111 2 3 4

  2. 1 2 3 4 11 111

  3. 111 11 4 3 2 1

  4. ERROR due to data type mismatch
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

VARCHAR columns store character data, so ORDER BY uses lexicographic (dictionary) sorting, not numeric. String '11' comes before '2' because '1' < '2'. The sorted order is: 1, 11, 111, 2, 3, 4.

Multiple choice technology databases

deference between union and union all

  1. Union all will remove the duplicate rows from the result set while Union does'nt

  2. Union will remove the duplicate rows from the result set while Union all does'nt

  3. Both Union and Union all will remove duplicate rows from result set

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

The UNION operator combines the results of two queries and removes duplicate rows, whereas UNION ALL combines the results and includes all duplicates, making UNION slower but cleaner.

Multiple choice technology databases

Features of Primary Key are:

  1. Unique

  2. Non Unique

  3. Null

  4. Not Null

  5. All of the above
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

A Primary Key constraint enforces two database integrity rules: every value in the primary key column(s) must be unique, and no value in the primary key column(s) can be null.

Multiple choice technology databases

A column of a table is declared as NUMBER(10,2).For which of the following values oracle returns an error during insertion

  1. 5392845.324

  2. 871039453.1

  3. 97234512.123

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

A column defined as NUMBER(10,2) can hold up to 10 significant digits, with 2 of those to the right of the decimal point (leaving 8 digits for the integer part). '871039453.1' has 9 integer digits, causing an overflow error.

Multiple choice technology databases

Select length(-1) from dual What is the output of the above sql stmt

  1. 0

  2. 1

  3. 2

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

The LENGTH function in Oracle/SQL counts the number of characters in a string expression, including special characters and signs. For LENGTH(-1), the function treats '-1' as a two-character string (minus sign and digit 1), not as a numeric value. The output is 2, not 1 (the digit count) or 0. This demonstrates that LENGTH operates on string representation, not mathematical values.

Multiple choice technology databases

The standard EMP table contains 14 records corresponding to the employess of the corporation.One of those records has a NULL value stored in the MGR column.If Issue the following command on the table:select count(mgr) from emp;which of the following choice indentifies the result oracle will return

  1. <14

  2. >14

  3. 14

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

COUNT(column_name) explicitly ignores NULL values in the specified column. With 14 total rows and one NULL in MGR, COUNT(mgr) returns 13 (not 14). This differs from COUNT(*) which counts all rows regardless of NULL values. Option A correctly states the result is less than 14, though the precise value is 13. The question tests understanding of aggregate function behavior with NULL values.

Multiple choice technology databases

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

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

ROWID access is fastest because ROWID contains the physical disk address of the row, enabling direct retrieval without traversing indexes. Primary key and unique index access require index traversal to find the ROWID first, then row access. Full table scan reads all blocks, making it slowest for single-row retrieval. ROWID is essentially a direct pointer to the row's location on disk.

Multiple choice technology databases

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 3

  3. 2

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

To join N tables without Cartesian products, you need N-1 join conditions. For 4 tables, you need 3 conditions. Each condition links two tables together. With 3 conditions for 4 tables, you create a chain: T1-T2, T2-T3, T3-T4. Fewer than 3 conditions would leave some tables disconnected, creating Cartesian products between disconnected sets. The minimum is n-1 conditions to connect n tables.