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

SQL*Plus will finish the statement and execute it when the user types in this:

  1. A left slash ( ) followed by [Enter].

  2. A colon ( : ) followed by [Enter].

  3. A semicolon ( ; ) followed by [Enter].

  4. A period ( . ) followed by [Enter].
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In SQL*Plus, a semicolon followed by the Enter key signals that the SQL statement is complete and ready for execution. The semicolon acts as a statement terminator in SQL*Plus. Option A is incorrect - a forward slash (/) executes statements in the buffer but doesn't terminate statement entry. Option B's colon is used for bind variables, not statement termination. Option D's period has no special meaning in SQL*Plus statement termination.

Multiple choice

After a table has been created, its structure can be modified using the SQL command:

  1. UPDATE TABLE [TableName].

  2. MODIFY TABLE [TableName].

  3. ALTER TABLE [TableName].

  4. CHANGE TABLE [TableName].
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ALTER TABLE is the standard SQL command for modifying the structure of an existing table. This includes adding, dropping, or modifying columns, constraints, and other table properties. UPDATE TABLE is used for modifying data within a table, not its structure. MODIFY TABLE and CHANGE TABLE are not valid SQL commands in any major database system.

Multiple choice

When using SQL*Plus, Oracle commands, column names, table names and all other database elements:

  1. are case insensitive.

  2. are case sensitive.

  3. must always be in lower case.

  4. must always be in upper case.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQL*Plus and Oracle database identifiers are case-insensitive by default. This means 'SELECT', 'select', and 'Select' are treated identically, and table/column names like 'Employee' and 'employee' refer to the same object. This case-insensitivity applies unless quoted identifiers are used, in which case they become case-sensitive. The default behavior makes SQL*Plus more user-friendly and less prone to typing errors.

Multiple choice

SQL*Plus will finish the statement and execute it when the user types _______.

  1. a left slash ( ) followed by [Enter]

  2. a colon ( : ) followed by [Enter]

  3. a semicolon ( ; ) followed by [Enter]

  4. a period ( . ) followed by [Enter]
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The semicolon is the standard SQL statement terminator in SQL*Plus. When you type a semicolon and press Enter, SQL*Plus recognizes the statement as complete and executes it. The forward slash (/) is used to execute the contents of the SQL buffer, not terminate a new statement. The colon (:) is used for bind variables in SQL*Plus. The period (.) is used in some SQL implementations but not in Oracle SQL*Plus for statement termination.

Multiple choice

What will happen if the given emp table is queried?

select enpno,DISTINCT ename,Salary from emp;

  1. EMPNO ,unique value of ENAME and then SALARY are displayed.

  2. EMPNO ,unique value of the two columns, ENAME and salary are displayed.

  3. DISTINCT is not a valid keyword in SQL.

  4. No values will be displayed because the statement will return an error.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The query will return an error because DISTINCT cannot be used for individual columns in a SELECT statement - it must apply to all selected columns or the entire row. The syntax 'select enpno, DISTINCT ename, Salary' is invalid because DISTINCT must come before the column list and apply to the combination of all columns, not just one column.

Multiple choice

Order entry clerks use the Orders form to enter the shipping date of an order. Once the shipping date has been entered into the database, the clerks are not allowed to modify it. What property can be set in the Property Palette for the Shipping_Date item to ensure that this data entry restriction is enforced?

  1. Enabled

  2. Database Item

  3. Update Allowed

  4. Update Only If NULL
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The 'Update Only If NULL' property ensures that a field can only be updated when it has no value (is NULL). Once a value is entered, it cannot be modified. This is perfect for the shipping date scenario - clerks can enter the date initially, but once saved, it becomes read-only. 'Enabled' controls whether the item can receive focus, 'Database Item' indicates if it's stored in the database, and 'Update Allowed' is a binary on/off setting.

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

Tom is developing a form for customers to order tickets to events. There is an Event_Date item in the Ticket block of the form, and he wants users to enter dates only for existing events. He used the LOV wizard to create an LOV based on a record group with the following query:

SELECT event_name, event_date FROM events ORDER BY event_nameOn the Column Properties page of the LOV (as shown in the exhibit), he clicked Look up return item and select the Tickets. Event_Date item from the items list. He runs the form to test it. However, when he invoked the LOV and chose an event, he received the error FRM-40212:

Invalid value for field EVENT_DATE.'

What should he do to correct this problem?

  1. Change the Validate from List property for the Event_Date item to No

  2. Create another item in the form to which the event name can be returned

  3. Modify the Record Group Query property for the record group to sort the list by event date

  4. Modify the Column Mapping property of the LOV so that the Event Date, rather than the Event Name is returned

  5. Modify the Record Group Query property for the record group so that the event date is the first column selected
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The error occurs because the LOV is returning the wrong column. The record group query selects event_name first and event_date second, but the Column Mapping is configured to return from the first column (event_name) to the Event_Date item. The solution is to modify the Column Mapping property so that the second column (event_date) is returned instead. Changing the query order or creating another item would not solve the fundamental mapping issue.

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.