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

SQL1203 error means?

  1. FUNCTIONALITY IS NOT IMPLEMENTED

  2. NO MORE JOIN SEEDS POSSIBLE

  3. Both 1 and 2

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

SQL1203 indicates that a requested feature or function is not implemented in the current database system. This is a capability error where the SQL engine doesn't support the specific operation being attempted.

Multiple choice technology databases

SQL1004 error means?

  1. Access denied

  2. EXECUTION INTERRUPTED BY USER

  3. REMOTE SQL SERVER IS NOT AVAILABLE

  4. SQL SERVER IS NOT RUNNING
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In this specific database client mapping, the SQL1004 error code indicates that access was denied, typically due to invalid credentials or insufficient permissions to connect to the database.

Multiple choice technology databases

Which key is related to SQL?

  1. Superkey

  2. Candidate key

  3. Primary key

  4. Composite key
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

All four options are SQL key concepts. Superkey (A) uniquely identifies tuples. Candidate key (B) is a minimal superkey. Primary key (C) is a selected candidate key. Composite key (D) is a key made of multiple attributes. All are correct.

Multiple choice technology databases

SQL1715 error means?

  1. Failed to validate subquery

  2. Failed to validate CASE expression

  3. Failed to validate column list

  4. Failed to validate foreign key.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQL1715 error specifically indicates failure to validate a subquery. This occurs when the database cannot process or verify the structure/logic of a nested query. It is distinct from CASE expression validation (B), column list validation (C), or foreign key validation (D) which have different error codes.

Multiple choice technology databases

SQL1707 error means?

  1. Failed to validate IN predicate

  2. Failed to validate HAVING clause

  3. Failed to validate CASE expression

  4. None of these
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL1707 error indicates failure to validate the HAVING clause in a SQL query. The HAVING clause is used to filter results after GROUP BY aggregation, and this error means the database cannot validate its structure or logic. It differs from IN predicate validation (A), CASE expression validation (C), and other SQL validations.

Multiple choice technology databases

In which scenario would index be most useful?

  1. The indexed column is declared as NOT NULL

  2. The indexed columns are used in the FROM clause

  3. The indexed columns are part of an expression

  4. The indexed column contains a wide range of values
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To answer this question, the user needs to know what an index is and how it works.

An index is a data structure that improves the speed of data retrieval operations on a database table. It does this by providing a quick lookup mechanism for finding rows that match certain column values in the table. Indexes are most useful when searching large tables for a specific value or set of values.

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

A. The indexed column is declared as NOT NULL: This option is not necessarily the most useful scenario for an index. Declaring a column as NOT NULL simply means that the column cannot contain NULL values. While this may improve data integrity, it does not necessarily make an index more useful.

B. The indexed columns are used in the FROM clause: This option is not necessarily the most useful scenario for an index, either. Using indexed columns in the FROM clause can help improve query performance by allowing the database to retrieve only the necessary rows. However, it does not necessarily make the index more useful than other scenarios.

C. The indexed columns are part of an expression: This option can be useful for an index. When indexed columns are part of an expression, the database can still use the index to search for values that match the expression. This can help improve query performance.

D. The indexed column contains a wide range of values: This option is the most useful scenario for an index. When a column contains a wide range of values, it can be difficult for the database to search through all the values to find the ones that match a specific criteria. However, if the column is indexed, the database can use the index to quickly find the values that match the criteria, which can greatly improve query performance.

Therefore, the correct answer is:

The Answer is: D

Multiple choice technology databases

Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?

  1. ORDER BY SALARY > 5000

  2. GROUP BY SALARY > 5000

  3. HAVING SALARY > 5000

  4. WHERE SALARY > 5000
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 a SELECT statement in SQL, including the purpose of each clause.

The WHERE clause is used to filter the rows returned by a query based on specified conditions. In this case, we want to limit the display to only those employees whose salary is greater than 5000. Therefore, the correct answer is:

The Answer is: D. WHERE SALARY > 5000

Option A, ORDER BY SALARY > 5000 is incorrect because the ORDER BY clause is used to sort the rows returned by a query in ascending or descending order based on a specified column. It is not used to filter rows based on specific conditions.

Option B, GROUP BY SALARY > 5000 is incorrect because the GROUP BY clause is used to group the rows returned by a query based on one or more columns. It is not used to filter rows based on specific conditions.

Option C, HAVING SALARY > 5000 is incorrect because the HAVING clause is used to filter the rows returned by a query based on conditions that involve aggregate functions, such as SUM, COUNT, AVG, MAX, and MIN. It is not used to filter rows based on specific conditions.

Multiple choice technology databases

When should you create a role? (Choose two)

  1. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement

  2. To grant a group of related privileges to a user

  3. When the number of people using the database is very high

  4. To simplify the process of granting and revoking privileges
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Roles simplify privilege management by grouping related privileges together, making it easier to grant and revoke permissions for multiple users. This is especially valuable when many users need similar access patterns.

Multiple choice technology databases

Which four are correct guidelines for naming database tables? (Choose four)

  1. Must begin with either a number or a letter

  2. Must be 1-30 characters long

  3. Should not be an Oracle Server reserved word

  4. Must contain only A-Z, a-z, 0-+, _, *, and #.
  5. Must contain only A-Z, a-z, 0-9, _, $, and #.

  6. Must begin with a letter
Reveal answer Fill a bubble to check yourself
B,C,E,F Correct answer
Explanation

Oracle table names must be 1-30 characters long, must begin with a letter, should not be reserved words, and can only contain alphanumeric characters and the special characters _, $, and #.

Multiple choice technology databases

You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was create.) How do you obtain the definition of the view

  1. Use the DESCRIBE command in the EMP_DEPT VU view

  2. Use the DEFINE VIEW command on the EMP_DEPT VU view

  3. Use the DESCRIBE VIEW command on the EMP_DEPT VU view

  4. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view

  5. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view

  6. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In Oracle, view definitions are stored in the USER_VIEWS data dictionary view, with the TEXT column containing the SELECT statement. This is the standard way to retrieve view definitions.

Multiple choice technology databases

Which three statements correctly describe the functions and use of constraints? (Choose three.)

  1. Constraints provide data independence

  2. Constraints make complex queries easy

  3. Constraints enforce rules at the view level

  4. Constraints enforce rules at the table level

  5. Constraints prevent the deletion of a table if there are dependencies

  6. Constraints prevent the deletion of an index if there are dependencies
Reveal answer Fill a bubble to check yourself
C,D,E Correct answer
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 =.