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

SELECT department, COUNT(1) FROM employees WHERE salary > 25000 GROUP BY department; What about this query :-) ?

  1. True

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

This is a valid and well-structured SQL query. It filters employees by salary (>25000), groups by department, and counts employees in each department using COUNT(1). The syntax is correct across all major SQL databases. The emoticon ':-)' suggests the question is asking if the query is valid.

Multiple choice technology programming languages

What are the check tables and value tables?

  1. Check table will be at field level checking.

  2. Value table will be at domain level checking

  3. Value table will be at field level checking

  4. Check table will be at domain level checking.
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

In SAP data dictionary, check tables provide field-level validation (values checked against foreign key table), while value tables provide domain-level validation (defining allowed values for any field using that domain). This two-level structure enables flexible data integrity checking.

Multiple choice technology databases

Which of the following types of statements can use a temporary tablespace?

  1. An index creation

  2. SQL statements with a GROUP BY clause

  3. A hash join operation

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

Temporary tablespaces store temporary data for operations like sorting, hashing, and index builds. All three listed operations (index creation, GROUP BY, and hash joins) generate intermediate results that may require temporary storage.

Multiple choice technology databases

Which of the following statements gives user desmond the ability to alter table gl.accounts?

  1. grant alter on gl.accounts to desmond;

  2. grant alter to desmond on gl.accounts;

  3. grant alter table to desmond;

  4. allow desmond to alter table gl.accounts;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The standard SQL syntax for granting object privileges is GRANT ON TO ;. Option 539220 correctly follows this structure. Other options either misplace the 'ON' clause, use incorrect 'ALTER TABLE' syntax for a grant, or use non-standard 'ALLOW' keywords.

Multiple choice technology databases

Which of the following statements gives user desmond the ability to alter table gl.accounts as well as give this ability to other accounts?

  1. grant alter any table with grant option to desmond;

  2. grant alter on gl.accounts to desmond with admin option;

  3. grant alter any table to desmond with grant option;

  4. grant alter any table to desmond with admin option;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ALTER ANY TABLE is a system privilege that requires WITH ADMIN OPTION to grant it to others. WITH GRANT OPTION only applies to object privileges, not system privileges. ALTER ON gl.accounts is an object privilege that doesn't meet the 'any table' requirement.

Multiple choice technology databases

The following SQL statement will allow user regina to perform which operations on sequence oe.orders_seq? GRANT ALL ON oe.orders_seq TO regina;

  1. Select the next value from oe.orders_seq.

  2. Alter sequence oe.orders_seq to change the next value.

  3. Change the number of sequence numbers that will be cached in memory.

  4. Both A and C.

  5. All of the above.
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

GRANT ALL on a sequence includes SELECT and ALTER privileges. SELECT allows fetching the next value, and ALTER allows changing sequence attributes like the cache size or increment value. Option D (Both A and C) is the most accurate description of these capabilities.

Multiple choice technology databases

User system granted SELECT ANY TABLE to user A using WITH ADMIN OPTION. A then granted SELECT ANY TABLE to user B. Ian A left the company, and his account is dropped. What happens to B's privileges?

  1. B loses his privileges.

  2. B retains his privileges.

  3. B loses his privileges if A was dropped with the CASCADE REVOKE option.

  4. B retains his privileges if A was dropped with the NOCASCADE REVOKE option.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SELECT ANY TABLE is a system privilege granted with WITH ADMIN OPTION. System privileges granted by one user are NOT automatically revoked when that user is dropped. B keeps the privilege because it was granted directly from the system, not dependent on A's account.

Multiple choice technology databases

Which of the following statements enables the role user_admin in the current session?

  1. alter session enable role user_admin;

  2. alter session set role user_admin;

  3. alter role user_admin enable;

  4. set role user_admin;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In Oracle SQL, the 'SET ROLE' statement is used to enable or disable roles for the current session. 'ALTER SESSION' is not used for enabling roles; the correct syntax is simply 'SET ROLE role_name;'.

Multiple choice technology databases

Which of the following SQL statements allows user augustin to use the privileges associated with the password-protected role info_czar, which has been granted to him?

  1. set role all;

  2. alter user augustin default role all;

  3. alter session enable role info_czar;

  4. alter session enable info_czar identified by brozo
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ALTER USER DEFAULT ROLE ALL enables all granted roles (including password-protected ones) automatically at login without requiring SET ROLE. Option A is invalid syntax, C is not valid syntax, and D is obsolete.

Multiple choice technology databases

You suspect unauthorized data manipulation language (DML) operations on a particular table. You want to track users who performing the transactions and the values used in the transactions. Also you plan to transfer these values to another table for analysis. How would you achieve this?

  1. By auditing the all DML operations on table.

  2. By using external table.

  3. By using triggers.

  4. By using anonymous PL/SQL blocks
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Triggers are database objects that automatically execute in response to DML events (INSERT, UPDATE, DELETE). They can track who performed the operation, capture the values involved, and even transfer those values to another table - meeting all requirements in the question. Auditing only tracks that operations occurred but doesn't capture values. External tables are for accessing external data files, not capturing DML activity.

Multiple choice technology mainframe

How many Indexes can be built on a table ?

  1. No limit

  2. 4

  3. 15

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

In theory, there is no hard limit to the number of indexes that can be built on a table. While practical limitations exist based on specific database systems and performance considerations, the conceptual answer is that there is no limit.

Multiple choice technology programming languages

Which statement is true for primary key and a unique key?

  1. Both are same.

  2. A table can have multiple primary key and unique key.

  3. Primary keys does not allow null values, but unique key allows only one null value.

  4. Both does not allow null values
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Primary keys uniquely identify rows and never allow NULL values. Unique keys also enforce uniqueness but can allow one NULL value (in most databases). A table can have only one primary key but multiple unique keys.

Multiple choice technology programming languages

What is true for DELETE & TRUNCATE commands?

  1. Truncate command is same as delete command with no where clause.

  2. Data cannot be rolled back when Truncate command is used, while data can be rolled back when delete command used.

  3. Data cannot be rolled back when Delete command is used, while data can be rolled back when truncate command used.

  4. Both are DML commands.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

TRUNCATE is a DDL operation that deallocates data pages and cannot be rolled back (even within a transaction). DELETE is a DML operation that logs individual row deletions and can be rolled back. TRUNCATE is also faster for large tables.

Multiple choice technology programming languages

Consider the following query: SELECT firstname, lastname, SUM(salary) FROM employees GROUP BY firstname, lastname HAVING SUM(salary) > 20000 ORDER BY lastname ASC Which part of the sample code above contains the SQL default and is therefore NOT necessary in the SQL statement?

  1. SUM

  2. >

  3. Group by

  4. ASC
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SQL ORDER BY clauses, ASC (ascending) is the default sort direction. Explicitly writing ASC is optional because the same result occurs without it. DESC must be specified explicitly when descending order is needed.