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

What will be the output of the query SELECT column FROM table ORDER BY column DESC LIMIT 7,10;

  1. Would skip the first 7, and then get you the next ten highest.

  2. not a valid mysql query

  3. Would skip the first 10, and then get you the next 7 highest.

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

The LIMIT clause with two parameters (LIMIT 7,10) means skip the first 7 rows (offset) and return the next 10 rows. Combined with ORDER BY column DESC, this skips the 7 highest values and returns the next 10 highest values.

Multiple choice technology databases

What will be the output of the query SELECT column FROM table ORDER BY column DESC LIMIT 7,10;

  1. Would skip the first 7, and then get you the next ten highest.

  2. not a valid mysql query

  3. Would skip the first 10, and then get you the next 7 highest.

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

The LIMIT clause with two parameters (LIMIT 7,10) means skip the first 7 rows (offset) and return the next 10 rows. Combined with ORDER BY column DESC, this skips the 7 highest values and returns the next 10 highest values.

Multiple choice technology databases

Which Oracle access method 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

Table access by ROWID is the fastest method because ROWID is the physical address of the row in the database. Accessing via ROWID requires a single direct I/O operation to locate the row. Primary key access and unique index access require traversing the index structure first, then accessing the table. Full table scan is the slowest as it reads every row.

Multiple choice technology databases

Which of the following can be a valid column name?

  1. Column

  2. 1966_Invoices

  3. Catch_#22

  4. #Invoices
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Oracle column names must start with a letter, can contain letters, numbers, and underscore (), but cannot start with a number. 'Catch#22' is valid because it starts with a letter and contains a hash symbol (#) which is allowed in column names. '1966_Invoices' starts with a number so it's invalid. 'Column' is a reserved keyword. '#Invoices' starts with a symbol.

Multiple choice technology databases

Which command will delete all data from a table and will not write to the rollback segment?

  1. DROP

  2. DELETE

  3. CASCADE

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

TRUNCATE is a DDL command that removes all rows from a table without generating rollback data, making it faster than DELETE. DELETE is a DML command that writes to rollback segments for potential recovery. DROP removes the entire table structure, not just data. CASCADE is a constraint option, not a data removal command.

Multiple choice technology databases

Which character is used to continue a statement in SQL*Plus?

  1. *

  2. /

  3. -

  4. @
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In SQL*Plus, the hyphen (-) is used as a continuation character to break long SQL statements across multiple lines. When placed at the end of a line, it indicates the statement continues on the next line. The other symbols have different purposes: * for wildcards, / for executing previous commands, @ for running scripts.

Multiple choice technology databases

Which of the following events can be used to fire triggers?

  1. sysdate

  2. Truncate

  3. avg

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

TRUNCATE is a DDL (Data Definition Language) operation that can fire triggers in database systems. Sysdate is a function, avg is an aggregate function, and to_date is a conversion function - none of these are events that fire triggers. Only DDL/DML operations like TRUNCATE can trigger database triggers.

Multiple choice technology databases

Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?

  1. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE '\';

  2. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;

  3. SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';

  4. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE '';
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To search for the literal pattern 'A_B' (where underscore is a literal character, not a wildcard), use LIKE '%A_B%' ESCAPE '\'. The ESCAPE clause specifies that backslash is the escape character, so _ treats underscore as a literal. Option A uses double backslash incorrectly. Options B and C have wrong ESCAPE syntax - missing escape character or using % incorrectly.

Multiple choice technology databases

To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write

  1. any outer join

  2. a left outer join

  3. a cross join

  4. a right outer 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). This is the opposite of a left outer join, which returns all rows from A. Cross joins produce Cartesian products and are not outer joins.

Multiple choice technology databases

You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?

  1. SELECT TO_DATE (SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;

  2. SELECT TO_CHAR (SYSDATE, 'FMDD, DY Month, YYYY') FROM dual;

  3. SELECT TO_CHAR (SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;

  4. SELECT TO_CHAR (SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

TO_CHAR converts a date to a string. 'FM' (Fill Mode) removes leading zeros or trailing blanks. 'Day' provides the full name of the day, 'DD' the day of the month, and 'Month' the full month name. Option 512993 matches the requested format.

Multiple choice technology databases

Which two statements complete a transaction? (Choose two.)

  1. DELETE employees;

  2. DESCRIBE employees;

  3. ROLLBACK TO SAVEPOINT C;

  4. ALTER TABLE employees SET UNUSED COLUMN sal;

  5. GRANT SELECT ON employees TO SCOTT;
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

In Oracle, DDL statements like ALTER TABLE and DCL statements like GRANT issue an implicit COMMIT, thereby completing the current transaction. ROLLBACK TO SAVEPOINT does not end a transaction; it only reverts to a specific point within it.

Multiple choice technology databases

Which object privileges can be granted on a view?

  1. none

  2. DELETE, INSERT, SELECT

  3. ALTER, DELETE, INSERT, SELECT

  4. DELETE, INSERT, SELECT, UPDATE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Views support DML object privileges: DELETE, INSERT, SELECT, and UPDATE. These allow users to manipulate data through the view (subject to view constraints). ALTER is a DDL privilege used for modifying object structure, not an object privilege granted on views.