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

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.

Multiple choice technology databases

TRUNCATE commnd fall into which category of SQL statements?

  1. DDL

  2. DML

  3. DCL

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

TRUNCATE is classified as DDL because it resets table storage and cannot be rolled back, unlike DELETE which is DML. DDL commands implicitly commit and modify database structure or metadata. This distinction is crucial for understanding transaction behavior and data recovery options.

Multiple choice technology databases

INSERT, UPDATE, DELETE SQL commands fall into which category of SQL statements?

  1. DDL

  2. DML

  3. DCL

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

Data Manipulation Language (DML) commands directly manipulate data within database objects. INSERT adds new rows, UPDATE modifies existing data, and DELETE removes rows, all operations on the data itself rather than structure. DML operations can be rolled back as part of transaction management.

Multiple choice technology databases

CREATE, ALTER, DROP SQL commands fall into which category of SQL statements?

  1. DDL

  2. DML

  3. DCL

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

DDL (Data Definition Language) commands define and modify database structures like tables, indexes, and schemas. DML (Data Manipulation Language) manipulates actual data with commands like INSERT, UPDATE, DELETE. DCL (Data Control Language) controls access permissions (GRANT, REVOKE), while TCL (Transaction Control Language) manages transactions (COMMIT, ROLLBACK).