Computer Knowledge

Database Management Systems

5,344 Questions

Database Management Systems (DBMS) form the core framework for data storage, retrieval, and security in modern software applications. Concepts such as the E-R model, backup planning, SQL integration, and big data architecture are essential for computer knowledge sections. This hub offers a comprehensive set of practice questions to master DBMS fundamentals and advanced database operations.

E-R Model ConceptsBackup and RecoverySQL Server UpgradesJDBC and ODBCBig Data CharacteristicsData VirtualizationOracle Database

Database Management Systems Questions

Multiple choice technology mainframe

Identify which one is more opt for DBRM?

  1. created by the DB2 precompiler and used in the bind process

  2. created by the cobol pre compiler and used in the bind process

  3. created by the cobol compiler and used in the bind process

  4. created by the DB2 compiler and used in the bind process
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A DBRM (Database Request Module) is created by the DB2 precompiler when it processes embedded SQL statements in application programs. The DBRM contains extracted SQL statements and is used in the bind process to create application plans. COBOL compilers don't create DBRMs - they compile the program logic.

Multiple choice technology web technology

Which of the following denote gateway to the database?

  1. Data links

  2. Connection objects

  3. Both A) and B)

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

In JDBC architecture, Connection objects represent the gateway to the database. They establish the communication channel and create Statement objects for executing queries. Data links (Option A) typically refers to specific linking mechanisms, not the general database gateway concept.

Multiple choice technology databases

Which of the following is not true for Stored Procedures?

  1. Simplifies code reuse, code standardization and code maintenance

  2. Controlled access to other database objects

  3. Improving application performance

  4. DB2 Stored Procedures can be written only in SQL.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Stored procedures simplify code management, provide controlled access to database objects, and improve performance by reducing network traffic. However, DB2 stored procedures can be written in multiple languages (SQL, Java, C, etc.), not just SQL - making statement D false.

Multiple choice technology databases

GET DIAGNOSTICS

  1. Is used to obtain info about the previous executed sql statement

  2. Analyze the db2diag error messages

  3. Returns the user ID calling the stored proc

  4. Gives the access plan of the packages
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

GET DIAGNOSTICS is a SQL statement used to retrieve diagnostic information about the most recently executed SQL statement, including error codes, warnings, and exception conditions. It does not analyze db2diag logs, return user IDs, or provide access plans.

Multiple choice technology databases

CREATE PROCEDURE wise.Test123 (IN VAR1 INTEGER) P1: BEGIN DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR SELECT DISTINCT NO FROM (VALUES (1),(2),(3),(3),(2)) AS TEST (NO); SET VAR1= VAR1 * VAR1 +VAR1; END P1@ What happens when you call wise.Test123 (10)

  1. Return Status = 0

  2. 3 rows returned by result-set

  3. 5 rows returned by result-set

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

The procedure creates a cursor but never opens it, and the SET statement only modifies the input parameter. The procedure returns status 0 (success) but no result set rows because the cursor was never opened. The calculation 10*10+10=110 only affects the local parameter variable.

Multiple choice technology databases

A file rahul.db2 has following content : Drop procedure rahul ( ) @ Create procedure rahul ( ) P1: begin Declare c1 cursor with return to client for Select current date from sysibm.sysdummy1; Open c1; End P1 @ What will following command do when fired on db2 command linedb2 –td@ -f rahul.db2

  1. NO SQL

  2. CONTAINS SQL

  3. READS SQL

  4. MODIFIES SQL
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The script drops a procedure and creates a new one that contains a SELECT statement. Since it modifies database objects (DROP and CREATE), it requires MODIFIES SQL DATA clause. The other options are for less restrictive operations.

Multiple choice technology databases

Which of the following can be a user defined SQLSTATE

  1. 20000

  2. 35000

  3. 60000

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

User-defined SQLSTATE values must be in the range 70000-99999 as per SQL standard. Option 71000 falls within this valid user-defined range, while the other options are either reserved for specific implementations or outside the valid range.

Multiple choice technology databases

A Simple stored procedure is created with Dynamic Result sets 3 However only 2 cursors with return to client are defined within the source code. What will happen when you call the SP ?

  1. Will executes successfully

  2. Fails as limit is 3 but only 2 opened

  3. Executes with warning

  4. Would not allow compiling the SP at the first place
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When DYNAMIC RESULT SETS is set to 3 but only 2 cursors are defined, the procedure executes successfully. The DYNAMIC RESULT SETS clause declares the maximum number of result sets that can be returned, and having fewer cursors than the maximum is perfectly valid.

Multiple choice technology databases

CREATE PROCEDURE p1() BEGIN CREATE TABLE T1 (C1 CHAR); INSERT INTO T1 VALUES ('A'); SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('B'); SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('C'); SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('D'); ROLLBACK TO SAVEPOINT SAVEPOINT3; ROLLBACK TO SAVEPOINT SAVEPOINT1; COMMIT WORK; INSERT INTO T1 VALUES ('E'); END@ Call p1() @ How many rows will be present in Table T1

  1. 0

  2. 1 (A)

  3. 5 (A,B,C,D,E)

  4. 2 (A,E)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The procedure inserts 'A', sets SAVEPOINT1, then inserts 'B', 'C', and 'D' with subsequent savepoints. Rolling back to SAVEPOINT1 removes 'B', 'C', and 'D', leaving only 'A'. After the COMMIT, 'E' is inserted, resulting in two rows: 'A' and 'E'.

Multiple choice technology databases

Which IBM tool can you use to debug stored procedure?

  1. Control centre

  2. IBM Data studio

  3. Visual Explain

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

IBM Data Studio is the primary integrated development environment (IDE) used for developing, debugging, and managing DB2 stored procedures. Other tools like Control Center and Visual Explain do not support stored procedure debugging.

Multiple choice technology databases

The table rows and column are also known as?

  1. Attributes and Fields

  2. Fields and Records

  3. Records and Fields

  4. Fields and Attributes
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In database terminology, horizontal rows are called 'records' (each row represents one complete entry), and vertical columns are called 'fields' (each column represents a specific attribute or property). Records contain the actual data values, while fields define what type of data each column holds.

Multiple choice technology databases

Normalization is…?

  1. A special way of selecting data

  2. The process of adding primary key to a table

  3. The process of arranging information stored in a database in a way, which removes redundancy and ambiguity.

  4. A special way of retrieving data
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Normalization is the process of organizing database tables to reduce redundancy and improve data integrity by splitting large tables into smaller, related ones. It eliminates duplicate data and ensures dependencies are logical, not based on arbitrary rules.