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

Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them

  1. The two statements produce identical results.

  2. The second statement returns a syntax error

  3. There is no need to specify DESC because the results are sorted in descending order by default

  4. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To understand these SQL statements, the user needs to know the basic syntax of the SELECT statement and the ORDER BY clause. The SELECT statement retrieves data from one or more tables, and the ORDER BY clause sorts the results in ascending or descending order based on one or more columns' values.

The first SQL statement retrieves the last_name, salary, and hire_date columns from the EMPLOYEES table and sorts the results in descending order based on the salary column's values.

The second SQL statement retrieves the last_name, salary, and hire_date columns from the EMPLOYEES table and sorts the results in descending order based on the second column, which is the salary column.

A. The two statements produce identical results. This is correct because both statements retrieve the same columns and sort the results in descending order based on the salary column's values.

B. The second statement returns a syntax error. This is incorrect because the second SQL statement is syntactically correct and will execute without errors.

C. There is no need to specify DESC because the results are sorted in descending order by default. This is incorrect because the default order is ascending, not descending. If the user wants to sort the results in descending order, they must specify DESC explicitly.

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement. This is incorrect because adding a column alias does not affect the sorting order. The two statements already produce identical results, so there is no need to modify them.

Therefore, the answer is: A. The two statements produce identical results.

Multiple choice technology databases

You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?

  1. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;

  2. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYYY') FROM dual;

  3. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;

  4. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;

  5. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to know the basic SQL functions like TO_CHAR and SYSDATE and their usage and format masks.

Option A: This option is incorrect because it uses the wrong format mask and does not give the required output format. The 'FMDAY' format mask is not correct to get the desired output format.

Option B: This option is incorrect because it uses the wrong format mask and does not give the required output format. The 'FMDD' format mask is not correct to get the desired output format.

Option C: This option is correct. It uses the correct format mask and gives the required output format. The 'FMDay' format mask gives the day of the week in the full name format and 'DD Month, YYYY' gives the date in the required format.

Option D: This option is incorrect because it uses the wrong format mask and does not give the required output format. The 'FMDY' format mask is not correct to get the desired output format.

Option E: This option is incorrect because it uses the wrong function and format mask. TO_DATE function is used to convert a string to a date, not to format the date. Additionally, 'DDD' format mask is not required to get the desired output format.

Therefore, the answer is:

The Answer is: C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;

Multiple choice technology databases

Which SELECT statement will the result 'ello world' from the string 'Hello World'?

  1. SELECT SUBSTR ('Hello',) FROM dual;

  2. SELECT INITCAP (TRIM ('Hello World',1,1) FROM dual;

  3. SELECT LOWER (SUBSTR ('Hello World',1,1) FROM dual

  4. SELECT LOWER (SUBSTR ('Hello World',2,1) FROM dual;

  5. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual;
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The question asks for 'ello world' from 'Hello World'. The correct approach is to remove the first character 'H' and convert the result to lowercase. Option E does exactly this: TRIM('H' FROM 'Hello World') removes 'H' from the beginning, then LOWER() converts 'ello World' to 'ello world'. The other options either don't remove the first character correctly or have syntax errors like missing parentheses.

Multiple choice technology databases

Which four statements correctly describe functions that are available in SQL? (Choose four)

  1. INSTR returns the numeric position of a named character.

  2. NVL2 returns the first non-null expression in the expression list.

  3. DECODE translates an expression after comparing it to each search value

  4. TRIM trims the heading of trailing characters (or both) from a character string

  5. NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal

  6. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal
Reveal answer Fill a bubble to check yourself
A,C,D,F Correct answer
Multiple choice technology databases

Which two statements are true regarding the ORDER BY clause? (Choose two)

  1. The sort is in ascending by order by default

  2. The sort is in descending order by default

  3. The ORDER BY clause must precede the WHERE clause

  4. The ORDER BY clause is executed on the client side

  5. The ORDER BY clause comes last in the SELECT statement

  6. The ORDER BY clause is executed first in the query execution
Reveal answer Fill a bubble to check yourself
A,E Correct answer
Explanation

ORDER BY sorts in ascending order by default (A is true). The ORDER BY clause must come last in a SELECT statement, after WHERE, GROUP BY, HAVING (E is true). It does not sort descending by default (B is false). It must come after WHERE, not before (C is false). It executes on the server, not client (D is false). It executes last in query processing, after the result set is formed (F is false).

Multiple choice technology databases

The UPDATE SQL clause can…

  1. update more than one row at a time.

  2. delete more than one row at a time.

  3. update only one row at a time.

  4. delete only one row at a time.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The UPDATE statement in SQL can modify multiple rows in a single execution. When the WHERE clause matches multiple rows, all those rows are updated simultaneously. UPDATE does not delete rows - that's what DELETE does. It can update one or many rows depending on the WHERE condition.

Multiple choice technology databases

Which are DML statements? (Choose all that apply)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

DML (Data Manipulation Language) statements manipulate data: MERGE combines insert/update/delete (B is true), UPDATE modifies existing data (C is true), DELETE removes rows (D is true). COMMIT is a transaction control statement (TCL), not DML (A is false). CREATE and DROP are DDL statements (Data Definition Language), not DML (E and F are false).

Multiple choice technology programming languages

Properties that besides retrieving and writing the value of the field, also it can trigger an event related to the field?

  1. True

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

Properties can indeed trigger events beyond simple read/write operations. For instance, in C# implementing INotifyPropertyChanged, a property setter can raise the PropertyChanged event to notify UI elements or other components of data changes, enabling reactive programming patterns.

Multiple choice technology packaged enterprise solutions

What are the requirements of an effective dated table?

  1. Define EFFDT

  2. Define EFF_STATUS

  3. Define EFFDT, EFF_STATUS

  4. Define EFFDT as key, in descending order
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

An effective dated table tracks data changes over time, requiring EFFDT (effective date) as the key field sorted in descending order. This ensures queries easily access the most current record first. Options A, B, and C are incomplete - defining EFF_STATUS or simply having the fields isn't sufficient. The key requirement is proper key definition and ordering.

Multiple choice technology packaged enterprise solutions

PeopleSoft tracks object changes using a system table. Which PeopleTools System table is used to track object changes?

  1. SYSRELEASE

  2. SYSOBJECTS

  3. PSRELEASE

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

PSRELEASE is the PeopleTools system table that tracks object changes, versions, and release information. It maintains the history of object modifications in the PeopleSoft system. The other options (SYSRELEASE, SYSOBJECTS, PSOBJECTS) are not the correct system tables for tracking object changes.

Multiple choice technology packaged enterprise solutions

When can a value be added in the underlying table?

  1. Prompt Table with Edit

  2. Translate Table Edit

  3. Yes/No Edit

  4. Prompt Table with No Edit
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Prompt Table with No Edit allows users to add new values to the underlying prompt table directly. With Prompt Table with Edit enabled, users can only select from existing values and cannot add new ones. Translate Table Edit and Yes/No Edit have different purposes.