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

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. 2

  3. 3

  4. 4

  5. 5

  6. There is no such criteria
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To join n tables without Cartesian products, you need at least (n-1) join conditions in the WHERE clause (or using ANSI JOIN syntax). For 4 tables, this means 3 conditions linking them in a chain (e.g., T1 join T2, T2 join T3, T3 join T4). Without adequate join conditions, the database creates a Cartesian product, resulting in every possible row combination (multiplying all row counts).

Multiple choice technology databases

Which of the following Sql statements is correct?

  1. TRUNCATE * from TABLE Sales

  2. TRUNCATE TABLE Sales

  3. TRUNCATE Sales TABLE

  4. TRUNCATE * FROM Sales
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The TRUNCATE TABLE statement is the standard SQL command used to delete all rows from a table efficiently. Syntax variations containing asterisks or placing the table keyword after the table name are incorrect syntax errors in standard database systems, making the other options wrong.

Multiple choice technology databases

What is true about Sequences?

  1. Once created a sequence belongs to a specific schema.

  2. Once created, a sequence is linked to a specified table.

  3. Once created a sequence is automatically available to all users.

  4. Only the DBA can control which sequence is used by a certain table.

  5. Once created a sequence is automatically used in all INSERT and UPDATE statements.
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Sequences in Oracle are independent schema objects (not tied to specific tables) that generate unique numeric values. Once created in a schema with appropriate permissions, sequences can be referenced and used by multiple users, tables, and applications through explicit calls (sequence.NEXTVAL, sequence.CURRVAL). They are not automatically applied to any operations.

Multiple choice technology databases

PRODUCT ======= Product ID Product Description Manufacturer ID MANUFACTURER ============ Manufacturer ID Manufacturer Name Referring to the above table, what type of relationship exists between the Product table and the Manufacturer table?

  1. Product - Many Manufacturer - Many

  2. Product - One or Many Manufacturer - One or Many

  3. Product - Many Manufacturer - One

  4. Product - One Manufacturer - One

  5. Product - One Manufacturer - Many
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Product table has a foreign key (Manufacturer ID) referencing the Manufacturer table's primary key. One manufacturer can produce many products (1:M from manufacturer's side), and each product belongs to exactly one manufacturer. This is a classic one-to-many relationship where the 'many' side is the Product table. Option A describes M:M, D describes 1:1, and E reverses the relationship.

Multiple choice technology databases

Which statements below are correct for TRUNCATE command

  1. Command is used to delete all the rows of the table.

  2. Command is used to delete complete table structure.

  3. Command free the space used by the table.

  4. Command deletes all the constraints,relations and previleges with other tables
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

TRUNCATE is a DDL command that removes all rows from a table efficiently without logging individual row deletions. It deallocates the space used by the table (though the exact behavior varies by DBMS). TRUNCATE does NOT delete the table structure itself - that would be DROP TABLE. It also does not delete constraints, relationships, or privileges - those remain intact.

Multiple choice technology databases

Which statements are correct for Primary and Unique key constraint?

  1. Primary key constraint creates clustered index by default.

  2. Unique key constraint creates clustered index by default.

  3. Primary key constraint allows null values.

  4. Unique key constraint allows null values
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

Primary key constraints uniquely identify each row and create a clustered index by default in SQL Server (for optimal query performance). Primary keys do NOT allow null values. Unique key constraints enforce uniqueness but create non-clustered indexes by default. Unlike primary keys, unique constraints DO allow one null value per column in SQL Server (though not all RDBMS behave this way).

Multiple choice technology databases

<> and != both are same operator into SQL ?

  1. True

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

Both <> (not equal) and != (not equal) are standard SQL operators that test for inequality. They are functionally equivalent and return the same results. Different SQL implementations may prefer one syntax over the other (<> is ANSI standard, != is common in many systems), but both work in most modern databases.

Multiple choice technology databases

SELECT * FROM r4r_emp; Above is part of DML in SQL?

  1. True

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

SELECT is a DML (Data Manipulation Language) command in SQL. DML includes commands like SELECT, INSERT, UPDATE, and DELETE that manipulate data stored in tables. The query shown retrieves data from the r4r_emp table, which is a classic DML operation. DDL (Data Definition Language) commands like CREATE, ALTER, DROP modify database structures, not data.

Multiple choice technology databases

A tablespace hold objects from different schemes

  1. True

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

A tablespace in Oracle database can hold database objects (tables, indexes, etc.) from different schemas. A schema is a logical grouping of objects owned by a user, while a tablespace is a physical storage location. Multiple schemas can store their objects in the same tablespace, and objects from one schema can be distributed across multiple tablespaces. This is correct.

Multiple choice technology mainframe

INITIALIZE statement cannot load a variable-length table (one that was defined using OCCURS DEPENDING ON)

  1. True

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

In COBOL, the INITIALIZE statement is restricted from processing variable-length tables defined with the OCCURS DEPENDING ON clause because their size changes dynamically at runtime. The compiler cannot determine memory allocation or element count in advance, making direct initialization invalid. Therefore, the statement is true, and the false option is incorrect.

Multiple choice technology performance

What is selectivity of an Index

  1. Ratio of number of distinct values in column/columns to the total number of rows in the table

  2. Ratio of total number of rows in the table to the number of distinct values in column/columns

  3. Ratio of distinct values in the table to the total number of rows in the table

  4. Total number of rows
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Index selectivity is defined as the ratio of distinct values in a column to the total number of rows. Higher selectivity (closer to 1) means the index is more effective because each distinct value points to fewer rows. Option A correctly states this definition. Option B inverts the ratio, and Options C and D are incorrect formulations.