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

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.

Multiple choice technology databases

Which of the following supports the XML data type?

  1. A unique index

  2. .A composite index

  3. A check constraint

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

In SQL Server, XML data type columns can be indexed. While XML columns require special index types (PRIMARY XML index and secondary XML indexes), the question tests basic knowledge that XML data type supports indexing operations. A unique index is a valid operation on XML columns, though with specific syntax and constraints.

Multiple choice technology databases

The DEPT table has the following data

  1. The INSERT statement is invalid; a valid table name is missing.

  2. 50 is not a valid DEPTNO value, since the subquery limits DEPTNO to 10.

  3. The statement will work without error.

  4. A subquery and a VALUES clause cannot appear together.
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice technology databases

The following statements are executed: create sequence my_seq; select my_seq.nextval from dual; select my_seq.nextval from dual; rollback; select my_seq.nextval from dual;

  1. 0

  2. 1

  3. 2

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

Sequences in Oracle are outside transaction control. After creating a sequence, first nextval returns 1, second returns 2. ROLLBACK does not affect sequence values, so the third nextval call returns 3, not 1.

Multiple choice technology databases

The current time in Dubai is 04-APR-2008 08:50:00, and the time in Dallas is 03-APR-2008 23:50:00. A user from Dubai is connected to a session in the database located on a server in Dallas. What will be the result of his query?

  1. 04?APR?2008 08:50:00

  2. 03?APR?2008 23:50:00

  3. 03?APR?2008 2324:50:00

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

Oracle date functions like SYSDATE return the database server's system date and time, not the client's local time. Since the database server is in Dallas, it returns Dallas time (03-APR-2008 23:50:00) regardless of user's Dubai location.

Multiple choice technology

Method to compare data in two tables

  1. Lookup

  2. Minus

  3. Either a or b is sufficient

  4. Both a and b are needed
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Both Lookup transformation and SQL MINUS operator can effectively compare data between two tables. Lookup joins sources for row-by-row comparison, while MINUS finds set differences. Either approach is sufficient for comparison tasks.

Multiple choice technology mainframe

What is the table name to see IBS ordering days

  1. IBS_ORDR_ST_DTL

  2. LOB_ORDR_TYP_DMN

  3. IBS_ORDR_ST_DTL_H

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

IBS_ORDR_ST_DTL is the correct table to view IBS (Insurance Business System) ordering days. The other options represent different tables or may not be the primary table for this information.

Multiple choice technology mainframe

What is the table name to see IBS ordering days

  1. IBS_ORDR_ST_DTL

  2. LOB_ORDR_TYP_DMN

  3. IBS_ORDR_ST_DTL_H

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

IBS_ORDR_ST_DTL is the table name used to see IBS ordering days. This table stores order state details including the days information for IBS (Insurance Business System) ordering processes.

Multiple choice technology databases

COMPLETE REFRESH by default will _________________all rows in an mview before inserting new rows

  1. Create

  2. Delete

  3. Append

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

A complete refresh of a materialized view deletes all existing rows before inserting the new dataset. This differs from fast refresh (incremental) which only applies changes. The DELETE operation ensures the mview contains exactly the data from the refresh query, with no stale rows remaining.

Multiple choice technology databases

On a partitioned table which index is used mostly

  1. Local

  2. Global

  3. Both

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

Local indexes are preferred on partitioned tables because they are automatically maintained with partition operations and provide better partition pruning. Global indexes span all partitions and require more maintenance during partition operations like DROP or TRUNCATE. Local indexes align with the table's partitioning strategy.