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 programming languages

select flightnumber, date, destination, boarded + transferred + nonrevenue as Total from marchflights where total < 100; What will happen when this query is submitted ?

  1. Error: as mathematical computation is not allowed while using proc sql.

  2. ERROR: The following columns were not found in the contributing tables: total.

  3. Warning : the type of the variable total is not declared(character or numeric).But due to automatic conversion, some unexpected value is being populated in variable total.

  4. Error: variable total nor recognized(because there are two spelling - Total and total)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SAS PROC SQL evaluates the WHERE clause before the SELECT clause. Therefore, referencing a column alias like total in the WHERE clause without the CALCULATED keyword fails with a column-not-found error.

Multiple choice technology programming languages

Which Proc sql will be used to determine the total number of miles traveled by frequent-flyer program members in each of three membership classes (Gold, Silver, and Bronze). Frequent-flyer program information is stored in the table Frequentflyers. MemberType TotalMiles Bronze 908786767 Gold 57665868 Silver 34343656

  1. proc sql; select membertype sum(milestraveled) as TotalMiles from frequentflyers group by membertype;

  2. proc sql; select membertype sum(milestraveled) as TotalMiles from frequentflyers order by membertype;

  3. proc sql; select membertype milestraveled sum(milestraveled) as TotalMiles from sasuser.frequentflyers group by membertype;

  4. proc sql; select membertype sum(milestraveled) from frequentflyers group by membertype;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To get total miles by membership type, use SUM(milestraveled) as the aggregate function with GROUP BY membertype. Option A correctly uses both SUM() and GROUP BY to calculate totals for each category. Option B uses ORDER BY instead of GROUP BY, which sorts but doesn't aggregate. Option C includes an unaggregated column, and Option D omits the alias for the calculated column.

Multiple choice technology programming languages

What is the purpose of the following statement ---- PROC SQL OUTOBS= 10;

  1. This restricts the number of rows (observations) in the output to 10.

  2. Tells the system to process first 10 observation from a SAS dataset

  3. Restricts the system to read 10 observations.

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

The OUTOBS= option in PROC SQL restricts the number of rows in the output to the specified value. It limits what is displayed or written to the destination, not how many rows are read from the source. This differs from INOBS= (limits input) and FIRSTOBS= (sets starting observation).

Multiple choice technology programming languages

Which of the following is not a valid ABAP data statement?

  1. Data fielda(5) type c

  2. Data fielda(5) type n

  3. Data fielda(5) type t

  4. Data fielda(5) type x
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In ABAP, the 't' data type represents time and has a fixed length of 6 characters (HHMMSS). Defining type t with an explicit length like fielda(5) is invalid because its length cannot be customized, whereas 'c', 'n', and 'x' allow length specifications.

Multiple choice technology programming languages

How would you clear the body of an internal table (with a header line). a) Clear ITAB[] b) Refresh ITAB [] c) Clear ITAB d) Refresh ITAB

  1. c&d

  2. a&d

  3. a&b

  4. c&b
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

For an internal table with a header line, 'CLEAR ITAB[]' clears only the body (the table content itself), while 'REFRESH ITAB' is the obsolete syntax equivalent to CLEAR ITAB[]. To clear only the body, use either 'CLEAR ITAB[]' or 'REFRESH ITAB[]' - options a and d.

Multiple choice technology programming languages

When is it better to buffer the table?

  1. When a table is read infrequently

  2. When a table is read frequently and the data seldom changes

  3. When a table is read frequently and the data is always changing

  4. When a table is linked to check tables
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Table buffering improves performance by storing table data in the local application server memory. It is best used for tables that are read frequently but whose data seldom changes, as frequent changes require expensive buffer invalidation across application servers.

Multiple choice technology programming languages

What is the fastest way to move one internal table to another internal table (assuming two tables of similar structure)? a) append lines of table1 to table2. b) loop at table1. Move: table1-field1 to table2-field1, table1-field2 to table2-field2. Append table2. Endloop. c) table2[] = table1[]. d) loop at table1. Move-corresponding table1 to table2. Endloop.

  1. a

  2. b

  3. c

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

The statement table2[] = table1[] performs a direct memory copy of the entire internal table in one operation. This is the fastest method because it uses optimized internal memory copying. APPEND LINES would work but is slower for full copies. LOOP variants are much slower as they process row-by-row. Move-corresponding requires matching field names and processes iteratively.

Multiple choice technology programming languages

Which keyword adds rows to an internal table while accumulating numeric values?

  1. INSERT

  2. APPEND

  3. COLLECT

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

COLLECT inserts rows into an internal table if a line with the same key does not exist, or aggregates (sums) the numeric fields with the existing entry if the key matches. INSERT and APPEND do not aggregate numeric values, and GROUP is used for categorization in loops.

Multiple choice technology programming languages

After a DESCRIBE TABLE statement SY-TFILL will contain

  1. The number of rows in the internal table

  2. The current OCCURS value

  3. Zero, if the table contains one or more rows

  4. The length of the internal table row structure
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DESCRIBE TABLE statement sets the system field SY-TFILL to contain the current number of rows (lines) populated in the internal table. It does not store the OCCURS initialization value, the row structure length, or zero for non-empty tables.

Multiple choice technology web technology

Table T1 wants to ensure that the key field t1-fielda entered is valid against a field t2-fielda in table T2. Which is the foreign key table?

  1. T1

  2. T2

  3. T3 from the dictionary

  4. Cannot be determined
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A foreign key table is the table that contains the foreign key field being validated. Here, T1 contains field t1-fielda which needs to be validated against the check table T2's field t2-fielda. Therefore T1 is the foreign key table (dependent table) and T2 is the check table.

Multiple choice technology

we can use our own SQL for creating a report

  1. True

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

Most reporting tools and database systems allow users to write their own SQL queries to create custom reports. While some tools have visual report builders, raw SQL access is a standard feature for flexibility and custom data extraction.

Multiple choice technology mainframe

If a validation indicator from DB2 is retrieved and validated,the program should handle it for values

  1. yes/no

  2. invalid values

  3. row not found

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

DB2 validation indicators require handling for multiple scenarios: yes/no conditions, invalid values that fail validation, and row-not-found cases when queries return no results. Comprehensive error handling must account for all these possible validation outcomes.

Multiple choice technology mainframe

Select query for a date field should be

  1. SELECT CHAR(CAST (tablename.Date_variable AS DATE), ISO

  2. SELECT DATE(tablename.Date_variable, ISO)

  3. SELECT (tablename.Date_variable AS DATE)

  4. ALL OF THEM
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct syntax uses CAST to convert the date variable to DATE format, then CHAR to format it with ISO standard. Options B and C use incorrect syntax, and D cannot be correct since only A follows proper SQL date casting conventions.

Multiple choice technology mainframe

For date and time stamp queries if the value is null then what is returned for low-date and low-timestamp

  1. (0000-00-00 and 0000-00-00-00.00.00.000000)

  2. (0001-01-01 and 0001-01-01-01.01.01.000001)

  3. (0001-01-01 and 0001-01-01-00.00.00.000000)

  4. (0001-01-01 and 0000-00-00-00.00.00.000000)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In DB2 database systems, when date or timestamp columns have null values, their low-value (minimum) default representations are '0001-01-01' for dates and '0001-01-01-00.00.00.000000' for timestamps. All-zero values are invalid in DB2, and other choices specify incorrect time components.

Multiple choice technology databases

Which of the following statements contains an error?

  1. SELECT * FROM emp WHERE empid = 493945;

  2. SELECT empid FROM emp WHERE empid= 493945;

  3. SELECT empid FROM emp;

  4. SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To solve this question, the user needs to know the basic syntax of SQL queries. The user must evaluate each query to identify the one that contains an error.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT * FROM emp WHERE empid = 493945;

This statement is correct. It selects all columns from the "emp" table where the "empid" column has a value of 493945.

B. SELECT empid FROM emp WHERE empid= 493945;

This statement is correct. It selects the "empid" column from the "emp" table where the "empid" column has a value of 493945.

C. SELECT empid FROM emp;

This statement is correct. It selects the "empid" column from the "emp" table without any filtering conditions.

D. SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;

This statement contains an error. It is missing the "FROM" keyword, which is required to specify the table from which the data should be selected. The correct statement would be "SELECT empid FROM emp WHERE empid = 56949 AND lastname = 'SMITH'".

Therefore, the answer is:

The Answer is: D