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

You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?

  1. The main query executes with the first value returned by the subquery

  2. The main query executes with the last value returned by the subquery

  3. The main query executes with all the values returned by the subquery

  4. The main query fails because the multiple-row subquery cannot be used with the comparison operator

  5. You cannot define a multiple-row subquery in the WHERE clause of a SQL query
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Single-row operators like =, >, <, >=, <= can only be used with subqueries that return exactly ONE row. When a subquery returns multiple rows, you must use multiple-row operators like IN, ANY, ALL, or EXISTS. Using = with a multi-row subquery causes an error because the database cannot compare a single value to multiple values simultaneously. Option E is incorrect because you CAN define multiple-row subqueries in WHERE clauses, but you must use appropriate operators (IN, ANY, ALL, EXISTS) instead of =.

Multiple choice technology databases

What is true about joining tables through an equijoin

  1. You can join a maximum of two tables through an equijoin

  2. You can join a maximum of two columns through an equijoin

  3. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement

  4. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

  5. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

To understand the concept of equijoin, the user should know the basics of SQL and joins. An equijoin is a type of join that combines columns from two or more tables based on their equality.

Let's go through each option and explain whether it is true or false:

A. You can join a maximum of two tables through an equijoin

  • This statement is false. You can join more than two tables through an equijoin.

B. You can join a maximum of two columns through an equijoin

  • This statement is false. You can join more than two columns through an equijoin.

C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement

  • This statement is false. You specify an equijoin condition in the WHERE clause of a SELECT statement.

D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

  • This statement is false. The columns in the join condition need not be primary key and foreign key columns; they can be any columns that have the same data type and contain the same values.

E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

  • This statement is true. If you want to join n tables, you need to specify a minimum of n-1 join conditions.

The Answer is: E

Multiple choice technology databases

A subquery can be used to _________.

  1. Create groups of data

  2. Sort data in a specific order

  3. Convert data to a different format

  4. Retrieve data based on an unknown condition
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A subquery is a nested query whose result is passed to the outer query. It is typically used to retrieve data based on a condition that is unknown at the time of writing the query, such as finding employees earning above average.

Multiple choice technology databases

what does the sql code -922 represents?

  1. query will retrieve only one row

  2. authorization failure

  3. no such code available

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

To answer this question, we need to know the context in which the SQL code -922 is being used. SQL codes are specific error or informational messages generated by the database management system (DBMS) when it encounters certain conditions or situations. Without context, it is difficult to determine the exact meaning of a particular SQL code.

However, in general, SQL code -922 is associated with authorization failures. This means that the user attempting to execute a particular SQL statement does not have the required permissions or privileges to perform the operation. The exact meaning and resolution of the error will depend on the specific DBMS being used and the context in which the error occurred.

Therefore, the correct answer is:

The Answer is: B. authorization failure.

Multiple choice technology databases

choose the environments which can access DB2

  1. TSO

  2. IMC

  3. CICS

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

DB2 for z/OS can be accessed from mainframe environments such as Time Sharing Option (TSO) and Customer Information Control System (CICS). These environments use attachment facilities to interact with the DB2 subsystem.

Multiple choice technology web technology

which one is responsible for flushing all DB2 buffers on the disk?

  1. QUIESCE

  2. FREEPAGE

  3. PCTFREE

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

QUIESCE is a DB2 utility that forces all pending changes from the buffer pool to be written to disk, ensuring data consistency. It's particularly important before operations like backups, COPY, or LOAD to guarantee that all data is safely stored on disk. QUIESCE essentially does a checkpoint - it flushes all modified pages from memory to disk and records a point of consistency. FREEPAGE and PCTFREE are storage parameters controlling how much free space is reserved in table spaces for future inserts/updates. TABLESPACE is a container for tables, not a command or utility.

Multiple choice technology web technology

what is index cardinality?

  1. no of duplicate values

  2. no of distinct values

  3. no of null values

  4. both 1 & 2
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Index cardinality refers to the number of distinct (unique) values in an indexed column. A high cardinality index has many unique values (like a primary key), making it very selective and efficient for queries. A low cardinality index has many duplicate values (like a gender column with only 'M' and 'F'), making it less selective. Cardinality helps the query optimizer choose the best execution plan - high cardinality indexes are preferred for filtering and joins. Option A (duplicate values) is the opposite of cardinality. Option C (null values) is unrelated to cardinality definition.

Multiple choice technology databases

Which are true regarding the use of outer joins?

  1. You cannot use IN operator in a condition that involves an outerjoin

  2. You use (+) on both sides of the WHERE condition to perform an outerjoin

  3. You use (*) on both sides of the WHERE condition to perform an outerjoin

  4. You use an outerjoin to see only the rows that do not meet the join condition

  5. In the WHERE condition, you use (+) following the name of the column in the table

  6. You cannot link a condition that is involved in an outerjoin to another condition by
Reveal answer Fill a bubble to check yourself
D,E,F Correct answer
Multiple choice technology databases

Which two statements complete a transaction? (Choose two)

  1. DELETE employees;

  2. DESCRIBE employees;

  3. ROLLBACK TO SAVEPOINT C;

  4. GRANT SELECT ON employees TO SCOTT;

  5. ALTER TABLE employees SET UNUSED COLUMN sal;

  6. Select MAX(sal) FROM employees WHERE department_id = 20;
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

In Oracle transactions, ROLLBACK TO SAVEPOINT C undoes changes to a named savepoint, and ALTER TABLE ... SET UNUSED COLUMN marks a column as unused (which auto-commits). DDL statements like ALTER auto-commit, and rollback to savepoint completes a transactional unit. The other options (DELETE, DESCRIBE, GRANT, SELECT) don't complete transactions - DESCRIBE and SELECT are read-only, while DELETE and GRANT are statements within transactions.

Multiple choice technology databases

Which two are true about aggregate functions? (Choose two.)

  1. You can use aggregate functions in any clause of a SELECT statement.

  2. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

  3. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

  4. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

  5. You can use aggregate functions on a table, only by grouping the whole table as one single group.

  6. You cannot group the rows of a table by more than one column while using aggregate functions.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Aggregate functions can be combined with single-row columns in a SELECT list if those columns are included in a GROUP BY clause. Additionally, aggregate functions accept columns, expressions, constants, or other functions as parameters.

Multiple choice technology databases

Which of the following is not a valid aggregate function

  1. COUNT

  2. SUM

  3. COMPUTE

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

To solve this question, the user needs to have knowledge about aggregate functions in SQL.

Aggregate functions are used to perform calculations on a set of values to return a single value.

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

A. COUNT: This is a valid aggregate function in SQL. It returns the number of rows that match a specified condition.

B. SUM: This is a valid aggregate function in SQL. It returns the sum of a set of values.

C. COMPUTE: This is not a valid aggregate function in SQL. There is no such function as COMPUTE in SQL.

D. MIN: This is a valid aggregate function in SQL. It returns the minimum value of a set of values.

Therefore, the answer is:

The Answer is: C