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

Which of the following SQL SELECT statement clauses is an example of the selection capability, but not the joining capability?

  1. WHERE d.deptno = 10

  2. SELECT *

  3. WHERE d.deptno = e.deptno

  4. SELECT id_number, description, cost
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Selection refers to filtering rows based on conditions. WHERE d.deptno = 10 selects only rows where deptno equals 10 - this is pure selection. WHERE d.deptno = e.deptno is joining (relating two tables). SELECT * and SELECT id_number... are projection operations (choosing columns), not selection.

Multiple choice

Which of the following constructs can be used to return data based on an unknown condition?

  1. An ORDER BY clause

  2. An GROUP BY clause

  3. A WHERE clause with an OR condition

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

Subqueries allow dynamic query execution where the exact condition may not be known at design time - the inner query's results determine the outer query's behavior. ORDER BY and GROUP BY are for sorting and aggregation, not conditional logic. WHERE with OR only handles known conditions.

Multiple choice

What will the PARTITION BY clause in a partitioned outer join statement do?

  1. Partition the output rows into equal segments

  2. Partition the table into equal subpartitions

  3. Partition the rows in the output based on the expression provided within the clause

  4. Partition the underlying table, using Oracle's partitioning option
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

PARTITION BY in a partitioned outer join divides the result set rows based on the specified expression, creating distinct partitions for the join operation. It does not partition the underlying table or create equal segments - it's about organizing the output for the join, not physical data distribution.

Multiple choice

Examine the given code:SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to the given code?
  1. It returns employees who have 50% of the salary greater than $23,000:
  2. It returns employees who have 50% commission rate or salary greater than $23,000:
  3. It returns employees who have 50% of salary less than $23,000:

  4. None of the above

Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The query uses OR logic - it returns employees where EITHER the commission_pct equals 0.5 (50%) OR salary exceeds 23000. The parentheses in the description matter - it's not '50% OF salary greater than 23000' (which would be mathematically different), but two separate conditions joined by OR.

Multiple choice

Which of the following privileges can only be granted to a user and NOT to a role?

  1. DELETE

  2. ALTER

  3. INSERT

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

The REFERENCES privilege (and the corresponding REFERENCES object privilege) is unique in Oracle - it can only be granted to specific users, not to roles. This security restriction prevents roles from being used as a backdoor to grant foreign key constraints. All other object privileges like SELECT, INSERT, UPDATE, DELETE, ALTER, and EXECUTE can be granted to either users or roles.

Multiple choice

For which constraints are indexes created when the constraint is added?

  1. Check

  2. Unique

  3. Not null

  4. Primary key

  5. Foreign key
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Oracle automatically creates indexes to enforce UNIQUE and PRIMARY KEY constraints. These indexes support the constraint's requirement that values be distinct across rows (or non-null and distinct for PK). CHECK, NOT NULL, and FOREIGN KEY constraints do not automatically create indexes - though you can and often should manually create indexes on foreign key columns for join performance.

Multiple choice

Which of the following SQL statements accepts user input for the columns to be displayed, table name, and the WHERE condition?

  1. SELECT &1, &2FROM &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
D Correct answer
Explanation

Option D correctly uses substitution variables: &1 for first column, '&2' as a literal string (single quotes preserved), EMP as hardcoded table, and &4 for the WHERE condition value. Options A and C incorrectly try to substitute the table name. Option B has malformed quoting with incorrect quote placement that would cause a syntax error.

Multiple choice

An index needs to be created on the EMP_ID column of the EMPLOYEE table which satisfies the following conditions:

  1. The index will be called EMP_PK.
  2. The index should be sorted in ascending order.
  3. The index should be created in the INDEX01 tablespace, which is a dictionary managed tablespace.
  4. All extents of the index should be 1 MB in size.
  5. The index should be unique.
  6. No redo information should be generated when the index is created.
  7. 20% of each data block should be left free for future index entries.

Which of the following commands create the index and meets all the requirements?

  1. CREATE UNIQUE INDEX emp_pk ON Employee(Emp_id) TABLESPACE index0l PCTFREE 20 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);

  2. CREATE UNIQUE INDEX emp_pk ON employee(emp_id) TABLESPACE index0l PCTFREE 20 STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0) NOLOGGING;

  3. CREATE UNIQUE INDEX emp_pk ON Employee(emp_id) TABLESPACE index0l PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0) NOLOGGING;

  4. CREATE UNIQUE INDEX emp_pk ON employee(emp_id) TABLESPACE index0l PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B meets all requirements: UNIQUE constraint ensures uniqueness, NOLOGGING suppresses redo generation, PCTFREE 20 reserves 20% of each block, STORAGE clause sets 1M extents, and the index is created in INDEX01 tablespace. Option A uses 'lm' instead of '1m' (typos), C incorrectly uses PCTUSED, and D lacks NOLOGGING.

Multiple choice

A SQL SELECT statement containing the ROWNUM pseudocolumn, an ORDER BY clause, and a subquery was created. What construct was created?

  1. A Complex View

  2. A Top-n Query

  3. A Materialized View

  4. A Hierarchical Report
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A Top-n query uses ROWNUM with ORDER BY in a subquery to limit results to the top n rows after sorting. The construct involves nesting a query with ORDER BY inside another that filters on ROWNUM to get the first n records. This is different from complex views, materialized views, or hierarchical reports.

Multiple choice

When is the use of fine-grained auditing beneficial?

  1. Unaudited access should be allowed to the CONTACT_ ADDRESS column of the CUSTOMER table from within the organization, but any access to the CONTACT_ADDRESS column occurring via the Internet should be tracked

  2. The Human Resources Administrator should be alerted every time someone accesses an executive's salary in the EMPLOYEE table

  3. Comparison between the before and after values of each update of the PRICE column of the PRODUCT table should be facilitated

  4. All users who updated the CUSTOMER table on a specific date should be identifiable

  5. All accesses of the EMPLOYEE table should be tracked
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Fine-grained auditing allows conditional audit policies based on specific column access or access conditions. Option A correctly uses FGA to track internet-based access to sensitive customer address data while allowing internal access. Option B demonstrates FGA's alert capability for specific high-value columns like executive salaries. Option C describes standard value-based auditing using triggers, not FGA. Option D and E describe standard table-level auditing which doesn't require FGA's granularity.

Multiple choice

What operator is used to prevent the error: 'ORA-01427:single row sub query returns more than one row'?

  1. Use the IN operator

  2. Use the >= operator

  3. Use the CAN EXIST operator

  4. Use the = operator
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The ORA-01427 error occurs when a subquery that should return only one row returns multiple rows. The IN operator is designed to handle multiple values and eliminates this error by checking if a value exists in the result set rather than requiring equality with a single row.

Multiple choice

The DBA issues the following command: SQL< alter table ORDER SHRINK SPACE What be the result of this statement?

  1. There would be only a compacting of the data, not a release of data

  2. There would be no impact on DML operations in the database

  3. There would be immediate release of the free space

  4. There would be an impact on the DML operations in the database
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The SHRINK SPACE operation compacts data and can release free space, but it requires exclusive locks on segments. During this operation, DML (Data Manipulation Language) operations like INSERT, UPDATE, DELETE are blocked, causing an impact on database operations.

Multiple choice

How many triggers can be applied to a single table in Oracle?

  1. 10

  2. 12

  3. 15

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

Oracle allows 12 triggers per table: one BEFORE and one AFTER for each of the six operations (INSERT, UPDATE, DELETE, plus their row-level variants INSERT ROW, UPDATE ROW, DELETE ROW). This gives maximum flexibility for enforcing business rules at different execution points.

Multiple choice

Which SELECT statement is an equijoin query between two tables?

  1. SELECT location.location_name, employee.salary FROM location, employee WHERE location.id = employee.location_no;

  2. SELECT location.location_name, employee.salary FROM location, employee WHERE employee.salary BETWEEN location.avg_salary AND location.max_salary;

  3. SELECT location.location_name, employee info.last_name FROM employee location, employee employee info WHERE employee info.id >= location.manager_id;

  4. SELECT location.location_name, employee.salary FROM location, employee WHERE location.id = employee.region_no(+);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

An equijoin is a join based on equality between columns from two tables. Option A shows the classic equijoin syntax using a WHERE clause with the equality operator (=) to match location.id with employee.location_no. Option B uses a BETWEEN range condition, not equality. Option C uses >= which is a non-equal comparison. Option D uses the outer join operator (+), which creates an outer join, not an equijoin.