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

The OLD and NEW Qualifier is used in which type of trigger?

  1. Row level DML trigger

  2. Statement level DML trigger

  3. System trigger

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

OLD and NEW qualifiers reference row values before and after the triggering DML operation. They are only available in row-level DML triggers because statement-level triggers have no specific row context. System and application triggers don't use row qualifiers.

Multiple choice technology databases

The command to disable all the triggers on the EMP table

  1. ALTER TRIGGERS ON TABLE EMP DISABLE

  2. ALTER EMP DISABLE ALL TRIGGERS

  3. NOT POSSIBLE TO DISABLE TRIGGERS

  4. ALTER TABLE EMP DISABLE ALL TRIGGERS
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle uses ALTER TABLE table_name DISABLE ALL TRIGGERS to disable all triggers on a table. The other options are not valid Oracle syntax.

Multiple choice technology databases

How many columns are presented after executing this query: SELECT address1||','||address2||','||address2 "Adress" FROM employee;

  1. 1

  2. 2

  3. 3

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

The query uses concatenation (||) to combine address1, a comma, address2, another comma, and address2 again into a single column named 'Adress'. Despite concatenating multiple values, the result is presented as ONE column with the alias 'Adress'. The duplicate use of address2 appears intentional or typographical but doesn't change column count.

Multiple choice technology databases

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Full table scan

  2. Table access by ROWID

  3. Access via unique index

  4. Primary key access
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ROWID is Oracle's physical row address. Access by ROWID is the fastest single-row retrieval method because it's a direct physical location lookup. Unique index requires index traversal first, full table scan reads all rows, and primary key access typically uses an index.

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, cannot start with a number or special character (except underscore, $, # after the first character). 1966_Invoices starts with a number, #Invoices starts with #, Column is a reserved word. Catch_#22 is valid.

Multiple choice technology databases

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

  1. drop

  2. truncate

  3. delete

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

TRUNCATE removes all rows from a table and does so as a DDL operation that does not generate individual row delete logs, meaning it does not write to the rollback segment. DROP removes the table definition, and DELETE logs each row, while CASCADE is not a standalone command for this purpose.

Multiple choice technology databases

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

  1. select * from EMP where nvl(EMPNO, '00000') = '59384';

  2. select 1 from EMP where EMPNO = '59834';

  3. select EMPNO, LASTNAME from EMP where EMPNO = '59384'

  4. select * from EMP where EMPNO = '59384';
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To answer this question, the user needs to understand what a primary key and an index are, and how they are related.

A primary key is a column or set of columns that uniquely identifies each row in a table. An index is a data structure that stores a copy of selected columns of a table to improve the speed of data retrieval operations on that table.

Now, let's go through each option and determine whether or not it will use the associated index on EMPNO:

A. select * from EMP where nvl(EMPNO, '00000') = '59384'; This statement will not use the associated index on EMPNO because the NVL function applied to EMPNO will turn an indexed column into an unindexed one. Using functions on indexed columns can result in the optimizer not choosing to use the index.

B. select 1 from EMP where EMPNO = '59834'; This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

C. select EMPNO, LASTNAME from EMP where EMPNO = '59384' This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

D. select * from EMP where EMPNO = '59384'; This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

Therefore, the statement that will not use the associated index on EMPNO is option A.

The Answer is: A

Multiple choice technology databases

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

  1. 03-JUL-00

  2. 10-JUL-00

  3. 11-JUL-00

  4. 17-JUL-00
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

NEXT_DAY(sysdate, 'MONDAY') returns the next occurrence of Monday after the current date. Since today is Monday July 10, 2000, the next Monday is July 17, 2000. The to_char format 'DD-MON-RR' produces '17-JUL-00'. Options A and B show dates before or on the current day, while option C shows July 11 which is a Tuesday.

Multiple choice technology databases

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 2

  3. 3

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

To prevent a Cartesian product when joining N tables, you need at least N-1 join conditions. For a four-table join, this means a minimum of 3 conditions are required in the WHERE clause to properly link all relations.

Multiple choice technology web technology

ADO Record set can hold multiples tables data.

  1. True

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

False is correct because classic ADO Recordsets typically hold data from a single table or query result. A Recordset represents rows from one source. To work with multiple related tables, you would need hierarchical Recordsets (using parent-child relationships) or, more commonly in modern scenarios, a DataSet in ADO.NET which can contain multiple DataTables with relationships.

Multiple choice technology databases

A table named DEPARTMENT has the following columns: DEPT_ID DEPT_NAME MANAGER AVG_SALARY Which of the following is the best way to prevent most users from viewing AVG_SALARY data?

  1. Encrypt the table's data

  2. Create a view that does not contain the AVG_SALARY column

  3. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data

  4. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Creating a view that excludes the AVG_SALARY column is the best approach - it provides most users access to needed data while hiding sensitive salary information. Views are standard SQL mechanism for column-level access control. Encryption doesn't control access, column-level revoke is complex and error-prone, and a separate table adds unnecessary complexity.

Multiple choice technology databases

Assuming USER1 has no authorities or privileges, which of the following will allow USER1 to create a view named VIEW1 that references two tables named TAB1 and TAB2?

  1. CREATEIN privilege on the database

  2. REFERENCES privilege on TAB1 and TAB2

  3. CREATE_TAB privilege on the database

  4. SELECT privilege on TAB1 and TAB2
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Creating a view requires SELECT privileges on all underlying tables referenced by the view definition. The CREATE_TAB or CREATEIN privileges allow creating tables or objects in a schema, while the REFERENCES privilege is used for referential integrity constraints, not for retrieving data to build a view.

Multiple choice technology databases

After the following SQL statement is executed: GRANT ALL PRIVILEGES ON TABLE employee TO USER user1 Assuming user USER1 has no other authorities or privileges, which of the following actions is USER1 allowed to perform?

  1. Drop an index on the EMPLOYEE table

  2. Grant all privileges on the EMPLOYEE table to other users

  3. Alter the table definition

  4. Drop the EMPLOYEE table
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The GRANT ALL PRIVILEGES on table grants table-level privileges including SELECT, INSERT, UPDATE, DELETE, and ALTER. It does NOT grant CONTROL or schema-level privileges needed to drop indexes, tables, or grant privileges to others. Altering the table definition (ALTER privilege) is allowed. Dropping indexes/tables requires higher authority.