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

Which of the following will DELETE all of the rows from table T03?

  1. DELETE * FROM TABLE T03

  2. DELETE ALL FROM T03

  3. DELETE * FROM T03

  4. DELETE FROM T03
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

DELETE FROM T03 is the correct SQL syntax to delete all rows from a table. Options A and C incorrectly use DELETE * which is invalid syntax - DELETE operates on rows, not columns. Option B incorrectly uses DELETE ALL which is not standard SQL syntax.

Multiple choice technology databases

Which of the following can NOT be done using the ALTER TABLE statement?

  1. Add a new column

  2. Drop a check constraint

  3. Change a column's name

  4. Change the length of a VARCHAR column
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In DB2, renaming a column must be done using the RENAME COLUMN statement rather than ALTER TABLE. Conversely, adding new columns, dropping check constraints, and changing VARCHAR length are standard operations that are supported directly via the ALTER TABLE statement.

Multiple choice technology databases

Which of the following will be a consequence of defining the column IDCOL2 in TABLE2 as a foreign key referencing the primary key (IDCOL1) of TABLE1?

  1. DB2 will no longer allow updating the value of IDCOL1 in TABLE1.

  2. When inserting a row in TABLE2, the only values that DB2 will allow for IDCOL2 are the existing values of IDCOL1.

  3. When inserting a row in TABLE2, DB2 will only allow foreign values for IDCOL2, that is values which do not exist in IDCOL1.

  4. When a SELECT statement joins TABLE1 with TABLE2, DB2 will automatically add the condition TABLE1.IDCOL1=TABLE2.IDCOL2 if not specified in the statement
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A foreign key constraint ensures that when inserting a row into TABLE2, the value for IDCOL2 must match an existing value in the referenced primary key IDCOL1 of TABLE1. This is referential integrity - preventing orphaned references. Option A is incorrect because IDCOL1 can be updated if cascade rules are defined. Option C describes the opposite behavior. Option D is incorrect - joins don't automatically add conditions.

Multiple choice technology databases

Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. COL1 is also used to perform arithmetic operations. Which of the following would be the most appropriate DB2 data type to use for column COL1?

  1. INTEGER

  2. REAL

  3. NUMERIC(7, 3)

  4. DECIMAL(10, 3)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

DECIMAL(10, 3) can store values up to 9999999.999 (10 total digits, 3 after decimal) and supports arithmetic operations. INTEGER doesn't support decimals, REAL is floating-point with potential precision loss, and NUMERIC(7, 3) only supports up to 9999.999 which is insufficient for the required range.

Multiple choice technology databases

A sequence was created with the DDL statement shown below: CREATE SEQUENCE my_seq START WITH 5 INCREMENT BY 5 CACHE 5 User1 successfully executes the following statements in Connections VALUES NEXT VALUE FOR my_seq INTO :con1hvar VALUES NEXT VALUE FOR my_seq INTO :con1hvar User2 successfully executes the following statement in Connection2: VALUES NEXT VALUE FOR my_seq INTO :con2hvar After User1 & User2 are finished, User3 executes the following statement in Connection3: SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1 Which value will be returned by the query?

  1. 20

  2. 25

  3. 50

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

In a DB2 data sharing environment, sequence caches are allocated per member. Connection 1 caches the first 5 values (5 to 25) and uses 5 and 10. Connection 2 caches the next 5 values (30 to 50) and uses 30. Connection 3 caches 55 to 75, returning 55.

Multiple choice technology databases

Given the following DDL and INSERT statements: CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 > 10; CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 100; INSERT INTO v1 VALUES(5); INSERT INTO v2 VALUES(5); INSERT INTO v3 VALUES(20); INSERT INTO v3 VALUES(100); How many of these INSERT statements will be successful? What is the expected sequence of value returned from the below query? SELECT Product-ID, Quantity from Customer ORDER BY Product-ID;

  1. 0

  2. 1

  3. 2

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

This question contains two separate questions - the first about INSERT success count and another about query results. For the INSERT question: View v1 has a filter (col1 > 10) but no CHECK OPTION, so INSERT INTO v1 VALUES(5) succeeds despite violating the filter. View v2 uses WITH CASCADED CHECK OPTION, which means the INSERT must satisfy both v2's filter and all underlying views' filters, so INSERT INTO v2 VALUES(5) fails. View v3 inherits the check option from v2, so INSERT INTO v3 VALUES(20) succeeds (meets both conditions) but INSERT INTO v3 VALUES(100) fails (violates v1's > 10 constraint). Thus exactly 2 INSERTs succeed.

Multiple choice technology databases

Which of the following is not true about XML Columns ?

  1. Data can be retrieved by SQL

  2. Data can be retrieved by XQUERY

  3. XML Columns must be altered to accommodate additional parent and child relationships

  4. Access to any portion of an XML document can be direct ,without reading whole document
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

XML columns in DB2 store XML documents as parsed hierarchical structures. The beauty of XML columns is their flexibility - they can accommodate any XML structure without requiring schema changes when new parent-child relationships are added. This is a key advantage over relational columns which would require ALTER TABLE statements. Options A, B, and D are all true about XML columns: they support both SQL and XQUERY retrieval, and allow direct access to XML document portions without reading the entire document.

Multiple choice technology databases

Which of the following constraint types can be used to ensure the value Of an INTEGER column references only positive values.

  1. Unique

  2. Check

  3. Referential

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

A CHECK constraint is used to enforce domain integrity by validating that column values satisfy a specified condition. To ensure an INTEGER column contains only positive values, you would define a CHECK constraint such as CHECK (column_name > 0). Option A (Unique) ensures only that all values are different but doesn't restrict them to positive values. Option C (Referential) is for foreign key relationships with other tables. Option D (Informational) is not a standard constraint type in DB2.

Multiple choice technology databases

Given that tables T1 and T2 contain the following rows : Table T1 : C1 C2 (5 4) (5 2) (5 5) Table T2 : C1 C2 (5 1) (5 2) (5 3) Which of the following queries will return only those rows that exists in T1 and not in T2 ?

  1. Select * from T1 minus select * from T2

  2. Select * from T1 except select * from T2

  3. Select * from T1 union except select * from T2

  4. Select * from T1 not exists select * from T2
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To find rows that exist in T1 but not in T2, you need a set difference operation. In SQL, the EXCEPT operator returns all rows from the first query that are not present in the second query. Option B is correct: SELECT * FROM T1 EXCEPT SELECT * FROM T2 will return only the rows from T1 that don't have matching rows in T2. Option A uses MINUS which is Oracle syntax, not DB2. Option C is invalid syntax - UNION and EXCEPT cannot be combined that way. Option D is incorrect because NOT EXISTS requires a correlated subquery, not a standalone SELECT.

Multiple choice technology databases

Consider the following table called EMPLOYEES: ID FIRSTNAME LASTNAME JOB LEVEL 1 Paul Jones DBA 2 2 George Baker MGR 1 3 Roger Melvin CLERK 3 4 Jim Smith MGR 1 5 Kevin Street CLERK 3 6 Chris Eaton MGR 1 If the following SQL statement is executed, how many rows will be deleted? DELETE FROM employees WHERE 1 = 1

  1. 0

  2. 1

  3. 3

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

The DELETE statement uses a WHERE clause condition '1 = 1' which is always true for every row. This means the condition applies to all rows in the table, so all 6 rows will be deleted. The WHERE clause doesn't filter any rows out - it's a way to make the DELETE statement apply to the entire table. This is a common pattern for deleting all rows while still using the WHERE clause syntax (versus TRUNCATE which has different behavior regarding logging and triggers).

Multiple choice technology databases

Given the following two tables: TAB1 C1 C2 1 Antarctica 2 Africa 3 Asia 4 Australia TAB2 CX CY 5 Europe 6 North America 7 South America Which of the following SQL statements will insert all rows found in table TAB2 into table TAB1?

  1. INSERT INTO tab1 SELECT cx, cy FROM tab2

  2. INSERT INTO tab1 VALUES (tab2.cx, tab2.cy)

  3. INSERT INTO tab1 VALUES (SELECT cx, cy FROM tab2)

  4. INSERT INTO tab1 (c1, c2) VALUES (SELECT cx, cy FROM tab2)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To insert all rows from one table into another, use INSERT INTO with a SELECT subquery. Option A is correct: INSERT INTO tab1 SELECT cx, cy FROM tab2 will insert all rows from tab2 into tab1, with the columns mapping in order (cx goes to c1, cy goes to c2). Option B is incorrect because VALUES requires literal values, not table references. Option C is incorrect because VALUES expects individual value expressions, not a SELECT statement. Option D is incorrect because VALUES cannot contain a SELECT subquery in that position.

Multiple choice technology databases

Given the following table and XML data stored in the CONTACTINFO column: CREATE TABLE clients( id INT PRIMARY KEY NOT NULL, name VARCHAR(50), status VARCHAR(10), contactinfo XML ) 25 EastCreek Toronto OntarioM8X-3T6 4165551358 9051112222 9051112222 by fax What is the result of the following XQuery expression? for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client//fax return $y

  1. 4165551358

  2. 9051112222

  3. No results because there is no child element

  4. No results because the XQuery expression is not a valid FLWOR expression
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The XQuery expression uses db2-fn:xmlcolumn to access XML data and searches for fax elements within the Client hierarchy. Looking at the XML structure, the fax element contains '9051112222'. The expression uses //fax which searches for fax elements at any depth under Client. The result returns the fax element with its content, which is 9051112222. Option A (4165551358) appears elsewhere in the document but is not in a fax element. Options C and D are incorrect - the expression is valid and does find a matching fax element.

Multiple choice technology databases

Given the following query: SELECT last_name, first_name, age, hire_date FROM employee WHERE age > 40 Which of the following clauses must be added to return the rows sorted by AGE, oldest first, and by LAST_NAME, from A to Z?

  1. SORT BY age ASC, last_name

  2. SORT BY age DESC, last_name

  3. ORDER BY age DESC, last_name

  4. ORDER BY age ASC, last_name
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Sorting rows in SQL requires the ORDER BY clause, making SORT BY options incorrect. To sort by age with the oldest first, the age column must use descending order (age DESC). To sort by last name alphabetically, ascending order is used, which is the default in SQL.

Multiple choice technology databases

Given the table, Customer contain the following data Customer Table: Product-ID Quantity 1 5 2 3 3 4 The following sequence of statement performed at the single unit of work: UPDATE Customer SET Quantity = 3 WHERE Product-ID = 1; SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS; UPDATE Customer SET Quantity = 5 WHERE Product-ID = 3; INSERT INTO Customer VALUES (5, 20); SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS; INSERT INTO Customer VALUES (4, 10); ROLLBACK TO SAVEPOINT s1; INSERT INTO Customer VALUES (6, 10); INSERT INTO Customer VALUES (7, 15); COMMIT;

  1. 1-5, 2-3, 3-5, 4-10, 5-20, 6-10, 7-15

  2. 1-3, 2-3, 3-4, 6-10, 7-15

  3. 1-3, 2-3, 3-4, 4-10, 5-20, 6-10, 7-15

  4. 1-3, 2-3, 3-4
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The transaction starts with Customer having (1,5), (2,3), (3,4). UPDATE sets Product-ID 1 to 3. SAVEPOINT s1 is set. UPDATE sets Product-ID 3 to 5. INSERT adds (5,20). SAVEPOINT s2 is set. INSERT adds (4,10). ROLLBACK TO s1 undoes everything after s1: the second UPDATE and both INSERTs are rolled back. After ROLLBACK, Product-ID 1 is 3, Product-ID 3 is 4 (unchanged from initial). Then INSERT adds (6,10) and (7,15). COMMIT finalizes: (1,3), (2,3), (3,4), (6,10), (7,15). Option B matches this final state.

Multiple choice technology databases

Given the following table and XML data stored in the EMPDETAILS column: CREATE TABLE employees (Emp-id INT PRIMARY KEY NOT NULL, Emp-name VARCHAR (20), Empdetails XML) The data in the EMPDETAILS are ProjectManager Chennai 987654 What is the result of the following XQuery Expression? For $y in db2-fn:xmlcolumn(EMPLOYEES. EMPDETAILS’)/Employee/designation return $y

  1. <designation>ProjectManager</designation>

  2. ProjectManager

  3. chennai

  4. <pincode>987654</pincode>
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The XQuery expression uses db2-fn:xmlcolumn to access the EMPDETAILS column and navigates to the designation element within the Employee/Details hierarchy. The expression returns the complete element including its tags, not just the text content. The result is 'ProjectManager' which includes the XML tags. Option B only returns the text content without tags. Option C is incorrect - Chennai is the working-place content, not designation. Option D is incorrect - pincode is a different element in the XML structure.