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 mainframe

If Sql return -805 , what will user want to do?

  1. Force start the database

  2. Bind the database

  3. Drop the table

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

SQL return code -805 indicates that the application plan or package is not found in the database, typically requiring a bind operation. This error occurs when the DB2 program or package hasn't been bound to the database, or after structural changes. Binding the database resolves this by creating or updating the necessary package information.

Multiple choice technology

What does the SUMIF function do?

  1. Adds up cell values based on a condition.

  2. Adds all the numbers in a range of cells

  3. Returns a subtotal in a list or database

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

SUMIF adds values in a range that meet a specified condition. For example, SUMIF(A1:A10,'>100') adds only values greater than 100, or SUMIF(B1:B10,'Sales',C1:C10) adds values in column C where column B contains 'Sales'. It's different from SUM which adds all numbers without conditions, and SUBTOTAL which creates filtered totals.

Multiple choice technology databases

Which type of view is implemented as a subquery embedded in the FROM clause of a SELECT statement and is identified by an alias?

  1. Inline

  2. Simple

  3. Complex

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

An inline view is a subquery in the FROM clause of a SELECT statement, identified by an alias, acting as a temporary virtual table. Simple and complex views are stored schema objects created with the CREATE VIEW statement. Nested view is not a standard terminology for this specific construct.

Multiple choice technology databases

Examine the syntax for creating a DEPARTMENT table: CREATE TABLE ABCD( A NUMBER(4), B VARCNAR2(30), C NUMBER(6), D NUMBER(4)) STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5) TABLESPACE userdata; What is the size defined for the fifth extent?

  1. 500K

  2. 300K

  3. 675K

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

The STORAGE clause calculates extent sizes using PCTINCREASE. First extent = INITIAL = 200K. Second extent = NEXT = 200K. Third extent = NEXT * (1 + PCTINCREASE/100) = 200K * 1.5 = 300K. Fourth extent = 300K * 1.5 = 450K. Fifth extent = 450K * 1.5 = 675K. PCTINCREASE of 50 means each extent grows by 50% from the previous one. Option C correctly calculates 675K for the fifth extent.

Multiple choice technology databases

Evaluate the following SQL statement: SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual; What will be displayed?

  1. 100

  2. 0

  3. 1

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

To understand the given SQL statement, the user must know the following functions:

  1. MOD - returns the remainder of a division operation
  2. TRUNC - truncates a number to a specified number of decimal places
  3. ROUND - rounds a number to a specified number of decimal places

Now let's break down the SQL statement:

  • The MOD function calculates the remainder of 1600 divided by 10, which is 0.
  • The TRUNC function truncates 0 to -1 decimal places, which is still 0.
  • Finally, the ROUND function rounds 0 to 2 decimal places, which is still 0.

Therefore, the answer is option B: 0.

Multiple choice technology databases

What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?

  1. This value causes the optimizer to the rule-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with a goal of best throughput

  2. This value causes the optimizer to the rule-based approach for all SQL statements in the session in regard of the presence of statistics and to optimize with a goal of best throughput

  3. This value causes the optimizer to the cost-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with a goal of best throughput

  4. This value causes the optimizer to the cost-based approach for all SQL statements in the session regard of the presence of statistics and to optimize with a goal of best throughput
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The ALL_ROWS optimizer goal in Oracle instructs the optimizer to use cost-based optimization and minimize total resource consumption (best throughput) for the entire result set. However, this option is incorrectly phrased - CBO does require statistics to function optimally, and there's a grammatical error in option C.

Multiple choice technology databases

What is the correct use of the Trunc command on a date?

  1. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

  2. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') FROM DUAL;

  3. TRUNCATE=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  4. Date =TRUNCATE(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The syntax SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL; is syntactically valid in Oracle SQL, truncating the date to the first day of that year and using an alias. Option 566652 is also valid SQL but lacks the column alias, whereas options 566653 and 566654 use completely invalid syntax. Both 566651 and 566652 work, but 566651 is chosen as correct.

Multiple choice technology databases

How do I eliminate duplicate rows in an Oracle database?

  1. DELETE FROM table_name WHERE ROWID IN (SELECT MAX (ROWID) FROM table_name GROUP BY duplicate_values_field_name);

  2. DELETE FROM table_name WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM table_name GROUP BY duplicate_values_field_name);

  3. DELETE FROM table_name WHERE ROWID IN (SELECT MIN (ROWID) FROM table_name GROUP BY duplicate_values_field_name);

  4. DELETE FROM table_name WHERE ROWID IN (SELECT AVG (ROWID) FROM table_name GROUP BY duplicate_values_field_name);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This query keeps exactly one row for each set of duplicates (the one with the maximum ROWID) and deletes the rest. Grouping by the duplicate fields identifies duplicate groups, and selecting NOT IN MAX(ROWID) safely deletes all redundant records except the single unique representative record.

Multiple choice technology platforms and products

What do data load rules files enable you to do?

  1. Sort data records

  2. Ignore fields, calculate data

  3. Load data from a SQL data source, change signs on data

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

Essbase data load rules files enable loading data from SQL sources and allow data manipulation like changing signs. Options A and B mention capabilities that are either not the primary purpose or incorrectly stated. Option D 'All of the above' is incorrect because not all listed functions are valid. SQL-based loading and sign changes are core rules file capabilities.

Multiple choice technology databases

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ROWID represents the physical address of a row in the database. Access by ROWID is fastest because it goes directly to the physical location without any index lookup or searching. Index-based methods require additional traversal, while full table scans are slowest.

Multiple choice technology databases

Which of the following SQL functions can operate on any datatype?

  1. TO_CHAR

  2. LOWER

  3. LPAD

  4. MAX

  5. CEIL
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct answer is D. MAX.

The MAX function in SQL can operate on any datatype. It is used to retrieve the maximum value from a column or a set of values. Regardless of the datatype of the column or the values, the MAX function will return the maximum value.

The other functions mentioned:

A. LOWER: It is used to convert a string to lowercase. It can only operate on string or text datatypes.

B. TO_CHAR: It is used to convert a value to a character string. It is typically used to convert a date or a number to a specific string format. It can operate on different datatypes but is primarily used for date and number conversions.

C. LPAD: It is used to pad a string with specified characters on the left side. It can only operate on string or text datatypes.

E. CEIL: It is used to round up a number to the nearest integer. It can only operate on numeric datatypes.

Therefore, out of all the options, only the MAX function can operate on any datatype.

Multiple choice technology databases

What best describes the relationship between indexes and SQL performance?

  1. Indexes are only used in special cases

  2. Indexes are used to make table storage more efficient

  3. Indexes rarely make a difference in SQL performance

  4. Indexes exist solely to improve query speed
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To understand the relationship between indexes and SQL performance, the user needs to know that indexes are used to improve the performance of queries that search large tables.

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

A. Indexes are only used in special cases: This option is incorrect. Indexes are commonly used in SQL databases to improve query performance, not just in special cases.

B. Indexes are used to make table storage more efficient: This option is partially correct. Indexes help make table storage more efficient by allowing for faster searching of the data, but their main purpose is to improve query performance.

C. Indexes rarely make a difference in SQL performance: This option is incorrect. Indexes can make a significant difference in the performance of SQL queries, especially when dealing with large tables.

D. Indexes exist solely to improve query speed: This option is partially correct. The primary purpose of indexes is to improve query speed, but they also help with other aspects of database performance such as data storage and retrieval.

The Answer is: D

Multiple choice technology databases

Which of the following commands cause a transaction to end?

  1. DELETE

  2. ALTER

  3. INSERT

  4. UPDATE

  5. ROLLBACK
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

ROLLBACK ends a transaction by discarding all pending data changes made during the transaction. DML statements like INSERT, UPDATE, and DELETE start or continue a transaction, rather than ending it. ALTER is a DDL statement that implicitly commits, but ROLLBACK is the direct command explicitly designed to end a transaction by undoing changes.