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

What is necessary for your query on an existing view to execute successfully?

  1. The underlying tables must have data.

  2. You need SELECT privileges on the view.

  3. The underlying tables must be in the same schema.

  4. You need SELECT privileges only on the underlying tables.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To query a view, a user only needs the SELECT privilege on the view itself. They do not require direct privileges on the underlying base tables, nor do the tables need to reside in the same schema.

Multiple choice technology databases

Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?

  1. The two statements produce identical results.

  2. The second statement returns a syntax error.

  3. There is no need to specify DESC because the results are sorted in descending order by default.

  4. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To understand these SQL statements, the user needs to know how to query a database and sort the results in descending order based on a specific column.

The first SQL statement selects the last name, salary, and hire date columns from the EMPLOYEES table and orders the results by salary in descending order using the ORDER BY clause with the DESC keyword.

The second SQL statement is identical to the first one but uses the shorthand ORDER BY 2 DESC instead of explicitly specifying the salary column name.

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

A. The two statements produce identical results: This option is correct. Both SQL statements select the same columns and sort the results in the same descending order based on the salary column. The shorthand ORDER BY 2 DESC in the second statement refers to the second column in the SELECT clause, which is the salary column.

B. The second statement returns a syntax error: This option is incorrect. The second SQL statement is a valid syntax and produces the same results as the first one.

C. There is no need to specify DESC because the results are sorted in descending order by default: This option is incorrect. SQL sorts the results in ascending order by default, so the DESC keyword is needed to sort the results in descending order.

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement: This option is incorrect. Adding a column alias does not change the way the ORDER BY clause works. It only renames the column in the result set.

The Answer is: A

Multiple choice technology databases

Which four statements correctly describe functions that are available in SQL? (Choose four.)

  1. INSTR returns the numeric position of a named character.

  2. NVL2 returns the first non-null expression in the expression list.

  3. TRUNCATE rounds the column, expression, or value to n decimal places.

  4. DECODE translates an expression after comparing it to each search value.

  5. TRIM trims the heading or trailing characters (or both) from a character string.

  6. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Reveal answer Fill a bubble to check yourself
A,D,E,F Correct answer
Explanation

INTR finds the numeric position of a substring within a string, making option A correct. DECODE is a conditional function that compares an expression to search values and returns corresponding results, so D is correct. TRIM removes leading or trailing characters (or both) from strings, so E is correct. NULLIF compares two expressions and returns null if equal or the first expression if not equal, so F is correct. NVL2 does not return the first non-null expression (that's COALESCE) - NVL2 returns different values based on whether the first expression is null. TRUNC removes decimal places without rounding, while ROUND would round.

Multiple choice technology databases

Which statement creates a new user?

  1. CREATE USER Susan;

  2. CREATE OR REPLACE USER Susan;

  3. CREATE NEW USER Susan DEFAULT;

  4. CREATE USER Susan IDENTIFIED BY blue;

  5. CREATE NEW USER Susan IDENTIFIED by blue;

  6. CREATE OR REPLACE USER Susan IDENTIFIED BY blue;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct syntax for creating a user in Oracle is CREATE USER username IDENTIFIED BY password. Option D shows this exact syntax. Options A and B are incomplete (missing IDENTIFIED BY clause). Options C and E incorrectly use CREATE NEW USER which is not valid Oracle syntax. Option F incorrectly uses CREATE OR REPLACE USER which is not a valid command for users (that syntax applies to objects like views or procedures).

Multiple choice technology databases

Mark for review Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?

  1. SELECT &1, "&2"FROM &3 WHERE last_name = '&4';

  2. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';

  3. SELECT &1, &2 FROM &3 WHERE last_name = '&4';

  4. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In SQL*Plus, substitution variables are denoted by &n (like &1, &2, etc.) and allow user input at runtime. Option C correctly shows this syntax with &1, &2 for columns, &3 for table name, and &4 for the WHERE condition value. Option A incorrectly quotes &2. Option B has incorrect quoting and syntax errors. Option D hardcodes the table name as EMP instead of using a substitution variable.

Multiple choice technology databases

The user Sue issues this SQL statement: GRANT SELECT ON sue.EMP TO Alice WITH GRANT OPTION; The user Alice issues this SQL statement: GRANT SELECT ON sue.EMP TO Rena WITH GRANT OPTION; The user Rena issues this SQL statement: GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM Alice; For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

  1. Alice only

  2. Alice and Rena

  3. Alice, Rena, and Timber

  4. Sue, Alice, Rena, and Timber
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Revoking a privilege from a user who granted it using WITH GRANT OPTION results in a cascading revoke. Since Sue revokes from Alice, Alice's grant to Rena, and Rena's subsequent grant to Timber are all cascades that get revoked.

Multiple choice technology databases

What is true about group functions ?

  1. You can use group functions in any clause of a select statement

  2. You can use group functions only in the column list of the SELECT statement by grouping on the single row columns

  3. You can pass column names, expressions, constants or functions as parameters to a group function

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

Group functions, such as SUM or AVG, accept columns, expressions, constants, or other functions as arguments. They cannot be used in any clause (like the WHERE clause) and can group by multiple columns, making the other options incorrect descriptions of SQL group function behavior.

Multiple choice technology databases

Which SELECT statement will result "ello world" from the string "Hello World"?

  1. SELECT SUBSTR('Hello',) FROM dual;

  2. SELECT INITCAP(TRIM('Hello World',1,1)) FROM dual;

  3. SELECT LOWER(SUBSTR('Hello World',1,1)) FROM dual;

  4. SELECT LOWER(SUBSTR('Hello World',2,1)) FROM dual;

  5. SELECT LOWER(TRIM('H' FROM 'Hello World')) FROM dual;
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The TRIM function with 'H' FROM removes the first character H, then LOWER converts to lowercase. Option E correctly removes 'H' from the beginning and converts the result to lowercase, yielding 'ello world'. Other options either use incorrect functions or wrong parameters.

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

Indexes are database objects created specifically to accelerate data retrieval operations. They work like book indexes, allowing the database engine to locate rows quickly without scanning entire tables. While indexes do require additional storage and can slow down INSERT/UPDATE/DELETE operations, their primary and sole purpose is query performance optimization.

Multiple choice technology databases

For which two constraints,does the oracle server implicitly creates an index ?

  1. NOT NULL

  2. PRIMARY KEY

  3. FOREIGN KEY

  4. CHECK

  5. UNIQUE
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

Oracle automatically creates a unique index when you define a PRIMARY KEY or UNIQUE constraint to enforce uniqueness. This index supports quick lookups to check for duplicate values before insert/update operations. NOT NULL and CHECK constraints are validated through column value inspection, while FOREIGN KEY constraints are enforced through referential integrity checks - none of these require automatic indexes.

Multiple choice technology databases

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 2

  3. 3

  4. 4

  5. 5

  6. There is no such criteria
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To join n tables without Cartesian products, you need at least (n-1) join conditions in the WHERE clause (or using ANSI JOIN syntax). For 4 tables, this means 3 conditions linking them in a chain (e.g., T1 join T2, T2 join T3, T3 join T4). Without adequate join conditions, the database creates a Cartesian product, resulting in every possible row combination (multiplying all row counts).

Multiple choice technology databases

Which of the following Sql statements is correct?

  1. TRUNCATE * from TABLE Sales

  2. TRUNCATE TABLE Sales

  3. TRUNCATE Sales TABLE

  4. TRUNCATE * FROM Sales
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The TRUNCATE TABLE statement is the standard SQL command used to delete all rows from a table efficiently. Syntax variations containing asterisks or placing the table keyword after the table name are incorrect syntax errors in standard database systems, making the other options wrong.

Multiple choice technology databases

What is true about Sequences?

  1. Once created a sequence belongs to a specific schema.

  2. Once created, a sequence is linked to a specified table.

  3. Once created a sequence is automatically available to all users.

  4. Only the DBA can control which sequence is used by a certain table.

  5. Once created a sequence is automatically used in all INSERT and UPDATE statements.
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Sequences in Oracle are independent schema objects (not tied to specific tables) that generate unique numeric values. Once created in a schema with appropriate permissions, sequences can be referenced and used by multiple users, tables, and applications through explicit calls (sequence.NEXTVAL, sequence.CURRVAL). They are not automatically applied to any operations.