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

Multiple choice technology databases

To use a FETCH staement with a cursor you always have to supply an explicit direction such next or prior.

  1. TRUE

  2. FALSE

  3. BOTH

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

This is false - FETCH with a cursor does NOT require explicit direction. If you omit NEXT/PRIOR/FIRST/LAST/ABSOLUTE/RELATIVE, the default is NEXT, which fetches the next row sequentially. The statement can use implicit direction, making the 'always' in the question incorrect.

Multiple choice technology programming languages

Is Prepared statement a precomplied SQL statement?

  1. True

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

A PreparedStatement is indeed precompiled - the SQL statement template is parsed, compiled, and optimized when the statement is created (often on the connection), not when it's executed. This allows efficient reuse with different parameter values and provides some performance benefits for repeated executions.

Multiple choice technology databases

Which four are valid Oracle constraint types?

  1. CASCADE

  2. UNIQUE

  3. NONUNIQUE

  4. CHECK

  5. PRIMARY KEY

  6. NOT NULL
Reveal answer Fill a bubble to check yourself
B,D,E,F Correct answer
Explanation

Oracle constraints are: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and (for deferred constraints) initially deferred/deferred. CASCADE is a referential action (ON DELETE CASCADE), not a constraint type. NONUNIQUE is not a constraint.

Multiple choice technology databases

Which is an iSQL*PLUS command?

  1. INSERT

  2. UPDATE

  3. SELECT

  4. DESCRIBE

  5. DELETE

  6. RENAME
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The DESCRIBE command is a command-line utility command in SQL*Plus and iSQL*Plus used to display the structure of a table. SELECT, INSERT, UPDATE, and DELETE are standard SQL Data Manipulation Language (DML) statements, not client-specific commands.