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

The STUDENT_GRADES table has the following columns:

STUDENT_ID NUMBER (12)

SEMESTER_END DATE

GPA NUMBER (4, 3)

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The correct query uses GROUP BY semester_end to group records by semester, then MAX(gpa) finds the highest GPA within each group. The WHERE clause filters out NULL values before grouping. Option A lacks GROUP BY and returns only one overall maximum. Option B has incorrect syntax (WHERE after GROUP BY). Options D and E have incorrect clause ordering (GROUP BY and WHERE positions are swapped).

Multiple choice

Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?

  1. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

  2. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

  3. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

  4. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

Multiple choice

Which of the following statements is true when considering the number of indexes to be created on a table?

  1. Every column that is updated requires an index.

  2. Every column that is queried is a candidate for an index.

  3. Columns that are part of a WHERE clause are candidates for an index.

  4. On a table used in a Data Warehouse application there should be no indexes.

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

Columns appearing in WHERE clauses are prime candidates for indexing because indexes speed up data retrieval. Not every queried column needs an index (indexes have overhead), and not every updated column should be indexed (indexes slow down updates). Data warehouses do use indexes, just different types.

Multiple choice

Examine the following statement:

SELECT empno_seq.CURRVAL FROM SYS.dual;

Which of the following values is displayed?

  1. Current value of the EMPNO_SEQ sequence

  2. Current value of the EMPNO_SEQ cursor

  3. Values of the EMPNO_SEQ column

  4. Current value of the EMPNO_SEQ index
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The EMPNO_SEQ.CURRVAL returns the current value of the sequence named EMPNO_SEQ. Sequences in Oracle are database objects that generate unique numeric values. CURRVAL specifically returns the last value obtained from that sequence in the current session.

Multiple choice

Assuming today is Monday, 10 July 2000, what is returned by the given statement? SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual;

  1. 03-JUL-00

  2. 10-JUL-00

  3. 12-JUL-00

  4. 11-JUL-00

  5. 17-JUL-00

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

The NEXT_DAY function returns the first occurrence of a specified day that is strictly later than the input date. Since today is Monday July 10, the NEXT Monday is July 17 (7 days later). The TO_CHAR format 'DD-MON-RR' produces '17-JUL-00'. Note that NEXT_DAY always looks forward, never returning the current day even if it matches the specified weekday.

Multiple choice

How many values can a subquery use with the <> operator return?

  1. Only 1

  2. Up to 2

  3. Up to 10

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

The <> (not equal) operator is a single-row comparison operator in SQL, meaning the subquery must return exactly one value. When a subquery returns multiple rows with comparison operators like =, <>, >, <, Oracle raises error ORA-01427 'single-row subquery returns more than one row'. For multiple values, you must use IN, ANY, or ALL operators instead.

Multiple choice

Examine the code given below:

SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to this 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 selects employees with either a 50% commission rate (commission_pct = 0.5) OR a salary exceeding 23,000. The OR condition means employees meeting EITHER criterion are returned. Option A incorrectly interprets it as a percentage calculation, and Option C misreads the comparison direction. This is a straightforward OR condition combining two independent filters.

Multiple choice

Which of the following Oracle access methods is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan

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

ROWID access is the fastest retrieval method because ROWID is the direct physical address of a row in the data file. When Oracle knows the ROWID, it can access the row in a single I/O operation without traversing indexes or scanning tables. Primary key and unique index lookups require index traversal first, then ROWID access. Full table scans read all blocks, which is the slowest method.

Multiple choice

Which of the following types of joins should be written to perform an outer join of tables A and B that returns all rows from B?

  1. Any outer join

  2. A left outer join

  3. A cross join

  4. A right outer join

  5. An inner join
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A right outer join returns all rows from the right table (B) and matching rows from the left table (A). Left outer join returns all from A, not B. Inner join returns only matching rows from both. Cross join creates a Cartesian product.

Multiple choice

How to create a table space?

  1. With alter database command

  2. With alter system command

  3. Using create command

  4. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Tablespaces are created using the CREATE TABLESPACE command in SQL. ALTER DATABASE and ALTER SYSTEM modify existing structures. The 'create' command is the standard DDL statement for creating new database objects like tablespaces.

Multiple choice

Which of the following SELECT statements should be used to extract the year from the system date to display it in the format “2001”?

  1. SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual;

  2. SELECT TO_DATE(SYSDATE, 'yyyy') FROM dual;

  3. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

  4. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

TO_CHAR converts a date value to a string with the specified format mask. The 'yyyy' format element extracts the 4-digit year from SYSDATE. TO_DATE is incorrect because it converts a string to a date, not the reverse. DECODE is not relevant for date formatting.

Multiple choice

How can whole record be retrieved in PL/SQL without using any loop or cursor?

  1. Not possible

  2. Bulk collect

  3. Procedure

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

BULK COLLECT retrieves entire result sets into collections in a single operation, eliminating the need for explicit loops or cursor fetches. This is a performance optimization that reduces context switching between the PL/SQL and SQL engines. Procedures and functions are program units, not data retrieval mechanisms.