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 table: TEMP_DATA TEMP DATE _____________ 45    12/25/2006 51    12/26/2006 67    12/27/2006 72    12/28/2006 34    12/29/2006 42    12/30/2006 And the following SQL statement: TE FUNCTION degf_to_c (temp INTEGER) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE newtemp INTEGER; SET newtemp = temp - 32; SET newtemp = newtemp * 5; RETURN newtemp / 9; END Which two of the following SQL statements illustrate the proper way to invoke the calar function DEGF_TO_C?

  1. VALUES degf_to_c(32)

  2. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data

  3. CALL degf_to_c(32)

  4. Both a and b
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A scalar user-defined function in DB2 can be evaluated using a VALUES statement or embedded directly inside a projection list of a SELECT query. It cannot be executed using CALL, which is strictly for stored procedures.

Multiple choice technology databases

To retrieve data from more than one table we could use

  1. Joins

  2. Unions

  3. Nested SELECT

  4. All of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To retrieve data from multiple tables, SQL provides several mechanisms: Joins combine columns from different tables; Unions combine rows from different queries; and Nested SELECT statements query tables hierarchically. Thus, all of these methods can be used.

Multiple choice technology databases

What is the restriction on using UNION in embedded SQL ?

  1. It has to be in a CURSOR

  2. cannot be used in Embedded SQL

  3. No restrictions

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

In embedded SQL (SQL within host programs like C, Java, COBOL), UNION operations must be used within a CURSOR declaration. This is because embedded SQL requires explicit result set handling mechanisms - a cursor provides the necessary interface to navigate through the UNION result set row by row. Without a cursor, the host program cannot properly retrieve and process the combined data from the UNION.

Multiple choice technology databases

Which of the following is a NOT a valid reason for defining a view on a table?

  1. Restrict users' access to a subset of table data

  2. Ensure that rows inserted remain within the scope of a definition

  3. Produce an action as a result of a change to a table

  4. Provide users with an alternate view of table data
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Views are virtual tables that provide restricted or alternate access to underlying data. Their purposes include: restricting user access to specific rows/columns (security), providing alternate representations of data (simplification), and with CHECK OPTION ensuring inserted rows conform to the view definition. However, producing an action as a result of a change is what TRIGGERS do - they execute code in response to data modification events (INSERT, UPDATE, DELETE). Views are passive queries; triggers are active procedures.

Multiple choice technology programming languages

Which of the following SQL statements will not execute successfully? (Choose all that apply)

  1. SELECT lname, jobid, sal, sal+500 FROM emp;

  2. SELECT lname, jobid, sal FROM emp (sal + 500);

  3. SELECT lname, jobid, sal, sal + 500 “Bonus” FROM emp;

  4. SELECT lname, jobid, sal, sal + 500 bonus FROM emp

  5. SELECT lname || to_char(sal+500) “Employee Bonus” FROM emp;

  6. SELECT lname, sal + 500 Employee Bonus FROM emp;
Reveal answer Fill a bubble to check yourself
B,D,F Correct answer
Explanation

The invalid statements are: SELECT lname, jobid, sal FROM emp (sal + 500); due to incorrect parenthesis syntax; SELECT lname, jobid, sal, sal + 500 bonus FROM emp due to a missing semicolon; and SELECT lname, sal + 500 Employee Bonus FROM emp; because multi-word aliases require double quotes.

Multiple choice technology programming languages

You run the following SQL statement: SELECT a.lname, a.location, b.dname, b.hiredate FROM emp a, emp b WHERE a.empid=b.empid; Which options are correct? (Choose all that apply)

  1. This is an equijoin.

  2. This is a self join.

  3. Line 2 will return an error.

  4. Line 3 will return an error.

  5. The statement will execute successfully.
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

This is a self join because the emp table is joined to itself using two different aliases (a and b). A self join is used when a table needs to be compared with itself, such as finding relationships between rows in the same table. The WHERE clause a.empid=b.empid creates an equijoin condition (using equality), but the defining characteristic is that it's a self join. The statement is syntactically valid and will execute successfully, returning each employee's lname and location paired with their dname and hiredate from the same row.

Multiple choice technology databases

Given the following table: TEMP_DATA TEMP DATE _____________ 45    12/25/2006 51    12/26/2006 67    12/27/2006 72    12/28/2006 34    12/29/2006 42    12/30/2006 And the following SQL statement: TE FUNCTION degf_to_c (temp INTEGER) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE newtemp INTEGER; SET newtemp = temp - 32; SET newtemp = newtemp * 5; RETURN newtemp / 9; END Which two of the following SQL statements illustrate the proper way to invoke the calar function DEGF_TO_C?

  1. VALUES degf_to_c(32)

  2. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data

  3. CALL degf_to_c(32)

  4. Both a and b
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The scalar function degf_to_c can be invoked inline within a SELECT statement projection or using a VALUES statement to evaluate the expression. It cannot be invoked using CALL, which is reserved for stored procedures, making 'Both a and b' the correct choice.

Multiple choice technology programming languages

You create the following PL/SQL block: DECLARE var1 CONSTANT NUMBER := 50; var2 NUMBER := 0; BEGIN SELECT acctno INTO var2 FROM bank_acct WHERE name = 'JORDAN'; var1 :=var2 + 2000; END; Which of the following lines in this block of PL/SQL code will produce an error?

  1. var2 NUMBER := 0;

  2. INTO var2

  3. WHERE name = 'JORDAN';

  4. var1 :=var2 + 2000;

  5. There are no errors in this PL/SQL block
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The variable var1 is declared as a CONSTANT in the PL/SQL block. Constants cannot have their values reassigned after initialization. The line var1 := var2 + 2000; attempts to modify this constant, which triggers a compilation error.

Multiple choice technology databases

t-sql variables case sensitive?

  1. True

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

T-SQL (Microsoft SQL Server) variables are case-insensitive. @MyVar and @myvar refer to the same variable. This is consistent with SQL Server's default case-insensitive collation behavior. The only case-sensitive elements in T-SQL are string comparisons (if using a case-sensitive collation) and object names in case-sensitive databases.

Multiple choice technology databases

On which two of the following database objects may the SELECT privilege be controlled?

  1. Index

  2. Sequence

  3. Nickname

  4. View
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

In DB2, SELECT privilege can be granted on tables, views, and nicknames. Nicknames are federated database objects that reference remote data sources and support the same privilege model as local tables. Views are virtual tables that also support SELECT privilege. Indexes are internal structures with no privilege control, and sequences use USAGE or NEXT VALUE privileges, not SELECT.

Multiple choice technology databases

Which of the following DB2 objects can be referenced by an INSERT statement to generate values for a column?

  1. Identity column

  2. Table function

  3. Sequence

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

Sequences in DB2 are explicitly referenced in INSERT statements using expressions like NEXT VALUE FOR sequence_name or PREVIOUS VALUE FOR sequence_name to generate unique numeric values for a column. Identity columns are implicitly auto-generated and not externally referenced. Table functions return row sets, not single values. Triggers execute code but don't generate values directly for INSERT.

Multiple choice technology databases

Which of the following events will NOT cause a trigger to be activated?

  1. A select operation

  2. An update operation

  3. An insert operation

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

Database triggers are event-driven procedures that fire only on data modification operations: INSERT, UPDATE, and DELETE. SELECT operations query data without modifying it, so they never activate triggers. This is by design since triggers are meant to enforce business rules, audit changes, or maintain referential integrity when data changes.